TD-17726: Inconsistencies between bound and unbound strings in Sybase

[Brian Howard]

Hi All,

With the attached, I am inserting into a Sybase db a non-printable character as part of a string. The character is 0xFE. The entire string is "232643þ001þ62" and TD will insert either "232643þ001þ62" or "232643û?001û?62" depending on whether or not the variable is bound into the dml or concatenated as a literal string, depending on where char_convert is set on or off and also depending on whether SybExecuteProc or SqlPrepareAndExecute is used.

We have to have "set char_convert off" otherwise our applications will crash and burn at inconsistant times ( I have so far been unable to create a reproducable case for this ), which means our code must use SybExecuteProc and a bound variable to get, what I believe is the correct behaviour.

Could you please have a look at this and advice if this is a defect or not.

regards

Brian

[Jeff Luther]

(I'm just looking at my reply again and have added a couple comments.)

First off, Brian, TD is in Unicode and all Unicode strings begin with the 2 chars 0xFF & 0xFE. Those are what identifies it as a Unicode string. ( I guess my point was that 0xFE is one of the 2 BOM -- byte order mark -- chars for a Unicode string and it's possible that that may contribute to a problem with the concat. of the string literal?)

I ran your test case and it's a little difficult to understand (I am not a SYBASE expert), but using SQLTalk to find out what got inserted, of the 4 rows inserted it looks like ID = 3 is the correct string, which apparently corresponds to this INSERT in your code I believe:

Insert into bh_1 ( s, bound, syb, conv ) Values ( :s, 1, 1, :cb1 )", ""

Is this what you see too?

advice if this is a defect or not

Is what a defect? That inconsistent 'crash and burn' you can't reproduce? Or the fact that the above INSERT is the correct form to use to get your string into the DB correctly?

I would say you found a way that works; I assume it's ID = 3 that I see, right?

The other issue I wanted to mention -- again because of Unicode -- that you might find that NVARCHAR is a better DB data type for you to use with TD Unicode characters: http://dcx.sybase.com/1200/en/dbreferen ... rying.html
Before I ran your test the VARCHAR column type gave me pause a bit, too. But as I said, your INSERT with ID = 3 appears to work for a VARCHAR column.)

[Brian Howard]

Hi Jeff,

Thankyou for your prompt response and thanks for the explanation regarding BOM.

When I asked "is this a defect" I was referring to the inconsistencies in the values being inserted depending on whether or not the column was bound etc.

The valid insert, in my opinion, and as you suggest, is the bound dml statement. It is also the safer option.

With regard to the "crash and burn" I mentioned, I may have found a way to reproduce this and hopefully will log a new forum item soon.

Please not the 0xFE value is recieved in a file that is generated by a very old external system and there is nothing we can do about it, we simply have to deal with it.

regards

Brian

[Jeff Luther]

Brian: - update and 2 months or more since your issue was reported as a defect. Neither the router/DB developer nor I can repro. your test case now with our internal Sybase and my testing with v6.0 SP6, current release of v6.0.

Your report + the initial report from another Sybase user was reported as defect TD-17726 and this is what the developer is now looking at. So, let me request 2 things from you:

1 -- Retest with v6.0 SP6 and let me know if you can still reproduce the issue where only the 3rd row in the DB test table gets inserted/fetched OK. Now for us all 4 rows -- with the checkbox UNchecked -- return VALID

2 -- Is your Sybase DB non-Unicode or Unicode? We are testing with a Unicode DB, but then again so was I (same DB) back in April when I could repro. your issue.

[Brian Howard]

Hi Jeff,

Apologies for the delay, I have been away.

I still get inconsistencies using SP 6 as you can see with the image below.

nmcSp6.jpg

But we have coded around this now, and only raised this so others would not get hit with the same issue, and to help you improve your product. So if these inconsistencies are not an issue with Unify, then I am not going to push for any further fix. Note the incosistency is now only when char_convert is not switched on.


cheers

[Jeff Luther]

you wrote:

we have coded around this now

and

Note the inconsistency is now only when char_convert is not switched on.

I was going to ask for a brief summary on what/how you "coded around" but maybe it's basically to not have char_convert ON ?

If a user comes up with a solution, fix, workaround to an issue I think it can be help for 'immortality' to add this to the general knowledge based rep'd by this forum. Thanks.

[Brian Howard]

Hi Jeff,

We have to always have char_convert on and to be safe we also always bind.

cheers

[Jeff Luther]

Thanks for the info, Brian!