none
ODBC: Infinite loop when insert multiple rows in table with trigger. RRS feed

  • Question

  • I'm getting an infinite loop when multiple rows are inserted by the same cursor and the table has a trigger. The inserts are done through ODBC 3. Would anyone know of a workaround for this issue? The only one I've found sofar is to do the inserts with separate cursors but that would slow things down when many inserts are done. We also considered moving to ADO.NET but that would require a lot of changes to our software.

    Here's a sample C++ program that gets the loop. The loop appears to be client-side since the program itself gets 100% of the CPU at the second SQLExecute:

        SQLRETURN       rc;
        SQLHENV odbcdr_env; 
    
        rc = SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HENV, &odbcdr_env);
        rc = SQLSetEnvAttr(odbcdr_env, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, SQL_IS_INTEGER);
        
        SQLHDBC	hDbc = SQL_NULL_HDBC;
        rc = SQLAllocHandle(SQL_HANDLE_DBC,	odbcdr_env, &hDbc);
    
        rc = SQLConnectW(hDbc, L"triggers", SQL_NTS, L"", SQL_NTS,L"", SQL_NTS);
    
        SQLHSTMT cursor1;
    
        rc = SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &cursor1 );
    
        rc = SQLPrepareW( cursor1, L"insert into dbo.brent1 (name) values ('odbc')", SQL_NTS);
    
        for ( int i = 0; i < 10; i++ ) {
            rc = SQLExecute(cursor1);
        }
    
        rc = SQLFreeHandle(SQL_HANDLE_STMT, cursor1);
    



    The triggers DSN uses the "SQL Server" driver to point to a SQL Server 2008 database. The smallest schema that demonstrates the problem is:

    USE [TriggerTest2]
    GO
    
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [dbo].[brent1](
    	[name] [nvarchar](50) NOT NULL
    ) ON [PRIMARY]
    
    GO
    
    CREATE TABLE [dbo].[brent2](
    	[name] [nvarchar](50) NOT NULL
    ) ON [PRIMARY]
    
    GO
    
    CREATE TRIGGER [dbo].[tr_brent2_insert] ON [dbo].[brent1]
    FOR INSERT
    AS
    BEGIN
    	DECLARE   @name varchar(50)
    	SELECT  @name=name
    	FROM INSERTED
       
    	INSERT INTO dbo.brent2 (name)	          
    	VALUES(@name)
    END
    
    
    GO
    
    



    I also tried setting different isolation levels but didn't see any difference. Would anyone know of some other settings I could try?

    thanks,
    Brent.

    • Edited by Brent R Monday, March 30, 2009 2:06 PM put samples in code blocks
    Monday, March 30, 2009 1:44 PM

Answers

  • This is really a good catch. the expected behavior should be: the second SQLExecute returns SQL_ERROR with
    24000, Invalid cursor state.
    However, there is an issue in your code. you cannot call SQLExecute with an insert statement continuously. Otherwise, even there is no infinite loop, there will be invalid cursor state error. You should add a SQLMoreResults statement after each SQLExecute.
    here goes the work around.
     rc = SQLPrepareW( cursor1, L"insert into dbo.brent1 (name) values ('odbc')", SQL_NTS);

        for ( int i = 0; i < 10; i++ ) {
            rc = SQLExecute(cursor1);
            rc = SQLMoreResults(cursor1);
        }
     As for the infinite loop. We've filed a bug and will do more investigation.
    Wednesday, April 1, 2009 8:13 AM
    Answerer

All replies

  • Thank you, sir.

    This is a very interesting behavior and I was able to reproduce it very easily with the example you have given.  We are looking into this issue now to see what can be done and why it is behaving this way.

    Thank you again!

    John
    This post is provided 'as is' and confers no express or implied warranties or rights.
    Tuesday, March 31, 2009 8:00 PM
    Moderator
  • This is really a good catch. the expected behavior should be: the second SQLExecute returns SQL_ERROR with
    24000, Invalid cursor state.
    However, there is an issue in your code. you cannot call SQLExecute with an insert statement continuously. Otherwise, even there is no infinite loop, there will be invalid cursor state error. You should add a SQLMoreResults statement after each SQLExecute.
    here goes the work around.
     rc = SQLPrepareW( cursor1, L"insert into dbo.brent1 (name) values ('odbc')", SQL_NTS);

        for ( int i = 0; i < 10; i++ ) {
            rc = SQLExecute(cursor1);
            rc = SQLMoreResults(cursor1);
        }
     As for the infinite loop. We've filed a bug and will do more investigation.
    Wednesday, April 1, 2009 8:13 AM
    Answerer
  • FYI:

    After more investigation, we found that the bug is in W2K3 (perhaps, WinXP).
    But it was fixed in Vista or later releases of Windows.

    Thanks,
    Ming.
    WDAC Team, Microsoft.


    Pak-Ming Cheung - MSFT
    Thursday, April 2, 2009 8:47 AM
    Answerer
  • Thanks everyone for your responses. I added the call to SQLMoreResults() to our application and it resolved the problem.
    Tuesday, April 14, 2009 6:26 PM