[Home]

Summary:ASTERISK-09634: [patch] MySQL stored procedures - FULL SUPPORT
Reporter:Edoardo Serra - WeBRainstorm S.r.l. (webrainstorm)Labels:
Date Opened:2007-06-10 10:16:42Date Closed:2007-06-12 17:12:45
Priority:MinorRegression?No
Status:Closed/CompleteComponents:Applications/General
Versions:Frequency of
Occurrence
Related
Issues:
Environment:Attachments:( 0) app_addon_sql_mysql.c.stored-procedures.1.patch
Description:Support for MySQL stored procedure was added with bug ASTERISK-8949 but it's not yet complete

The patch implemented in r347 only 'flushes' other result sets, if present, but doesn't allow you to access them (You need to 'flush' result sets, otherwise you won't be able to execute other queries in the same connection)

In addition, according to mysql documentation, result sets should be cleared before calling mysql_next_result (http://dev.mysql.com/doc/refman/5.0/en/mysql-next-result.html)

This patch implement mysql_next_result as a dialplan instruction allowing you more granular control over result sets returned by stored procedures.

Consider a stored procedure like:

CREATE PROCEDURE `ast_test`()
BEGIN
SELECT 'First result' AS resultnumber;
SELECT 'Second result' AS resultnumber;
END

...it returns two different results sets that can be accessed with:

exten => mysql,1,MYSQL(Connect connid hostname username password database)
exten => mysql,n,MYSQL(Query resultid ${connid} CALL\ ast_test\(\))
exten => mysql,n,MYSQL(Fetch fetchid ${resultid} field)
exten => mysql,n,NoOp(${field})
exten => mysql,n,MYSQL(Clear ${resultid})
exten => mysql,n,MYSQL(Nextresult resultid ${connid})
exten => mysql,n,MYSQL(Fetch fetchid ${resultid} field)
exten => mysql,n,NoOp(${field})
exten => mysql,n,MYSQL(Clear ${resultid})
exten => mysql,n,MYSQL(Disconnect ${connid})

The patch also restores mysql_query error message introduced in r316

Hope it helps

My 2 cents
Comments:By: Edoardo Serra - WeBRainstorm S.r.l. (webrainstorm) 2007-06-10 10:33:02

The patch also contains some fixes to application documentation (variable naming)
Hope it helps
By: Edoardo Serra - WeBRainstorm S.r.l. (webrainstorm) 2007-06-10 10:44:28

BTW, Disclaimer just faxed
By: Edoardo Serra - WeBRainstorm S.r.l. (webrainstorm) 2007-06-11 02:11:32

Disclaimer faxed just after bug submission

Tnx
By: Jason Parker (jparker) 2007-06-12 17:12:45

Added to svn trunk in revision 396.

Thanks, this is good. :)