How to Write a Query in extensions.conf to Interact with PostgreSQL Database in Asterisk

Hello Asterisk community,

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

[PostgreSQL Unicode]
Description=PostgreSQL ODBC driver (Unicode version)
Driver=psqlodbcw.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1

[PostgreSQL]
Description = ODBC for PostgreSQL
Driver = /usr/lib/x86_64-linux-gnu/odbc/psqlodbcw.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libodbcpsqlS.so
UsageCount = 2

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.”

  1. Show us what you’ve tried

  2. Tell us what you’re trying to achieve

  3. Show us exactly what the errors are, or what the result is and why this is
    not what you expected.

Antony.

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]

This is the func_odbc.ini

[READSQL]
dsn=asterisk
readsql=${ARG1}

[WRITESQL]
dsn=asterisk
writesql=${VAL1}

“This is the func_odbc.ini”

I wonder whether that is the problem - it should be func_odbc.conf

Antony.

Sorry for that it is a typo. It is actually func_odbc.conf.

Is there anything else to do? I think i missed some configuration.

That does not match what you posted in the other configuration files.

I made the change from ‘dsn’ to 'MyPostgreSQLDSN, but it seems that the issue remains unresolved.

func_odbc.conf
[READSQL]
dsn=MyPostgreSQLDSN
readsql=${ARG1}

[WRITESQL]
dsn=MyPostgreSQLDSN
writesql=${VAL1}

ERROR
ERROR[1578730][C-00000001]: func_odbc.c:876 acf_odbc_read: Unable to execute query [SELECT * FROM users WHERE user_id = 1]

When I changed “dsn” to “mydb,” I encountered this error:

func_odbc.c:503 execute: SQL Exec Direct failed (-1)![SELECT * FROM users WHERE user_id = 1]
[Oct 31 06:21:23] ERROR[1580278][C-00000003]: func_odbc.c:876 acf_odbc_read: Unable to execute query [SELECT * FROM users WHERE user_id = 1]

(Note: “mydb” is defined in “res_odbc.conf”)

Any update on this? I am stuck here !

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.

Thank you for the response, I tried the same once but that is not the issue.

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.

I have connected and made query in ISQL command-line utitlity

SQL> select * from users;
±-----------±--------------------------------------------------±----------------------------------------------------------------------------------------------------±--------------------------±--------------------------+
| user_id | username | email | created_on | last_login |
±-----------±--------------------------------------------------±----------------------------------------------------------------------------------------------------±--------------------------±--------------------------+
| 1 | john_doe | john@example.com | 2023-10-27 11:04:22.382281| |
±-----------±--------------------------------------------------±----------------------------------------------------------------------------------------------------±--------------------------±--------------------------+
SQLRowCount returns 1
1 rows fetched

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.

Hey, Do you have any idea ? still i am facing this issue.

I don’t use Postgres, but I do use MariaDB via ODBC with Asterisk.

The following works for me; maybe it’ll help you identify anything different
about your setup:

My /etc/odbc.ini:

[Unicorn]
Driver=MariaDB ODBC 3.0 Driver
DATABASE=Unicorn
DESCRIPTION=MariaDB via ODBC
SERVER=apollo.example.net
UID=Unicorn
PASSWORD=53f787fc178a
PORT=3306

My /etc/odbcinst.ini:

[MariaDB ODBC 3.0 Driver]
Description=MariaDB Connector/ODBC v.3.0
Driver=/usr/lib/x86_64-linux-gnu/odbc/libmaodbc.so
Threading=2
UsageCount=1

My /etc/asterisk/res_odbc.conf:

[Unicorn]
enabled => yes
dsn => Unicorn
username => Unicorn
password => 53f787fc178a
pre-connect => yes
connect_timeout => 3
negative_connection_cache => 3
logging => yes
idlecheck => 15

My /etc/asterisk/func_odbc.conf:

[GENERIC]
dsn=Unicorn
readsql=${ARG1}

In the dialplan (happens to be written in AEL, but that’s not important):

Set(PricePerSec=${ODBC_GENERIC(select PricePerSec(‘${Prefix}’))});

Antony.

AEL is important! :slight_smile:

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()

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.