MySQL Bugs: #23398: SQLDescribeCol(W) not working

Bug #23398	SQLDescribeCol(W) not working
Submitted:	17 Oct 2006 22:28	Modified:	21 Nov 2006 21:10
Reporter:	Frank Maas (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	5.0.5, 5.0.6, 5.0.7?	OS:	Windows (Win2000)
Assigned to:		CPU Architecture:	Any

Description:
While testing this new version for bug #20963 I found that I was able to issue a SELECT, but that I got no results, or better that the application sees no results. An SQL.LOG file is attached (for developers) and the same code works flawlessly using 3.51.

How to repeat:
I have no means other than my application to try. I tried another simple query

SELECT count(*) FROM t1

got the result bound and printed the result. That should have read "25" but I got "0¿{?" or <48><168><123><2> in decimal ASCII.

Hi Frank and thanks for your problem report.
What client are you using?
Using default MS ODBC client odbct32.exe I am getting correct results:
SELECT count(*) FROM a

	Full Connect(Default)

	Env. Attr. SQL_ATTR_ODBC_VERSION set to SQL_OV_ODBC3

	Successfully connected to DSN 'Test5on5'.
SQLExecDirect:
				In:				hstmt = 0x00851A10, szSqlStr = "SELECT count(*) FROM a", 
										cbSqlStr = -3
				Return:	SQL_SUCCESS=0

Get Data All:
"count(*)"
2
1 row fetched from 1 column.

I am using an IVR application build with APEX software that offers an ODBC interface. If you can point me to an available resource for testing the ODBC connection with other means, then feel free. I have no idea where to find the odbct32.exe you mention (searched the Net, but found only references to it). The platform is running Win2003, so please recomment and I'll get back to you with results.

Regards,
Frank

Frank, I got it with MS Visual studio 2005 Pro. Maybe it's a part of express edition too.
Feel free to reopen this report if you have any more info to provide.

Thanks for your interest in MySQL.

Well.... I found some tool to check an ODBC connection and indeed I was able to fetch results. So I am in the situation where a 3rd party product is able to fetch results through MyODBC 3 and not through MyODBC 5... where lies the error??

I am reopening this bug with attached an SQL logfile that contains the output from both the test request ('SELECT appcode, 'test' FROM app_desc') and an created test via the third party product (same statement, without the column 'test'). I am not sure that the bug really lies within '5', but if it does or if its behaviour can be easily changed, future problems with (other) 3rd party products might be circumvented.

At the same time I will contact support for the 3rd party product to see what they come up with. I'll keep the bug report updated with news from that side.

Regards,
Frank

Frank, thanks for the log. Please, do not open new bug report. You can always change synopsis and status of this one.

@Tonci: I wasn't planning on opening a new report and will add any result to this report. However, I cannot change the status of this bug either: apart from "Can't repeat" I can only choose to Close it. Which I do not want either...

Changing status back to "Open" so that synopsis can be changed.

Changed synopsis. Changed severity (although it is still critical for me ;-) )

It seems that MyODBC 5.0.5 does something different when returning results than v3. With a simple ODBC test program I managed to execute a query and to get results. But with a different tool (read: IVR application) I could not get any results other than garbage. The same tool runs flawlessly with ODBC v3 (same query, same database). Logfiles previously attached. Issue is reported to supplier of IVR system as well.

Tonci,

As you requested not to open a new bug, I will share my thoughts here - but perhaps this bug is not what it seems to be. I have tried to trace down the problem I experience via logfiles and the source code. I find two things, based on this result of SQLDescribeCol:

EXIT  SQLDescribeCol  with return code 1 (SQL_SUCCESS_WITH_INFO) 
  HSTMT               02204BF8
  UWORD                        1 
  UCHAR *             0x00000000 
  SWORD                       66 
  SWORD *             0x00000000
  SWORD *             0x007EDE68 (12)
  SQLULEN *           0x007EDEE4 (0)
  SWORD *             0x007EDEE2 (0)
  SWORD *             0x00000000

First of: this call is made with a NULL pointer for the buffer to store the column name. This probably worked in 3.x (I did not check that source), but in v5 it throws a warning. That warning is STATE_01004, but the displayed diagnostics is that of 01003. Could there be some sort of mixup between the (might I say) rather complex translation of states?

Secondly: this call seems to return 0 for the column length. Could it be that getLength() is malfunctioning? Because if it is, then the following might be important. One of the differences between the query via the third party product and via the test-tool is that the latter does not use column binding, where the first does. Not using bound columns, the testtool does a getData call with a fixed bufferlength which retrieves results. But with bound columns the MyODBC call makes use of getLength to set that buffer length.
I have tried to get into the functioning of this all, but without a good debug facility and the ability to put breakpoints this is rather hard.

So just my 2cts, I hope it is not too much of a sack of garbage.

Regards, 
Frank

Just tested with 5.0.6-beta: things got worse. The SQLDescribeCol - call now lets the application crash completely. From the SQL trace file I can see that the SQLDescribeCol is entered, but never exited. If you have a debug-version of the .dll, I might give that a go?

Frank - Thanks alot for the feedback. We have fixed some of the logic regarding the issues you are seeing. We will have a new release very shortly for you to try.

Frank, setting the report back to "Open" so you can test again when new version is released.

Tried it with 5.0.7 and problem persists. But please see bug #24082 as I am far from sure that I am really using the new version.

Hi Frank. Can we change the synopsis to SQLDescribeCol(W) not working? In that case I would set this report to verified since I also hit that problem.

Changed synopsis:
Problem seems to be in SQLDescribeCol or a supporting routine

Changed version:
Problem was detected in 5.0.5, but 5.0.6 is experiencing similar behaviour. 5.0.7 seems to be the same as 5.0.6 (see bug #24082), at least the behaviour is the same.

Changed Severity:
It seems that some apps can retrieve results via the Connector, hence the previous S2 state. But starting with 5.0.6 my app simply crashes when calling SQLDescribeCol which makes it rather critical.

Verified. Test case is on internal wiki page.

Successful fetch of data in 5.00.08 and 5.00.09.