[Asterisk-Dev] Realtime fails using unixODBC and FreeTDS against MSSQL

[Trælnes AS]

I get an error "...[FreeTDS][SQL Server]Invalid cursor state ...." when
trying to look up a local sip user. 

We have setup unixODBC using FreeTDS against MSSQL and other things works
ok. Like registering clients, writing CDR (using same DNS) etc.

I posted this problem to the FreeTDS mailing list and got this answer
regarding the "invalid cursor state" error message:
"This can be caused by application trying do use 2 active statement on same
connection (like a select and an update together) or if application do not
close correctly statement (not getting all data)."

If I look in the log it looks like this happens. There are 2 equal sql
statements prepared/executed and then it fails. Looking at other operations
including sql statements it is never 2 equal statements following each
other.

Also the FreeTDS documentations states:
"Is FreeTDS thread safe?
Different threads may all use separate connections without interfering with
each other. Threads may not share a DBPROCESS or CS_CONNECTION without
controlling access via a mutex." 

To summarize my configurations that leads up to this failure:

1. Dialing number 201 from 202 will execute this part in extensions.conf:
[local]
exten => _2XX,1,Dial(SIP/${EXTEN})

2. I guess asterisk then, since this is configured to use realtime, look up
this number form our sip_buddies table (we have named it
RealTimeSipBuddies). I think here is where the error happens: asterisk try
to prepare/execute statement multiple times on same connection which is not
allowed according to FreeTDS.
This is executed 2 times according to unixODBC log:
[ODBC][10551][SQLPrepare.c][180]
                Entry:
                        Statement = 0x81328f0
                        SQL = [SELECT * FROM RealTimeSipBuddies WHERE name =
?][length = 47 (SQL_NTS)]

3. Asterisk returns an error message and the operation is cancelled.

Here is log details given to asterisk console:
Nov 15 12:26:33 WARNING[19734]: res_odbc.c:171 odbc_smart_execute: SQL
Execute returned an error -1: 24000: [FreeTDS][SQL Server]Invalid cursor
state (41)
Nov 15 12:26:33 WARNING[19734]: res_odbc.c:171 odbc_smart_execute: SQL
Execute returned an error -1: 00000: [FreeTDS][SQL Server]Invalid cursor
state (41)
Nov 15 12:26:33 WARNING[19734]: res_config_odbc.c:124 realtime_odbc: SQL
Execute error!
[SELECT * FROM RealTimeSipBuddies WHERE name = ?]


Here is log which gives error from unixODBC:

[ODBC][10551][SQLExecute.c][354]
                Exit:[SQL_ERROR]
                DIAG [24000] [FreeTDS][SQL Server]Invalid cursor state

4. After this I will get several other error messages when trying to do
other things that executes realtime statements. Like closing client, and try
to register again then login fails. This only happens after the "Invalid
cursor state" happens. Before that registering clients is no problem.

Any ideas about how to solve this?

Best regards,
Marius