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
