MySQL Bugs: #2658: SELECT in a stored procedure must have INTO

Bug #2658	SELECT in a stored procedure must have INTO
Submitted:	5 Feb 2004 18:07	Modified:	6 Feb 2004 8:51
Reporter:	Ted Toth	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.0	OS:	Windows (Window XP Professional)
Assigned to:		CPU Architecture:	Any

Description:
I created the following stored procedure with no errors:

CREATE PROCEDURE `rb_GetPortals`()
BEGIN
SELECT  rb_Portals.PortalID, rb_Portals.PortalAlias, rb_Portals.PortalName, rb_Portals.PortalPath, rb_Portals.AlwaysShowEditButton
FROM    rb_Portals;
END

However when I call the C api mysql_prepare with the query "call rb_GetPortals()" I get the error "SELECT in a stored procedure must have INTO".

The reason I'm calling mysql_prepare is because I'm trying to enhance  MySQLDriverCS (the C# .Net data provider) to support stored procedures and using a prepared statement appears to be the most compatible way to do this.

How to repeat:
Create a stored procedure with a simple select and then in C try to create a prepared statement to call it.

Verified against the current 5.0 bk tree.  Thank you.

You will have to add support for multiple result sets to your C# driver, and set the client flag CLIENT_MULTI_RESULTS (#define CLIENT_MULTI_RESULTS    131072  /* Enable/disable multi-results */) before stored procedure execution can return results. 

Since a stored procedure doesn't know ahead of time how many results will be returned, the client needs to support reading any multiple of results. Therefore, if the server sees that you don't have CLIENT_MULTI_RESULTS set, it will give you this error.

Other forums with no answers refer to this question. And some libmysqlclient programmers may be confused. Here is the basic solution:

//C or C++ API
mysql_real_connect(&mysql,...,CLIENT_MULTI_RESULTS);

(Where ... are the other parameters.)

Note that you can combine the flags, with bit OR operation like so:
...,CLIENT_MULTI_RESULTS|CLIENT_SSL);

See mysql_real_connect() in the manual for detailed information.

I am still seeing the same error even after I specified the client_flags. 

mysql_real_connect(conn, opt_host_name, opt_user_name, opt_password, opt_db_name, opt_port_num, opt_socket_name, opt_flags);

where opt_flags == CLIENT_MULTI_STATEMENTS || CLIENT_MULTI_RESULTS;

mysql_query(conn, "call test.getusers()"); 

The above call failed with error msg "SELECT in a stored procedure must have INTO". My getusers() procedure is a simple "SELECT * from users" statement which could return multiple rows.

I am using MySQL 5.0.2 on Windows 2000. Thanks for your help.

I tried this on RedHat Linux, and specifying CLIENT_MULTI_STATEMENTS worked.

[4 Dec 11:14pm] Michael Zhou:
>[...]
>where opt_flags == CLIENT_MULTI_STATEMENTS || CLIENT_MULTI_RESULTS;

Try

CLIENT_MULTI_STATEMENTS | CLIENT_MULTI_RESULTS

instead.

Is it possible to get multiple results from PHP api? I'm using PHP 5.0.3 with MySQL client version 4.1.7 and setting CLIENT_MULTI_RESULTS flag does not help. 
mysql_connect(DB_HOST, DB_USER, DB_PASS, false, CLIENT_MULTI_STATEMENTS) - still returns SELECT in a stored procedure must have INTO

Of course, I tried CLIENT_MULTI_RESULT and CLIENT_MULTI_STATEMENTS | CLIENT_MULTI_RESULTS flags.

I get the same error while trying to execute a stored procedure in MySql 5.0.2 using
Mono Web Service (with ByteFX). 

We are running Linux Fedora 2, Apache 2, Mono 1.0.4, MySql 5.0.2. We use VIEWS and STORED PROCEDURES extensively in our application and I need to find a way to make them work with mono web services.

More information:

TABLE - "tblTest" in "mysql" DATABASE.
----------------------------------------------------------------
id        int(11) PRIMARY KEY
UserID    varchar(30)
UserPWD   varchar(30)
-----------------------------

I have only 1 record in the TABLE - tblTest

id        UserID         UserPWD
--------------------------------
1         chandan        abcd

My STORED PROCEDURE is "sp1"
-----------------------------------------------------
DELIMITER $$

DROP PROCEDURE IF EXISTS `mysql`.`sp1`$$
CREATE PROCEDURE `mysql`.`sp1`()
BEGIN
select * from mysql.tblTest;
END$$

DELIMITER ;

My Mono Code is:
---------------------------
using System;

using System.ComponentModel;

using System.Data;
using System.Diagnostics;

using System.Web;

using System.Web.Services;

using System.Xml;

using System.Web.Services.Protocols;

using ByteFX.Data.MySqlClient;

public class TestWebService: System.Web.Services.WebService
{
 [WebMethod]
 public string TestMySql()
 {
  MySqlConnection conn = new MySqlConnection
  ("Server=localhost;Database=mysql;User ID=root;Password=abcd123;");

  MySqlDataAdapter adapter = new MySqlDataAdapter("call mysql.sp1()",conn);
  DataSet dataset = new DataSet();
  adapter.Fill(dataset," ");
  string result = "";
  result = dataset.GetXml();
  return result;
 }
}

ERROR:                 StackTrace
-------------------------------------------------------start
in <0x000e0>
      ByteFX.Data.MySqlClient.Driver:ReadPacket ()
in <0x00092> ByteFX.Data.MySqlClient.Driver:Send
(ByteFX.Data.MySqlClient.DBCmd,byte[])
in <0x000be> ByteFX.Data.MySqlClient.MySqlCommand:ExecuteBatch (bool)
in <0x0005a> (wrapper remoting-invoke-with-check)
      ByteFX.Data.MySqlClient.MySqlCommand:ExecuteBatch (bool)
in <0x00077> ByteFX.Data.MySqlClient.MySqlDataReader:NextResult ()
in <0x0005d> (wrapper remoting-invoke-with-check)
      ByteFX.Data.MySqlClient.MySqlDataReader:NextResult ()
in <0x0013a> ByteFX.Data.MySqlClient.MySqlCommand:ExecuteReader (
     System.Data.CommandBehavior)
in <0x00054> (wrapper remoting-invoke-with-check)
      ByteFX.Data.MySqlClient.MySqlCommand:ExecuteReader
(System.Data.CommandBehavior)
in <0x00012>
ByteFX.Data.MySqlClient.MySqlCommand:System.Data.IDbCommand.ExecuteReader
(
     System.Data.CommandBehavior)
in <0x000be> System.Data.Common.DbDataAdapter:Fill
(System.Data.DataSet,int,int,string,
     System.Data.IDbCommand,System.Data.CommandBehavior)
in <0x00045> System.Data.Common.DbDataAdapter:Fill (System.Data.DataSet)
in <0x000fd> dWebServices.TestWebService:TestMySql ()
------------------------------------------------------------------------------------end_stacktrace

I have also tried using MyODBC adapter, but no luck. I get the same error.

Please advise. 

Chandan
chandan@damaka.net

bug by delphi dbexpress:
SQL Server Error: SELECT in a stored procedure must have INTO.

Is it possible to get multiple results from delphi7 with dbexpress? I'm using delphi7 with dbexpress with
MySQL version 5.0.4 and i need to set CLIENT_MULTI_RESULTS flag to let stored procedure has full work.

i found some idea what i think it must work but it dosnot
this is my forecasting :

SQLConnection1.SQLConnection.SetOption('CLIENT_MULTI_RESULTS', LongInt(True));

can any body help me please.

I can only confirm that it also occurs with old-fashioned VB5,
when accessing a MySQL base via ADO.

Regarding work-arounds, I wonder:
might there exist any way of telling the server
that *this* stored procedure will have only one result set?

I am having this same bug too using ASP.NET and MySQL 5.0.4-beta-nt and accessing the database using a DSN-less connection and the MySQL ODBC 3.51 Driver.  

I don't see anywhere in the options for establishing the ODBC connection to enable the CLIENT_MULTI_RESULTS option.  I also can't find a way call the mysql_set_server_option function to set the option once the connection has been opened.  

Right now it looks like there's no way to use stored procedures with .net which stinks.

After some further searching around I was happy to discover the MySQL Connector/Net (http://www.mysql.com/products/connector/net/).  The connector works great and allows MySQL stored procedures to be run with .net.

Hi All,

Did someone already knows the solution for this?

I'm receiving same error using mySQLDriverCS and stored procedure.

for your immediate assistance please.