CODEWITHSUNDEEP
sqldb2ibm-midrangedb2-400
user3311539
user3311539

Reputation:

prepare fetch statement in db2

I have been trying to prepare a Fetch statement in db2 as below

Stmt = %trim('FETCH EXSQCRS INTO ?'); 
EXEC SQL PREPARE SQLSTMT2 FROM :STMT ;

EXSQCRS is a cursor.

But this is failing with -104 error. Any clue on how to write this?

Upvotes: 1

Views: 2854

Answers (2)

jmarkmurphy
jmarkmurphy

Reputation: 11473

You can't prepare the fetch. You can prepare the SQL statement that is used to define the cursor though. So it would look like this:

dcl-s stmt      Varchar(256) Inz('');

exec sql declare S1 statement;
exec sql declare C1 cursor for S1;

stmt = 'select * from customer where cusno = ?';
exec sql prepare S1 from :stmt;
exec sql open C1 using :customerNumber;

exec sql fetch C1 into :customerDS;
dow %subst(sqlstate:1:2) = '00'
    or %subst(sqlstate:1:2) = '01';

  ... process it here ...

  exec sql fetch C1 into :customerDS;
enddo;
exec sql close C1;

Note there is no error checking here. You really want to do that on all executable sql statements. Note also that the exec sql declare ... statements are not executable. Not even the declare C1 cursor. I will usually put my sql in their own procedures. The prepare and open will go in one procedure, the fetch in another, and the close in another.

Some notes about using procedures and local variables with embedded sql. When using static cursors, you need to put the declare in the same procedure as the open because the host variables are in the declare statement, and they need to be the same variables in scope when you do the open. That is because the declare is totally commented out, and generates no code. It is not executable. The host variables appear in the code generated for the open in this case.

With prepared cursors, you do not need to put the declare in the same procedure as the open like you do with static cursors. That is because the host variables that are bound to parameter markers in the prepared statement appear in the open statement. The declare can be up at the head of the program with the global declarations. It is a global declaration after all regardless of where the declare is physically located. I like to explicitly declare my prepared statements even though you don't have to. I put them up with the cursor declarations.

I generally will have the fetch procedure return a boolean (indicator), or a record count so that I don't have to repeat the call in the code. The fetch is indeed repeated here just because I did not use procedures.

And finally, the one exception I have with testing errors is that I do not test for an error after the close because the only error that will throw is that the cursor isn't open which is not an error in my mind, it is what I want the state of the cursor to be when I am finished with it.

Upvotes: 1

Charles
Charles

Reputation: 23783

FETCH isn't a statement that can be prepared. It's simply an executable statement. See Actions allowed on SQL statements

You need to read up on Embedded SQL programming

Simple example with dynamic SQL

 D EMPNUM          S              6A 
 D NAME            S             15A
 D STMT            S            500A   INZ('SELECT LASTNAME         -
 D                                     FROM CORPDATA.EMPLOYEE WHERE -
 D                                     EMPNO = ?')

  //************************************************************
  // Prepare STMT as initialized in declare section            *
  //************************************************************
  /FREE
   EXEC SQL
    PREPARE S1 FROM :STMT;
   //
   //************************************
   // Declare Cursor for STMT           *
   //************************************
   EXEC SQL
    DECLARE C1 CURSOR FOR S1;
   //
   //****************************************************
   // Assign employee number to use in select statement *
   //****************************************************
   EMPNUM = '000110';

   //*********************
   // Open Cursor        *
   //*********************
   EXEC SQL
    OPEN C1 USING :EMPNUM;
   //
   //**********************************************
   // Fetch record and put value of               *
   // LASTNAME into NAME                          *
   //**********************************************
   EXEC SQL
     FETCH C1 INTO :NAME;

Upvotes: 2

Related Questions