Bug #12514 Unknown SQL Type
Submitted: 11 Aug 2005 7:55 Modified: 16 Nov 2007 10:32
Reporter: Yuri Sprague Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:5.0 OS:Windows (Win 2003)
Assigned to: CPU Architecture:Any

[11 Aug 2005 7:55] Yuri Sprague 
Description:
When I run queries from the app I'm creating using MyODBC 5.0 with zero values in any of the fields, I receive the error, "Unknown SQL Type - 0". An example of such a query is, "SELECT ROUND(fPresentHousingPayment, 2) fPresentHousingPayment, ROUND(fPANDI, 2) fPANDI FROM vw_Loans WHERE sLoanNumber = '201002388'".  The queries run without trouble in Query Browser or from the SQL command line, so it's definitely a problem with the ODBC.  I had a similar problem with the latest release of MyODBC 3.51.

How to repeat:
Run a query similar to the above-mentioned through MyODBC 5.0.  The value in the numeric fields is zero.
[11 Aug 2005 8:43] Vasily Kishkin 
Could you please say me you reported about the bug early? I mean MyODBC 3.51. If not - could you please write here table definition of vw_Loans ?
[11 Aug 2005 9:05] Yuri Sprague 
Here are the definitions you requested.
[12 Aug 2005 1:22] Yuri Sprague 
Any idea as to the cause?  This problem has essentially put a stop to any significant development for the project I'm working on.
[22 Aug 2005 9:22] Vasily Kishkin 
Sorry. I was not able to reproduce the bug. I attached my test case in "Files". Could you please build and run my test case ?
[22 Aug 2005 9:23] Vasily Kishkin 
Test case

Attachment: test.c (text/plain), 5.29 KiB.
[22 Aug 2005 11:19] Yuri Sprague 
I tried building the code in test.c in a new Visual C++.NET project - I don't want to work with pure C - and received the error, "c:\Downloads\MySQL\Bugs\12514\Test\Test\Test.cpp(113): error C2664: 'SQLSetEnvAttr' : cannot convert parameter 3 from 'SQLINTEGER' to 'SQLPOINTER'" for the statement `CHECKRC( SQLSetEnvAttr(hEnv,SQL_ATTR_ODBC_VERSION,Version,0 ), "SQLSetEnvAttr()" );`.  When I did a cast conversion, i.e., when I changed the statement to `CHECKRC( SQLSetEnvAttr(hEnv,SQL_ATTR_ODBC_VERSION,(SQLPOINTER)Version,0 ), "SQLSetEnvAttr()" );`, I received the warning, 'warning C4312: 'type cast' : conversion from 'SQLINTEGER' to 'SQLPOINTER' of greater size`.  I stepped through the code and when this line was processed, the program skipped down to the `if (error) exit (1);` line, so nothing was accomplished. What do I do now to get the Version variable to be correct?

Also, please see `MySQL Forums->ODBC->Unknown SQL Type` for more input from other users who have experienced the same SQL error as I.
[23 Aug 2005 1:09] Yuri Sprague 
By substituting the constant 0x00000003 for Version in the call to SQLSetEnvAttr, the project built without errors.  When I ran it, however, I found that the value of zero in the column D in the test_dec table and test_dec_view view was not returned.  The output is printing as zero in the DOS box because the format specifier in the printf function is %d, which is for a decimal integer, when it should be %e for a double.  I defined the double variable d as -1 in the declaration and -1 was always output, indicating the calls to SQLFetch and SQLBindCol are not working correctly.  I've attached my test.cpp file and a screen shot of the output for you to examine.
[30 Aug 2005 8:32] Vasily Kishkin 
I've got follow result of your test case:

D:\STORE ODBC FILES\12514\user>test.exe
Connect....
Test....
testing table...
d = 0
testing view...
d = 0
Disconnect....

it seems right. Maybe it is not ODBC problem ? Could you please say me exact version of mysqld ?
[31 Aug 2005 7:56] Yuri Sprague 
mysqld-nt 5.0.11.  Have you checked out `MySQL Forums->ODBC->Unknown SQL Type` for more input from other users who have experienced the same SQL error as I?
[31 Aug 2005 8:00] Yuri Sprague 
Though I originally was using version 5.0.9.
[1 Sep 2005 3:24] Yuri Sprague 
I tested this on a PC running XP SP2 and server version 5.0.11 and get almost the same result.  I also now get the messages 'Unknown SQL Type - 65535' on text fields and 'Unknown SQL Type - 65530' on tinyints.  I retested on Win 2003 and the same thing occurred.  I also ran the troublesome queries in Query Browser and on ta SQL command line and they both returned results without any problems, which leads me again to state that there's a problem with ODBC 5.  If you run another test, please code in MS Visual C# to determine if C#'s interaction with ODBC may be at least part of the cause - and, again, please look at the aforementioned forum entry which has messages from people having the same problem.
[22 Sep 2005 8:59] Vasily Kishkin 
Sorry...I was not able to reproduce the bug. i created test case on C#. It works fine. The result of new test case is follow:

testing table...
Data: 0.00
Type: decimal

testing view...
Data: 0.00
Type: decimal

I attached my test case in "Files".
[22 Sep 2005 9:00] Vasily Kishkin 
C# test case

Attachment: 12514.zip (application/x-zip-compressed, text), 5.63 KiB.
[15 Oct 2007 16:05] Susanne Ebrecht 
Hi,

could you please try this with the newest version MyODBC 5.1 and tell us, if the error still occur?

Regards,

Susanne
[16 Nov 2007 0:00] Bugs System 
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[14 Jan 2008 23:10] Ling Lin 
It looks like a ODBC driver error. Update your ODBC driver to the latest one: mysql-connector-odbc-3.51.23-win32.msi.