ODBC Connection issues

Asterisk Asterisk Support
1

Hi there,

I am attempting to connect Asterisk 22 to a MariaDB server with ODBC. ODBC is working correctly, verified by running isql -v asterisk and it connects successfully:

root@sip:/etc/asterisk# isql -v asterisk
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| echo [string]                         |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

I have verified in MySQL/MariaDB logs that the correct user is connecting to the correct database, however when I run `database query “SELECT * FROM ps_endpoints LIMIT 1;” I get an error telling me there is no such table:

sip*CLI> database query "SELECT * FROM ps_endpoints LIMIT 1;"
[2025-03-27 15:40:21] WARNING[3131]: db.c:335 db_execute_sql: Error executing SQL (SELECT id FROM ps_endpoints LIMIT 1;): no such table: ps_endpoints

It works when running it in isql:

SQL> SELECT id FROM ps_endpoints;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                                                                                                                                                                                                                                             |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
SQLRowCount returns 0
SQL> SELECT id FROM ps_endpoints;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                                                                                                                                                                                                                                             |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1                                                                                                                                                                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
SQLRowCount returns 1
1 rows fetched
2

On Thursday 27 March 2025 at 09:24:43, santasenforcer via Asterisk Community
wrote:

Hi there,

I am attempting to connect Asterisk 22 to a MariaDB server with ODBC.

What is the content of /etc/asterisk/func_odbc.conf ?

Antony.

–
I bought a book on memory techniques, but I’ve forgotten where I put it.

3

On Thursday 27 March 2025 at 09:24:43, santasenforcer via Asterisk Community
wrote:

Hi there,

I am attempting to connect Asterisk 22 to a MariaDB server with ODBC.

sip*CLI> database query “SELECT * FROM ps_endpoints LIMIT 1;”

Has Asterisk 22 changed the “database” command so that it now works with
external ODBC connections?

Previous versions use the “database” command to interrogate / manipulate the
internal Asterisk SQLite database…

Antony.

“The future is already here. It’s just not evenly distributed yet.”

  • William Gibson
4

Not a lot at the moment as I have been testing. I am still not seeing “Connected: Yes” when querying odbc show:

sip*CLI> odbc show

ODBC DSN Settings
-----------------

  Name:   asterisk
  DSN:    asterisk
    Number of active connections: 1 (out of 100)
    Logging: Enabled
    Number of prepares executed: 0
    Number of queries executed: 0

function_odbc.conf is:

[general]
enabled=yes

[testdb]
dsn=asterisk
readsql=SELECT 1;
5

Such a line doesn’t exist. It shows you the number of active connections, which is 1 so there is a connection.

6

Thanks. You’ve actually helped a bit here.

I can get it working through the func_odbc.conf now. Although when I try and use the pjsip commands, for example pjsip show endpoints, I get a “No objects found”, and the user I have created fails to register as well.

7

Provide the contents of sorcery.conf and extconfig.conf

8

As well as the schemas, as recently at least one person had the wrong schema as they found it… somewhere else.

9

Thanks,

sorcery.conf:

[general]
autoload=yes
debug = yes

[res_pjsip]
endpoint = odbc,asterisk,ps_endpoints
aor = odbc,asterisk,ps_aors
auth = odbc,asterisk,ps_auths
contact = odbc,asterisk,ps_contacts
transport = odbc,asterisk,ps_transports

extconfig.conf:

[settings]
ps_endpoints => odbc,asterisk
ps_auths => odbc,asterisk
ps_aors => odbc,asterisk
ps_contacts => odbc,asterisk
ps_transports => odbc,asterisk

Schema I got from the asterisk 22.2.0 download in contrib/realtime/mysql/mysql_config.sql.

10

This is incorrect. It should be “realtime” instead of “odbc”. You also REALLY don’t want to put transports in ODBC.

11

So sorcery.conf should be realtime instead of odbc, for example:

endpoint = realtime,asterisk,ps_endpoints
aor = realtime,asterisk,ps_aors
auth = realtime,asterisk,ps_auths
contact = realtime,asterisk,ps_contacts
transport = realtime,asterisk,ps_transports

instead?

12

The sample[1] has the full correct format.

[1] asterisk/configs/samples/sorcery.conf.sample at master · asterisk/asterisk · GitHub

13

It’s also on the docs site[1].

[1] Setting up PJSIP Realtime - Asterisk Documentation

14

Great. Thank you!

I’ve now managed to lose the ability to pjsip show endpoints since changing over to realtime :sweat_smile: and all I get now is pjsip export.

15

Consult the console output at startup to see what failed.

16

Thanks for all your help. Looking at the log I am getting the following lot of errors when searching specifically for pjsip:

root@sip:/var/log/asterisk# tail -f /var/log/asterisk/full | grep pjsip
[2025-03-27 23:17:48] ERROR[21222] sorcery.c: Error attempting to apply configuration res_pjsip to sorcery.
[2025-03-27 23:17:48] ERROR[21222] res_pjsip/config_system.c: Failed to open SIP system sorcery
[2025-03-27 23:17:48] ERROR[21222] res_pjsip.c: Failed to initialize SIP 'system' configuration section. Aborting load
[2025-03-27 23:17:48] ERROR[21222] loader.c: res_pjsip declined to load.
[2025-03-27 23:17:48] ERROR[21222] loader.c: Declined modules which depend on res_pjsip: res_pjsip_authenticator_digest, res_pjsip_endpoint_identifier_ip, res_pjsip_endpoint_identifier_user, res_pjsip_registrar, res_pjsip_geolocation, res_pjsip_pubsub, res_pjsip_pidf_body_generator, res_pjsip_outbound_authenticator_digest, res_pjsip_outbound_publish, res_pjsip_xpidf_body_generator, res_pjsip_aoc, res_pjsip_mwi_body_generator, res_pjsip_pidf_digium_body_supplement, res_pjsip_dialog_info_body_generator, res_pjsip_pidf_eyebeam_body_supplement, res_pjsip_mwi, res_pjsip_exten_state, res_pjsip_publish_asterisk, res_pjsip_sdp_rtp, chan_pjsip, res_pjsip_t38, res_pjsip_outbound_registration, res_pjsip_transport_websocket, res_pjsip_nat, res_pjsip_one_touch_record_info, res_pjsip_history, res_pjsip_messaging, res_pjsip_dlg_options, res_pjsip_logger, res_pjsip_acl, res_pjsip_diversion, res_pjsip_notify, res_pjsip_path, res_pjsip_dtmf_info, res_pjsip_empty_info, res_pjsip_phoneprov_provider, res_pjsip_rfc3326, res_pjsip_session, res_pjsip_send_to_voicemail, res_pjsip_sips_contact, res_pjsip_header_funcs, res_pjsip_refer, res_pjsip_rfc3329, res_pjsip_caller_id, func_pjsip_aor, func_pjsip_endpoint, func_pjsip_contact, res_prometheus, res_pjsip_endpoint_identifier_anonymous, res_hep_pjsip

My sorcery.conf file is now:

root@sip:/etc/asterisk# cat sorcery.conf
[res_pjsip]
endpoint=realtime,ps_endpoints
auth=realtime,ps_auths
aor=realtime,ps_aors
domain_alias=realtime,ps_domain_aliases
contact=realtime,ps_contacts

[res_pjsip_endpoint_identifier_ip]
identify=realtime,ps_endpoint_id_ips
17

Is res_pjsip loading before res_odbc[1]?

[1] Setting up PJSIP Realtime - Asterisk Documentation

18

No, res_odbc is loading before res_pjsip.

19

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