Author: byterock
Date: Mon Mar 10 13:33:51 2008
New Revision: 10901
Modified:
dbd-oracle/trunk/Changes
dbd-oracle/trunk/Oracle.h
dbd-oracle/trunk/Oracle.pm
dbd-oracle/trunk/dbdimp.c
dbd-oracle/trunk/oci.def
dbd-oracle/trunk/ocitrace.h
Log:
new patch for XML
Modified: dbd-oracle/trunk/Changes
==============================================================================
--- dbd-oracle/trunk/Changes (original)
+++ dbd-oracle/trunk/Changes Mon Mar 10 13:33:51 2008
@@ -1,5 +1,6 @@
=head1 Changes in DBD-Oracle 1.21(svn rev xxxx)
+ Added support for direct insert of large XML data with XMLType from Hendrik
Fuss
Fixed memory leak (not releasing Temp Lob with OCILobFreeTemporary) when
created for a bind John Scoles
Added suppot for bind_param_inout_array with use with execute_array from
John Scoles
Added enhancement for Embedded Objects handling from Paul G. Weiss
Modified: dbd-oracle/trunk/Oracle.h
==============================================================================
--- dbd-oracle/trunk/Oracle.h (original)
+++ dbd-oracle/trunk/Oracle.h Mon Mar 10 13:33:51 2008
@@ -98,5 +98,5 @@
#define ORA_VARCHAR2_TABLE 201
#define ORA_NUMBER_TABLE 202
-
+#define ORA_NTY 108
/* end of Oracle.h */
Modified: dbd-oracle/trunk/Oracle.pm
==============================================================================
--- dbd-oracle/trunk/Oracle.pm (original)
+++ dbd-oracle/trunk/Oracle.pm Mon Mar 10 13:33:51 2008
@@ -22,7 +22,7 @@
ORA_VARCHAR2 ORA_STRING ORA_NUMBER ORA_LONG ORA_ROWID ORA_DATE
ORA_RAW ORA_LONGRAW ORA_CHAR ORA_CHARZ ORA_MLSLABEL ORA_NTY
ORA_CLOB ORA_BLOB ORA_RSET ORA_VARCHAR2_TABLE ORA_NUMBER_TABLE
- SQLT_INT SQLT_FLT
+ SQLT_INT SQLT_FLT XMLType
) ],
ora_session_modes => [ qw( ORA_SYSDBA ORA_SYSOPER ) ],
);
@@ -1193,7 +1193,7 @@
ORA_VARCHAR2 ORA_STRING ORA_NUMBER ORA_LONG ORA_ROWID ORA_DATE
ORA_RAW ORA_LONGRAW ORA_CHAR ORA_CHARZ ORA_MLSLABEL ORA_NTY
ORA_CLOB ORA_BLOB ORA_RSET ORA_VARCHAR2_TABLE ORA_NUMBER_TABLE
- SQLT_INT SQLT_FLT
+ SQLT_INT SQLT_FLT XMLType
=item SQLCS_IMPLICIT
@@ -1482,7 +1482,7 @@
Additional values when DBD::Oracle was built using OCI 8 and later:
- ORA_CLOB, ORA_BLOB, ORA_NTY, ORA_VARCHAR2_TABLE, ORA_NUMBER_TABLE
+ ORA_CLOB, ORA_BLOB, ORA_NTY, ORA_VARCHAR2_TABLE, ORA_NUMBER_TABLE, XMLType
See L</Binding Cursors> for the correct way to use ORA_RSET.
@@ -3221,6 +3221,36 @@
Any NULL values found in the embedded object will be returned as 'undef'.
+=head1 Support for Insert of XMLType
+
+Inserting large XML data sets into tables with XMLType fields is now supported
by DBD::Oracle. The only special
+requirement is the use of bind_param() with an attribute hash parameter that
specifies ora_type as ORA_NTY. For
+example with a table like this;
+
+ create table books (book_id number, book_xml XMLType);
+
+one can insert data using this code
+
+ $sql='insert into books values (1,:p_xml)';
+ $xml= '<Books>
+ <Book id=1>
+ <Title>Programming the Perl DBI</Title>
+ <Subtitle>The Cheetah Book</Subtitle>
+ <Authors>
+ <Author>T. Bunce</Author>
+ <Author>Alligator Descartes</Author>
+ </Authors>
+
+ </Book>
+ </Books>....
+ <Book id=10000> ...';
+ my $sth =$dbh-> prepare($sql);
+ $sth-> bind_param("p_xml", $xml, { ora_type => ORA_NTY });
+ $sth-> execute();
+
+In the above case we will assume that $xml has 10000 Book nodes and is over
32k in size and is well formed XML.
+This will also work for XML that is smaller than 32k as well. Attempting to
insert malformed XML will cause an error.
+
=head1 Oracle Related Links
=head2 Oracle Instant Client
Modified: dbd-oracle/trunk/dbdimp.c
==============================================================================
--- dbd-oracle/trunk/dbdimp.c (original)
+++ dbd-oracle/trunk/dbdimp.c Mon Mar 10 13:33:51 2008
@@ -262,6 +262,7 @@
case 116: /* SQLT_RSET OCI 8 cursor variable */
case ORA_VARCHAR2_TABLE: /* 201 */
case ORA_NUMBER_TABLE: /* 202 */
+ case ORA_NTY: /* SQLT_NTY Well realy only XML clobs not embedded
objects */
return 1;
}
return 0;
@@ -985,6 +986,99 @@
/* ================================================================== */
+#define MAX_OCISTRING_LEN 32766
+
+SV *
+createxmlfromstring(SV *sth, imp_sth_t *imp_sth, SV *source){
+
+ dTHX;
+ OCIXMLType *xml = NULL;
+ ub4 len;
+ sword status;
+ ub1 src_type;
+ dvoid* src_ptr = NULL;
+ D_imp_dbh_from_sth;
+ SV* sv_dest;
+ dvoid *bufp;
+ ub1 csform;
+ ub2 csid;
+ csid = 0;
+ csform = SQLCS_IMPLICIT;
+
+
+ len = SvLEN(source);
+ bufp = SvPV(source, len);
+
+ if (DBIS->debug >=3)
+ PerlIO_printf(DBILOGFP, " creating xml from string that is %d
long\n",len);
+
+ if(len > MAX_OCISTRING_LEN) {
+ src_type = OCI_XMLTYPE_CREATE_CLOB;
+
+ if (DBIS->debug >=5)
+ PerlIO_printf(DBILOGFP, " use a temp lob locator for large xml \n");
+
+ OCIDescriptorAlloc_ok(imp_dbh->envhp, &src_ptr, OCI_DTYPE_LOB);
+
+ OCILobCreateTemporary_log_stat(imp_dbh->svchp, imp_sth->errhp,
+ (OCILobLocator *) src_ptr, (ub2) OCI_DEFAULT,
+ (ub1) OCI_DEFAULT, OCI_TEMP_CLOB, FALSE,
OCI_DURATION_SESSION, status);
+
+ if (status != OCI_SUCCESS) {
+ oci_error(sth, imp_sth->errhp, status, "OCILobCreateTemporary");
+ }
+ csid = (SvUTF8(source) && !CS_IS_UTF8(csid)) ? utf8_csid :
CSFORM_IMPLIED_CSID(csform);
+
+ OCILobWriteAppend_log_stat(imp_dbh->svchp, imp_dbh->errhp, src_ptr,
+ &len, bufp, (ub4)len, OCI_ONE_PIECE,
+ NULL, NULL,
+ csid, csform, status);
+
+ if (status != OCI_SUCCESS) {
+ oci_error(sth, imp_sth->errhp, status, "OCILobWriteAppend");
+ }
+
+ } else {
+ src_type = OCI_XMLTYPE_CREATE_OCISTRING;
+ if (DBIS->debug >=5)
+ PerlIO_printf(DBILOGFP, " use a OCIStringAssignText for small xml \n");
+
+
+ OCIStringAssignText(imp_dbh->envhp,
+ imp_dbh->errhp,
+ (CONST text*) source,
+ (ub2) SvLEN(source),
+ (OCIString **) &src_ptr);
+ }
+
+
+
+ status= OCIXMLTypeCreateFromSrc(imp_dbh->svchp,
+ imp_dbh->errhp,
+ (OCIDuration)OCI_DURATION_CALLOUT,
+ (ub1)src_type,
+ (dvoid *)src_ptr,
+ (sb4)OCI_IND_NOTNULL,
+ &xml);
+
+ if (status != OCI_SUCCESS) {
+ oci_error(sth, imp_sth->errhp, status,
"OCIXMLTypeCreateFromSrc");
+ }
+
+ /* free temporary resources */
+ if ( src_type == OCI_XMLTYPE_CREATE_CLOB ) {
+ OCILobFreeTemporary(imp_dbh->svchp, imp_dbh->errhp,
+ (OCILobLocator*) src_ptr);
+
+ OCIDescriptorFree((dvoid *) src_ptr, (ub4) OCI_DTYPE_LOB);
+ }
+
+
+ sv_dest = newSViv(0);
+ sv_setref_pv(sv_dest, "OCIXMLTypePtr", xml);
+ return sv_dest;
+
+}
void
@@ -1147,6 +1241,9 @@
case SQL_LONGVARCHAR:
return 8; /* Oracle LONG */
+ case SQL_UDT:
+ return 108; /* Oracle NTY */
+
case SQL_CLOB:
return 112; /* Oracle CLOB */
@@ -2259,6 +2356,74 @@
return 1;
}
+static int
+dbd_rebind_ph_xml( SV* sth, imp_sth_t *imp_sth, phs_t *phs) {
+ dTHX;
+ dTHR;
+ OCIType *tdo = NULL;
+ sword status;
+ SV* ptr;
+
+ if (DBIS->debug >= 3)
+ PerlIO_printf(DBILOGFP, " in dbd_rebind_ph_xml\n");
+
+ /*go and create the XML dom from the passed in value*/
+
+ phs->sv=createxmlfromstring(sth, imp_sth, phs->sv );
+
+ 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;
+ }
+
static int
dbd_rebind_ph(SV *sth, imp_sth_t *imp_sth, phs_t *phs)
@@ -2277,6 +2442,7 @@
phs->name, (SvPOK(phs->sv) ? neatsvpv(phs->sv,0) :
"NULL"),(SvUTF8(phs->sv) ? "is-utf8" : "not-utf8"),
phs->ftype, phs->csid, phs->csform, phs->is_inout);
+
switch (phs->ftype) {
case ORA_VARCHAR2_TABLE:
done = dbd_rebind_ph_varchar2_table(sth, imp_sth, phs);
@@ -2291,6 +2457,9 @@
case SQLT_RSET:
done = dbd_rebind_ph_rset(sth, imp_sth, phs);
break;
+ case ORA_NTY:
+ done = dbd_rebind_ph_xml(sth, imp_sth, phs);
+ break;
default:
done = dbd_rebind_ph_char(imp_sth, phs);
}
Modified: dbd-oracle/trunk/oci.def
==============================================================================
--- dbd-oracle/trunk/oci.def (original)
+++ dbd-oracle/trunk/oci.def Mon Mar 10 13:33:51 2008
@@ -374,4 +374,4 @@
OCILobFreeTemporary
OCILobLocatorAssign
OCILobCreateTemporary
-
+OCIXMLTypeCreateFromSrc
Modified: dbd-oracle/trunk/ocitrace.h
==============================================================================
--- dbd-oracle/trunk/ocitrace.h (original)
+++ dbd-oracle/trunk/ocitrace.h Mon Mar 10 13:33:51 2008
@@ -38,6 +38,13 @@
+#define
OCIXMLTypeCreateFromSrc_log_stat(svchp,envhp,src_type,src_ptr,xml,stat)\
+ stat =OCIXMLTypeCreateFromSrc
(svchp,envhp,(OCIDuration)OCI_DURATION_CALLOUT,(ub1)src_type,(dvoid
*)src_ptr,(sb4)OCI_IND_NOTNULL, xml);\
+ (DBD_OCI_TRACEON) \
+ ? PerlIO_printf(DBD_OCI_TRACEFP,\
+
"%sOCIXMLTypeCreateFromSrc_log_stat(%p,%p,%p,%p,%p)=%s\n",\
+ OciTp, (void*)svchp,(void*)envhp, src_type,
src_ptr,oci_status_name(stat)),stat \
+ : stat
#define OCILobLocatorIsInit_log_stat(envhp,errhp,loc,is_init,stat)\
stat =OCILobLocatorIsInit (envhp,errhp,loc,is_init );\
