Invalid cursor state

From: Harp <hmetu_at_gmx.net>
Date: 15 Feb 2005 04:57:21 -0800
Message-ID: <1108472241.273033.169840@g14g2000cwa.googlegroups.com>

Hi!
I created this stored procedure with sql *plus and compiled it:

CREATE OR REPLACE PROCEDURE LondonWorkers(fName IN OUT VARCHAR2,lName IN OUT VARCHAR2,adr IN OUT VARCHAR2)
  AS

      CURSOR workers_cursor IS
      SELECT
          firstname, familyname, address
      FROM customers
      WHERE address = 'London';
  BEGIN
      FOR customers IN workers_cursor LOOP
      fName := customers.firstname;
      lName := customers.familyname;
      adr := customers.address;
      EXIT WHEN  workers_cursor%NOTFOUND;
      END LOOP;

  END LondonWorkers;
  /
I tried to call the procedure via odbc:
//connections ok.

lstrcpy( (char *) pSqlStmt, "{CALL LondonWorkers(?,?,?)}");

    rc = SQLExecDirect(hStmt, pSqlStmt, lstrlen((char *)     pSqlStmt));
if (rc != SQL_SUCCESS){
  DisplayError(SQL_HANDLE_STMT, hStmt, (SQLCHAR *)"SQLExecDirect"); }
//it was okay till here.

//now display data
 while ( rc == SQL_SUCCESS ){

      rc = SQLFetch( hStmt );//it gives an error here
      if ( rc == SQL_SUCCESS ){
        ShowMessage(Data);


      }

      else{
        if (rc != SQL_NO_DATA){
         DisplayError(SQL_HANDLE_STMT,hStmt,(SQLCHAR *)"SQLFetch");
        }


      }

 }//end of while

If I try to display, I get "invalid cursor state". I tried to do the following:
string result;
 while ( rc == SQL_SUCCESS ){

      string result = result + "\t" + firstname;
      result = result + "\t" + familyname;
      result = result + "\t" + address;
      ShowMessage(result);
      rc = SQLFetch( hStmt );
      if ( rc == SQL_SUCCESS ){
        ShowMessage(Data);


      }

      else{
        if (rc != SQL_NO_DATA){
         DisplayError(SQL_HANDLE_STMT,hStmt,(SQLCHAR *)"SQLFetch");
        }


      }

 }//end of while
Then I noticed that the last row of the resultset is displayed, and by SQLFetch(...), it displays the error-message - indicating that there is no more data.
How can I improve my program (sql-statement/c++-code) to get all the required results?
Is it possible to use array in the sql-statement? How?
Much thanks,
Harp Received on Tue Feb 15 2005 - 06:57:21 CST