Re: DBD::Oracle, Support binding of integers so they are returned as IVs

Martin J. Evans wrote:
> Hi,
> 
> With reference to the rt I created "Support binding of integers so they
> are returned as IVs" at http://rt.cpan.org/Public/Bug/Display.html?id=49818
> 
> I am now at the point where being unable to bind columns to results-sets
> in DBD::Oracle with a bind type of SQL_INTEGER (or whatever) so they
> look like integers in Perl is slowing some code of ours down
> dramatically. We convert a fetchall_arrayref returned structure into
> JSON with JSON::XS and JSON::XS converts strings to "string" and numbers
> to a plain number. Our select returns a number of columns which are
> really integer columns and as the result-set is very large the extra
> space we use encoding integers as "number" is more than just annoying.
> JSON::XS appears to know a perl scalar has been used in the context of a
> number as if we add 0 to the integer columns returned in
> fetchall_arrayref it encodes them as plain numbers instead of strings
> like "number" (see the rt for the snippet from JSON::XS which does
> this). As a result, a workaround we are using now is to loop through the
> rows adding 0 to all integer columns. I believe DBI allows bind_col to
> be called without a destination scalar so you can use it to specify the
> type of the bind e.g., SQL_INTEGER but still call fetchall_arrayref.
> 
> Does anyone know if it is feasible to make this work with DBD::Oracle
> and if so do you have some pointers as to how it may be achieved. I am
> not looking for anyone else to do the work but would like to sound
> people out about the possibility before I launch into it.
> 
> Thanks
> 
> Martin
> 
> 

What follows is a very rough patch (definitely not finished) which
proves you can do what I wanted to do. However, there on no checks on
the column being bound existing and I'm not sure how to save the TYPE
attribute when bind_col is called before execute (that is when the
result-set is not described yet). Basically, I think more is required in
dbd_st_bind_col but I've not sure as yet what that is and it is possible
returning 1 is a total hack. I'd appreciate any advice to complete this.

Index: oci8.c
===================================================================
--- oci8.c	(revision 13427)
+++ oci8.c	(working copy)
@@ -3279,10 +3279,31 @@
 						while(datalen && p[datalen - 1]==' ')
 							--datalen;
 					}
-					sv_setpvn(sv, p, (STRLEN)datalen);
-					if (CSFORM_IMPLIES_UTF8(fbh->csform) ){
-						SvUTF8_on(sv);
-					}
+
+                    if ((fbh->req_type == 3) &&
+                        ((fbh->dbtype == 2) || (fbh->dbtype == 3))){
+                        char *e;
+                        char zval[32];
+                        long val;
+
+                        memcpy(zval, p, datalen);
+                        zval[datalen] = '\0';
+                        val = strtol(zval, &e, 10);
+
+                        if ((val == LONG_MAX) || (val == LONG_MIN) ||
+                            (e && (*e != '\0'))) {
+                            oci_error(sth, imp_sth->errhp, OCI_ERROR,
+                                      "invalid number or over/under flow");
+                            return Nullav;
+                        }
+
+                        sv_setiv(sv, val);
+                    } else {
+                        sv_setpvn(sv, p, (STRLEN)datalen);
+                        if (CSFORM_IMPLIES_UTF8(fbh->csform) ){
+                            SvUTF8_on(sv);
+                        }
+                    }
 				}
 			}

Index: dbdimp.c
===================================================================
--- dbdimp.c	(revision 13427)
+++ dbdimp.c	(working copy)
@@ -869,7 +869,16 @@
 	return 1;
 }

+int dbd_st_bind_col(SV *sth, imp_sth_t *imp_sth, SV *col, SV *ref, IV
type, SV *attribs) {
+    dTHX;

+    int field = SvIV(col);
+
+    imp_sth->fbh[field-1].req_type = type;
+
+    return 1;
+}
+
 int
 dbd_db_disconnect(SV *dbh, imp_dbh_t *imp_dbh)
 {
Index: dbdimp.h
===================================================================
--- dbdimp.h	(revision 13427)
+++ dbdimp.h	(working copy)
@@ -191,6 +191,8 @@
 	int			piece_lob;  /*use piecewise fetch for lobs*/
 	/* Our storage space for the field data as it's fetched	*/
 	sword		ftype;	/* external datatype we wish to get	*/
+        IV            req_type;                /* type passed to
bind_col */
+
 	fb_ary_t	*fb_ary ;	/* field buffer array			*/
 	/* if this is an embedded object we use this */
 	fbh_obj_t	*obj;
@@ -371,6 +373,7 @@
 #define dbd_st_FETCH_attrib	ora_st_FETCH_attrib
 #define dbd_describe		ora_describe
 #define dbd_bind_ph			ora_bind_ph
+#define dbd_st_bind_col         ora_st_bind_col
 #include "ocitrace.h"

 /* end */
Index: Oracle.h
===================================================================
--- Oracle.h	(revision 13427)
+++ Oracle.h	(working copy)
@@ -67,6 +67,7 @@
 int	 dbd_db_do _((SV *sv, char *statement));
 int	 dbd_db_commit     _((SV *dbh, imp_dbh_t *imp_dbh));
 int	 dbd_db_rollback   _((SV *dbh, imp_dbh_t *imp_dbh));
+int dbd_st_bind_col(SV *sth, imp_sth_t *imp_sth, SV *col, SV *ref, IV
type, SV *attribs);
 int	 dbd_db_disconnect _((SV *dbh, imp_dbh_t *imp_dbh));
 void dbd_db_destroy    _((SV *dbh, imp_dbh_t *imp_dbh));
 int	 dbd_db_STORE_attrib _((SV *dbh, imp_dbh_t *imp_dbh, SV *keysv, SV
*valuesv));

Martin
-- 
Martin J. Evans
Easysoft Limited
http://www.easysoft.com