Summary: | ASTERISK-09116: Allow calling MySQL 5 stored procedures from dialplan | ||
Reporter: | Max Litnitskiy (litnimax) | Labels: | |
Date Opened: | 2007-03-27 11:51:24 | Date Closed: | 2007-03-27 12:34:52 |
Priority: | Major | Regression? | No |
Status: | Closed/Complete | Components: | 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. |