Maxim Litnitsky
2006-Jan-20 15:26 UTC
[Asterisk-Users] Calling MySQL 5 stored procedures from app_mysql
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
Tony Mountifield
2006-Jan-21 03:23 UTC
[Asterisk-Users] Re: Calling MySQL 5 stored procedures from app_mysql
In article <76133d030601201426q58d4dd6bw1a7eebf07db68427@mail.gmail.com>, Maxim Litnitsky <litnimax@gmail.com> wrote:> > 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:Please could you post the relevant sections of your dialplan?> 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 newCompare the above line ^^^^^^^^^^> 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 > stackwith this above line ^^^^^^^ There is a parameter missing between fetchid and vm_active. Note the two spaces. I suspect you might have a typo in the variable substitution for ${resultid}. Cheers Tony -- Tony Mountifield Work: tony@softins.co.uk - http://www.softins.co.uk Play: tony@mountifield.org - http://tony.mountifield.org