BUG & test case: ODBC client hangs or gets an error when I..

Hi,

the application below runs into Error 24000 "Invalid Cursor State" after
executing the INSERT a few times.

If I change the Trigger to

CREATE TRIGGER Trigger1 ON [Tabelle1]
FOR INSERT NOT FOR REPLICATION
AS
BEGIN
INSERT INTO Tabelle2
SELECT DISTINCT Text1
FROM inserted l
LEFT JOIN Tabelle2 r ON l.Text1 = r.[Key]
WHERE r.[Key] IS NULL
END

the ODBC client hangs with 100% CPU somewhere in the ODBC stack. No
Network activity. The last command executed in the Enterprise Manager
says "dbcc inputbuffer(54)" while the client hangs.

Inserts from other sources like Query Analyzer or a native JDBC driver
work as expected in any case (at least so far).


System environment
==================
ODBC 2000.85.1132.00 <-> SQL Server 8.00.760 SP3
ODBC 2000.85.1132.00 <-> SQL Server 8.00.2039 SP4
ODBC 3.70.11.46 <-> SQL Server 8.00.2039 SP4

I did not find any differences between the three scenarios except that
the first one crashed a bit faster than the others (usually no more than
5 lines).

The application screnario is a bit more complicated than shown, but the
principal structure and the behavior is the same.

I have no quantitative numbers but it seems that a higher performance
clients and/or network connections raises the probability of this error.
This might be the explanation why the application has run successfully
for about 6 years and half a million inserts.


Marcel Müller


Table Structure
===============
CREATE TABLE [dbo].[Tabelle1] (
[text1] [varchar] (255) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Tabelle2] (
[key] [varchar] (255) NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Tabelle2] WITH NOCHECK ADD
CONSTRAINT [PK_Tabelle2] PRIMARY KEY CLUSTERED
(
[key]
) ON [PRIMARY]
GO

CREATE TRIGGER Trigger1 ON [Tabelle1]
FOR INSERT NOT FOR REPLICATION
AS
IF @@ROWCOUNT = 1
BEGIN
DECLARE @key AS VARCHAR(255)
SET @key = (SELECT TOP 1 Text1 FROM inserted)
IF NOT EXISTS (SELECT * FROM Tabelle2 WHERE [Key] = @key)
INSERT INTO Tabelle2 VALUES (@key)
END
ELSE
BEGIN
INSERT INTO Tabelle2
SELECT DISTINCT Text1
FROM inserted l
LEFT JOIN Tabelle2 r ON l.Text1 = r.[Key]
WHERE r.[Key] IS NULL
END
/*
Tabelle 2 holds a fast and consistent version of SELECT DISTINCT of
Tabelle 1. Tabelle1 typically contains only some hundrets of distinct
values in the related column while the total number of lines is in the
order of 10^7 to 10^8, which makes even a stream aggregate on the index
slow.
*/
===============

Application
===============
// Test.cpp : Defines the entry point for the console application.

#include <stdio.h>
#include <windows.h>
#include <sql.h>
#include <sqlext.h>

const char* sConnect =
"DRIVER={SQL SERVER};SERVER=yamato;UID=sa;PWD=***;Database=Statistik";
const char* sStmt = "INSERT INTO Tabelle1 (Text1) VALUES (?)";

// db-Handles for odbc-access (Owned by the connection thread)
HENV hEnv;
HDBC hDBC;
HSTMT hStmt;

char col1[255];
SQLINTEGER len1;


RETCODE evalSQLFunc(RETCODE result, const char* text)
{ switch (result)
{case SQL_INVALID_HANDLE:
printf("\n%s SQL Error: invalid handle.\n", text);
exit(1);
case SQL_ERROR:
UCHAR szErrMsg[4000];
UCHAR szSqlState[6];
SQLINTEGER errnr;
SQLSMALLINT errmsg;
SQLError(hEnv, hDBC, hStmt, szSqlState, &errnr, szErrMsg, sizeof
szErrMsg, &errmsg);
printf("\n%s SQLError: %.*s\nSQLState: %s\n", text, sizeof szErrMsg,
szErrMsg, szSqlState);
exit(2);
}
return result;
}

int main(int argc, char* argv[])
{
evalSQLFunc(SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv),
"SQLAllocHandle(ENV...)");
evalSQLFunc(SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION,
(SQLPOINTER)SQL_OV_ODBC3, SQL_IS_INTEGER), "SQLSetEnvAttr");
evalSQLFunc(SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDBC),
"SQLAllocHandle(DBC...)");
evalSQLFunc(SQLSetConnectAttr(hDBC, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER)
1, SQL_INTEGER), "SQLSetConnectAttr(SQL_ATTR_AUTOCOMMIT...)");

SQLCHAR szOutConn[2048];
SQLSMALLINT cbOutConn;
evalSQLFunc(SQLDriverConnect(hDBC, 0, (UCHAR*)sConnect, SQL_NTS,
szOutConn, sizeof(szOutConn), &cbOutConn, SQL_DRIVER_NOPROMPT),
"SQLDriverConnect");

/* SQL INSERT-STATEMENT */
evalSQLFunc(SQLAllocHandle(SQL_HANDLE_STMT, hDBC, &hStmt),
"SQLAllocHandle(STMT...)");
evalSQLFunc(SQLPrepare(hStmt, (UCHAR*)sStmt,
(SQLINTEGER)strlen(sStmt)), "SQLPrepare");

evalSQLFunc(SQLBindParameter(hStmt, 1, SQL_PARAM_INPUT, SQL_C_BINARY,
SQL_VARCHAR, sizeof col1, 0, col1, 0, &len1), "SQLBindParameter(1...)");

for (int i = 0; i < 100000; ++i)
{ len1 = sprintf(col1, "Test %i\n", i);
evalSQLFunc(SQLExecute(hStmt), "SQLExecute");
printf("\r%s", col1);
}

return 0;
}

Having pleasure using this old stuff?

In your case, maybe you have some sort of concurrency problem: the
SQL-Server has yet to finish the previous insert when the next arrives. I
would think that this is impossible because you are using a single
connection but with this old stuff, we never know. If I were you, I would
use the serializable isolation level to make the insert. For example, in
the case where @@row = 1:

...
IF NOT EXISTS (SELECT * FROM Tabelle2 with (Updlock, Holdlock)
WHERE [Key] = @key)
INSERT INTO Tabelle2 VALUES (@key)
...

Also, your design looks curious and not well normalized. If the values for
@key are the same between Tabelle1 and Tabelle2, then you should store it
only on Tabelle2 and use a foreign key relationship between Tabelle1 and
Tabelle2 using the primary key of Tabelle2. After that, you use a stored
procedure to check the value of the key against Tabelle2 and insert it if
it's not already there and only after that, make the insertion into
Tabelle1.

Using Triggers might looks funny but this can lead you to a lot of trouble.
For example, with this design, the value of @@identity is changed inside the
trigger when you perform an insertion on Tabelle1. Some stuff - for example
using an ODBC linked table inside Access - will choke on this.

Also, if I were you, I would use ADO and an OLEDB provider instead of this
sql.h stuff; see for example:

http://www.codersource.net/mfc_ado_insert.html
http://www.codersource.net/c++_ado_stored_procedure.html
http://msdn.microsoft.com/en-us/library/aa905885(SQL.80).aspx

Probably that a lot of people will tell you that using ODBC will give you a
faster code because it is older (and simpler) but when your code chokes and
stop, there is nothing faster in it.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"Marcel Müller" wrote in message

> Hi,
>
> the application below runs into Error 24000 "Invalid Cursor State" after
> executing the INSERT a few times.
>
> If I change the Trigger to
>
> CREATE TRIGGER Trigger1 ON [Tabelle1]
> FOR INSERT NOT FOR REPLICATION
> AS
> BEGIN
> INSERT INTO Tabelle2
> SELECT DISTINCT Text1
> FROM inserted l
> LEFT JOIN Tabelle2 r ON l.Text1 = r.[Key]
> WHERE r.[Key] IS NULL
> END
>
> the ODBC client hangs with 100% CPU somewhere in the ODBC stack. No
> Network activity. The last command executed in the Enterprise Manager says
> "dbcc inputbuffer(54)" while the client hangs.
>
> Inserts from other sources like Query Analyzer or a native JDBC driver
> work as expected in any case (at least so far).
>
>
> System environment
> ==================
> ODBC 2000.85.1132.00 <-> SQL Server 8.00.760 SP3
> ODBC 2000.85.1132.00 <-> SQL Server 8.00.2039 SP4
> ODBC 3.70.11.46 <-> SQL Server 8.00.2039 SP4
>
> I did not find any differences between the three scenarios except that the
> first one crashed a bit faster than the others (usually no more than 5
> lines).
>
> The application screnario is a bit more complicated than shown, but the
> principal structure and the behavior is the same.
>
> I have no quantitative numbers but it seems that a higher performance
> clients and/or network connections raises the probability of this error.
> This might be the explanation why the application has run successfully for
> about 6 years and half a million inserts.
>
>
> Marcel Müller
>
>
> Table Structure
> ===============
> CREATE TABLE [dbo].[Tabelle1] (
> [text1] [varchar] (255) NOT NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Tabelle2] (
> [key] [varchar] (255) NOT NULL
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[Tabelle2] WITH NOCHECK ADD
> CONSTRAINT [PK_Tabelle2] PRIMARY KEY CLUSTERED
> (
> [key]
> ) ON [PRIMARY]
> GO
>
> CREATE TRIGGER Trigger1 ON [Tabelle1]
> FOR INSERT NOT FOR REPLICATION
> AS
> IF @@ROWCOUNT = 1
> BEGIN
> DECLARE @key AS VARCHAR(255)
> SET @key = (SELECT TOP 1 Text1 FROM inserted)
> IF NOT EXISTS (SELECT * FROM Tabelle2 WHERE [Key] = @key)
> INSERT INTO Tabelle2 VALUES (@key)
> END
> ELSE
> BEGIN
> INSERT INTO Tabelle2
> SELECT DISTINCT Text1
> FROM inserted l
> LEFT JOIN Tabelle2 r ON l.Text1 = r.[Key]
> WHERE r.[Key] IS NULL
> END
> /*
> Tabelle 2 holds a fast and consistent version of SELECT DISTINCT of
> Tabelle 1. Tabelle1 typically contains only some hundrets of distinct
> values in the related column while the total number of lines is in the
> order of 10^7 to 10^8, which makes even a stream aggregate on the index
> slow.
> */
> ===============
>
> Application
> ===============
> // Test.cpp : Defines the entry point for the console application.
>
> #include <stdio.h>
> #include <windows.h>
> #include <sql.h>
> #include <sqlext.h>
>
> const char* sConnect =
> "DRIVER={SQL SERVER};SERVER=yamato;UID=sa;PWD=***;Database=Statistik";
> const char* sStmt = "INSERT INTO Tabelle1 (Text1) VALUES (?)";
>
> // db-Handles for odbc-access (Owned by the connection thread)
> HENV hEnv;
> HDBC hDBC;
> HSTMT hStmt;
>
> char col1[255];
> SQLINTEGER len1;
>
>
> RETCODE evalSQLFunc(RETCODE result, const char* text)
> { switch (result)
> {case SQL_INVALID_HANDLE:
> printf("\n%s SQL Error: invalid handle.\n", text);
> exit(1);
> case SQL_ERROR:
> UCHAR szErrMsg[4000];
> UCHAR szSqlState[6];
> SQLINTEGER errnr;
> SQLSMALLINT errmsg;
> SQLError(hEnv, hDBC, hStmt, szSqlState, &errnr, szErrMsg, sizeof szErrMsg,
> &errmsg);
> printf("\n%s SQLError: %.*s\nSQLState: %s\n", text, sizeof szErrMsg,
> szErrMsg, szSqlState);
> exit(2);
> }
> return result;
> }
>
> int main(int argc, char* argv[])
> {
> evalSQLFunc(SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv),
> "SQLAllocHandle(ENV...)");
> evalSQLFunc(SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION,
> (SQLPOINTER)SQL_OV_ODBC3, SQL_IS_INTEGER), "SQLSetEnvAttr");
> evalSQLFunc(SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDBC),
> "SQLAllocHandle(DBC...)");
> evalSQLFunc(SQLSetConnectAttr(hDBC, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER) 1,
> SQL_INTEGER), "SQLSetConnectAttr(SQL_ATTR_AUTOCOMMIT...)");
>
> SQLCHAR szOutConn[2048];
> SQLSMALLINT cbOutConn;
> evalSQLFunc(SQLDriverConnect(hDBC, 0, (UCHAR*)sConnect, SQL_NTS,
> szOutConn, sizeof(szOutConn), &cbOutConn, SQL_DRIVER_NOPROMPT),
> "SQLDriverConnect");
>
> /* SQL INSERT-STATEMENT */
> evalSQLFunc(SQLAllocHandle(SQL_HANDLE_STMT, hDBC, &hStmt),
> "SQLAllocHandle(STMT...)");
> evalSQLFunc(SQLPrepare(hStmt, (UCHAR*)sStmt, (SQLINTEGER)strlen(sStmt)),
> "SQLPrepare");
>
> evalSQLFunc(SQLBindParameter(hStmt, 1, SQL_PARAM_INPUT, SQL_C_BINARY,
> SQL_VARCHAR, sizeof col1, 0, col1, 0, &len1), "SQLBindParameter(1...)");
>
> for (int i = 0; i < 100000; ++i)
> { len1 = sprintf(col1, "Test %i\n", i);
> evalSQLFunc(SQLExecute(hStmt), "SQLExecute");
> printf("\r%s", col1);
> }
>
> return 0;
> }