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.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchEnterpriseLinux

SearchDataCenter

Close