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/1...@sip,5,,Agent/1000,Local/1...@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/1...@sip,5,,Agent/1000,Local/1...@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
_______________________________________________
-- Bandwidth and Colocation Provided by http://www.api-digital.com --
asterisk-users mailing list
To UNSUBSCRIBE or update options visit:
http://lists.digium.com/mailman/listinfo/asterisk-users
