Sql error invalid cursor

This is a reposting of an article Patrick Roth originally wrote on my Developing for Dynamics GP blog.

In a recent case of mine, the ISV was running into a common issue that I’ve run into before more than a few times.  Actually something I’ve done a few times myself.

The ISV was attempting to use pass through sanScript to populate a SQL temp table.

What he found was that the first SQL_Execute() statement was OK but the subsequent statements would fail with the error:

SQL Errror: 0 [Microsoft][SQL Server Native Client 10.0]Invalid cursor state

The Dexterity code I’d potentially use (omitting error checking and variable declarations).

Code Example

SQL_Connect(SQL_connection);
SQL = "use TWO";
status = SQL_Execute(SQL_connection, SQL);

if status = 0 then
  SQL = "select CUSTNMBR from RM00101";
  status = SQL_Execute(SQL_connection, SQL);
  {get all customer numbers in customer master and add to temp table.}
  if status = 0 then
    status = SQL_FetchNext(SQL_connection);
    while status <> 31 do
      clear table myTempTable;
      SQL_GetData(SQL_connection,1,'Master ID' of table myTempTable);
      save table myTempTable;
      status = SQL_FetchNext(SQL_connection);
    end while;
  end if;
  SQL = "select VENDORID from PM00200";

  status = SQL_Execute(SQL_connection, SQL);
  {get all vendors add to temp table.}
  if status = 0 then
    status = SQL_FetchNext(SQL_connection);
    while status <> 31 do
      clear table myTempTable;
      SQL_GetData(SQL_connection,1,'Master ID' of table myTempTable);
      save table myTempTable;
      status = SQL_FetchNext(SQL_connection);
    end while;
  end if;
end if;
SQL_Terminate(SQL_connection);

What I would find is that my table wouldn’t be populated with the Vendor ID values.

Debugging this (or adding the call to SQL_GetError()) I’d find that the second SQL_Execute() function is what is failing with the error noted.

Why does this happen?

The reason is that I have returned a recordset from the second call.

We note that the first call actually set my database context with the “use TWO” statement.

Because no recordset was generated, the call to the RM00101 table does not fail.

However as the second SQL_Execute() statment reading the RM00101 can return a recordset (even if it doesn’t due to a where clause), the next SQL_Execute() statement will fail with the ‘invalid cursor state’ error.

How do we fix this?  Do we have to terminate the connection?

No – nothing that drastic and the fix is rather easy.

We just need to clear the previous results – Dexterity has the function SQL_Clear() to do this.

So to resolve our issue, we would just need to add this to the above code.

Revised Code Snippet

<customer query omitted for clarity>

{clear previous results to avoid 'invalid cursor state' errors}
SQL_Clear(SQL_connection);
SQL = "select VENDORID from PM00200";
status = SQL_Execute(SQL_connection, SQL);

{get all vendors add to temp table.}
if status = 0 then
  status = SQL_FetchNext(SQL_connection);
  <code to loop through the records omitted for clarity>

Hope this helps out,

Patrick Roth
Senior Escalation Engineer
Dynamics GP Developer Support

PS. Yes I know, the queries are rather lame and would be better performed as one UNION query.  And of course I’m ignoring the possibility of duplicates here.  And yes the BEST way to fill the temp table would be to do an insert query into a temp table.  But those are the types of things you ignore in order to get a simple example.

This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on http://www.winthropdc.com/blog.