into our database and I found an issue with how we are reading data
when retrieving it into an sqlda structure using dynamic sql. Here is
the issue:
This particular table has several VARCHAR(256) fields. I set up a few
records that spanned all 256 char bytes then ran the debugger. I
found that I am only receiving the first 254 bytes of data in sqlda-
>sqvar.sqldata. I understand that the first two bytes in sqldata arefor the length but can anyone tell me why all 256 bytes of data are
not coming through?
Sample of the data in the database where two columns that are both
VARCHAR(256) with a record that has all 256 bytes inserted in each
field:
USERNAME
PASSWORD
-------------- --------------
22222222222222222222222222222222222222222222222222 222222222222222222222222222222
22222222222222222222222222222222222222222222222222 222222222222222222222222222222
22222222222222222222222222222222222222222222222222 222222222222222222222222222222
2222222222222456
77777777777777777777777777777777777777777777777777 7777777777777
77777777777777777777777777777777777777777777777777 777777777777777777777777777777
77777777777777777777777777777777777777777777777777 777777777777777777777777777777
777777777777777777777777777777768
So now I run my program that fetches the data into the sqlda-
>sqlvar.sqldata and this is what I see:
(dbx) p sqldata[0]
'^A' <----length is 256
(dbx) p sqldata[1]
'\0' <---part of length field?
(dbx) p sqldata[2]
'2' <----start of the USERNAME
data
(dbx) p sqldata[3]
'2'
(dbx) p sqldata[253]
'2'
(dbx) p sqldata[254]
'2'
(dbx) p sqldata[255]
'4' <-----254th byte of the data (or
253rd if you are looking at the index)
(dbx) p sqldata[256]
'^A' <---this should be a value of 5
but instead it is the length for the PASSWORD field
(dbx) p sqldata[257]
'\0'
(dbx) p sqldata[258]
'7' <---start of the PASSWORD field
(dbx) p sqldata[259]
'7'
so it seems I am only getting back a total of 256 bytes including the
length fields and that truncates the data. I found the following
statement by Knut in a different post and it brought up some
questions:
"No, not for VARCHAR data. The string 'some text' requires 12 bytes
on disc
(2 length + 1 null-indicator + 9 data bytes) whereas 'some more text'
uses
17 bytes on disc (2 + 1 + 14 data bytes). "
1) does this mean there could be 2-3 fields for length + null and that
this could vary depending on the length?
2) does this also mean that even though I could insert 256 bytes of
data into the VARCHAR(256) we can only use 253 for data to account for
the 3 extra bytes in sqldata? ( would be suprised if the answer is
yes!)
or
did we just do something wrong with the lengths somewhere in the
call? sqllen is set to 256 after the DESCRIBE...should the
application set this to 259?
I appreciate your time.