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

[Tony Mountifield]

In article <76133d030601201426q58d4dd6bw1a7eebf07db68427 at mail.gmail.com>,
Maxim Litnitsky <litnimax at 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 new

Compare 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
> stack

with 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 at softins.co.uk - http://www.softins.co.uk
Play: tony at mountifield.org - http://tony.mountifield.org