Re: Invalid cursor state

From: Holger Baer <holger.baer_at_science-computing.de>
Date: Tue, 15 Feb 2005 14:54:14 +0100
Message-ID: <cusuu7$d0a$1@news.BelWue.DE>

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)
> 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 herekeep assigning
> 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
>

Hello Harp,

after being tempted to answer your posts, I more and more realize that you're trying to run without being able to walk. So please do yourself a favour and stop using odbc for a while and learn to use sql*plus.

Even better would be to start thinking in the first place, but that's for later.

Look at your procedure: In the loop, you assign the current address to the variable adr, thus overwriting its content. Then after the loop finishes, the last adress is what you get out. Now this is not rocket science, since this is true for any procedural language I'm aware of.

What you want could be e.g.
a) the procedure immediately returning each found address, then you

should be looking at pipelined functions - a procedure is just the wrong tool for you
b) the procedure returning the complete list of adresses (and names),

then you would need e.g. an array or 'table of varchar2' datatype c) the procedure returning a ref cursor

Here's one way to do what you tried in sql*plus:

baer_at_DEMO10G>select * from customers;

FIRSTNAME FAMILYNAME

-------------------- --------------------

ADDRESS

tom                  jones

London

roby                 williams

London

baer_at_DEMO10G>declare

   2    fname customers.firstname%type;
   3    lname customers.familyname%type;
   4    adr   customers.address%type;

   5 begin
   6 londonworkers (fname, lname, adr);    7 dbms_output.put_line ('Name: '|| fname || ' ' || lname ||' ' || adr);    8 end;
   9 /
Name: roby williams London

PL/SQL procedure successfully completed.

Ok, no surprise - only one row returned.

Now we could be returning a table of varchar2: baer_at_DEMO10G>edit
Wrote file afiedt.buf

1 create or replace procedure londonworkers (lname out name_list) is

   2     lnames name_list := name_list();
   3     x integer := 0;

   4 begin
   5 lname := name_list();
   6 for i in (select familyname from customers where address='London') loop

   7       x := x+1;
   8       lname.extend;
   9      lname(x) := i.familyname;

10 end loop;
11* end;
baer_at_DEMO10G>/

Procedure created.

baer_at_DEMO10G>declare

   2 names name_list;
   3 begin
   4 londonworkers(names);
   5 for i in 1..names.count loop
   6 dbms_output.put_line (names(i));    7 end loop;
   8 end;
   9 /

PL/SQL procedure successfully completed.

baer_at_DEMO10G>set serveroutput on
baer_at_DEMO10G>/
jones
williams

PL/SQL procedure successfully completed.

I really mean it: get yourself a good book about pl/sql programming (e.g. Connor McDonalds Mastering PL/SQL) and learn to walk. Then run (away from odbc ;-) )

HTH Holger Received on Tue Feb 15 2005 - 07:54:14 CST