Re: Patch for DBD::Oracle (number/varchar2 table bind functionality)
. * maxlen(int) = sizeof(int); * maxlen(double) = sizeof(double); -- Tim Oertel |Why should I be content to simply VP Engineering | live in this world, when I, as a Ashergroup, Inc. | human being, can CREATE it?! 585-586-0020 | IM via google/XMPP: ma...@jabber.com
Re: DBD-Oracle stored proc with array bug
Built from SVN, works in our test environment.
John Scoles wrote, on or about 12/18/2010 8:08 PM:
Ok I got it to work 100% with the test seems you gave a little too much SP
You will find it in trunk of DBD::Oracle
which is here
http://svn.perl.org/modules/dbd-oracle/trunk
Thanks again
Cheers
John Scoles
On Tue, Dec 14, 2010 at 10:39 AM, Tim Oertel t...@samasher.com
mailto:t...@samasher.com wrote:
Hi,
We were recently trying to pass an array to a stored procedure.
It worked fine the first call, but subsequent calls were receiving
the wrong array size, and with old array entries.
After some investigation, it looks like once an array parameter
is used with a given size array, it ignores any future array length.
i.e.
@array_of_something = (1, 2, 3, 4, 5);
$sth-bind_param(1, \...@array_of_something, {
ora_type = ORA_NUMBER_TABLE,
ora_internal_type = SQLT_INT });
$sth-execute();
@array_of_something = (6, 7, 8);
$sth-bind_param(1, \...@array_of_something, {
ora_type = ORA_NUMBER_TABLE,
ora_internal_type = SQLT_INT });
$sth-execute();
...doesn't do what is expected. The stored proceedure
receives an array of (6, 7, 8, 4, 5), in the second execute().
In the process of debugging that, we grabbed newer versions of DBD::Oracle,
to check if a more recent version had a fix, and lacking that, creating
a patch against svn.
Turned out we ran into a second bug, our dev system has recent Oracle
libraries on it, but our db is Oracle 9.2, and (after investigation) it
looks like there is a known issue with Oracle 10.2 libs, doing a ping
against 9.2, which causes a listener crash (I think).
The fix for that was to revert to the server version check if the server
is too old.
So, I've attached a patch, plus a test case for the first problem.
The patch is a patch against svn, 14564. It was tested on a Linux
box, with 10.2.0.3 client libs and 9.2.0.8.0 server, also running on
Linux.
If I've screwed up formatting, or need to do this differently, let me
know, and I'll try to fix it. If the fixes are wrong, I'm all ears
on that too.
--
Tim Oertel |Why should I be content to simply
VP Engineering | live in this world, when I, as a
Ashergroup, Inc. | human being, can CREATE it?!
585-586-0020 | IM via google/XMPP: ma...@jabber.com
DBD-Oracle stored proc with array bug
Hi,
We were recently trying to pass an array to a stored procedure.
It worked fine the first call, but subsequent calls were receiving
the wrong array size, and with old array entries.
After some investigation, it looks like once an array parameter
is used with a given size array, it ignores any future array length.
i.e.
@array_of_something = (1, 2, 3, 4, 5);
$sth-bind_param(1, \...@array_of_something, {
ora_type = ORA_NUMBER_TABLE,
ora_internal_type = SQLT_INT });
$sth-execute();
@array_of_something = (6, 7, 8);
$sth-bind_param(1, \...@array_of_something, {
ora_type = ORA_NUMBER_TABLE,
ora_internal_type = SQLT_INT });
$sth-execute();
...doesn't do what is expected. The stored proceedure
receives an array of (6, 7, 8, 4, 5), in the second execute().
In the process of debugging that, we grabbed newer versions of DBD::Oracle,
to check if a more recent version had a fix, and lacking that, creating
a patch against svn.
Turned out we ran into a second bug, our dev system has recent Oracle
libraries on it, but our db is Oracle 9.2, and (after investigation) it
looks like there is a known issue with Oracle 10.2 libs, doing a ping
against 9.2, which causes a listener crash (I think).
The fix for that was to revert to the server version check if the server
is too old.
So, I've attached a patch, plus a test case for the first problem.
The patch is a patch against svn, 14564. It was tested on a Linux
box, with 10.2.0.3 client libs and 9.2.0.8.0 server, also running on
Linux.
If I've screwed up formatting, or need to do this differently, let me
know, and I'll try to fix it. If the fixes are wrong, I'm all ears
on that too.
--
Tim Oertel |Why should I be content to simply
VP Engineering | live in this world, when I, as a
Ashergroup, Inc. | human being, can CREATE it?!
585-586-0020 | IM via google/XMPP: ma...@jabber.com
testcase.tgz
Description: Binary data
Index: ocitrace.h
===
--- ocitrace.h (revision 14564)
+++ ocitrace.h (working copy)
@@ -77,6 +77,14 @@
: stat
#endif
+#define OCIServerRelease_log_stat(sc,errhp,b,bl,ht,ver,stat)\
+ stat =OCIServerRelease(sc,errhp,b,bl,ht,ver);\
+ (DBD_OCI_TRACEON) \
+ ? PerlIO_printf(DBD_OCI_TRACEFP,\
+%sOCIServerRelease(%p)=%s\n,\
+OciTp, sc,oci_status_name(stat)),stat \
+ : stat
+
#define OCIServerVersion_log_stat(sc,errhp,b,bl,ht,stat)\
stat =OCIServerVersion(sc,errhp,b,bl,ht);\
(DBD_OCI_TRACEON) \
Index: oci8.c
===
--- oci8.c (revision 14564)
+++ oci8.c (working copy)
@@ -4506,3 +4506,27 @@
imp_sth-lob_refetch = NULL;
}
+ub4
+ora_db_version(SV *dbh, imp_dbh_t *imp_dbh)
+{
+ sword status;
+ text buf[2];
+ ub4 vernum;
+
+ if( imp_dbh-server_version 0 ) {
+ return imp_dbh-server_version;
+ }
+
+
+ /* XXX should possibly create new session before ending the old so
*/
+ /* that if the new one can't be created, the old will still work.
*/
+ OCIServerRelease_log_stat(imp_dbh-svchp, imp_dbh-errhp, buf, 2,
+ OCI_HTYPE_SVCCTX, vernum , status);
+ if (status != OCI_SUCCESS) {
+ oci_error(dbh, imp_dbh-errhp, status,
OCISessionServerRelease);
+ return 0;
+ }
+ imp_dbh-server_version = vernum;
+ return vernum;
+}
+
Index: dbdimp.c
===
--- dbdimp.c(revision 14564)
+++ dbdimp.c(working copy)
@@ -409,6 +409,8 @@
DBD_ATTRIB_GET_IV( attr, ora_drcp_incr, 13, svp,
imp_dbh-pool_incr);
#endif /*ORA_OCI_112*/
+ imp_dbh-server_version = 0;
+
/* check to see if DBD_verbose or ora_verbose is set*/
if (DBD_ATTRIB_TRUE(attr,dbd_verbose,11,svp))
DBD_ATTRIB_GET_IV( attr, dbd_verbose, 11, svp, dbd_verbose);
@@ -1637,7 +1639,6 @@
/* If no number of entries to bind specified,
* set phs-array_numstruct to the scalar(@array) bound.
*/
- if( phs-array_numstruct = 0 ){
/* av_len() returns last array index, or -1 is array is empty */
int numarrayentries=av_len( arr );
if( numarrayentries = 0 ){
@@ -1647,7 +1648,6 @@
phs-array_numstruct);
}
}
- }
/* Fix charset */
csform = phs-csform;
if (trace_level = 2 || dbd_verbose = 3 ){
@@ -2036,7 +2036,6 @@
/* If no number of entries to bind specified,
* set phs-array_numstruct to the scalar(@array) bound.
*/
- if( phs-array_numstruct = 0 ){
/* av_len() returns last array index, or -1 is array is empty */
int numarrayentries=av_len( arr );
if( numarrayentries = 0
Re: DBD-Oracle stored proc with array bug
Okay, I'll keep my eyes on the list, at least until then. :)
John Scoles wrote, on or about 12/14/2010 12:43 PM:
Ok thanks for that.
At first glance this looks like it might fix another reported bug and it
is doing some work for me as I wanted to add in
OCIServerRelease_log for my own selfish reasons so thanks for that:)
It will be going into 1.28 as 1.27 is reserved for a PERL_POLLUTE release which
should be out soon
Cheers
John Scoles
On 14/12/2010 10:39 AM, Tim Oertel wrote:
Hi,
We were recently trying to pass an array to a stored procedure.
It worked fine the first call, but subsequent calls were receiving
the wrong array size, and with old array entries.
After some investigation, it looks like once an array parameter
is used with a given size array, it ignores any future array length.
i.e.
@array_of_something = (1, 2, 3, 4, 5);
$sth-bind_param(1, \...@array_of_something, {
ora_type = ORA_NUMBER_TABLE,
ora_internal_type = SQLT_INT });
$sth-execute();
@array_of_something = (6, 7, 8);
$sth-bind_param(1, \...@array_of_something, {
ora_type = ORA_NUMBER_TABLE,
ora_internal_type = SQLT_INT });
$sth-execute();
...doesn't do what is expected. The stored proceedure
receives an array of (6, 7, 8, 4, 5), in the second execute().
In the process of debugging that, we grabbed newer versions of
DBD::Oracle,
to check if a more recent version had a fix, and lacking that, creating
a patch against svn.
Turned out we ran into a second bug, our dev system has recent Oracle
libraries on it, but our db is Oracle 9.2, and (after investigation) it
looks like there is a known issue with Oracle 10.2 libs, doing a ping
against 9.2, which causes a listener crash (I think).
The fix for that was to revert to the server version check if the server
is too old.
So, I've attached a patch, plus a test case for the first problem.
The patch is a patch against svn, 14564. It was tested on a Linux
box, with 10.2.0.3 client libs and 9.2.0.8.0 server, also running on
Linux.
If I've screwed up formatting, or need to do this differently, let me
know, and I'll try to fix it. If the fixes are wrong, I'm all ears
on that too.
--
Tim Oertel |Why should I be content to simply
VP Engineering | live in this world, when I, as a
Ashergroup, Inc. | human being, can CREATE it?!
585-586-0020 | IM via google/XMPP: ma...@jabber.com
