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

[Marcelo Terres (JIRA)]

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

Marcelo Terres commented on ASTERISK-22826:
-------------------------------------------

What's the solution?

It continues to not working in Asterisk 13.6. And now that cdr_odbc show not be used anymore (https://issues.asterisk.org/jira/browse/ASTERISK-23770), I think it will be nice to solve this question correctly.



> 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 was sent by Atlassian JIRA
(v6.2#6252)