[freetds] Invalid Cursor State

[Trælnes AS]

Hello!

We are running an Asterisk BETA 1.2 on Linux and we use Asterisk Realtime
against MSSQL database. Realtime uses ODBC to communicate with the MSSQL and
we have unixODBC that uses FreeTDS to talk to MSSQL. During some of the sql
calls that Asterisk makes we get an ODBC error and when I put on ODBC trace
I get the following error:

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

When surfing the net I can find some samples of this error but no actual
solution. Try searching google with: FreeTDS "Invalid cursor state".

I understand that this error message can have several sources of error but
since I find other having the same problem there could be some problem with
FreeTDS and I am looking for a workaround or some way to dig deeper into
this problem.

Can anyone help me to solve this?

Best regards
Marius

[Frediano Ziglio]

Post by Trælnes AS
Hello!
We are running an Asterisk BETA 1.2 on Linux and we use Asterisk Realtime
against MSSQL database. Realtime uses ODBC to communicate with the MSSQL and
we have unixODBC that uses FreeTDS to talk to MSSQL. During some of the sql
calls that Asterisk makes we get an ODBC error and when I put on ODBC trace
[ODBC][9136][SQLExecute.c][354]
Exit:[SQL_ERROR]
DIAG [24000] [FreeTDS][SQL Server]Invalid cursor state
When surfing the net I can find some samples of this error but no actual
solution. Try searching google with: FreeTDS "Invalid cursor state".
I understand that this error message can have several sources of error but
since I find other having the same problem there could be some problem with
FreeTDS and I am looking for a workaround or some way to dig deeper into
this problem.
Can anyone help me to solve this?
Best regards
Marius

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).

freddy77

[James K. Lowden]

Post by Frediano Ziglio

Post by Trælnes AS
[ODBC][9136][SQLExecute.c][354]
Exit:[SQL_ERROR]
DIAG [24000] [FreeTDS][SQL Server]Invalid cursor state

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 you can reproduce the problem with a limited test, a TDSDUMP log will
show whether or not your application is making the mistakes Frediano
cites.

--jkl

[Trælnes AS]

Post by Frediano Ziglio

Post by Trælnes AS
[ODBC][9136][SQLExecute.c][354]
Exit:[SQL_ERROR]
DIAG [24000] [FreeTDS][SQL Server]Invalid cursor state

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 you can reproduce the problem with a limited test, a TDSDUMP log will
show whether or not your application is making the mistakes Frediano
cites.

Thanks for your response.

1. It looks like it could be 2 active statements since in the unixODBC log
there are 2 statements just after each other and the error happens right
after that>

[ODBC][2715][SQLPrepare.c][180]
Entry:
Statement = 0x8133988
SQL = [SELECT * FROM RealTimeSipBuddies WHERE name =
?][length = 47 (SQL_NTS)]
[ODBC][2715][SQLAllocHandle.c][872]
Exit:[SQL_SUCCESS]
Output Handle = 0x8213d88
[ODBC][2715][SQLPrepare.c][180]
Entry:
Statement = 0x8213d88
SQL = [SELECT * FROM RealTimeSipBuddies WHERE name =
?][length = 47 (SQL_NTS)]


2. I reproduced the problem and I got a TDSDUMP. But for it seems to be ok,
I cannot see anything wrong here. But also I see a lot I do not
understand... Can maybe someone have a quick look at the log file attached?
I also find it to be strange that in the TDSDUMP I cannot find 24000 or
"Invalid cursor state" or other messages that I find in the unixODBC
log....?

3. If it is so that there are 2 active statements on same connection would
the problem then be in unixODBC (it doesn't handle threads?) or in the
client application? (which in this case is Asterisk....)

Best regards,
Marius
-------------- next part --------------
A non-text attachment was scrubbed...
Name: freetds.log.3.zip
Type: application/x-zip-compressed
Size: 37419 bytes
Desc: not available
Url : http://lists.ibiblio.org/pipermail/freetds/attachments/20051115/0e9e5a05/attachment.bin

[Lowden, James K]

From: Tr?lnes AS
Sent: Tuesday, November 15, 2005 11:13 AM

Post by Frediano Ziglio

Post by Trælnes AS
[ODBC][9136][SQLExecute.c][354]
Exit:[SQL_ERROR] DIAG [24000] [FreeTDS][SQL
Server]Invalid cursor state

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).

1. It looks like it could be 2 active statements since in the
unixODBC log there are 2 statements just after each other and the
error happens right after that>
[ODBC][2715][SQLPrepare.c][180]
Statement = 0x8133988
SQL = [SELECT * FROM RealTimeSipBuddies WHERE name =
?][length = 47 (SQL_NTS)] [ODBC][2715][SQLAllocHandle.c][872]
Exit:[SQL_SUCCESS]
Output Handle = 0x8213d88
[ODBC][2715][SQLPrepare.c][180]
Statement = 0x8213d88
SQL = [SELECT * FROM RealTimeSipBuddies WHERE name =
?][length = 47 (SQL_NTS)]

Yes.

2. I reproduced the problem and I got a TDSDUMP. But for it seems
to be ok, I cannot see anything wrong here. But also I see a lot
I do not understand... Can maybe someone have a quick look at the
log file attached? I also find it to be strange that in the TDSDUMP
I cannot find 24000 or "Invalid cursor state" or other messages
that I find in the unixODBC log....?

It's a little odd. When the tds library hits a problem like that, it normally produces a message 20019:

"Attempt to initiate a new SQL Server operation with results pending."

Such messages are logged always in 0.63 and are logged in the snapshot if 'debug flags' in freetds.conf includes 0x80 (the default). (Memo to file: make sure the debug mask is mentioned at the top of the TDSDUMP log.)

Your log doesn't include such a message. Perhaps the DM detected the problem before passing it to FreeTDS.

3. If it is so that there are 2 active statements on same connection
would the problem then be in unixODBC (it doesn't handle threads?)
or in the client application? (which in this case is Asterisk....)

The client application. It's not a matter of threading; it's a matter of the design of the TDS protocol and the of the server. Cf. http://www.freetds.org/faq.html#pending.

I think for the moment you're stuck. :-(

Regards,

--jkl

-----------------------------------------
The information contained in this transmission may be privileged and
confidential and is intended only for the use of the person(s) named
above. If you are not the intended recipient, or an employee or agent responsible
for delivering this message to the intended recipient, any review, dissemination,
distribution or duplication of this communication is strictly prohibited. If you are
not the intended recipient, please contact the sender immediately by reply e-mail
and destroy all copies of the original message. Please note that we do not accept
account orders and/or instructions by e-mail, and therefore will not be responsible
for carrying out such orders and/or instructions. If you, as the intended recipient
of this message, the purpose of which is to inform and update our clients, prospects
and consultants of developments relating to our services and products, would not
like to receive further e-mail correspondence from the sender, please "reply" to the
sender indicating your wishes. In the U.S.: 1345 Avenue of the Americas, New York,
NY 10105.