Dear Asterisk Community,
We are using the Asterisk with Realtime Communication with the Postgresql Database via ODBC. We have created our own tables in the Postgresql DB for “sippeers, sipusers, queues, queue_members, queue_log” ( These are configured in the “asterisk/extconfig.conf” file ). It is working fine without any issues.
Now we are in the process of migrating the Postgresql Database to Oracle Database v19c. Therefore we are trying to make the Asterisk to communicate with the Oracle DB via ODBC. We have successfully connected the Oracle Database using ODBC in Asterisk v18.11.
ol7*CLI> odbc show
ODBC DSN Settings
Name: altumcc
DSN: altumcc
Number of active connections: 1 (out of 1)
Logging: Disabled
ol7*CLI>
But while we try to register a SIP User from the Softphone ( Zoiper ), we are getting the error as following.
– Registered SIP ‘2003’ at 192.168.12.150:5060
[Apr 30 18:36:33] WARNING[11305]: res_config_odbc.c:540 update_odbc: Key field ‘name’ does not exist in table ‘sip_buddies@altumcc’. Update will fail
[Apr 30 18:36:33] WARNING[11305]: res_odbc.c:529 ast_odbc_print_errors: SQL Execute returned an error: HY000: [Oracle][ODBC][Ora]ORA-01747: invalid user.table.column, table.column, or column specification.
** The User has been registered in the Asterisk when we checked with the “sip show peers” in the console. It shows the proper IP Address & Port for the SIP User 2003 **
ol7CLI> sip show peers
Name/username Host Dyn Forcerport Comedia ACL Port Status Description Realtime
2003/2003 192.168.12.150 D Auto (No) No 5060 Unmonitored Cached RT
1 sip peers [Monitored: 0 online, 0 offline Unmonitored: 1 online, 0 offline]
ol7CLI>
** Since the update in the Oracle Database Table “sip_buddies” was failed due to the above mentioned error, the IP Address was not updated in the Oracle DB table. Please find the below query output for the same. **
[root@ol7 asterisk]# isql -vvvvvvvvvvvvvv altumcc
±--------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
±--------------------------------------+
SQL> select name,username,ipaddr from sip_buddies where name = ‘2003’ ;
±------±---------±---------------+
| NAME | USERNAME | IPADDR |
±------±---------±---------------+
| 2003 | 2003 | 192.168.12.162 |
±------±---------±---------------+
SQLRowCount returns -1
1 rows fetched
SQL>
As I referred in the internet for the “ORA-01747” Error and found that some sites tells that the ‘name’ is the reserved keyword in Oracle therefore we should not use it in the query. But the column ‘name’ is exists in the Oracle Database Table and having the data as well.
Also the Oracle is Case Insensitive and when we use (’) single quote for the column name then it will be case sensitive. So I have also created one more column in the sip_buddies ( sippeers ) as ‘NAME’. So now there are two columns one in lowercase ‘name’ and another one is uppercase ‘NAME’ in the sip_buddies table, but still the error occurs.
[ Below are the System & Driver Details ]
Operating System - Oracle Linux 7.4
Asterisk Version - 18.11
Oracle Database Version - 19c
unixODBC Version - 2.3.1-11.el7
unixODBC-Devel Version - 2.3.1-11.el7
oracle-instantclient-basic.x86_64 - 21.6.0.0.0-1
oracle-instantclient-devel.x86_64 - 21.6.0.0.0-1
oracle-instantclient-odbc.x86_64 - 21.6.0.0.0-1.el8
oracle-instantclient-sqlplus.x86_64 - 21.6.0.0.0-1
** Note - We have also tried using the same Oracle Client Version 19 as well but still the above error occurs. **
** Note - We have also tried this in the Debian 9.3 Stretch as well but still the above error occurs. **
[Below is the odbcinst.ini configuration file]
[Oracle 21 ODBC driver]
Description = Oracle ODBC driver for Oracle 21
Driver = /usr/lib/oracle/21/client64/lib/libsqora.so.21.1
Setup =
FileUsage =
CPTimeout =
CPReuse =
[Below is the odbc.ini file ]
[altumcc]
Description = Oracle ODBC driver for Oracle 21
Driver = Oracle 21 ODBC driver
Database = altumcc
DSN = altumcc
ServerName = altumcc
UserID = altumcc
Password = altumcc
AggregateSQLType = FLOAT
Application Attributes = T
Attributes = W
BatchAutocommitMode = IfAllSuccessful
BindAsFLOAT = F
CacheBufferSize = 20
CloseCursor = F
DisableDPM = F
DisableMTS = T
DisableRULEHint = T
EXECSchemaOpt =
EXECSyntax = T
Failover = T
FailoverDelay = 10
FailoverRetryCount = 10
FetchBufferSize = 64000
ForceWCHAR = F
LobPrefetchSize = 8192
Lobs = T
Longs = T
MaxLargeData = 0
MaxTokenSize = 8192
MetadataIdDefault = F
QueryTimeout = T
ResultSets = T
SQLGetData extensions = F
SQLTranslateErrors = F
StatementCache = F
Translation DLL =
Translation Option = 0
UseOCIDescribeAny = F
[ Below is the “/etc/tnsnames.ora” file ]
altumcc=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.12.78)(PORT=1521)))(CONNECT_DATA=(SID=altumcc)))
[ Below is the “asterisk/res_odbc.conf” file ]
[altumcc]
dsn => altumcc
username => altumcc
password => altumcc
pre-connect => yes
[ Below is the “asterisk/extconfig.conf” file ]
sipusers => odbc,altumcc,sip_buddies
sippeers => odbc,altumcc,sip_buddies
queues => odbc,altumcc,call_queue_values
queue_members => odbc,altumcc,call_queue_members
queue_log => odbc,altumcc,queue_log
Please help us with the solution and let us know whether we are missing anything here. Also we request you to share with us site/reference/doc on how to make the Asterisk communication with Oracle DB for Realtime Communication.
We seeking help on any other specific way on communicating the Oracle Database Tables for Realtime communication from Asterisk.
Thanks in advance.