[Home]

Summary:ASTERISK-14887: [patch] SQLColumns Error When Using Postgresql Schema
Reporter:Steve Hindmarch (hindmasj)Labels:
Date Opened:2009-09-25 04:35:21Date Closed:2011-06-07 14:08:24
Priority:MinorRegression?No
Status:Closed/CompleteComponents:Resources/res_odbc
Versions:Frequency of
Occurrence
Related
Issues:
Environment:Attachments:( 0) 20090923__catalog_and_schema.diff.txt
( 1) 20090925__issue15963__1.6.1.diff.txt
( 2) sqltest.c
Description:The call to SQLColumns in function ast_odbc_find_table in res_odbc.c does not properly use the catalog and schema fields and so database tables that use these are not properly described by the function.

This was tested using Asterisk 1.6.1.6 on Fedora Core 10. Database packages used were

postgresql-8.3.8-1.fc10.x86_64
postgresql-odbc-08.03.0200-1.fc10.x86_64
unixODBC-2.2.12-9.fc10.x86_64

Test was repeated on a server running OEL 5 with

postgresql-8.1.11-1.el5_1.1
postgresql-odbc-08.01.0200-3.1
unixODBC-2.2.11-7.1


****** ADDITIONAL INFORMATION ******

As an example, realtime for SIP will check the table structure to ensure the correct fields for registration exist before it will complete a registration. If you set up a table to use realtime SIP configuration and define the table in extconfig.conf as "tname" then you can use realtime to register peers. But if you define the table as "public.tname" then registration fails and you get log debug messages like the following.

WARNING[10431] res_config_odbc.c: Key field 'ipaddr' does not exist in table 'foo.sip@asterisk'.  Update will fail
DEBUG[10431] res_config_odbc.c: Skip: 62; SQL: UPDATE public.sip SET ipaddr=? WHERE name=?
DEBUG[10431] res_config_odbc.c: Parameter 1 ('ipaddr') = '10.215.42.138'
DEBUG[10431] res_config_odbc.c: Skipping field 'port'='5060' (2/76)
DEBUG[10431] res_config_odbc.c: Skipping field 'regseconds'='1253704257' (4/76)
DEBUG[10431] res_config_odbc.c: Skipping field 'useragent'='ipDialog SipTone 1.2.0 rc Z_21 UA' (10/76)
DEBUG[10431] res_config_odbc.c: Skipping field 'lastms'='0' (20/76)
DEBUG[10431] res_config_odbc.c: Skipping field 'defaultuser'='stone' (40/76)
Comments:By: Tilghman Lesher (tilghman) 2009-09-25 10:08:41

Patch uploaded and ready for testing.
By: Tilghman Lesher (tilghman) 2009-09-25 10:12:52

Separate patch for 1.6.1 (original patch is for trunk) because of some minor differences in the includes.
By: Steve Hindmarch (hindmasj) 2009-09-28 09:24:31

I have tried the 1.6.1 patch and the part that splits the schema out of the name works but still the structure of the table is not described. It appears that the result set returned is empty. The result of the first SQLFetch is SQL_NO_DATA and the loop is not entered. When the same test is applied without a schema name all of the table columns are properly described.

Selecting data from the tables succeeds whether or not a schema name is used.

I have created a little test app that isolates the problem away from any asterisk code. Running this test app shows that the problem is in ODBC.


By: Tilghman Lesher (tilghman) 2009-09-28 18:09:04

In that case, file an issue with the psqlodbc project.