Store data in external db using an AGI script


#1

Hi,

Currently I’m trying to store data in an external database. If you want to store data in an external database (sqlite3 db) from an AGI (python) script, is there a certain thing that needs to be done?

Even a simple select statement does not return anything when the statement is being executed through asterisk.

is there something I’m missing?


#2

You would normally talk to the database directly from the script, without involving Asterisk.


#3

I understand, that’s also what I’m doing. But running an sql statement from the script return nothing from the database.

a simple “select * from table” returns None.

whereas running the exact same statement from the python interpreter returns data.

That’s why I am wondering if I am missing some sort of setting or a step that I forgot/don’t know about…

regards


#4

Here is part of the code in which I try to connect to a db and query some data.

#!/usr/bin/env python
import sys
import sqlite3
from asterisk.agi import *

conn = sqlite3.connect('ast_db.db')
c = conn.cursor()

agi = AGI()
try:
    query_result = c.execute('SELECT * FROM ast_user_extension').fetchone()
except Exception, e:
    agi.verbose('ERROR: {}'.format(e))

agi.verbose('DATA: {}'.format(query_result[0]))
agi.hangup()

#5

Did you verify the permision ?


#6

Yes the permission is verified


#7

It even gives me an error when doing the select query:

Rx << DATABASE ERROR: no such table: ast_user_extension

the table does exist

sqlite> .tables
ast_user_extension


#8

it is very self explanatory
That error need to be solved at the top of the script because it is not an Asterisk issue


#9

How is it self explanatory when you run the script from the command line and the data is returned, but when it’s run from asterisk as age script it does not return anything?

>>>python database_lookup.py 
data: [(u'table', u'ast_user_extension', u'ast_user_extension', 2, u'CREATE TABLE ast_user_extension (column1, column2)')]
result [(u'data', u'data')]

#10

Different environment variables.

Different user running it.

Different current directory.

SELinux enabled and trying to access resource not allowed to Asterisk executable.


#11

Different environment variables.

What kind of environment variables should I look for?

Different user running it.

it’s run as root

Different current directory.

the database is in the same directory as the script (agi-bin)

SELinux enabled and trying to access resource not allowed to Asterisk executable.

I’m using Ubuntu and selinux is not enabled


#12

Environment:

Anything used by the database libraries.

Directory,

I’m pretty sure scripts are run in Asterisk’s current directory, and it won’t change to the AGI script directory… Putting writeable data in a bin directory doesn’t conform with normal Linux guidelines.


#13

post how you run your script for the command line and how you do it from Asterisk ,


#14

this is in my dial plan which calls the script:

exten => 501,1,AGI(database_lookup.py)

and this is how I run it from the command line

python database_lookup.py 
 data: [(u'table', u'ast_user_extension', u'ast_user_extension', 2, u'CREATE TABLE ast_user_extension (column1, column2)')]
 result [(u'data', u'data')]

#15

Asterisk cli full output when you run the agi, also enable agi debug


#16
Connected to Asterisk 13.21.0 currently running on 
  == Using SIP RTP CoS mark 5
       > 0x7f3070401fe0 -- Strict RTP learning after remote address set to: 10.4.8.114:8000
    -- Executing [501@bla:1] AGI("SIP/phone-000000f5", "database_lookup.py") in new stack
    -- Launched AGI Script /var/lib/asterisk/agi-bin/database_lookup.py
<SIP/phone-000000f5>AGI Tx >> agi_request: database_lookup.py
<SIP/phone-000000f5>AGI Tx >> agi_channel: SIP/phone-000000f5
<SIP/phone-000000f5>AGI Tx >> agi_language: en
<SIP/phone-000000f5>AGI Tx >> agi_type: SIP
<SIP/phone-000000f5>AGI Tx >> agi_uniqueid: 1528651011.370
<SIP/phone-000000f5>AGI Tx >> agi_version: 13.21.0
<SIP/phone-000000f5>AGI Tx >> agi_callerid: phone
<SIP/phone-000000f5>AGI Tx >> agi_calleridname: unknown
<SIP/phone-000000f5>AGI Tx >> agi_callingpres: 0
<SIP/phone-000000f5>AGI Tx >> agi_callingani2: 0
<SIP/phone-000000f5>AGI Tx >> agi_callington: 0
<SIP/phone-000000f5>AGI Tx >> agi_callingtns: 0
<SIP/phone-000000f5>AGI Tx >> agi_dnid: 501
<SIP/phone-000000f5>AGI Tx >> agi_rdnis: unknown
<SIP/phone-000000f5>AGI Tx >> agi_context: test
<SIP/phone-000000f5>AGI Tx >> agi_extension: 501
<SIP/phone-000000f5>AGI Tx >> agi_priority: 1
<SIP/phone-000000f5>AGI Tx >> agi_enhanced: 0.0
<SIP/phone-000000f5>AGI Tx >> agi_accountcode: 
<SIP/phone-000000f5>AGI Tx >> agi_threadid: 139846633305856
<SIP/phone-000000f5>AGI Tx >> 
<SIP/phone-000000f5>AGI Rx << data: []
<SIP/phone-000000f5>AGI Tx >> 510 Invalid or unknown command
<SIP/phone-000000f5>AGI Rx << DATABASE ERROR: no such table: ast_user_extension
<SIP/phone-000000f5>AGI Tx >> 510 Invalid or unknown command
    -- <SIP/phone-000000f5>AGI Script database_lookup.py completed, returning 0
    -- Auto fallthrough, channel 'SIP/phone-000000f5' status is 'UNKNOWN'

exten => 501,1,System(database_lookup.py)

Yes for the second part of logging I forgot to reload dial plan :expressionless:

Here is the log:

Executing [502@test:1] System("SIP/phone-0000010c", "t.py") in new stack [2018-06-10 21:33:32] WARNING[10135][C-0000327a]: app_system.c:148 system_exec_helper: Unable to execute 'script.py'

Now it says that it is unable to execute…


#17

Instead of AGI try running your script using System() and verify if works


#18

Looks to me as though you have two problems:

  1. you are sending a stream that should be going to some sort of log, to Asterisk.

  2. You are successfully accessing the database manager, but asking it for a table that does not exist.

Your second bit of logging looks like you are still using AGI, not System, but using it more correctly. If you really used System there would be no AGI to log “AGI Tx” and “AGI Rx”.


#19

Had to wait before I could respond because I reached my limit.

exten => 501,1,System(database_lookup.py)

Here is the log:

Executing [502@test:1] System(“SIP/phone-0000010c”, “t.py”) in new stack [2018-06-10 21:33:32] WARNING[10135][C-0000327a]: app_system.c:148 system_exec_helper: Unable to execute ‘script.py’

Now it says that it is unable to execute…


#20

That error relates to a different script, called script.py.