[Asterisk-Users] Calling MySQL 5 stored procedures from app_mysql

[Maxim Litnitsky]

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