Hi everyone,
It's been a while since I last posted here. In September 2003 I was
trying to add support for binding XMLType objects to DBD-Oracle, so that
you could easily insert large (ie >4k) XML data into an XMLType table.
Unfortunately my employer fired me in October 2003 and in the remaining
time I had to work on other projects, so I wasn't able to complete the
DBD project. Since I won't have access to an oracle database from now
on, I thought the least I can do is to provide my code as it is to the
list. Blame German economy. :-(
The attached patch (based on DBD-Oracle 1.15) enables you to upload
XMLType objects (OCIXMLTypePtr) by binding them as SQLT_NTY. In dbdimp.c
I added a function dbd_rebind_ph_nty for that purpose. You need to
create that XMLType object first using the C function
createxmlfromstring in xml.c.
The XMLType object is either created from an OCIString or from a
temporary CLOB depending on the length of the source string. Have a look
at the bottom of ociap.h, all the (undocumented) XMLType functions are
there, and there are also some constants in oci.h.
I'm not sure if the CLOB code currently works.
Here is another code fragment:
my $xml = createxml($dbh, '<test>Test document</test>');
my $sth = $dbh->prepare('INSERT INTO xml_type VALUES (?)');
$sth->bind_param(1, $xml, { ora_type => 108 }); # SQLT_NTY
$sth->execute();
Please note that this code is really just early development, which I
wouldn't publish under normal circumstances. :) I just hope it might me
useful to someone.
Well then, time to say goodbye
thanks to Tim and everyone who contributed to dbi for a great piece of
software
Cheers,
Hendrik
--
hendrik fu�
morphochem AG
gmunder str. 37-37a
81379 muenchen
tel. ++49-89-78005-0
fax ++49-89-78005-555
[EMAIL PROTECTED]
http://www.morphochem.de
151a152
> case 108: /* SQLT_NTY */
992a994,996
> case SQL_UDT:
> return 108; /* Oracle NTY */
>
1004a1009,1072
> static int
> dbd_rebind_ph_nty(sth, imp_sth, phs)
> SV* sth;
> imp_sth_t *imp_sth;
> phs_t *phs;
> {
> OCIType *tdo = NULL;
> sword status;
> SV* ptr;
>
> if (phs->is_inout)
> croak("OUT binding for NTY is currently unsupported");
>
> /* ensure that the value is a support named object type */
> /* (currently only OCIXMLType*) */
> if ( sv_isa(phs->sv, "OCIXMLTypePtr") ) {
> OCITypeByName(imp_sth->envhp, imp_sth->errhp, imp_sth->svchp,
> (CONST text*)"SYS", 3,
> (CONST text*)"XMLTYPE", 7,
> (CONST text*)0, 0,
> OCI_DURATION_CALLOUT, OCI_TYPEGET_HEADER,
> &tdo);
>
> ptr = SvRV(phs->sv);
> phs->progv = (void*) SvIV(ptr);
> phs->maxlen = sizeof(OCIXMLType*);
> }
> else
> croak("Unsupported named object type for bind parameter");
>
>
> /* bind by name */
>
> OCIBindByName_log_stat(imp_sth->stmhp, &phs->bndhp, imp_sth->errhp,
> (text*)phs->name, (sb4)strlen(phs->name),
> (dvoid *) NULL, /* value supplied in BindObject later */
> 0,
> (ub2)phs->ftype, 0,
> NULL,
> 0, 0,
> NULL,
> (ub4)OCI_DEFAULT,
> status
> );
>
> if (status != OCI_SUCCESS) {
> oci_error(sth, imp_sth->errhp, status, "OCIBindByName SQLT_NTY");
> return 0;
> }
> if (DBIS->debug >= 3)
> PerlIO_printf(DBILOGFP, " pp_rebind_ph_nty: END\n");
>
>
> /* bind the object */
>
> OCIBindObject(phs->bndhp, imp_sth->errhp,
> (CONST OCIType*)tdo,
> (dvoid **)&phs->progv,
> (ub4*)NULL,
> (dvoid **)NULL,
> (ub4*)NULL);
>
> return 2;
> }
1309a1378,1380
> case 108:
> done = dbd_rebind_ph_nty(sth, imp_sth, phs);
> break;
1331c1403
< int at_exec = (phs->desc_h == NULL);
---
> int at_exec = (phs->desc_h == NULL) && (phs->ftype != 108);
1419c1491
< if (SvROK(newvalue) && !IS_DBI_HANDLE(newvalue))
---
> if (SvROK(newvalue) && (!IS_DBI_HANDLE(newvalue)) && (sql_type!=SQLT_NTY))
1420a1493
> /* ref allowed for OCIXMLType* */
2219a2293
> case SQLT_NTY: sql_fbh.dbtype = SQL_UDT; break;
#include "oci.h"
#include <stdio.h>
/* This helper function creates an XMLType object from a string source.
*
* The resulting object can be bound to a placeholder, if ora_type =>
* SQLT_NTY is specified.
*/
static void checkerr(errhp, status)
OCIError *errhp;
sword status;
{
text errbuf[512];
ub4 buflen;
sb4 errcode;
switch (status)
{
case OCI_SUCCESS:
break;
case OCI_SUCCESS_WITH_INFO:
printf("Error - OCI_SUCCESS_WITH_INFO\n");
break;
case OCI_NEED_DATA:
printf("Error - OCI_NEED_DATA\n");
break;
case OCI_NO_DATA:
printf("Error - OCI_NO_DATA\n");
break;
case OCI_ERROR:
OCIErrorGet ((dvoid *) errhp, (ub4) 1, (text *) NULL, &errcode,
errbuf, (ub4) sizeof(errbuf), (ub4) OCI_HTYPE_ERROR);
printf("Error - %s\n", errbuf);
exit(1);
break;
case OCI_INVALID_HANDLE:
printf("Error - OCI_INVALID_HANDLE\n");
break;
case OCI_STILL_EXECUTING:
printf("Error - OCI_STILL_EXECUTE\n");
break;
case OCI_CONTINUE:
printf("Error - OCI_CONTINUE\n");
break;
default:
break;
}
}
#define MAX_OCISTRING_LEN 32766
SV* createxmlfromstring(SV* dbh, char* source) {
OCIXMLType *xml = NULL;
ub4 len;
ub1 src_type;
dvoid* src_ptr = NULL;
D_imp_dbh(dbh);
SV* sv_dest;
len = strlen(source);
if(len > MAX_OCISTRING_LEN) {
src_type = OCI_XMLTYPE_CREATE_CLOB;
printf("OCIDescriptorAlloc\n");
checkerr( imp_dbh->errhp,
OCIDescriptorAlloc((dvoid*)imp_dbh->envhp,
(dvoid **)&src_ptr,
(ub4)OCI_DTYPE_LOB,
(size_t)0,
(dvoid**)0) );
printf("OCILobCreateTemporary\n");
checkerr( imp_dbh->errhp,
OCILobCreateTemporary(imp_dbh->svchp,
imp_dbh->errhp,
(OCILobLocator*) src_ptr,
(ub2)0,
SQLCS_IMPLICIT,
OCI_TEMP_CLOB,
OCI_ATTR_NOCACHE,
OCI_DURATION_SESSION) );
printf("OCILobWrite\n");
checkerr (imp_dbh->errhp,
OCILobWriteAppend(imp_dbh->svchp,
imp_dbh->errhp,
(OCILobLocator*) src_ptr,
&len,
(ub1*)source,
len,
OCI_ONE_PIECE,
(dvoid *)0,
(sb4 (*)(dvoid*,dvoid*,ub4*,ub1 *))0,
0,
SQLCS_IMPLICIT));
} else {
src_type = OCI_XMLTYPE_CREATE_OCISTRING;
printf("OCIStringAssignText\n");
checkerr( imp_dbh->errhp,
OCIStringAssignText(imp_dbh->envhp,
imp_dbh->errhp,
(CONST text*) source,
(ub2) strlen(source),
(OCIString **) &src_ptr)
);
}
printf("OCIXMLTypeCreateFromSrc\n");
checkerr( imp_dbh->errhp,
OCIXMLTypeCreateFromSrc(imp_dbh->svchp,
imp_dbh->errhp,
(OCIDuration)OCI_DURATION_CALLOUT,
(ub1)src_type,
(dvoid *)src_ptr,
(sb4)OCI_IND_NOTNULL,
&xml)
);
/* free temporary resources */
if( src_type == OCI_XMLTYPE_CREATE_CLOB ) {
checkerr( imp_dbh->errhp,
OCILobFreeTemporary(imp_dbh->svchp, imp_dbh->errhp,
(OCILobLocator*) src_ptr) );
checkerr( imp_dbh->errhp,
OCIDescriptorFree((dvoid *) src_ptr, (ub4) OCI_DTYPE_LOB) );
}
sv_dest = newSViv(0);
sv_setref_pv(sv_dest, "OCIXMLTypePtr", xml);
return sv_dest;
}
