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

[Rusty Newton (JIRA)]

     [ https://issues.asterisk.org/jira/browse/ASTERISK-22826?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Rusty Newton reassigned ASTERISK-22826:
---------------------------------------

    Assignee:     (was: Rusty Newton)
    
> 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
>            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