On Fri, Sep 26, 2003 at 02:50:50PM +0200, Hendrik Fu� wrote:
> Hi everyone,
>
> I've hacked together a few lines of perl and Inline C in order to bind
> variables of type XMLType to placeholders. I can currently insert XML
> data of less than 32k into an XMLType table or column, but I considered
> integrating the code into DBD::Oracle, so that it would be available to
> everyone.
Great.
> I'm still busy figuring out how to do that, but this raises some design
> questions as well:
>
> In contrast to BLOB/CLOB etc. XMLType is a named type (SQLT_NTY), not a
> primitive Oracle Type. So how should you specify the type name
> ("SYS.XMLTYPE"), or should I rather invent a new SQLT_XMLTYPE?
Why do you need to do either?
Fetching an SQLT_NTY value should (I imagine) return some kind of
'handle' (a blessed reference) which can be passed to other methods
to act on.
Take a look at how LOBs are handled when the ora_auto_lob attribute
is false:
http://homepage.eircom.net/~timbunce/DBD-Oracle-1.15-20030922beta.tar.gz
here's part of the docs:
$sth = $dbh->prepare( <<" SQL", { ora_auto_lob => 0 } );
SELECT bindata, chardata
FROM lob_example
WHERE lob_id = ?
FOR UPDATE
SQL
$sth->execute( $lob_id );
my ( $bin_locator, $char_locator ) = $sth->fetchrow_array();
$sth->finish();
open BIN_FH, "/binary/data/source" or die;
open CHAR_FH, "/character/data/source" or die;
my $chunk_size = 4096; # Arbitrary chunk size
# BEGIN WRITING BIN_DATA COLUMN
my $offset = 1; # Offsets start at 1, not 0
my $length = 0;
my $buffer = '';
while( $length = read( BIN_FH, $buffer, $chunk_size ) ) {
$dbh->ora_lob_write( $bin_locator, $offset, $length );
$offset += $length;
}
The $bin_locator is a blessed reference created using sv_setref_pv().
Here's what that's setup:
case 112: /* CLOB */
case 113: /* BLOB */
case 114: /* BFILE */
fbh->ftype = fbh->dbtype;
fbh->disize = fbh->dbsize *10 ;
fbh->fetch_func = (imp_sth->auto_lob)
? fetch_func_autolob : fetch_func_getrefpv;
fbh->bless = "OCILobLocatorPtr";
fbh->desc_t = OCI_DTYPE_LOB;
OCIDescriptorAlloc_ok(imp_sth->envhp, &fbh->desc_h, fbh->desc_t);
break;
and the fetch_func_getrefpv function, that's used when the ora_auto_lob
attribute is false is just:
static int
fetch_func_getrefpv(SV *sth, imp_fbh_t *fbh, SV *dest_sv)
{
/* See the Oracle::OCI module for how to actually use this! */
sv_setref_pv(dest_sv, fbh->bless, (void*)fbh->desc_h);
return 1;
}
Because sv_setref_pv() is used and the class name (fbh->bless =
"OCILobLocatorPtr") ends in Ptr, that means any XS code that uses
an OCILobLocator as a parameter can simply be declared as:
void
ora_lob_write(dbh, locator, offset, data)
SV *dbh
OCILobLocator *locator <== XS does the magic and validation
UV offset
SV *data
I've not looked at handling SQLT_NTY types but I hope the core of any
support would be very similar.
Perhaps you could outline the kind of API that XML SQLT_NTY types need.
Tim.
