On 06/09/13 09:18, Martin J. Evans wrote:
On 05/09/13 20:28, Mike Grau wrote:
Hello,
I am completely new at this, but I am trying to read a BLOB in chunks
via ODBC in Oracle 11g using DBD::ODBC-1.43, unixODBC-2.3.1 and
oracle-instantclient11.2-odbc-11.2. I can read an entire BLOB in one
read and write it to the filesystem, but I really need to be able to do
it in chunks, I think.
Reading the BLOB in chunks, but I am always getting one byte less than
requested, losing the last byte, which results in a corrupt document (a
PDF) when written to the filesystem. I'm at a loss as to why this is
happening and how to correct it.
Using this snippet based on the example provided by DBD::ODBC...
while($len = $s->odbc_lob_read(1, \my $x, 8, {TYPE => 999})) {
print "len=$len, x=$x\n";
}
... produces this output:
len=7, x=435886
len=7, x=0000 n
len=7, x=0001282
len=7, x=60 0000
And a tracefile shows the same.
SQLGetData(col=1,type=-2)=1 (retlen=2046252)
<- odbc_lob_read= ( 7 ) [1 items] at /root/lob_read.pl line 45
>> odbc_lob_read DISPATCH (DBI::st=HASH(0x203c940) rc1/1 @5 g2 ima0
pid#11494) at /root/lob_read.pl line 45
-> odbc_lob_read for DBD::ODBC::st (DBI::st=HASH(0x203c940)~0x203c9d0 1
SCALAR(0x1e0a440) 8 HASH(0x203c970)) thr#1cea010
SQLGetData(col=1,type=-2)=1 (retlen=2046244)
<- odbc_lob_read= ( 7 ) [1 items] at /root/lob_read.pl line 45
>> odbc_lob_read DISPATCH (DBI::st=HASH(0x203c940) rc1/1 @5 g2 ima0
pid#11494) at /root/lob_read.pl line 45
-> odbc_lob_read for DBD::ODBC::st (DBI::st=HASH(0x203c940)~0x203c9d0 1
SCALAR(0x1e0a440) 8 HASH(0x203c9a0)) thr#1cea010
Can someone please give me some guidance how to get the full chunk
requested?
I'm using
unixODBC-2.3.1 - built from the tarball
DBD-ODBC-1.43 - installed from the tarball (perl Makefile.PL; make;
make install)
oracle-instantclient11.2-odbc-11.2.0.3 installed from RPM
DBI is the distro's perl-DBI-1.617-4.1.1.x86_64 (openSUSE 12.3)
The database and client are both x86_64
The instant client is link against libodbcinst.so.1 and unixODBC now
provides libodbcinst.so.2, so I symlinked them:
libodbcinst.so.1 -> /usr/local/lib64/libodbcinst.so.2
Ugly, but seems to work.
Ultimately I want to index documents stored in Oracle with Sphinx and so
must use ODBC. I'd like to know that this much is working before moving
on to Sphinx.
TIA -- Mike G.
Bear with me and I'll take another look at that code. If I cannot see anything
I might need to send you a new version with slightly different logging. By all
means nag me if I don't get back to you by tomorrow.
Martin
When I tested odbc_lob_read it does not miss bytes out but it does return at
most buf len - 1 bytes and this is because SQLGetData always puts a trailing
NUL byte in the buffer if the type is a char. So if you want 8 bytes at a time
you currently need to pass a buf len of 9 if you want them back as chars. My
test was retrieving bytes back as SQL_C_CHARs.
It is arguable, DBD::ODBC should add 1 knowing that SQLGetData will terminate
with a NUL byte if the type is a SQL_C_CHAR. I'd accept an RT for that as if
everyone is using odbc_lob_read as documented they should never assume the
buffer returned contains buf len bytes - they should use the returned length.
As for that TYPE=>999 which I'm assuming you got from the example included with
DBD::ODBC, it was a mistake that got checked in when I was testing the TYPE
parameter - set it to SQL_BINARY or omit it if your sure Oracle has this down as a
binary column.
The bug in DBD::ODBC is that if you retrieve a lob as SQL_BINARY it is still
assuming the buffer is NUL terminated and it is not. The secondary bug is TYPE
is coded as Type. The following patch should fix both of these:
$ git diff
diff --git a/ODBC.xs b/ODBC.xs
index 574dc47..8cc8e1a 100644
--- a/ODBC.xs
+++ b/ODBC.xs
@@ -155,7 +155,7 @@ odbc_lob_read(sth, colno, bufsv, length, attr = NULL)
if (attr) {
SV **svp;
DBD_ATTRIBS_CHECK("odbc_lob_read", sth, attr);
- DBD_ATTRIB_GET_IV(attr, "Type", 4, svp, sql_type);
+ DBD_ATTRIB_GET_IV(attr, "TYPE", 4, svp, sql_type);
}
if (SvROK(bufsv)) {
bufsv = SvRV(bufsv);
diff --git a/dbdimp.c b/dbdimp.c
index 5abfb27..ff3226e 100644
--- a/dbdimp.c
+++ b/dbdimp.c
@@ -5938,7 +5938,10 @@ IV odbc_st_lob_read(
"Driver did not return the lob length - SQL_NO_TOTAL)");
return -1;
}
- retlen = length - 1;
+ retlen = length;
+ if (col_type == SQL_C_CHAR) {
+ retlen -= 1;
+ }
} else if (rc == SQL_SUCCESS) {
if (len == SQL_NULL_DATA) {
return 0;
If you can apply those changes to your a newly download copy of DBD::ODBC and
rebuild it does that fix your issue? If you cannot do that I'll try and release
a new test version tonight but I'm mid moving git repositories right now so I'm
not promising that.
Martin
