Solved TD-17862: SQLBase truncated binary data on updates

Report SqlBase bugs and possible workarounds.
Georg Diczig
Germany
Posts: 175
Joined: 15 Mar 2017, 15:15
Location: Cologne Germany

TD-17862: SQLBase truncated binary data on updates

Post by Georg Diczig » 18 Jun 2012, 09:42

Updating binary data in a SQLBase database is truncated with 2 bytes:
BinaryUpdate.png
I have created a simple testcase to demonstrate this.

What does the tool?
1. Inserts the String "12345" from the "Input data" ML in the database
2. Read the inserted string from the database
3. Update the string without any changes
4. Read the updated string from the database and put it in the "Output data" ML.

What is the problem?
The problem is that the output data is truncated!

What the output data really should be equal to?
The output data muss be the same as the input data.

What are the effects caused by this bug?
We are no more able to store compressed data in a SQLBase database!
We are storing a lot of compressed data not only text but also rtf and images as binary data. If the data is now truncated with 2 or 4 bytes SalStrUncompress is no more able to uncompress
the data and so the whole data is lost not only the last 2 bytes!

Why are we using text to demonstrate a binary bug?
In fact a string is a binary data and helps in the visualization for this issue.

How to reproduce the bug
1. Create a new database INFOTEXTE and load the attached unload file
2. Run the attached application.

The bug is reproducible with TD 6.0 SP6 and TD 6.1.
SqlBase 11.6.2
You do not have the required permissions to view the files attached to this post.
Best Regards
Georg Diczig

Jeff Luther

Re: TD-17862: SQLBase truncated binary data on updates

Post by Jeff Luther » 18 Jun 2012, 20:23

Looks like this is a new thread for a simplified test case from the forum thread you reported last week. Thank you for simplifying it!

I am going to take a look but see a couple of 'apples vs. oranges' things in the test case:
* SQLBase LONG-type column is LONG VARBINARY -- this is fairly new column type for binary data -- but as I found out with testing, it can handle Unicode/text data as well

* Multiline field is type String and is assigned '12345'

* code does this, assigns a Long string var.: Set llstInfotext = ml1

* Then calls: SqlSetLongBindDatatype( 3, 23 )
but '12345' is Unicode data, not binary data, so this call is incorrect.

CONCLUSION: call "SqlSetLongBindDatatype( 3, 23 )" is wrong for the data the test case is handling, since '12345' is text data and that 2nd param. should be 22.

I've modified your test and will attach here. Run my mod. test case and click Yes first to see how fetch returns '12345' but the buffer length is wrong because TD internally treats binary different than Unicode/double-byte string data.

Note I've changed DB to ISLAND and changed DB test table name since I drop/create to keep test self-contained and didn't want your INFOTEXTE table to get deleted!
You do not have the required permissions to view the files attached to this post.

Jeff Luther

Re: TD-17862: SQLBase truncated binary data on updates

Post by Jeff Luther » 18 Jun 2012, 20:33

P.S. Georg -- My 2 cents is that while LONG VARBINARY works for managing Unicode/text data, as my modified test case shows, if you try to mix binary/text data in that VARBINARY column you will need to make a choice about how to call SqlSetLongBindDatatype().

My suggestion is that if in SQLBase you want some LONG data to be:
* text/Unicode, use LONG VARCHAR
* binary, use LONG VARBINARY

That way for each column you know what param. #2 in this call, SqlSetLongBindDatatype(), should be.

Dave Rabelink
Founder/Site Admin
Founder/Site Admin
Netherlands
Posts: 3388
Joined: 24 Feb 2017, 09:12
Location: Gouda, The Netherlands

Re: TD-17862: SQLBase truncated binary data on updates

Post by Dave Rabelink » 18 Jun 2012, 21:18

Jeff, now you are confusing me.

A binary type in the database means you can store anything which is represented by zero's and ones.
So when you have a buffer "ABCD", it is a set of zero's and ones which is interpreted as a string. But for binary type this does not matter.

I could store EBCDIC, ANSI, UNICODE, WAV, DOC, RTF, BMP in the binary type. The type does not care how the bitpattern is interpreted.

By assigning BINARY type in SqlSetLongBindDatatype you actually say to TD not to mess with the data and pass it 1:1, without any conversion to the database.
So when the buffer contains a bitpattern which represents as a UNICODE string, the same bitpattern should be stored in the database.
(TD does not even know it is a UNICODE string).

It would be strange if you need to create several columns for different data if the data is just stored 1:1. That is why there is a binary type.
Regards,
Dave Rabelink

Image
Articles and information on Team Developer Tips & Tricks Wiki
Download samples, documents and resources from TD Sample Vault
Videos on TDWiki YouTube Channel

Georg Diczig
Germany
Posts: 175
Joined: 15 Mar 2017, 15:15
Location: Cologne Germany

Re: TD-17862: SQLBase truncated binary data on updates

Post by Georg Diczig » 19 Jun 2012, 08:13

Dave: thanks for your clarification!
Jeff: I totaly agree with Dave:
A binary type in the database means you can store anything which is represented by zero's and ones.
I also wrote in my post that we are storing the data compressed in the database and I only use a string in the sample to get a better "visualization".

Please read again your post from Mon Mar 07, 2011 8:15 pm:
https://support.guptatechnologies.com/su ... 355#p23643

You wrote:
* Before the compress the String variable is, well, a string of Unicode characters
* After the compress the String variable is a binary value and needs to be handled as such.
* DB Table column has to support BINARY data; SqlSetLongBindDatatype( ..., 23 ) used during INSERT and before FETCH
BTW, here is where SqlSetLongBindDatatype needs to be called in SQL statements:
INSERT/UPDATE - between prep and exe:
SqlPrepare
SqlSetLongBindDatatype
SqlExecute
...
We are doing exactly the same steps! And I want to save my data in single column! That works with SQLBase in PTF4 and also works with SQLServer!

Now I changed for you the reprocase so that the textinformation is now compressed before saving it in the database.
And see what's happening:
BinaryUpdateCompressed.png
The saved data is lost because SalStrUncompress is not able to uncompress the truncated data!
To reproduce please see the attached reprocase: BinaryUpdateCompressed.zip

Regarding:
Looks like this is a new thread for a simplified test case from the forum thread you reported last week. Thank you for simplifying it!
I don't think so! In my case last week I handeled an INSERT and in this case I handle an UPDATE.
Using INSERT 's in a while SqlFetchNext loop only the first row from the resultset is truncated.
Using UPDATE's each update is truncated!
I see here a difference an I don't know what internaly the databaserouter does, so I think this is an important information for the TD-developer.
Thats why I opened a new thread!


I see that at the same time you added yesterday the defect TD-17862:
https://support.guptatechnologies.com/su ... 337#p33180

I think this is the same issue but maybe not!
TD-17862 is handling INSERT's and not UPDATE's. Also see my hint regarding INSERT's in a while loop.

Also I think the [SOLVED] marker is not correct! Maybe you can change it to [DEFECT ADDED] and update TD-17862 with this thread.

Thanks!
You do not have the required permissions to view the files attached to this post.
Best Regards
Georg Diczig

Return to “SqlBase Bug Reports”

Who is online

Users browsing this forum: [Ccbot] and 0 guests