| Bug #2273 | SELECT in a stored procedure must have INTO (#1289) | ||
|---|---|---|---|
| Submitted: | 3 Jan 2004 16:59 | Modified: | 24 Jun 2004 12:16 |
| Reporter: | Daniel Kasak | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 3.51.06 | OS: | Windows (Windows 2000) |
| Assigned to: | Peter Harvey | CPU Architecture: | Any |
[5 Jan 2004 10:13]
Dean Ellis
Verified using MyODBC/MySQL current sources.
[9 Mar 2004 18:05]
Daniel Kasak
I hate to be a pain, but I would really appreciate knowing where we stand on this issue time-wise. I understand that it might be low priority in the grand scheme of things. I'd just like to have a very rough estimate on when it will be investigated and / or fixed. Thanks.
[20 Mar 2004 9:01]
Peter Harvey
Working on Bug 3028. Complete solution for this has forced me to start the implementation of descriptors. Hope to complete this within week, make a release, and then evaluate which bugs to address next.
[27 Mar 2004 13:36]
Raido Valgeväli
Definetely in ODBC driver, not associated with Access. Any script connecting through ADO or DAO gives the error. Some mysql graphical clients act the same. (I don't understand why the priority of this bug is low - after fixing this bug I promise to buy MySQL server commercial licence.)
[27 Mar 2004 20:05]
Daniel Kasak
Just another note. I haven't tested this yet, but I'm told ( by Raido Valgeväli - see previous post ) that the same problem exists when connecting via PHP. So it looks like it's not merely an issue with MyODBC, but purhaps something further up the heirarchy in the client libraries. Could someone who knows what they're talking about more than me please verify this so we can move this bug to the right area?
[6 Apr 2004 13:55]
chris smith
Hi, I can also verify that I get the same bug in PHP, I thought it was just me, but I guess not, sorry I have no clue as to the cause, but I would really like it fixed, pretty please. Thanks Chris
[6 Apr 2004 13:57]
chris smith
Sorry I forgot to mension that I am running Red hat Linux version 9 if this helps. Chris
[15 Apr 2004 2:27]
Daniel Kasak
Due to a couple of people confirming that this is not restricted to MyODBC, but also affects PHP, I have decided that this bug would be better placed against the 'MySQL Client Library (libmysql)' category. Anyone who disagrees please feel free to say so.
[2 Jun 2004 18:01]
k self
Just to add, this problem also exists when using JDBC and Connector/J
[2 Jun 2004 18:20]
Mark Matthews
In order to avoid this error, the libmysql being used must be from MySQL-4.1 or newer, and the client flag CLIENT_MULTI_RESULTS must be set (the server needs to know if it can send back multiple result sets to the client, because if a stored procedure does not use 'SELECT INTO' for each result set, the results have to go 'somewhere', and that's to the client). If you're using JDBC, you need to use the nightly snapshot builds (this functionality was added after Connector/J 3.1.1 shipped) from http://downloads.mysql.com/snapshots.php If you're using PHP, you'll need to use PHP/MySQLi (which comes with MySQL-5.0), as it supports multiple result sets being returned to the client (which will avoid this error). Multiple result set support for ODBC should ship with Connector/ODBC 3.53. (as a side effect, notice that you don't have to use cursors/refcursors to support returning result sets to the client from stored procedures, which is 'nice' in my opinion).
[2 Jun 2004 18:21]
Mark Matthews
Slight correction...PHP/MySQLi comes with _PHP_ 5.0 (not MySQL-5.0 as I wrote earlier).
[3 Jun 2004 8:28]
Daniel Kasak
MyODBC-3.53, eh? Damn! Don't suppose someone has a roadmap of MyODBC releases? Guys? :)
[21 Jun 2004 23:47]
Peter Harvey
MyODBC is statically linked against the MySQL client library. At present MyODBC 3.51.8 is linked against MySQL 4.0.20. This means that MyODBC will inherit all the limitations of the client library to which it is linked. It would appear that this is the cause of this problem - that neither MyODBC nor the client it is linked against support some features in latest MySQL. The solution here is MyODBC 3.53 linked against a more recent MySQL client. MyODBC 3.53 is near completion but no date has been set yet. In the meantime MyODBC users should use the latest stable MyODBC (ie 3.51.x) with the latest stable MySQL (ie 4.0.x). Guys; does it make sense to close this bug given the above explanation or wait until 3.53 is released?
[13 Apr 2005 23:53]
Daniel Kasak
Any word on the progress of MyODBC-3.53? Perhaps an unofficial binary?
Description: I have created a simple stored procedure in MySQL-5.0.0, which is a contains a select statement ( eg select * from table_name ). I can sucessfully run the stored procedure from the MySQL client with the 'call' statement. If, however, I try to run the stored procedure from MS Access via a pass-through query ( Access passes the query to MySQL to process and accepts any results that it is given ), I get the following error: SELECT in a stored procedure must have INTO (#1289) An ODBC trace ( of a slightly more complicated sp, that accepts a variable and queries on the value ) follows: MSACCESS 684-710 ENTER SQLAllocStmt HDBC 08E91348 HSTMT * 02434C40 MSACCESS 684-710 EXIT SQLAllocStmt with return code 0 (SQL_SUCCESS) HDBC 08E91348 HSTMT * 0x02434C40 ( 0x08e91620) MSACCESS 684-710 ENTER SQLGetStmtOption HSTMT 08E91620 UWORD 0 PTR 0x0012C660 MSACCESS 684-710 EXIT SQLGetStmtOption with return code 0 (SQL_SUCCESS) HSTMT 08E91620 UWORD 0 PTR 0x0012C660 MSACCESS 684-710 ENTER SQLSetStmtOption HSTMT 08E91620 UWORD 0 <SQL_QUERY_TIMEOUT> SQLPOINTER 0x0000003C MSACCESS 684-710 EXIT SQLSetStmtOption with return code 0 (SQL_SUCCESS) HSTMT 08E91620 UWORD 0 <SQL_QUERY_TIMEOUT> SQLPOINTER 0x0000003C (BADMEM) MSACCESS 684-710 ENTER SQLGetStmtOption HSTMT 08E91620 UWORD 3 PTR 0x0012C660 MSACCESS 684-710 EXIT SQLGetStmtOption with return code 0 (SQL_SUCCESS) HSTMT 08E91620 UWORD 3 PTR 0x0012C660 MSACCESS 684-710 ENTER SQLSetStmtOption HSTMT 08E91620 UWORD 3 <SQL_MAX_LENGTH> SQLPOINTER 0x7FFFFFFF MSACCESS 684-710 EXIT SQLSetStmtOption with return code 0 (SQL_SUCCESS) HSTMT 08E91620 UWORD 3 <SQL_MAX_LENGTH> SQLPOINTER 0x7FFFFFFF (BADMEM) MSACCESS 684-710 ENTER SQLExecDirectW HSTMT 08E91620 WCHAR * 0x0012E074 [ -3] "call sp_actions_by_user(71)\ 0" SDWORD -3 MSACCESS 684-710 EXIT SQLExecDirectW with return code -1 (SQL_ERROR) HSTMT 08E91620 WCHAR * 0x0012E074 [ -3] "call sp_actions_by_user(71)\ 0" SDWORD -3 DIAG [S1000] [MySQL][ODBC 3.51 Driver][mysqld-5.0.0-alpha]SELECT in a stored procedure must have INTO (1289) MSACCESS 684-710 ENTER SQLErrorW HENV 08E912A0 HDBC 08E91348 HSTMT 08E91620 WCHAR * 0x0012C5F4 (NYI) SDWORD * 0x0012C640 WCHAR * 0x02860B18 SWORD 4095 SWORD * 0x0012C62C MSACCESS 684-710 EXIT SQLErrorW with return code 0 (SQL_SUCCESS) HENV 08E912A0 HDBC 08E91348 HSTMT 08E91620 WCHAR * 0x0012C5F4 (NYI) SDWORD * 0x0012C640 (1289) WCHAR * 0x02860B18 [ 88] "[MySQL][ODBC 3.51 Driver][mysqld-5.0.0-alpha" SWORD 4095 SWORD * 0x0012C62C (88) MSACCESS 684-710 ENTER SQLErrorW HENV 08E912A0 HDBC 08E91348 HSTMT 08E91620 WCHAR * 0x0012C5F4 (NYI) SDWORD * 0x0012C640 WCHAR * 0x02860BE0 SWORD 3995 SWORD * 0x0012C62C MSACCESS 684-710 EXIT SQLErrorW with return code 100 (SQL_NO_DATA_FOUND) HENV 08E912A0 HDBC 08E91348 HSTMT 08E91620 WCHAR * 0x0012C5F4 (NYI) SDWORD * 0x0012C640 WCHAR * 0x02860BE0 SWORD 3995 SWORD * 0x0012C62C MSACCESS 684-710 ENTER SQLFreeStmt HSTMT 08E91620 UWORD 1 <SQL_DROP> MSACCESS 684-710 EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS) HSTMT 08E91620 UWORD 1 <SQL_DROP> How to repeat: Create a simple stored procedure that selects data from a table. Test that it runs from the mysql client. Create a pass-through query in MS Access. Select the MyODBC data source that points to the MySQL server. Enter the call statement, eg: call sp_my_test_stored_procedure(); The error: SELECT in a stored procedure must have INTO (#1289) should be returned for all sps that return a dataset, as far as I can tell. Suggested fix: No idea. I assume that since calling the sp from the mysql client works, that the issue is with MyODBC. I'd be more than happy to provide more info if necessary.