Calling a Stored Procedure from Asterisk

Asterisk Asterisk Support
1

I would like to seperate my busines logic to a dedicated db2 box. I have a program that I can call in a SQL query. So I created an entry in func_odbc.conf:

The extension looks like this:

[quote];If the extension is number 7, answer the extension, and hangup
exten => 7,1,Answer()
exten => 7,n,Set(ODBC_db2log(A,))
exten => 7,n,Set(ODBC_db2test())
exten => 7,n,Read(empNumber,employee-number,5,10)
exten => 7,n,Playback(you-entered)
exten => 7,n,SayDigits(${empNumber},m)
exten => 7,n,Playback(goodby)
exten => 7,n,Set(ODBC_db2log(H,))
exten => 7,n,Hangup()
[/quote]

The ODBC_db2log is the test of writing records to the db2 database.

I’m using the IBM ODBC client I believe it is iSeriesAccess version 7.1.0.1.0.

2

First, to read data from odbc functions it may look like “select my_function_call(’${Arg1}’) from dual”.

Second, you possibly have to define two methods - one to read, one to write:

[my_method]
write="call my_procedure(${my_arg1})"
read="select my_function(${my_arg1}) from my_table"

How to manage best depends on circumstances. But first, find in reference differences between write and read declarations.

And, at last, it seems that impossibility to make “call my_method()” is not an asterisk problem (at least, not fully). Maybe something wrong with connection setting or odbc driver or security restrictions of DB you’re using.

3

I found the issue, the file group must be specified (just like a database) instead of MyCall it would be MyFileGroup/MyCall (etc…). Thank you for clearifying that, this really helps. However My next question is How do I iterate through the columns and the rows?

For instance If I need to go through a list of tasks and ask the user if they completed it. In PHP I’d write:

echo 'Has ' . $row['task_user'] . 'completed the task, ' . $row['task'] . echo '?';

$row would come from the while statement for each row in a MySQL Record Set.

How do I do that for the dialplan? Or do I need to write an individual sql call for each column I want?
If I can’t iterate through rows I could check each row for its response and update that row. The select would only return rows without that response.

Let me know what my options are. I know that Asterisk is very sophisticated… and completely cool to work with. It’s nice to hear my voice. I just need to know what I can accomplish with it to start writing the code… efficiently.

Thanks!

4

I’m just a bit in PHP, so I understand you code only roughly. I neither understand what do you want to do.
So, as main issue was somehow solved (I wonder =) ), maybe you’ll try to start a new topic?

p.s. But as far as I understand, you may watch towards ARRAY dialplan function, because it’s surely possible to return much more than one value per one sql-query.