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