[Home]

Summary:ASTERISK-01330: CDR-ODBC and MS-Sql 2000 no entries are made, but CDR-ODBC reports success
Reporter:thepartydog (thepartydog)Labels:
Date Opened:2004-04-02 13:27:22.000-0600Date Closed:2004-09-25 02:12:19
Priority:MinorRegression?No
Status:Closed/CompleteComponents:Core/General
Versions:Frequency of
Occurrence
Related
Issues:
Environment:Attachments:
Description:I have setup an ODBC connection to a MS-SQL 2000 database. On the asterisk box (0.7.2-stable),I am able to write to the database from php, tsql, and isql. At startup, Asterisk reports a good connection to the Data Source. At the end of every call, it reports "cdr_odbc: Query Successful!", but no data is written to the table in MS-SQL.

****** ADDITIONAL INFORMATION ******

I know this is far out on the bleeding edge to get these systems working together. I think the issue is with the way that asterisk uses unix-odbc sqlcmd, but I'm not sure yet. My php inserts don't use it.
Comments:By: Brian West (bkw918) 2004-04-02 23:50:14.000-0600

post your freetds, unixodbc (odbc.ini and odbcinst.ini), your table structure. cdr_odbc.conf (I bet the userfield is missing from your table structure)

bkw
By: Brian West (bkw918) 2004-04-02 23:52:36.000-0600

Also check out http://bugs.digium.com/bug_view_page.php?bug_id=0000578

MSSQL 7.0 works with it.. maybe M$ has toyed with MSSQL 2000 so it won't work.  Maybe freetds needs to be updated??!?!?
By: thepartydog (thepartydog) 2004-04-05 14:51:29

Let's start with the table stucture, since it is the one I know the least about.
the fields are:

calldate datetime
clid varchar(80)
src varchar(80)
dst varchar(80)
dcontext varchar(80)
channel varchar(80)
dstchannel varchar(80)
lastapp varchar(80)
lastdata varchar(80)
duration int
billsec int
disposition varchar(45)
amaflags int
accountcode varchar(20)
uniqueid varchar(32)
userfield varchar(255)
By: thepartydog (thepartydog) 2004-04-05 15:09:14

Free Tds is version 0.63
freetds.conf:
# A typical Microsoft SQL Server 2000 configuration
[barracuda]
host = barracuda.*************.com
port = 1433
tds version = 8.0

unixODBC is version 2.2.8

odbc.ini:
[MS-SQL]
Description     = MS-SQL ODBC Driver for asterisk
Driver          = MS-SQL
Servername      = barracuda
UserName        = asterisk
Password        = spelling

odbcinst.ini:
[MS-SQL]
Description     = ODBC for MS-SQL
Driver          = /usr/local/lib/libtdsodbc.so
FileUsage       = 5
UserName        = asterisk
Password        = ********

It pretty much all looks good to me.
Darin

edited on: 04-05-04 14:02
By: Brian West (bkw918) 2004-04-05 23:50:56

from src or RPM?  If you installed from package/RPM compile from src and try again.  I have seen this problem before...
By: Brian West (bkw918) 2004-04-05 23:51:55

Also put a debug in to print the query... then manually run that query from isql to see if it fails or not.  If it fails show me the query.. then try to make it not fail manually then I will modify it to work correctly in this case.

Thanks,
bkw
By: thepartydog (thepartydog) 2004-04-06 12:38:51

All are built from latest available source. Exactly how do you put a debug in to print the query?


Darin

btw Thanks for all the help.
By: Brian West (bkw918) 2004-04-06 12:56:56

accually you can't the way its setup now... or I think you can't... the joys of ODBC are here!!!

but around:

ODBC_res = SQLPrepare(ODBC_stmt, sqlcmd, SQL_NTS);

put an

ast_verbose( VERBOSE_PREFIX_4 "DEBUG: %s\n", sqlcmd);

Before and after that to see if you get a query thats filled in or not.

:)
By: Brian West (bkw918) 2004-04-06 12:59:22

strike that

      > DEBUG: INSERT INTO cdr (calldate,clid,src,dst,dcontext,channel,dstchannel,lastapp,lastdata,duration,billsec,disposition,amaflags,accountcode,uniqueid,userfield) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
      > DEBUG: INSERT INTO cdr (calldate,clid,src,dst,dcontext,channel,dstchannel,lastapp,lastdata,duration,billsec,disposition,amaflags,accountcode,uniqueid,userfield) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)


Thats all you will get.
By: Brian West (bkw918) 2004-04-06 13:02:44

INSERT INTO cdr (calldate,clid,src,dst,dcontext,channel,dstchannel,lastapp,lastdata,duration,billsec,disposition,amaflags,accountcode,uniqueid,userfield) VALUES ('2003-11-19 18:26:15','\"Extension 14\" <14>','14','14','default', 'SIP/14-7c01','','VoiceMail','u14',2,2,'ANSWERED',3,'','1234567890.0','THIS SUCKS')
By: thepartydog (thepartydog) 2004-04-06 13:26:28

The debug returned:
     > DEBUG: INSERT INTO cdr (calldate,clid,src,dst,dcontext,channel,dstchannel,lastapp,lastdata,
duration,billsec,disposition,amaflags,accountcode,uniqueid,userfield) VALUES (?,?,?,?,?,?,?,?,?,?,?,
?,?,?,?,?)
      > DEBUG: INSERT INTO cdr (calldate,clid,src,dst,dcontext,channel,dstchannel,lastapp,lastdata,
duration,billsec,disposition,amaflags,accountcode,uniqueid,userfield) VALUES (?,?,?,?,?,?,?,?,?,?,?,
?,?,?,?,?)
      > cdr_odbc: Query Successful!

But again nothing in the database.

I ran your query above with isql, it returnded "SQLRowCount returns -1" and that data is in the database table.

Darin
By: Brian West (bkw918) 2004-04-06 16:26:21

If I had access to a server I could figure it out...  Its been a while since I did cdr_odbc.c I will have to jump in and look over it again.  Find me on IRC #asterisk-bugs @ irc.freenode.net bkw_ is my nick.

bkw
By: Brian West (bkw918) 2004-04-08 17:11:30

run the query from the bug not dated 04-06-04 13:02

and let me know what it does.
By: thepartydog (thepartydog) 2004-04-08 17:17:00

I ran the query from the bug note dated 04-06-2004 13:02 with isql and it returnded "SQLRowCount returns -1" and the data is in the database table.

Darin
By: Brian West (bkw918) 2004-04-10 06:55:31

try to modify that query till it works.. then we will see why its foobared.
By: thepartydog (thepartydog) 2004-04-13 12:34:36

My appologies, the query did work from isql.
By: Brian West (bkw918) 2004-04-16 20:05:23

so I wonder whats wrong.... does it work now?
By: Brian West (bkw918) 2004-04-30 00:10:34

Let me know if its fixed now if not we will reopen.
By: Brian West (bkw918) 2004-05-20 20:25:29

FOUND THE FIX!!! w00t!
By: Brian West (bkw918) 2004-05-20 20:26:36

http://asterisk.bkw.org/diff/cdr_odbc.diff

coming to a CVS near you... apparently FreeTDS has issues this should fix it for good.