SQL fieldtype changes when a query is modified to sort; GetData returns nothing

[ic2]

I posted this in comp.lang as well; this is the same with one addition:

I use the following code to read data from a MySQL database over ODBC
in my VO program:

oConn:=SQLConnection{"Myodbc",cDB,cpw}

cSQL:="SELECT somefield,text FROM Myview where Keyfield is null

oSelectView:=SQLSelect{ cSQL, oConn }
oSelectView:Gotop()
cField:="text"
cFieldType:=oSelectView:FIELDINFO( DBS_TYPE, cField)
cContent:=oServer:Getdata(cField) // Content of field 'text'
correctly assigned

This has always worked. The text field is of type 'text' and returns
"C" in the above FieldInfo statement.

Now my client wanted to insert the data in a specific order and asked
me to change the query as follows:

cSQL:="SELECT somefield,text FROM Myview where Keyfield is null ORDER
BY leadid ASC

So basically it only differs with this added:

ORDER BY leadid ASC

The result is quite strange. While GetData still retrieves the content
of MySQL varchar fields (like 'somefield' in the above query) this is
not the case for the text field anymore. The returned fieldtype
changed from "C" to "M" and the GetData now returns an empty content.

I emphasize that the query does not give a different result (e.g. if
executed in MySQL Workbench where we have one test view record
ready), also not in the VO program where the other fields return
content as before.

What could cause an ORDER BY addition to give this problem?

Addition: We replaced 'text' with CAST(tekst as CHAR(10000)) as tekst but this did not solve the problem.
Dick

[robert]

Dick,

I think the ODBC driver returns different column descriptions for the 2 sql statements.
The VO SQL Code responds to the difference by giving the column a different type.

I am not sure what the structure of the table is, but my past experience has shown me that it usually is a good idea to include the primary key column in the column list.
And what happens if you do not select the columns from 'MyView' but directly from the underlying tables ?

The fact that the results of these select statements look the same in the MySql Workbench only tells you that the syntax is ok. MySql Workbench does not use ODBC to talk with the database, but uses the MySQL API.
My guess is that the problem is somewhere in the ODBC driver.
Which version of the ODBC driver are you using ?
For my customers I have seen big differences between different versions of the ODBC drivers.

Robert

[ic2]

Hello Robert,

I've forwarded your reply to the developer who is doing the web/MySQL site and he thought your remarks make a lot of sense.

But he also found that on delivering the unsorted data the 'added' data needn't be exported which meant that the reason we added the ORDER BY was not needed anymore. So we restored the original working code. I'll keep your remarks for future reference!

Dick

[ohernandez@sistemas-liasa.com]

Dick,
I had the same problem getting info from the system objects y ADS, the cicharacter returned empty fields.
What I did was to create an empty structure and insert into it.
Something like:
part of the script...
"SELECT CONVERT(SPACE(200),SQL_CHAR) NOMBRE, FALSE Table_Trans_Free, 0 CONTADOR INTO {2} FROM SYSTEM.IOTA WHERE 1=0; "+CRLF+;
"INSERT INTO {2} SELECT NAME, Table_Trans_Free,0 FROM SYSTEM.TABLES; "+CRLF

In this case NAME is CICHAR
Later I replace {n} with a function like String_format.