[Asterisk-Users] Calling MySQL 5 stored procedures from app_mysql
Maxim Litnitsky
litnimax at gmail.com
Fri Jan 20 15:26:07 MST 2006
Hello all.
I am trying to use app_mysql.
It works for selects and functions, but does not want to work with
procedures.
Pls have a look:
Calling function:
CREATE FUNCTION f_1(a VARCHAR(20)) RETURNS INTEGER RETURN (SELECT count(*)
from peer where name = a);
Result:
-- Executing Macro("IAX2/100-3", "local|100") in new stack
-- Executing MYSQL("IAX2/100-3", "Connect connid localhost asterisk
asterisk pbx_manager_new") in new stack
-- Executing Set("IAX2/100-3", "QUERY=SELECT f_1('100')") in new stack
-- Executing MYSQL("IAX2/100-3", "Query resultid 1 SELECT f_1('100')")
in new stack
-- Executing MYSQL("IAX2/100-3", "Fetch fetchid 2 vm_active") in new
stack
Jan 21 00:26:15 WARNING[31631]: app_addon_sql_mysql.c:318 aMYSQL_fetch:
ast_MYSQL_fetch: numFields=1
-- Executing NoOp("IAX2/100-3", "Voicemail active: 1") in new stack
-- Executing MYSQL("IAX2/100-3", "Clear 2") in new stack
-- Executing MYSQL("IAX2/100-3", "Disconnect 1") in new stack
-- Executing Hangup("IAX2/100-3", "") in new stack
== Spawn extension (macro-local, s, 8) exited non-zero on 'IAX2/100-3' in
macro 'local'
== Spawn extension (users, 123, 1) exited non-zero on 'IAX2/100-3'
-- Hungup 'IAX2/100-3'
So it found 1 peer.
Now trying to call a procedure:
CREATE PROCEDURE p_pbxuser_user_data1(IN _name VARCHAR(20))
BEGIN
SELECT
vm_active
FROM peer,voicemail,pbxuser WHERE peer.name = _name AND
voicemail.mailbox = _name and pbxuser.peer_name = _name;
Result:
-- Executing Macro("IAX2/100-5", "local|100") in new stack
-- Executing MYSQL("IAX2/100-5", "Connect connid localhost asterisk
asterisk pbx_manager_new") in new stack
-- Executing Set("IAX2/100-5", "QUERY=call p_pbxuser_user_data1('100')")
in new stack
-- Executing MYSQL("IAX2/100-5", "Query resultid 1 call
p_pbxuser_user_data1('100')") in new stack
-- Executing MYSQL("IAX2/100-5", "Fetch fetchid vm_active") in new
stack
Jan 21 00:28:46 WARNING[31696]: app_addon_sql_mysql.c:115 find_identifier:
Identifier 0, identifier_type 2 not found in identifier list
Jan 21 00:28:46 WARNING[31696]: app_addon_sql_mysql.c:330 aMYSQL_fetch:
aMYSQL_fetch: Invalid result identifier 0 passed
-- Executing NoOp("IAX2/100-5", "Voicemail active: ") in new stack
-- Executing MYSQL("IAX2/100-5", "Clear ") in new stack
Jan 21 00:28:46 WARNING[31696]: app_addon_sql_mysql.c:115 find_identifier:
Identifier 0, identifier_type 2 not found in identifier list
Jan 21 00:28:46 WARNING[31696]: app_addon_sql_mysql.c:348 aMYSQL_clear:
Invalid result identifier 0 passed in aMYSQL_clear
-- Executing MYSQL("IAX2/100-5", "Disconnect 1") in new stack
-- Executing Hangup("IAX2/100-5", "") in new stack
== Spawn extension (macro-local, s, 8) exited non-zero on 'IAX2/100-5' in
macro 'local'
== Spawn extension (users, 123, 1) exited non-zero on 'IAX2/100-5'
-- Hungup 'IAX2/100-5'
Not found. But when I call from MySQL CLI it works:
mysql> call p_pbxuser_user_data1('100');
+-----------+
| vm_active |
+-----------+
| 1 |
+-----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Any ideas?
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.digium.com/pipermail/asterisk-users/attachments/20060120/364605c4/attachment.htm
More information about the asterisk-users
mailing list