Solved TD-15967: Problem with TD 6.0, Sqlbase 11.6 and Long String Variable

Report SqlBase bugs and possible workarounds.
giscon

TD-15967: Problem with TD 6.0, Sqlbase 11.6 and Long String Variable

Post by giscon » 09 Jun 2011, 14:10

Hello,
the following problem occurred after migrating some of our TD3.1 applications to TD6.0 and migrating some old databases to SqlBase 11.6.
When selecting a Long Varchar column into a Long String variable (Text), the buffer of the variable is two bytes too short, the 2 extra bytes for the end of string are missing.

Bug description
Here is a simple, short example to explain the problem:
We select a long varchar containing 9 characters (text is for example: 'Testvalue') into a LongString variable
We expect to get a StringLength of 9 and a BufferSize of 20 ( 2 * 9 + 2)
But we get only a buffersize of 18 !
As a consequence, further string processing, e.g. appending another string to the variable (Set sNew = sLongStr || sOtherString), gives wrong results.

It doesn't matter whether we fetch into a window variable, a global variable or if we do the select in a function with a local variable. In all cases, the buffer is too short. Since TEXT is the default in TeamDeveloper for long string binding, we don't need to call the function SqlSetLongBindDatatype.
If we use the function nevertheless, the bug is still there.


But if we select the Long Varchar column into a DataField (or a MultilineField or a TableColumn), all is o.k.
Of course the DataField has to be specified as Long String, if it would have datatype = String, the SqlPrepareAndExecute and the SqlFetch would give no errors, but the DataField would stay empty...

The effect, that the buffer of the LongString variable is too short, seems to occur only with TD 6.0 and SqlBase 11.6.
With an older SqlBase-version, e.g. SqlBase 11.5, the select into the variable will result in a correct buffersize with two zero-bytes at the end.
If we try an Oracle Database, all is ok, too - selecting into a LongString variable works fine as well as selecting into DataField.


Annotation
In our TD3.1-applications the problem does not occur, all works fine.
Selecting long varchar into variable or selecting into datafield: in all cases the buffer length is o.k.
Of course, in TD3.1 we get for 'Testvalue' a StringLength of 9 and a BufferSize of 10, because in TD3.1 strings are in ANSI and not in Unicode.
Even with TD3.1 and SqlBase 11.6 (had to try a little bit to get them work together) all works fine, no problem, buffersize is correct.


Conclusion
For me the problem seems to be somewhere in the cooperation of SqlBase11.6 and TD6.0 !

Constellation and Versions
Team Developer 6.0 SP2 with EMP5347 (Build 25118)
SqlBase 11.6 SP1
Windows XP SP3

Connection either to a local database on the PC with TD6.0 installed (using win32client.ws32 - local Unify SqlBase 11.6 Service)
or to a database on a remote server, the descripted behaviour is identical.

A testcase to reproduce:
TD60_Testcase_LongString.zip
Best Regards
Guenter
You do not have the required permissions to view the files attached to this post.
Last edited by giscon on 10 Jun 2011, 07:38, edited 1 time in total.

Jeff Luther

Re: TD-15967: Problem with TD 6.0, Sqlbase 11.6 and Long String Variable

Post by Jeff Luther » 09 Jun 2011, 19:30

migrating some old databases
How old is "old" ?

Also, does the data look correct in SB v11.6? That is, if you SQLTalk, for example, to query that DB column does it display "TestValue" ?? or is the 'e' missing?
We select a long varchar containing 9 characters (text is for example: 'Testvalue') into a LongString variable
We expect to get a StringLength of 9 and a BufferSize of 20 ( 2 * 9 + 2)
But we get only a buffersize of 18 !
So the fetched value in your example look like "TestValu". I would be very interested in how data looks in the DB before you use a TD app. to fetch it. SQLTalk is a good way to check this and let us know. Thanks.

giscon

Re: TD-15967: Problem with TD 6.0, Sqlbase 11.6 and Long String Variable

Post by giscon » 10 Jun 2011, 08:53

The "old" databases were sqlbases 8.5. To get their data into 11.6, a complete unload and load had to be performed, that was no problem.

But this issue can be reproduced on a very new created sqlbase11.6 - database with any table that has a long varchar column containing text only.
We needed the long varchar datatype in our applications to get over the limit of 254 chars for some long description fields.

The appended testcase application can create such a test-table with a long varchar column in a database of your choice and inserts a testrecord (with 'Testvalue'). Then you can select into a datafield and a variable and see the difference.

SqlTalk
In SqlTalk all seems o.k. the 'e' is not missing. 'SELECT * from TestLongVarchar' shows the whole string 'Testvalue'.
That's the same as in TD6.0 and selecting into a datafield, the problem comes up when TD6.0 selects into a LongString Variable, especially when further processing that LongString-Variable (concatenation), see the testcase-apt

Best Regards
Guenter

Jeff Luther

Re: TD-15967: Problem with TD 6.0, Sqlbase 11.6 and Long String Variable

Post by Jeff Luther » 14 Jun 2011, 21:47

I'll look at this...

tested with:
MS SQL Server 2005: I changed the DB column type to NTEXT and your test runs OK with MS SS. length = 9, buffer length = 20. For more info. on NTEXT, click here: http://msdn.microsoft.com/en-us/library ... 80%29.aspx

SQLBase v11.6: I see the issue you refer to: on select length = 9, but buffer length = 18 (missing those 2 extra bytes for end-of-string).
Yes, a defect all right. I added TD-15761 for the 'lost character' using a variable in v6.

Mike Vandine

Re: TD-15967: Problem with TD 6.0, Sqlbase 11.6 and Long String Variable

Post by Mike Vandine » 21 Jul 2011, 05:45

Hi Guenter,

This is fixed with the first EMP after the TD6.0 SP3 release. Please contact me directly for availability of this EMP.

mike.vandine@guptatechnologies.com

giscon

Re: TD-15967: Problem with TD 6.0, Sqlbase 11.6 and Long String Variable

Post by giscon » 22 Jul 2011, 16:34

With SP3 EMP5361 this bug is fixed. With sqlbase 11.6 it's alright now (with my testcase-application you now get the correct string length (9) and buffer length (20), thank you.

However, with that EMP and older sqlbase-releases (tested with 8.5 and 11.5), my testcase now shows an odd effect: When selecting into a Long String Variable, the buffer is now even 2 bytes longer than needed (in the testcase string length 9 and buffer size 22).
But that seems not lead to subsequent errors, so the buffer has in these cases two reserve-bytes .. :)

Guenter

giscon

Re: TD-15967: Problem with TD 6.0, Sqlbase 11.6 and Long String Variable

Post by giscon » 25 Jul 2011, 08:39

I must correct my first answer after trying this EMP 5361 for SP3, with this EMP it's correct ONLY with Sqlbase 11.6:
With older sqlbase-releases (tested with 8.5 and 11.5), now the following odd effect occurs:
When selecting into a Long String Variable, the buffer is now even 2 bytes longer than needed (in the testcase string length 9 and buffer size 22).

And that DOES lead to subsequent errors when you concatenate the variable with another string, you can use my above testcase to reproduce it.
A workaround for this new problem could be to use a SalStrLeftX after getting the long string variable value to shorten it to the correct length, but this will be a lot of work... :roll:

And with Oracle
the long string is still truncated (since SP3) which is even worse.
With EMP 5361 the buffer of the wrong truncated result has now also two more bytes ...

Regards
Guenter

Mike Vandine

Re: TD-15967: Problem with TD 6.0, Sqlbase 11.6 and Long String Variable

Post by Mike Vandine » 25 Jul 2011, 10:28

Hi Guenter,

I have created defect TD-15967 for this issue!

Regarding the Oracle problem, apparently the TD-15761 was only a fix for SQLBase. We are looking at this for Oracle and I will update the other thread with the defect number created for that issue.

Best regards,

Mike Vandine

Re: TD-15967: Problem with TD 6.0, Sqlbase 11.6 and Long String Variable

Post by Mike Vandine » 25 Jul 2011, 15:38

Hi Nils,

I have updated the defect to include the problem that you reported with SQL Server.

Sorry for not replying to your other thread. As you probably know, my speciality is SQLBase, which is why I got involved in the thread in the first place. I don't scan the TD6 forums, so missed your other report. Please note that this was not an intentional slight.

Thanks for bringing this to our attention.

Best regards,

Mike

Mike Vandine

Re: TD-15967: Problem with TD 6.0, Sqlbase 11.6 and Long String Variable

Post by Mike Vandine » 08 Aug 2011, 03:36

This is slated to be released in the EMP5368 release. There is no fixed date for this release yet, but this should be soon; we are urgently working on two other EMPs now.

Best regards,

Jean-Marc Gemperle

Re: TD-15967: Problem with TD 6.0, Sqlbase 11.6 and Long String Variable

Post by Jean-Marc Gemperle » 09 Aug 2011, 12:47

Hi

Just for your information: You should see concatenation using LONGS and other related issues fixed in the soon coming EMP5363. I verified this testcase OK.
Note that this was a problem with
TD 5.2 SP4 RTM ( fixed now with EMP5360)
TD 5.2 SP3 was OK but problem started to show with EMP5361 see snapshot

What actually fixed that issue is TD-15968 I say this because there couple other bug entered that related to this affecting AFAIK all supported database at least SB ,SQLSERVER & ORACLE.

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

giscon

Re: TD-15967: Problem with TD 6.0, Sqlbase 11.6 and Long String Variable

Post by giscon » 09 Aug 2011, 13:29

Hi Jean-Marc,
in your lower picture (with build no. 25866) the string concatenation alone may be o.k., but not the bug with Oracle that the long value itself is truncated!
This truncation happens since SP3.

The content of the long varchar column in the testcase is "Testvalue" ( 9 characters) and therefore the string length should be 9 and the buffer length 20.
But the lower picture shows that still only "Testv" (5 characters only !) is selected from the Oracle database.
For these 5 characters the buffer length of 12 is o.k. and so the concatenation works, but these 5 characters are not the whole long string !

Hope that this bug will be fixed, too.

Regards
Guenter
Last edited by giscon on 09 Aug 2011, 14:16, edited 1 time in total.

Jean-Marc Gemperle

Re: TD-15967: Problem with TD 6.0, Sqlbase 11.6 and Long String Variable

Post by Jean-Marc Gemperle » 09 Aug 2011, 13:50

Hi

Thanks for notice this....was so focused on the concat issue that couple of customer reported, not the truncation...
Will verify this ASAP...

JM

Jean-Marc Gemperle

Re: TD-15967: Problem with TD 6.0, Sqlbase 11.6 and Long String Variable

Post by Jean-Marc Gemperle » 09 Aug 2011, 14:52

Re: Data truncated(Oracle) in Long String TD6 Sp3

Postby jmgemperle on Tue Aug 09, 2011 1:49 pm
Hi Again,

Yes there is a problem but I like to say *BUT* here :
Indeed the testcase truncates the LONG actually the testcase uses ORACLE LONG data type and not VARCHAR2 > 254 or CLOB...Now if you use those you would have no such problem (I tested it), not to say (ME) you should not use them but from ORACLE and MS LONG DATATYPE are deprecated since 9i...see

http://msdn.microsoft.com/en-us/library ... 10%29.aspx

Starting with the Oracle database 9i release, the LONG data type is deprecated. Oracle recommends using the LOB data types instead. Hence, when performing operations on the Oracle database using the Oracle Database adapter, we recommend using Oracle database artifacts that operate on LOB data types and not the LONG data type.



In any case this used to work in 6.0 SP3 or 5.2 SP3, broken in 6.0 SP3 EMP5361 a 5.2 SP4 and interestingly enough the SAME PROBLEM happens with LONG RAW as I entered recently an issue for that
TD-16022
ORACLE LONG RAW DATA truncation

You can test if you want with this : viewtopic.php?f=32&t=1735
It fails with LONG RAW but is OK with BLOB.
So it is the SAME issue and will add this TESTCASE (FOR TEXT) to this BUG and remove the "RAW" in description.

Basically we see problems with oracle LONG; but as far as I can see it is all OK with TEXT varchar2>254/CLOB and BINARY BLOB data types including concatenation now...

So I would recommend to use them and not the deprecated data types...soon or later will have problem if continue using them also I would believe from what I recall that you might see some performance issue with them...I'm not 100% sure but recall issues along those lines possibly depending the ORACLE client used..recall we certified 9i R2 but removed it...(as no longer supported by ORACLE) yet you still can connect with WIN32 using 9i but no one should do that...

Jean-Marc

Jeff Luther

Re: TD-15967: Problem with TD 6.0, Sqlbase 11.6 and Long String Variable

Post by Jeff Luther » 25 Aug 2011, 17:58

This defect is fixed with the EMP5365 release

Return to “SqlBase Bug Reports”

Who is online

Users browsing this forum: [Ccbot] and 0 guests