Multi Result support

[emilsjolander]

I am getting the following error when calling on my stored procedure

Error 1312: PROCEDURE mydb.GetAllNotes can't return a result set in the given context

I am calling on my stored procedure with this code

db.Query("CALL MyStoredProcedure()")

[arnehormann]

Hi @emilsjolander, thanks for reporting this!
I don't know about Julien, but I haven't used stored procedures yet.
Can you provide SQL and go code for a failing case (play.golang.org or gist)?
It would be faster than us (or at least me) rewriting that code.

[emilsjolander]

I actually got them working by following the instructions in the last comment of this thread https://groups.google.com/forum/?fromgroups=#!topic/golang-nuts/raVDmAzkZ5U

adding

clientMultiStatements |
   clientMultiResults |

to line 209 in packets.go

[arnehormann]

@julienschmidt I can't really say what this does to the driver - e.g. when you really have multiple results per query, how do you get the next one with the current api? Can you determine if there's only one result returned by a stored procedure? My guess would be we can't include this, but I just don't know. Maybe yet another dsn switch for those who really need it?

[emilsjolander]

well i kinda knew it wasn't a fix, but i needed to quickly get a project for a school working ^^

[arnehormann]

Still, thanks @emilsjolander! Can you provide some code? Maybe we'll add it to the tests, we'll have to check/discuss this.

[julienschmidt]

I know about this issue. Does it work with clientMultiStatements flag only for you?

There is no way to determine how many results the procedure produces.
With the current database/sql API it is impossible to handle multiple results (at least in a way that makes sense).
I'm going to make a design proposal for that after the Go1.1 release.

The clientMultiStatements is not present because I don't know a case where multiple statements don't also produce multiple results. This is also a nice way to prevent SQL injections.
But it might be the case, that it works with stored procedures. So give it a try ;)

[arnehormann]

Hi @emilsjolander, can you please test Juliens suggestion and get back to us?

[sdming]

Did this issue fixed?

I got same error when call procedure with code

CALL sp_a(?,?,?) 

The third parameter is output parameter, so I change code to following

SET @sp_var_p1 = ?;
SET @sp_var_p2 = ?;
CALL sp_a ( @sp_var_p1, @sp_var_p2, @sp_var_p3 );
SELECT @sp_var_p3;

I got another error "check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET @sp_var_p2 = ?; "

[arnehormann]

Go does not support multiple results in database/sql (yet). The driver closely matches the api, that's why it's not supported here. Support for multiple statements per query is missing as well. They could produce multiple results which the driver could not handle in a sensible way. That's a limitation for stored procedures, too.
We didn't follow up on this issue because we don't have test code for stored procedures and adding both clientMultiStatements and clientMultiResults will not be supported by the driver until Go is multi result capable in the core library. For your use case, clientMultiStatements is probably sufficient.
Please clone the driver, change packets.go to include clientMultiStatements in the flags and tell us if it works for you that way.
If it does, we'll discuss adding it to the driver in some way (e.g. configurable with a dsn-flag).

[sdming]

changed code to

    clientFlags := uint32(  
        clientProtocol41 |  
            clientSecureConn |  
            clientLongPassword |  
            clientTransactions |  
            clientMultiStatements |  
            clientMultiResults | // https://github.com/go-sql-driver/mysql/issues/66  
            clientLocalFiles,  
    ) 

1: it works

    CALL sp_query ( ?, ?, ? );

CREATE PROCEDURE `sp_query`(IN p1 INT, IN p2 varchar(100), p3 NUMERIC(10,4))
BEGIN    
   SELECT p1,  p2, p3;
END

2: it works

    CALL sp_exec ( ?, ?, ? );

CREATE PROCEDURE `sp_exec`(IN p1 INT, IN p2 varchar(100), p3 NUMERIC(10,4))
BEGIN    
   set p1 = 123;
   set p2 = "p2";
   set p3 = 3.14;
END

3: it dones't work

SET @p2 = ?; 
CALL sp_exec_inout ( ?, @p2, @p3 );
SELECT @p2, @p3; 

CREATE PROCEDURE `sp_exec_inout`(IN p1 INT, INOUT p2 varchar(100), OUT p3 NUMERIC(10,4))
BEGIN    
   set p1 = 123;
   set p2 = "p2";
   set p3 = 3.14;
END

[arnehormann]

Does it need clientMultiResults or does it work without it (using only clientMultiStatements)?