On 06/09/2013 17:52, Martin J. Evans wrote:
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
Sorry but the github app is now totally broken so I cannot even clone
repositories on my Windows machine and so I'm unable to generate a test
release of DBD::ODBC. I'll let you know if I manage to fix it. In the
mean time if you can apply the above patch that is your best solution.
Martin
--
Martin J. Evans
Wetherby, UK
