MySQL Bugs: #3291: SQLFetchScroll generates error 516 when used on dynamic cursor

Bug #3291	SQLFetchScroll generates error 516 when used on dynamic cursor
Submitted:	25 Mar 2004 4:44	Modified:	2 Jul 2004 0:06
Reporter:	Ben Weijers	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	3.51.06.00	OS:	Windows (Windows XP)
Assigned to:	Timothy Smith	CPU Architecture:	Any

Description:
I am using MyODBC 3.51.06.00 to connect to MySQL 4.0.15 running local on a
Windows XP machine.

I am using a table defined as:

+--------------+----------+------+-----+---------+----------------+
| Field        | Type     | Null | Key | Default | Extra          |
+--------------+----------+------+-----+---------+----------------+
| RECNUM       | int(11)  |      | PRI | [NULL]  | auto_increment |
| ID           | int(11)  |      | UNI | 0       |                |
| NAME         | char(30) |      |     |         |                |
| U_NAME       | char(30) |      | MUL |         |                |
| ADDRESS      | char(30) |      |     |         |                |
| CITY         | char(14) |      |     |         |                |
| STATE        | char(2)  |      |     |         |                |
| ZIP          | char(10) |      |     |         |                |
| PHONE_NUMBER | char(20) |      |     |         |                |
| FAX_NUMBER   | char(20) |      |     |         |                |
+--------------+----------+------+-----+---------+----------------+

 It contains data

+--------+----+-----------------------+-----------------------+-----------------------------+---------------+-------+-------+----------------------+------------+
| RECNUM | ID | NAME                  | U_NAME                | ADDRESS                     | CITY          | STATE | ZIP   | PHONE_NUMBER         | FAX_NUMBER |
+--------+----+-----------------------+-----------------------+-----------------------------+---------------+-------+-------+----------------------+------------+
|      1 |  1 | Books-R-Us            | BOOKS-R-US            | 456 N.W. 156th Ave.         | Miami         | FL    | 44444 | (305) 222-7532       |            |
|      2 |  2 | Book World            | BOOK WORLD            | 6501 S.E. 25th Ave.         | Coral Gables  | FL    | 44427 | (305) 445-9723       |            |
|      3 |  3 | The Book Emporium     | THE BOOK EMPORIUM     | 145 N.E. 56th AVE.          | Coral Springs | FL    | 67823 | (407) 223-5678       |            |
|      4 |  4 | Technical Book Supply | TECHNICAL BOOK SUPPLY | 14 E. Main Street           | San Diego     | CA    | 91941 | (800) TECHBOOK       |            |
|      5 |  5 | Construction Resource | CONSTRUCTION RESOURCE | P.O. Box 1792               | Denver        | CO    | 92345 | (800) 545-7236       |            |
|      6 |  6 | Ace Publications      | ACE PUBLICATIONS      | 4514 Lombard St - Suite 123 | San Fransisco | CA    | 92014 | (415) 456-8765 x.888 |            |
|      7 |  7 | Software Publishing   | SOFTWARE PUBLISHING   | P.O. Box 123                | Bostn         | MA    | 91234 | (800) 567-9875       |            |
|      8 |  8 | Paperback Merchant    | PAPERBACK MERCHANT    | 2734 Center Blvd.           | Fort Worth    | TX    | 87632 | (404) 879-9999       |            |
|      9 |  9 | Business Work Books   | BUSINESS WORK BOOKS   | 9182A A. Street             | San Diego     | CA    | 91920 | (619) 555-1231       |            |
|     10 | 10 | Olde Book Merchant    | OLDE BOOK MERCHANT    | 1371 Market Street          | San Fransisco | CA    | 92314 | (415) 555-9823       |            |
+--------+----+-----------------------+-----------------------+-----------------------------+---------------+-------+-------+----------------------+------------+

Issuing a select statement on this table that has a where clause, using a
dynamic cursor I get error:
   HY000 - 516 - Driver Failed to set the internal dynamic result
when I use SQLFetchScroll no matter what fetch direction is used.

I experimented by replacing the call to SQLFetchScroll in a call to SQLFetch.
If I do that, no error is generated.

I am extracting the part of the code that is relevant. The program does:

 SQLSetStmtAttr (hstmt, SQL_ATTR_CURSOR_TYPE, (SQLPOINTER)SQL_CURSOR_DYNAMIC, SQL_IS_UINTEGER);
 SQLSetStmtAttr (hstmt, SQL_ATTR_CURSOR_SCROLLABLE, (SQLPOINTER)SQL_SCROLLABLE, SQL_IS_UINTEGER);
 SQLPrepare (hstmt, "select * from vendor where id > 3 order by id", SQL_NTS);
 SQLExecute (hstmt);
 SQLFetchScroll (hstmt, SQL_FETCH_NEXT, 0);

How to repeat:
I am extracting the part of the code that is relevant. The program does:

 SQLSetStmtAttr (hstmt, SQL_ATTR_CURSOR_TYPE, (SQLPOINTER)SQL_CURSOR_DYNAMIC, SQL_IS_UINTEGER);
 SQLSetStmtAttr (hstmt, SQL_ATTR_CURSOR_SCROLLABLE, (SQLPOINTER)SQL_SCROLLABLE, SQL_IS_UINTEGER);
 SQLPrepare (hstmt, "select * from vendor where id > 3 order by id", SQL_NTS);
 SQLExecute (hstmt);
 SQLFetchScroll (hstmt, SQL_FETCH_NEXT, 0);

Problem text

Attachment: mysqlbug.txt (text/plain), 4.15 KiB.

I tried to repeat this, both on FreeBSD and Windows XP.

When I run my test program (which I am attaching to this bug - check the Files section), I get 7 rows fetched (with ID 4 - 10).

Can you please modify my test program so that it shows the error you 're getting?  By the way, I'm using Borland's C++ builder on Windows, and gcc on Unix.  I tested MyODBC 3.51.07 against MySQL 4.0.20.

test SQLFetchScroll - please modify so that it shows your bug

Attachment: fetchscroll.c (application/octet-stream, text), 10.66 KiB.

Hi,
i got the same error with SQLExtendedFetch and dynamic recordsets.
I use the CRecordset class from the MFC with Visual C++ 6.0 on Windows 2000.
The installed MySQL server version is 4.0.17 and MyODBC has version 3.51.11. I'm new to MySQL but wrote a lot of different C/C++ programs running over ODBC with MS SQL Server, DB2 UDB, MS Access. The program I tested uses encrypted strings and try to write this strings into varchar fields. There are no problems with MS SQL Server and MS Access over ODBC with "dynasets" and this kind of data.
I hope you can find and fix that problem in your ODBC driver, so I can go on with testing.
Regards,
Jeremy

P.S. By the way, the Testprogram is not GPL licensed, it's just Freeware. So no 
source code is available to you.