112
I'm not sure about the details, but the API can't handle multiple result
sets. So only one SELECT per query.
Also, there is no way to use OUT-parameters.
One way to pass data out from procedures is not to use procedures but
sql functions instead:
DELIMITER //
CREATE FUNCTION `test.f_example`(the_param INT)
DETERMINISTIC
RETURNS Int;
BEGIN
SET the_param = the_param + 1;
RETURN the_param;
END;//
Then Use:
SELECT f_example(%d) AS new_param
Or, a less attractive solution is:
DELIMITER //
CREATE PROCEDURE `test.example`(IN the_param INT, OUT out_param INT)
DETERMINISTIC
BEGIN
SET out_param = the_param + 1;
END;//
Then use:
CALL example (%d, @out_param);
And in a separate statement:
SELECT @out_param
According to someone, all variables in pike are passed as values so an
OUT variable from mysql would be impossible. Though it looks like
sscanf() is an exception.
JCRM wrote:
> I am unable to call stored procedures on a MySQL database from within Pike
>
> I've created a basic test case:
>
> DELIMITER //
> CREATE PROCEDURE `test.example`(INOUT the_param INT)
> DETERMINISTIC
> BEGIN
> SET the_param = the_param + 1;
> SELECT 1 AS result1;
> SELECT 2 AS result2a, 3 AS result2b;
> END;//
>
> DELIMITER ;
>
> >From within the MySQL CLI this works as I would expect:
>
> mysql> SET @v=3;
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> CALL `test.example`(@v);
> +---------+
> | result1 |
> +---------+
> | 1 |
> +---------+
> 1 row in set (0.00 sec)
>
> +----------+----------+
> | result2a | result2b |
> +----------+----------+
> | 2 | 3 |
> +----------+----------+
> 1 row in set (0.00 sec)
>
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> SELECT @v;
> +------+
> | @v |
> +------+
> | 4 |
> +------+
> 1 row in set (0.00 sec)
>
> But from Hilfe:
>
>> object db;
>> db=Sql.Sql("mysql://root:dbadmin@/test");
>> int v=3;
>> db->query("CALL `test.example`(%d)",v);
>>
> big_query(): Query "CALL `test.example`(3)" failed (PROCEDURE
> sslprov.test.example can't return a result set in the given context)
> /usr/local/pike/7.6.112/lib/modules/Sql.pmod/mysql.pike:0:
> mysql(/*Localhost via UNIX socket*/)->big_query("CALL `test.example`(3)")
> /usr/local/pike/7.6.112/lib/modules/Sql.pmod/mysql.pike:637:
> mysql(/*Localhost via UNIX socket*/)->big_query("CALL
> `test.example`(3)",UNDEFINED,UNDEFINED)
>
> /usr/local/pike/7.6.112/lib/modules/Sql.pmod/Sql.pike:480:
> Sql.mysql(/*Localhost via UNIX socket*/)->query("CALL `test.example`(3)",3)
>
> I get this error regardless of whether the parameter is an IN or INOUT -
> I guess this is something to do with the multiset stuff in MySQL
>
> Any ideas?
>
>
>
|