Variants of SQL_BINARY type are incorrectly truncated when returned in a result set

[GoogleCodeExporter]

What steps will reproduce the problem?
1. Define a table with a CHAR(13) FOR BIT DATA COLUMN
2. Insert a few rows that have sequential zeroes. Ex:
x'20000103223021595898000000'
3. Select the data, and print it using pythons binascii.b2a_hex() method

What is the expected output? What do you see instead?
Expected: 20000103223021595898000000
Actual:   20

What version of the product are you using? On what operating system?
DB2 v9.5
ibm_db-1.0-py2.5-linux-i686.egg 
Python 2.5.5

Please provide any additional information below.
in ibm_db.c line 6567, PyString_FromString() is called on the str_val union
member, but that method detects a null byte as a string terminator, which
results in the data being truncated. 
Using PyString_FromStringAndSize() with the str_val, and the column_info
size reported by the CLI results in the correct value. 

I've attached the output of `svn diff' as proof-of-concept, but I'm not
sure what implications it might have. It did solve the immediate problem.  

All of our tables use CHAR(13) FOR BIT DATA as the primary key, so this is
a significant problem. 

Original issue reported on code.google.com by bhorns...@gmail.com on 1 Mar 2010 at 9:39

Attachments:

• ibm_db.c.diff

[GoogleCodeExporter]

Hi Bhornseth,
I understand the problem. This problem arises due to PyString_FromString() 
detects
x'000' as string terminator. 
If i use the method you provided, then I get correct result. But if you define a
field as “CHAR(13) FOR BIT ATA COLUMN” and insert a data which is less then 
13 bytes
in length, then at the time of fetching you will get data of length 13 bytes 
only as
database will always return data size as 13. So, the output would be your data
followed by spaces (x'20'). If you use VARCHAR(13) instead, then the database 
will
return exact length and there wouldn't be any following spaces. I think you are 
aware
of this problem .

Let me know what you think of this, I will take your views into consideration 
while
fixing this.

Thanks,
Rahul Priyadarshi

Original comment by rahul.pr...@in.ibm.com on 4 Mar 2010 at 11:15

• Changed state: Accepted
• Added labels: OpSys-All

[GoogleCodeExporter]

Thanks for the informative reply, Rahul.  I was aware of the caveat with data 
that
was potentially shorter than the full column length, but I'm not familiar 
enough with
the DB2 cli to implement a better solution, so I'll defer to your knowledge on 
that. :)

I believe my naive patch will be alright for our situation as the only columns 
of
this type we use /always/ contain 13 characters (we use the DB2 
generate_unique()
function). Admittedly, the patch I provided is more of a workaround than a 
solution.
I much appreciate any advice you're able to offer as an alternative, even if an
official patch takes a while.

Unfortunately, we have a couple hundred tables across several database servers 
that
use this column type, and converting them to VARCHAR isn't an immediate option 
for
us. We'll definitely look into using this for new tables in the meantime.

Original comment by bhorns...@gmail.com on 4 Mar 2010 at 4:14

[GoogleCodeExporter]

For the CHAR fields, if data is shorter than column length , you will always 
get data
of fixed length followed by  spaces. For more info refer to this link
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.qb.db
conn.doc/doc/c0006171.html
.  

If you want data with actual length then you must have to use VARCHAR. In your 
case
CHAR is more more efficient. 

It will be available with our next release.

Thanks,
Rahul Priyadarshi

Original comment by rahul.pr...@in.ibm.com on 5 Mar 2010 at 12:29

[GoogleCodeExporter]

Fixed in ibm_db-1.0.1

Original comment by rahul.pr...@in.ibm.com on 27 Mar 2010 at 8:41

• Changed state: Fixed

[GoogleCodeExporter]

Thanks for the quick turnaround, folks. This is working well.

Original comment by bhorns...@gmail.com on 31 Mar 2010 at 8:52