TD-22575: Short binary DB columns issues

Post found bugs and possible workarounds.
maslikhin

TD-22575: Short binary DB columns issues

Post by maslikhin » 10 Sep 2015, 13:25

Hi,

First of all, I speak about MS SQL Server. I don't know about other servers.
Second, when I say "short column", I mean column with a width of less than 255 bytes.

1. I can't use String variable to write data into short binary column. I must use Long String variable and call SqlSetLongBindDatatype(n, 23) function, otherwise I get sql error 26.
However, this is not a problem, though it's confusing.
2. On the other hand, I can't read data from short binary column into Long String variable. I must use Short variable, otherwise I get sql error on data fetching.
3. As you can see in attached test case last byte of written data was lost on write phase. Well, this is well-known feature (or bug?), and not only for String, but for Long String too. I should write one byte more. It's troublesome.
4. Well, I use String variable. And what I have in a result? I have not a binary data, but unicode string - double-wided and with zero terminator. This is not what I want to get. As I understand, I have no way to get original binary data. And this is a real issue.

Well, in reality I almost never use short binary columns to store data, but in some cases I read large binary columns by chunks:

Code: Select all

select cast(substring(data, 1, 254) as varbinary(254)) from ...
This is the same case I wrote above (pp. 2,4). Is there any workaround?

The test case is attached. If you want to run this test app, you must properly inintialize SqlDatabase, SqlUser and SqlPassword variables at SAM_AppStartup section.

Best regards, Vladimir.
You do not have the required permissions to view the files attached to this post.

Jean-Marc Gemperle

TD-22575: Short binary DB columns issues

Post by Jean-Marc Gemperle » 08 Dec 2015, 16:46

Hi

Thanks for the testcase that shows all the points you mentioned.
When data types used are <255 short string should be used, when >=255 long string should be used. This is only when using the 'regular' connectivity that is the sqlodb32.dll odbc router that uses the SQLBase API. When using OLEDB string vs long string is unimportant.

so

1)Bug: short string should be used and should work but as you find out you are forced to use long string to male it work it breaks the rule I know... SqlSetLongBindDatatype() is expected when using binary data of type STRING and NOT of type BINARY (not needed) and this when using regular connectivity. Basically makes sense you use SqlSetLongBindDatatype() because we need to know if the STRING is a binary or TEXT. As you understand when using BINARY data type this is not required. Unfortunately CSTRUCT***() calls only works on STRING data type ( when unless you modify external call to use BINARY)

2)Bug inconsistency; you inserted fine with long string but cant read with long string and here forced to use short string....

3) and 4) I can see anyway data loss and fetched result to be wrong

WORKAROUND ?
Though first about OLEDB and as expected no error insert/select since string vs long string does not matter, unfortunately data loss on insert and when fetching wrong data received. 1 snapshot.
So I try again native connectivity, merge the outline and used BINARY data instead as well as changing Receive String : LPVOID to Receive Binary: LPVOID and commenting SqlSetLongBindDatatype().... Inserts and fetch no error, data inserted is FINE but fetch is wrong see 2 snapshot

Anyway here is the bug for all this
TD-22575
SQLServer varbinary < 255 insert/fetch issues

Cheers
JM
You do not have the required permissions to view the files attached to this post.

Return to “Bug Reports”

Who is online

Users browsing this forum: [Ccbot] and 0 guests