I’m currently working on a project where I need to interact with a PostgreSQL database from my Asterisk dial plan defined in extensions.conf. The goal is to execute SQL queries and retrieve data from the database based on specific conditions.
I have already configured the ODBC DSN for PostgreSQL, and the database connection is successfully established. However, I’m struggling with writing the correct query in my extensions.conf file.
this is res_odbc.conf
[mydb]
enabled => yes
dsn => MyPostgreSQLDSN
username => user
password => userpwd
pre-connect => yes
sanitysql => select 1
logging => yes
this is odbc.ini
[MyPostgreSQLDSN]
Description = PostgreSQL ODBC DSN
Driver = PostgreSQL
Database = my_db
Servername = localhost
Port = 5432
Protocol = 8.4
ReadOnly = no
Socket = /var/run/postgresql
this is odbcinst.ini
[PostgreSQL ANSI]
Description=PostgreSQL ODBC driver (ANSI version)
Driver=psqlodbca.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1
You’ve shown us pretty much everything except the bit you’re having a problem
with “I’m struggling with writing the correct query in my extensions.conf
file.”
Show us what you’ve tried
Tell us what you’re trying to achieve
Show us exactly what the errors are, or what the result is and why this is
not what you expected.
I created a postgresql database and created a table users
this is my extension.conf
[internal]
exten => 123,1,Answer()
same => n,Set(pg_stat_activity=${ODBC_READSQL(SELECT * FROM users WHERE user_id = 1)})
same => n,Verbose(1, PostgreSQL Stat Activity: ${pg_stat_activity})
same => n,Playback(demo-congrats)
same => n,Hangup()
this is the error i am getting when i am calling to 123
func_odbc.c:876 acf_odbc_read: Unable to execute query [SELECT * FROM users WHERE user_id = 1]
Generally, when assembling strings, it is good practice to separate the static parts from the dynamic parts. This makes your work easier to read and test.
In this case, the SQL string should probably not be completely dynamic in the dialplan. Instead, you might want to put the entire static statement into your func_odbc.conf file. Then, call it from your dialplan without any parameters ie. ${ODBC_READSQL()}.
Also, READSQL may be a keyword in func_odbc.conf file, so something like READUSERONE might be better nomenclature.
What about this change idea? To avoid use of the keyword?
Other options to test your SQL connectivity include verifying with isql command-line utility.
Additionally, if you could provide more detailed error logs, including anything referencing SQL/ODBC found during Asterisk start-up, that would be most helpful.
I’ve never bothered with ODBC. My first resort would be to write an external script, with communication via FastAGI or ARI, and have that manage all the database access, using a proper programming language like Python.
During startup i am getting the referencing ODBC MySQL. But i am not using MySQL
res_odbc.c: Registered ODBC class ‘mydb’ dsn->[MyPostgreSQLDSN]
[Nov 13 09:58:15] WARNING[1798390] res_config_mysql.c: MySQL RealTime: No database user found, using ‘asterisk’ as default.
[Nov 13 09:58:15] WARNING[1798390] res_config_mysql.c: MySQL RealTime: No database password found, using ‘asterisk’ as default.
[Nov 13 09:58:15] WARNING[1798390] res_config_mysql.c: MySQL RealTime: No database host found, using localhost via socket.
[Nov 13 09:58:15] WARNING[1798390] res_config_mysql.c: MySQL RealTime: No database name found, using ‘asterisk’ as default.
[Nov 13 09:58:15] WARNING[1798390] res_config_mysql.c: MySQL RealTime: No database port found, using 3306 as default.
[Nov 13 09:58:15] WARNING[1798390] res_config_mysql.c: MySQL RealTime: No database socket found (and unable to detect a suitable path).
[Nov 13 09:58:15] NOTICE[1798390] res_config_ldap.c: No directory user found, anonymous binding as default.
The sample func_odbc offers a ODBC_SQL example but one thing to consider is the filtering of data becomes extremely important when you are exercising that much power so close to your data. Dialplan functions like SQL_ESC are your friend. There’s also the abstraction benefits to consider by isolating the separate language parts into separate files.
Pertinent for OP, it is not clear if changing from ODBC_READSQL to ODBC_GENERIC – or any other ODBC_NOT_A_KEYWORD_ELSEWHERE – was yet attempted.
Assuming your isql muhdsn is still working, here’s some simplified Asterisk configs to try (change muhdsn as appropriate to match the section title in your /etc/odbc.ini file):
/etc/asterisk/res_odbc.conf
[muhdsn]
enabled => yes
dsn => muhdsn ; # match this with your /etc/odbc.ini [section] title
pre-connect => yes
/etc/asterisk/func_odbc.conf
[USER_STAT]
dsn=muhdsn
readsql=SELECT user_id FROM users WHERE user_id='${SQL_ESC(${ARG1})}'
/etc/asterisk/extensions.conf
[internal]
exten = 123,1,Answer()
same = n,Set(pg_stat_activity=${ODBC_USER_STAT(1)})
same = n,Verbose(1, PostgreSQL Stat Activity: ${pg_stat_activity})
same = n,Playback(demo-congrats)
same = n,Hangup()
