MySQL Bugs: #11846: DIAG [S1T00] Driver Failed to set the internal dynamic result (516)

Bug #11846	DIAG [S1T00] Driver Failed to set the internal dynamic result (516)
Submitted:	10 Jul 2005 17:25	Modified:	14 Feb 2008 16:29
Reporter:	Jeremy	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	3.51.21, 5.1	OS:	Windows (W2K)
Assigned to:	Jess Balint	CPU Architecture:	Any

Description:
Hi,
I'm using a Freeware-Program that works with ODBC and dynamic cursors or alternativ keydriven cursors (dynasets). I've tried to run a sql query (implemented in CRecordset by MFC with Visual C++ 6.0) within this application and the odbc driver sends the message "Failed to set the internal dynamic result". The following lines are copied from the SQL-Trace Utility:

HomeWorker      320-15c	EXIT  SQLBindCol  with return code 0 (SQL_SUCCESS)
		HSTMT               01B31D38
		UWORD                       13 
		SWORD                       -2 <SQL_C_BINARY>
		PTR                0x0164F730
		SQLLEN                   255
		SQLLEN *            0x01623CDC (0)

HomeWorker      320-15c	ENTER SQLExtendedFetch 
		HSTMT               01B31D38
		UWORD                        1 <SQL_FETCH_NEXT>
		SQLLEN                     1
		SQLULEN *           0x0162D708
		UWORD *             0x0164FB20

HomeWorker      320-15c	EXIT  SQLExtendedFetch  with return code -1 (SQL_ERROR)
		HSTMT               01B31D38
		UWORD                        1 <SQL_FETCH_NEXT>
		SQLLEN                     1
		SQLULEN *           0x0162D708
		UWORD *             0x0164FB20

		DIAG [S1T00] Driver Failed to set the internal dynamic result (516) 

HomeWorker      320-15c	ENTER SQLErrorW 
		HENV                01B31540
		HDBC                01B315E8
		HSTMT               01B31D38
		WCHAR *             0x0032E434 (NYI) 
 		SDWORD *            0x0032E6C0
		WCHAR *             0x0032E034 
		SWORD                      511 
		SWORD *             0x0032E6CA

HomeWorker      320-15c	EXIT  SQLErrorW  with return code 0 (SQL_SUCCESS)
		HENV                01B31540
		HDBC                01B315E8
		HSTMT               01B31D38
		WCHAR *             0x0032E434 (NYI) 
 		SDWORD *            0x0032E6C0 (516)
		WCHAR *             0x0032E034 [      48] "Driver Failed to set the"
		SWORD                      511 
		SWORD *             0x0032E6CA (48)

HomeWorker      320-15c	ENTER SQLErrorW 
		HENV                01B31540
		HDBC                01B315E8
		HSTMT               01B31D38
		WCHAR *             0x0032E434 (NYI) 
 		SDWORD *            0x0032E6C0
		WCHAR *             0x0032E034 
		SWORD                      511 
		SWORD *             0x0032E6CA

HomeWorker      320-15c	EXIT  SQLErrorW  with return code 100 (SQL_NO_DATA_FOUND)
		HENV                01B31540
		HDBC                01B315E8
		HSTMT               01B31D38
		WCHAR *             0x0032E434 (NYI) 
 		SDWORD *            0x0032E6C0
		WCHAR *             0x0032E034 
		SWORD                      511 
		SWORD *             0x0032E6CA

HomeWorker      320-15c	ENTER SQLFreeStmt 
		HSTMT               01B31D38
		UWORD                        1 <SQL_DROP>

HomeWorker      320-15c	EXIT  SQLFreeStmt  with return code 0 (SQL_SUCCESS)
		HSTMT               01B31D38
		UWORD                        1 <SQL_DROP>

HomeWorker      320-15c	ENTER SQLDisconnect 
		HDBC                01B318E0 

..... and so on.
The table has no data and the query should return only SQL_NO_DATA_FOUND.
The table has only varchar fields with a length of up to 50 Byte each. The odbc driver could have a memory allocation error, I guess. Right now, no usage of the following table is possible.
Regards
  jeremy_steinbergde@yahoo.de

How to repeat:
Here is the SQL-Statement to create the table to test this behaviour.
Important: U should set "use dynamic cursor" in the odbc administrator utility.

CREATE TABLE IF NOT EXISTS `users` (
  `UserID` varchar(15) NOT NULL default '',
  `Name` varchar(30) default '',
  `Vorname` varchar(30) default '',
  `Strasse` varchar(50) default '',
  `Hausnummer` varchar(5) default '',
  `Plz` varchar(8) default '',
  `Wohnort` varchar(50) default '',
  `Geburtstag` varchar(10) default '',
  `PWD` varchar(20) default '',
  `Bank` varchar(50) default '',
  `Blz` varchar(8) default '',
  `KtoNr` varchar(10) default '',
  `Waehrung` varchar(50) default '',
  PRIMARY KEY  (`UserID`)
) TYPE=MyISAM COMMENT='Programmbenutzer'; 

Suggested fix:
Im not sure about the reason for this problem. If I had the source code (windows only) of the odbc driver, I could debug the driver on run and could send you a fix, or at least the statements, where the driver got the problem.
I'm unable to work on unix sourcecodes, 'cause I'm just a Windows developer and cannot spend a lot of time to reduce the sources at windows level.
I hope you can reproduce the error and fix it, so I can go on with testing MySQL.
The MySQL server itself looks very good and has a lot of nice features, that are very interesting for me. But I'm using just ODBC-driver-connections into databases with my programs, so I have to keep my MSDE-database, until there's a fix or workaround available.

Could you please send some test case to reproduce the bug ? MFC is not problem for us.

Hi,
I'm sorry, but there's no test case available. Like I wrote before, I'm just using a  Freeware Software to check this error out. 
Is it possible to get the windows "only" source code of the odbc driver? So I could try to debug it against the software and maybe I'll find out what the driver is missing.
Regards
  Jeremy

You can download sources from url:

http://dev.mysql.com/get/Downloads/MyODBC3/MyODBC-3.51.11.zip/from/pick

But We don't have exactly windows sources.

Hi,
I downloaded the sources for myodbc and tried to build them with VC 6.
I'm sorry but this sources can't be build under windows.
The driver build fails cause the file my_dbug.h is missing, and myodbcinst
returns with the build error "2 unresolved externals" :
 _MYODBCUtilGetDriverNames
_MYODBCUtilGetDataSourceNames.

I can only help to find out the error, if I got a source version that can be build under windows without any errors.

Regards
  Jeremy

P.S.: QT is not available, and I don't like to download and build it, cause I do not need it.

I have to ask odbc developer about dynamic cursors. I created test case but I can't set statement attribute (SQL_ATTR_CURSOR_TYPE).

My test case is attached. It was verified on 3.51.12.

Test case

Attachment: test.c (text/plain), 4.00 KiB.

Any development on this? I am trying to adapt a payroll system that works with MS Jet and MS SQL server to run on My SQL and run into the same problem:

Driver Failed to set the internal dynamic result
State:S1T00,Native:516,Origin:[MySQL][ODBC 3.51 Driver][mysqld-4.0.24a]

The app is written in C++ VC 6.0 with MFC and uses recordsets derived from CRecordset. It falls over at the initial opening of any database:

Lp              94c-bf0	ENTER SQLExtendedFetch 
		HSTMT               01053708
		UWORD                        1 <SQL_FETCH_NEXT>
		SQLLEN                     1
		SQLULEN *           0x0012F4D8
		UWORD *             0x0BFC0EE8

Lp              94c-bf0	EXIT  SQLExtendedFetch  with return code -1 (SQL_ERROR)
		HSTMT               01053708
		UWORD                        1 <SQL_FETCH_NEXT>
		SQLLEN                     1
		SQLULEN *           0x0012F4D8
		UWORD *             0x0BFC0EE8

		DIAG [S1T00] [MySQL][ODBC 3.51 Driver][mysqld-4.0.24a]Driver Failed to set the internal dynamic result (516) 
I am testing on Win2000. The database runs on NW 6.5

Just to add to the above. I get the same error with MySQL 5.0.17 on Windows 2000. If I change all opens to "snapshot" then it runs, but that is naturally not a viable option.

The problem isn't solved yet. I've downloaded the new version 3.51.12 and installed it on w2k. There's no change between 3.51.11 and 3.51.12. Maybe you should check out the memory handling of your code. Fact: your new driver produces c++ exceptions when you try to open the my-sql tables with a dynamic result set (use of cursor library!).
I'll hope you can fix up the problems, 'cause without this changes, there's no way to use my-sql on windows platforms with odbc driven support.
Regards
 Jeremy

In addition to the previous mail: Use the MFC class CRecordset with cursor type = dynaset to create a valuable testcase. Don't forget to create tables with primary key's on it. To open the tables use a command like that:
CRecordset::Open(CRecordset::dynaset);
If you sublcass CRecordset with an own class use the member
m_nDefaultType = dynaset;
To test without cursor library you can use
m_nDefaultType = dynamic; 
instead.
Regards
  Jeremy

We need a full driver trace for this problem, not just the snippets that have already been provided.

Why do you need a full trace? You have a complete sample program you can run. Granted, I could try to create one, but that means that I'd have to set up a test bed again as I have changed computer since I logged this bug 2 years ago!

I don't see a "complete sample program" associated with this bug. The code attached by Vasily does not demonstrate the reported bug at all, even if it is changed to use dynamic cursors.

OK. Let me dig out some code and post back.

Anders, thanks. Waiting on your test case.

OK. Here is the sample code:

	m_database.OpenEx(_T("DSN=Test;PWD=")+m_Losenord+_T(";UID=")+m_Anvandarnamn);

	CStatvarSet statvarSet;
	statvarSet.m_strFilter="Nr=?";
	statvarSet.m_Nrfilter=39;
	statvarSet.m_pDatabase = &m_database;
	//statvarSet.Open(CRecordset::dynamic);
	statvarSet.Open(CRecordset::snapshot);
	if(!statvarSet.IsEOF()) {
	}
	statvarSet.Close();
	m_database.Close();
	CDialog::OnOK

It will fail when set to "dynamic" but work OK as snapshot for example. The sample table looks like this:

CREATE TABLE LP_STATVARIABLER (Nr INTEGER NOT NULL, Namn CHAR(30) NULL, Enhet INTEGER, Nollas INTEGER, Per_kommun INTEGER)
GO
CREATE UNIQUE INDEX PrimaryKey ON LP_STATVARIABLER (Nr)
GO

I have uploaded a complete VC2005 project for you to play with.

VS2005 sample to show ODBC driver bug

Attachment: MySQLTest.zip (application/zip, text), 50.68 KiB.

Original testing was with the .12 driver. Downloaded the latest, .20 one, same thing:

DBMS: MySQL
Version: 4.0.26a
ODBC Driver Manager Version: 03.52.0000
Driver Failed to set the internal dynamic result
State:S1000,Native:517,Origin:[MySQL][ODBC 3.51 Driver][mysqld-4.0.26a]

Any development on this?

Test case VS2005

Attachment: bug11864.c (text/x-csrc), 4.01 KiB.

Hello all.

We do not support dynamic cursors yet so the next best case we can do is shown in output from my attached test case (Option value changed to default static cursor):

Connect....
Test....

INFO: (.\NokkiaIssue9159\bug11864.c:40): SQLSetStmtAttr()
*** [MySQL][ODBC 3.51 Driver][mysqld-5.0.50-pb1046-log]Option value changed to default static cursor
*** ODBC Code: 01S02, Driver Code: 502

Disconnect....

I am setting this to "Verified" even though I am convinced there are other reports on same problem in bugsdb. Same happens with 5.1 Aplha.

When you say "We do not support dynamic cursors yet", does that mean that you plan to do so in the future? I am afraid that our app will not work with static cursors as it is a multi-user app.

Anders, I share your fears and hopes too but I can not tell when this problem will be fixed. Let's wait for developers to join discussion.

I got a notification that this bug was modified Jan 5, but can see no additional info here? What exactly has been modified?

fix + test (depends on bug#33388 fix)

Attachment: bug11846.diff (application/octet-stream, text), 2.63 KiB.

Note that this patch depends on the fix for bug#33388 which is not yet committed into the source repository. At the moment, this patch only addresses Connector/ODBC 5.1.

Fix committed in rev1013. Will be released in 5.1.2.

A note has been added to the 5.1.2 changelog: 

Dynamic cursors on statements with parameters were not supported.