Re: odbc_lob_read chunk is short

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