Re: Invalid cursor state

Comments embedded.

Harp wrote:
> 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)

If you are NOT expecting any IN parameters then you don't need to declare it as an IN OUT, just use OUT.

> 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;

FOR loop will automatically close and exit when no data found. You don't need EXIT WHEN here. Use this with,

OPEN cursor;
LOOP
   FETCH cursor INTO ...;
   IF (cursor%NOTFOUND) THEN

      EXIT;
   END IF;
   ...
END LOOP;
CLOSE cursor;

Unless there is ONLY one row returned from the cursor, your LOOP will end with putting last row values from the resultset in the OUT parameters.

> 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

You have called a procedure with 3 OUT parameters... which means you will ONLY get one value each. This is not a resultset that you can loop thru on the client side (and most likely the reason for your error). You need to either return RefCursor or just use SELECT query to get the resultset back which you can loop thru. I am not sure what the syntax for that would be in ODBC.

>
> 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

See above note referring to last row of the resultset.

> 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

I am not familiar with programming in ODBC so I am afraid I cannot provide any syntax example.

Regards
/Rauf Received on Tue Feb 15 2005 - 07:19:35 CST