TD-18616: MS SS ERROR mixed TEXT & VARCHAR in Select

[MSchmidt]

System:
TD 6.1 .Net WIN32 Mode
ODBC to MS Sql Server 2008

sample:
Select city,remark,street,description from TABLE into :df1,:ml1,:df2,:ml2 where ....

city: varchar(50)
remark: text
street varchar(50)
description varchar(5000)

df1 string datafield
ml1 multiline field
df2 string datatfield
ml2 multiline field

in this case the content of ml2 is not readable. it shows "asian" letters.

if you change the order of the fields in the select to:
Select city,description,street,remark from TABLE into :df1,:ml2,:df2,:ml1 where ....
the field ml2 is readable

so a in a select statement the normal situation of mixed datatype is impossible.

conclusion:
In a select varchar(>250) fields don't show readable content in multiline fields, if in the select statement the varchar fields (>250) are placed after a text field.

regards
M.Schmidt

[Jeff Luther]

We need a test case for this. "Asian" chars. indicate ANSI chars, so first of all the ML must be Long String. Other than that, what type of LONG data are you trying to store and retrieve?

so a in a select statement the normal situation of mixed datatype is impossible.

Not true as far as I know, not if you correctly use SqlSetBindDataType, etc. Otherwise, I'd have to see it to believe it (and that's why a test case is needed).

Select city,remark,street,description from TABLE into :df1,:ml1,:df2,:ml2 where ....

That, however, says nothing about what type of data are in your TABLE to begin with. Is it old ANSI data?

I suggest a full test case:
* create test DB table with appropriate columns of interest: ID, city, description
* insert a row
* fetch a row

[MSchmidt]

Ok Jeff

here is the test case with a sql script to make the table with one line of content in a SQL Server DB. (2005,2008 or 2012).

Please use ODBC for testing.

There you can see and "believe".

Again: if you change the order of the columns in the select statement and if you place the text column at the end (or after the varchar(5000) column) all is working fine.

regards
M.Schmidt


P.S.
In my first note you could find the details of the datafield in the DB.
So i don't know, why you ask for "type of data"

[Jeff Luther]

I will look at this tomorrow, Hr. M.

So i don't know, why you ask for "type of data"

Well, that's because I wanted to see the actual DB column type, but someone might refer to "TEXT" when the column type could be text, ntext.

** See how this page: http://msdn.microsoft.com/en-us/library/ms187993.aspx
defines MS SS TEXT as: "Variable-length non-Unicode data..."

** With out being able to see *exactly* and test -- and I have to go by a description only -- that scary English word assumption comes to my mind. Either I have to make one (and sometime I do not know that I am!), sometimes the customer has (perhaps) made one, etc.

So, those 2 bullets above are why I asked. I'll look at your test tomorrow to better understand the issue. Thanks much for the test case.

[Jeff Luther]

I looked at your test case and right you are! The issue you are reporting for MS SQL Server DB table with a REMARK TEXT, DESCRIPTION VARCHAR(5000)

If the REMARK column in the SELECT column list before DESCRIPTION then DESCRIPTION's value is fetched back looking like ANSI/'garbage' chars
(You had reported that DESCRIPTION has to be last in the list. I tested and see it does not matter where DESCRIPTION is, only that it come after REMARK in the list.)

If DESCRIPTION comes after REMARK in the SELECT list or REMARK is not in the list, DESCRIPTION is fetched and displays in the ML field OK.

I see the same issue with both current v6.1 SP2 and current v6.0 SP7. It's a problem all right; fortunately there's an easy workaround in the meantime.

I will enter in a defect for this issue. New defect: TD-18616

[MSchmidt]

please have a look at this problem.

even there is a workaround we have lots of problems with the issue.

Our customers have self made reports.
Cause a specific order of table columns in a select statement can cause problems,
they have lots of reports running many years without any failure, but now in the
environment of TD 6.1 the printed output shows non readable chars.

And it's a pain, to change the order of input items of many reports, a customer has created.


So please solve the problem in the near future.

kind regards
M.Schmidt

[Mike Vandine]

This is verified fixed in the TD6.1 SP4 release.