Skip to main content

Connection is busy with results from another hstmt

  • November 6, 2018
  • 8 replies
  • 1 view
A
  • Anonymous

 Hello,

 We are using RM/COBOL with the WOW Extension Designer version 10.01, InstantSQL and SQL Server 2016 database.  We have a situation where we are receiving this error even though we have an end statement following the query that is causing the error.  We also have an end statement prior to the query that is receiving the error.

Does anyone have any ideas?

Thank you.

    8 replies

    D

     Hello,

     We are using RM/COBOL with the WOW Extension Designer version 10.01, InstantSQL and SQL Server 2016 database.  We have a situation where we are receiving this error even though we have an end statement following the query that is causing the error.  We also have an end statement prior to the query that is receiving the error.

    Does anyone have any ideas?

    Thank you.

    This does not sound like a problem with WOW. It looks to me like you're passing the wrong query handle to the "end statement". (Do you mean SQL END QUERY?) Is it possible that you reused the data item holding the QueryHandle for a new SQL PREPARE QUERY, without performing a SQL END QUERY FIRST? I would turn on ODBC Tracing and see what is actually going on.
    A
    • Anonymous
    • November 7, 2018

     Hello,

     We are using RM/COBOL with the WOW Extension Designer version 10.01, InstantSQL and SQL Server 2016 database.  We have a situation where we are receiving this error even though we have an end statement following the query that is causing the error.  We also have an end statement prior to the query that is receiving the error.

    Does anyone have any ideas?

    Thank you.

    Yes - sorry; I did indeed mean the SQL END QUERY statement.
    I've turned on ODBC Tracing and recreated the error. How do I tell which of the other queries shown use the same query handle? Forgive my ignorance, I haven't worked with this tool before.
    D

     Hello,

     We are using RM/COBOL with the WOW Extension Designer version 10.01, InstantSQL and SQL Server 2016 database.  We have a situation where we are receiving this error even though we have an end statement following the query that is causing the error.  We also have an end statement prior to the query that is receiving the error.

    Does anyone have any ideas?

    Thank you.

    No problem.

    The trace file contains entries for every ODBC statement that is processed. (One for the call and one for the exit.) Here's an example of a SQLExecDirect which prepares a SQL statement for execution and then begins executing it:

    TestADO 1e2c-1f84 ENTER SQLExecDirectW
    HSTMT 0x009388E8
    WCHAR * 0x010B8A80 [ 77] "SELECT OfOrderNumber, OfTotalOrderValue FROM Orders where OfOrderNumber = 101"
    SDWORD 77

    TestADO 1e2c-1f84 EXIT SQLExecDirectW with return code 0 (SQL_SUCCESS)
    HSTMT 0x009388E8
    WCHAR * 0x010B8A80 [ 77] "SELECT OfOrderNumber, OfTotalOrderValue FROM Orders where OfOrderNumber = 101"
    SDWORD 77

    The value after the HSTMT is the "handle" of the SQL statement. It must be passed to other ODBC calls to retrieve the results of the execution. Then there must be a SQLFreeStmt call to tell the database that the application is done with the statement. Here's an example of that:

    TestADO 1e2c-1f84 ENTER SQLFreeStmt
    HSTMT 0x009388E8
    UWORD 2 <SQL_UNBIND>

    TestADO 1e2c-1f84 EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)
    HSTMT 0x009388E8
    UWORD 2 <SQL_UNBIND>

    TestADO 1e2c-1f84 ENTER SQLFreeStmt
    HSTMT 0x009388E8
    UWORD 0 <SQL_CLOSE>

    TestADO 1e2c-1f84 EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)
    HSTMT 0x009388E8
    UWORD 0 <SQL_CLOSE>

    TestADO 1e2c-1f84 ENTER SQLFreeStmt
    HSTMT 0x009388E8
    UWORD 1 <SQL_DROP>

    TestADO 1e2c-1f84 EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)
    HSTMT 0x009388E8
    UWORD 1 <SQL_DROP>

    The program generating these calls is being very through. The SQL_CLOSE is all that is actually necessary. Note that the value after HSTMT is the same as the one on the SQLExecDirect. I'm thinking that you don't have a call to SQLFreeStmt with a SQL_CLOSE before the next SQLExecDirect. (Of course, Instant SQL is making all these calls for you, so it will be necessary to figure out which Instant SQL statement is generating which ODBC calls.)
    A
    • Anonymous
    • November 7, 2018

     Hello,

     We are using RM/COBOL with the WOW Extension Designer version 10.01, InstantSQL and SQL Server 2016 database.  We have a situation where we are receiving this error even though we have an end statement following the query that is causing the error.  We also have an end statement prior to the query that is receiving the error.

    Does anyone have any ideas?

    Thank you.

    Actually, I do have a SQL END QUERY before the query that receives the Connection Busy error.
    CHECK-CLAIM-ELIGIBILITY.
    SQL END QUERY sql-QueryHandle.

    MOVE 9999 TO sql-Return.

    INITIALIZE sql-QrySQL,
    SQL-ROW-COUNT,
    SQL-RETURN-STATUS,
    sql-QueryHandle,
    ELIGIBILITY-STATUS-RETURN-FIELDS.

    SQL CONSTRUCT QUERY
    sql-QrySQL,
    "{CALL dbo.usp_CheckClaimLineCoveredAndEligibilityStatus
    - " (",
    sql-DirFixed, IMG-SOURCE-ID,
    ",",
    sql-DirQtTrim, PARM-PARTICIPANT,
    ",",
    sql-DirQtTrim, PARM-FAMILY-ID,
    ",",
    sql-DirQtTrim, PARM-GENERAL-COVERAGE-ALPHA,
    ",",
    sql-DirTrim, EP-CLAIM-NO,
    ",",
    sql-DirTrim, EP-PRO-TYPE,
    ",",
    sql-DirTrim, EP-CAUSE-CODE,
    ",",
    sql-DirTrim, EP-DOS-FROM,
    ",",
    sql-DirTrim, EP-DOS-TO,
    ")}".
    PERFORM LCS-SQL-PREPARE-QUERY.
    IF SQL-OK
    MOVE sql-QueryHandle TO Current-QueryHandle
    PERFORM LCS-SQL-START-QUERY

    I've been working my way through the trace and am adding SQLFreeStmt calls.
    Here are the trace entries for the error:
    WOWRT 191c-1d6c ENTER SQLPrepare
    HSTMT 0x029E7090
    UCHAR * 0x037E9EB6 [ 140] "{CALL dbo.usp_CheckClaimLineCoveredAndEligibilityStatus ( 19, '084487707', 'A', '01', 'AUTO', 'HG', 'IL', {d'2018-09-20'}, {d'2018-09-20'})}"
    SDWORD 140

    WOWRT 191c-1d6c EXIT SQLPrepare with return code 0 (SQL_SUCCESS)
    HSTMT 0x029E7090
    UCHAR * 0x037E9EB6 [ 140] "{CALL dbo.usp_CheckClaimLineCoveredAndEligibilityStatus ( 19, '084487707', 'A', '01', 'AUTO', 'HG', 'IL', {d'2018-09-20'}, {d'2018-09-20'})}"
    SDWORD 140

    WOWRT 191c-1d6c ENTER SQLNumResultCols
    HSTMT 0x029E7090
    SWORD * 0x023BFA1C

    WOWRT 191c-1d6c EXIT SQLNumResultCols with return code -1 (SQL_ERROR)
    HSTMT 0x029E7090
    SWORD * 0x023BFA1C

    DIAG [S1000] [Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt (0)

    So I need to look for another query that used the handle '0x029E7090'?
    D

     Hello,

     We are using RM/COBOL with the WOW Extension Designer version 10.01, InstantSQL and SQL Server 2016 database.  We have a situation where we are receiving this error even though we have an end statement following the query that is causing the error.  We also have an end statement prior to the query that is receiving the error.

    Does anyone have any ideas?

    Thank you.

    It is interesting that the failure occurred on the SQLNumResultCols but not on the SQLPrepare. SQLPrepare returned success, and SQLNumResultsCols is only valid if the statement handle has been prepared or executed.

    I see that the SQL END QUERY is passing sql-QueryHandle as the handle, but I can't see what LCS-SQL-PREPARE-QUERY uses when it calls SQL PREPARE QUERY.

    I'm not an expert in InstantSQL, but I know about ODBC and I'm looking at the InstantSQL documentation, so I'm making a guess at what LCS-SQL-PREPARE-QUERY looks like.

    Can you send more of the trace. The calls to SQLFreeStmt just prior to SQLPrepare would be helpful.
    A
    • Anonymous
    • November 7, 2018

     Hello,

     We are using RM/COBOL with the WOW Extension Designer version 10.01, InstantSQL and SQL Server 2016 database.  We have a situation where we are receiving this error even though we have an end statement following the query that is causing the error.  We also have an end statement prior to the query that is receiving the error.

    Does anyone have any ideas?

    Thank you.

    Code too long to add as a message here, please find it here.

    D

     Hello,

     We are using RM/COBOL with the WOW Extension Designer version 10.01, InstantSQL and SQL Server 2016 database.  We have a situation where we are receiving this error even though we have an end statement following the query that is causing the error.  We also have an end statement prior to the query that is receiving the error.

    Does anyone have any ideas?

    Thank you.

    Hmmm. I see calls to SQLFreeStmt that close and drop handle 0x0316E100, and then I see a call to SQLAllocStmt that returns 0x0316E100 back again, which I guess is OK. Then I see a call to SQLPrepare that uses 0x0316E100, which is also OK. And then there is a call to SQLNumResultsCol and it returns an error about "Incorrect syntax near the keyword 'SET'". (This makes no sense because the prepared statement didn't have a keyword SET in it.)

    This sequence happens many times in the trace, and then I see a SQLFetch, which succeeds, but there isn't a call to SQLFreeStmt to release the handle. Then there is a call to SQLAllocStmt which returns a different handle, 0x032CD0B8, which I assumes means that 0x0316E100 hasn't been freed, then the SQLPrepare again, which succeeds, and then the SQLNumResultCols, which this time returns the "Connection is busy" error.

    Things seem to go south at this SQL statement:

    {CALL dbo.usp_RevenueScheduleConversion_select ( 19, NULL, NULL, 'A', 'A', 'A', '450', 1, ?, ?)}

    This appears to be a stored procedure, and perhaps the syntax error is in that stored procedure, but it is odd that a SQLFetch is performed but then no more SQLFetch's are made. One normally sees SQLFetch's made until it essentially returns a End of FIle error. Here, we fetch one row and then move on to the next SQL statement. I think that you have a loop that is ending prematurely and not freeing the handle.

    So, I think that this is a programming mistake. I suggest that you put DISPLAY statements before each call to an Instant SQL function and display the handle, then perhaps you'll find the problem. Or walk through the code with the CodeWatch debugger.

    I'm sorry I can't be of more help.
    A
    • Anonymous
    • November 7, 2018

     Hello,

     We are using RM/COBOL with the WOW Extension Designer version 10.01, InstantSQL and SQL Server 2016 database.  We have a situation where we are receiving this error even though we have an end statement following the query that is causing the error.  We also have an end statement prior to the query that is receiving the error.

    Does anyone have any ideas?

    Thank you.

    I'm sorry I must disagree with you - you've been a great help.
    My co-worker and I now have a new tool to help us track down this kind of problem.

    Thank you for taking the time to work on this with me.

    Recent badge winners

    Explore all badges
    Terms & ConditionsAccessibility statement