[asterisk-bugs] [JIRA] (ASTERISK-22826) cdr_adaptive_odbc SQL execute error with PostgreSQL

[Kevin Harwell (JIRA)]

    [ https://issues.asterisk.org/jira/browse/ASTERISK-22826?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=214076#comment-214076 ] 

Kevin Harwell commented on ASTERISK-22826:
------------------------------------------

Quoting the columns in cdr_adaptive_odbc would possibly break other types of SQL servers (for instance, MySQL doesn't accept quoted column names by default) and we don't want to require users to have to configure their SQL servers a certain way for Asterisk.

When using a postgres server and cdr_adaptive_odbc utilizing aliases is currently the best solution to this problem.
                
> cdr_adaptive_odbc SQL execute error with PostgreSQL
> ---------------------------------------------------
>
>                 Key: ASTERISK-22826
>                 URL: https://issues.asterisk.org/jira/browse/ASTERISK-22826
>             Project: Asterisk
>          Issue Type: Bug
>          Components: CDR/cdr_adaptive_odbc, Resources/res_odbc
>            Reporter: Anthony Messina
>            Assignee: Kevin Harwell
>            Severity: Minor
>
> Names "start" and "end" is reserwed keywords in postgresql and other sql-compliant databases.
> When using following cdr table scheme:
> {noformat}
> CREATE TABLE cdr (
>     id bigint NOT NULL,
>     accountcode character varying(20) NOT NULL,
>     src character varying(80) DEFAULT ''::character varying NOT NULL,
>     dst character varying(80) DEFAULT ''::character varying NOT NULL,
>     dcontext character varying(80) DEFAULT ''::character varying NOT NULL,
>     clid character varying(80) DEFAULT ''::character varying NOT NULL,
>     channel character varying(80) DEFAULT ''::character varying NOT NULL,
>     dstchannel character varying(80) DEFAULT ''::character varying NOT NULL,
>     lastapp character varying(80) DEFAULT ''::character varying NOT NULL,
>     lastdata character varying(80) DEFAULT ''::character varying NOT NULL,
>     start timestamp with time zone DEFAULT now() NOT NULL,
>     answer timestamp with time zone,
>     end timestamp with time zone,
>     duration integer DEFAULT 0,
>     billsec integer DEFAULT 0,
>     disposition disposition_t,
>     amaflags character varying(80) DEFAULT ''::character varying,
>     uniqueid character varying(32) DEFAULT ''::character varying,
>     userfield character varying(255)
> );
> {noformat}
> , there will be an error inserting into cdr table:
> {noformat}
> [Jun 29 11:20:12] ERROR[8297]: cdr_pgsql.c:297 pgsql_log: cdr_pgsql: Failed to insert call detail record into database!
> [Jun 29 11:20:12] ERROR[8297]: cdr_pgsql.c:298 pgsql_log: cdr_pgsql: Reason: ERROR:  syntax error at or near "end"
> LINE 1: ...ntcode,src,dst,dcontext,clid,channel,start,answer,end,durati...
>                                                              ^
> [Jun 29 11:20:12] ERROR[8297]: cdr_pgsql.c:299 pgsql_log: cdr_pgsql: Connection may have been lost... attempting to reconnect.
> [Jun 29 11:20:12] ERROR[8297]: cdr_pgsql.c:302 pgsql_log: cdr_pgsql: Connection reestablished.
> [Jun 29 11:20:12] ERROR[8297]: cdr_pgsql.c:308 pgsql_log: cdr_pgsql: HARD ERROR!  Attempted reconnection failed.  DROPPING CALL RECORD!
> [Jun 29 11:20:12] ERROR[8297]: cdr_pgsql.c:309 pgsql_log: cdr_pgsql: Reason: ERROR:  syntax error at or near "end"
> LINE 1: ...ntcode,src,dst,dcontext,clid,channel,start,answer,end,durati...
>                                                              ^
> [Jun 29 11:20:12] ERROR[8297]: cdr_pgsql.c:297 pgsql_log: cdr_pgsql: Failed to insert call detail record into database!
> [Jun 29 11:20:12] ERROR[8297]: cdr_pgsql.c:298 pgsql_log: cdr_pgsql: Reason: ERROR:  syntax error at or near "end"
> LINE 1: ...,channel,dstchannel,lastapp,lastdata,start,answer,end,durati...
>                                                              ^
> [Jun 29 11:20:12] ERROR[8297]: cdr_pgsql.c:299 pgsql_log: cdr_pgsql: Connection may have been lost... attempting to reconnect.
> [Jun 29 11:20:12] ERROR[8297]: cdr_pgsql.c:302 pgsql_log: cdr_pgsql: Connection reestablished.
> [Jun 29 11:20:12] ERROR[8297]: cdr_pgsql.c:308 pgsql_log: cdr_pgsql: HARD ERROR!  Attempted reconnection failed.  DROPPING CALL RECORD!
> [Jun 29 11:20:12] ERROR[8297]: cdr_pgsql.c:309 pgsql_log: cdr_pgsql: Reason: ERROR:  syntax error at or near "end"
> LINE 1: ...,channel,dstchannel,lastapp,lastdata,start,answer,end,durati...
> {noformat}
> , proposed to rename column "end" to "callend" , "start" to "callstart", and for better naming "answer" to "callanswer".
> ****** ADDITIONAL INFORMATION ******
> Asterisk: asterisk-1.6.0-beta9
> System: Gentoo Linux AMD64, latest
> Database: postgresql-8.3.3

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.asterisk.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira