Q
Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

SQL server error message -321

To solve the SQL error -321 on IBM i6.1, use the new values statement to overcome the error. If you are using an older release, declare a cursor using dynamic SQL.

I'm trying to run a query as below from an SQLRPGLE program, but I am receiving a SQL server error message: SQL error -312 (variable cannot be used). The code is as below:
d  mycount     s      10p  0 
  
c     eval string = 'select count(*) into : mycount from MyTable' 
 * 
c/exec-sql 
c+ prepare S1 from string 
c/end-sql 
 * 
c/exec-sql 
c+ execute S1 
c/end-sql
How do I overcome this error and execute the code dynamically?
If you are on IBM iSeries 6.1, then you can you can use the new values statement as I demonstrate below with SQL pocedural language:
  DECLARE v1 int; 
  SET string1= 'VALUES(SELECT COUNT(*)  from MyTable) INTO ?'; 

  PREPARE s1 from string1; 
  EXECUTE s1 using v1; 
The solution is more complicated if you are on a release prior to 6.1 since you need to declare a cursor using dynamic SQL.
DECLARE C1 CURSOR FOR S1 ; 

  SET STRING2 = 'SELECT COUNT(*) FROM MyTable' ; 
  PREPARE S1 FROM STRING2 ; 
  OPEN C1 ; 
  FETCH C1 INTO V1 ; 
 
This was last published in June 2009

Dig Deeper on DB2 UDB (universal databases)

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataCenter

Close