[asterisk-users] [cdr_odbc] error: Cannot insert the value NULL into column 'calldate'

[Rajkumar S]

Hi,

I am trying to get * log to mssql server. I have odbc and freetds
configured, but my insert query is missing calldate which is a NOT
NULL field in database schema.

cdr_adaptive_odbc: Insert failed on 'sqlserver:cdr'.  CDR failed:
INSERT INTO cdr
(clid,src,dst,dcontext,channel,lastapp,lastdata,duration,billsec,disposition,amaflags,uniqueid)
VALUES ('1000','1000','100','sip','SIP/1000-09388800','AddQueueMember','test,Local/1000 at sip,5,,Agent/1000,Local/1000 at sip',9,9,'ANSWERED',3,'1235681438.1')

Schema is:

My table structure is:

      		CREATE TABLE cdr (
      		        [calldate]      [datetime]              NOT NULL ,
      		        [clid]          [varchar] (80)          NOT NULL ,
      		        [src]           [varchar] (80)          NOT NULL ,
      		        [dst]           [varchar] (80)          NOT NULL ,
      		        [dcontext]      [varchar] (80)          NOT NULL ,
      		        [channel]       [varchar] (80)          NOT NULL ,
      		        [dstchannel]    [varchar] (80)          NOT NULL ,
      		        [lastapp]       [varchar] (80)          NOT NULL ,
      		        [lastdata]      [varchar] (80)          NOT NULL ,
      		        [duration]      [int]                   NOT NULL ,
      		        [billsec]       [int]                   NOT NULL ,
      		        [disposition]   [varchar] (45)          NOT NULL ,
      		        [amaflags]      [int]                   NOT NULL ,
      		        [accountcode]   [varchar] (20)          NOT NULL ,
      		        [uniqueid]      [varchar] (32)          NOT NULL ,
      		        [userfield]     [varchar] (255)         NOT NULL
      		)

How can I make sure that my insert commands reflects my database schema?

I have attached details of all my config files below.

I can connect using isql.

isql -v DSN_NAME sa password
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+

When I make a call I get the following error in console:

Unable to retrieve database handle.  CDR failed.
SQL Execute returned an error -1: 23000: [FreeTDS][SQL Server]Cannot
insert the value NULL into column 'calldate', table
'production.dbo.cdr'; column does not allow nulls. INSERT fails. (153)
SQL Execute returned an error -1: 01000: [FreeTDS][SQL Server]The
statement has been terminated. (55)
SQL Execute error -1! Attempting a reconnect...
Connection is down attempting to reconnect...
Disconnected 0 from sqlserver [DSN_NAME]
Database handle deallocated
Connecting sqlserver
res_odbc: Connected to sqlserver [DSN_NAME]
SQL Execute returned an error -1: 23000: [FreeTDS][SQL Server]Cannot
insert the value NULL into column 'calldate', table
'production.dbo.cdr'; column does not allow nulls. INSERT fails. (153)
SQL Execute returned an error -1: 01000: [FreeTDS][SQL Server]The
statement has been terminated. (55)
SQL Execute error -1! Attempting a reconnect...
Connection is down attempting to reconnect...
Disconnected 0 from sqlserver [DSN_NAME]
Database handle deallocated
Connecting sqlserver
res_odbc: Connected to sqlserver [DSN_NAME]
cdr_adaptive_odbc: Insert failed on 'sqlserver:cdr'.  CDR failed:
INSERT INTO cdr
(clid,src,dst,dcontext,channel,lastapp,lastdata,duration,billsec,disposition,amaflags,uniqueid)
VALUES ('1000','1000','100','sip','SIP/1000-09388800','AddQueueMember','test,Local/1000 at sip,5,,Agent/1000,Local/1000 at sip',9,9,'ANSWERED',3,'1235681438.1')


a16-q1:/etc/asterisk# cat cdr.conf
[general]
enable=yes
;unanswered = no
;batch=no
;size=100
;time=300
;scheduleronly=no
;safeshutdown=yes
;endbeforehexten=no

[csv]
usegmtime=yes    ; log date/time in GMT.  Default is "no"
loguniqueid=yes  ; log uniqueid.  Default is "no"
loguserfield=yes ; log user field.  Default is "no"

[odbc]
usegmtime=yes    ; log date/time in GMT.  Default is "no"
loguniqueid=yes  ; log uniqueid.  Default is "no"
loguserfield=yes ; log user field.  Default is "no"

;
; cdr_odbc.conf
;

[global]
username => sa
password => password
dsn => DSN_NAME
loguniqueid=yes
dispositionstring=yes
table=cdr
usegmtime=yes

a16-q1:/etc/asterisk# cat cdr_adaptive_odbc.conf
[first]
connection=sqlserver
table=cdr
alias calldate => start

a16-q1:/etc/asterisk# cat res_odbc.conf
[ENV]

[sqlserver]
enabled => yes
dsn => DSN_NAME
share_connections => no
limit => 5
username => sa
password => password
pre-connect => yes
sanitysql => select count(*) from systables
backslash_is_escape => no

a16-q1:/etc/asterisk# ls
asterisk.conf           cdr.conf       chan_dahdi.conf
extensions.conf  iax.conf      modules.conf      queuerules.conf
res_odbc.conf
cdr_adaptive_odbc.conf  cdr_odbc.conf  dialplan         func_odbc.conf
  manager.conf  musiconhold.conf  queues.conf      sip.conf

Thanks for reading and with much regards,

raj