[Home]

Summary:ASTERISK-09116: Allow calling MySQL 5 stored procedures from dialplan
Reporter:Max Litnitskiy (litnimax)Labels:
Date Opened:2007-03-27 11:51:24Date Closed:2007-03-27 12:34:52
Priority:MajorRegression?No
Status:Closed/CompleteComponents:Applications/General
Versions:Frequency of
Occurrence
Related
Issues:
Environment:Attachments:
Description:Currenly we have the following line in app_addon_sql_mysql.c (line 230):

if (mysql_real_connect(mysql,dbhost,dbuser,dbpass,dbname,0,NULL,0)) {

Using this connect string we get error from mysql driver when trying to call a procedure that returns result set (e.g. not using OUR parameters but calling SELECT statement).

It's not handy to call SELECT @out_param every time you call a procedure. So I suggest to change line 230 to the following:

if (mysql_real_connect(mysql,dbhost,dbuser,dbpass,dbname,0,NULL,CLIENT_MULTI_STATEMENTS|CLIENT_MULTI_RESULTS)) {

This will allow to use SELECT from procedures like in the following example:

DROP PROCEDURE IF EXISTS p_provider_route_list;
DELIMITER //
CREATE PROCEDURE p_provider_route_list(
IN _called VARCHAR(64),
IN _accountcode INTEGER
) DETERMINISTIC
BEGIN
UPDATE asterisk_peer, ENTVOIPACCT  SET
 asterisk_peer.ipaddr = ENTVOIPACCT.ATRALIAS
WHERE asterisk_peer.accountcode = ENTVOIPACCT.ATRACCTID AND ATRACCTTYID = 2;

SELECT peer_account.ATRACCTID AS peer_account, peer_account.ATRALIAS AS name, protocol, code
 FROM provider_routetrunk, provider_route, ENTVOIPACCT user_account, ENTVOIPACCT peer_account, provider_ratelist
  WHERE provider_routetrunk.account_id= provider_route.account_id
   AND user_account.ATRACCTID = _accountcode  AND user_account.ATRRATEID = provider_ratelist.dtl_ratelist_id AND provider_ratelist.routegroup_id = provider_route.routegroup_id
   AND provider_route.is_enabled = 1 AND peer_account.ATRACCTSTAT = 1
   AND peer_account.ATRACCTID = provider_route.account_id
   AND _called  LIKE CONCAT(code,'%')
    ORDER BY code DESC, priority ASC;
END //
DELIMITER ;

And this is how it is used in dialplan:

context route-no-reseller-call {
_X. => {
  MySQL(Connect connid ${DBHOST} ${DBUSER} ${DBPASS} ${DB});
  NoOp(${EXTEN});
  MYSQL(Query resultid ${connid} CALL\ p_provider_route_list(\'74951234\'\,20864));
fetch:
   MYSQL(Fetch fetchid ${resultid} PEERACCOUNT PEERNAME PROTOCOL CODE);
  // Try to fetch one route and place a call
  if  ("${fetchid}" = "1") {
   NoOp(try route ${PEERNAME});
   Dial(SIP/${EXTEN}@${PEERNAME},${DIAL_TIMEOUT},S(${DURATION_LIMIT})${DIAL_OPTIONS})
   switch (DIALSTATUS}") {

Comments:By: Serge Vecher (serge-v) 2007-03-27 12:34:36

this feature has been recently added to asterisk-addons trunk (new features are not added to release branches). Please test out this new functionality in trunk.