CODEWITHSUNDEEP
sqldb2-400rpgle
VAI Jason
VAI Jason

Reputation: 544

SQLRPGLE syntax for Exec sql from a varying length variable?

On IBMi (database is DB2 for i) in SQLRPGLE I have a program that builds a large SQL statement into a variable that I would like to run.

When I try to run it as a variable I receive a token error

Some background

Here is an example that works because it does not use a variable

Exec SQL                                                                
Create table MyLib/MyFile as(select * from XXLIB/XXFILE)   
DATA INITIALLY DEFERRED REFRESH DEFERRED                       
maintained by user;

When I save this in a variable like @SQLStm and then try to execute as SQL 

Exec SQL                  
   :@SQLStm;

I get the error Token : was not valid. Valid tokens: .

Also I am open to different approaches https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_71/cl/runsqlstm.htm Like RUNSQLSTM SRCFILE(MYLIB/MYFILE) SRCMBR(MYMBR)

Maybe there is a way to take a variable and save it to a source member? Then use RUNSQLSTM over the source member

Showing some code: Definition for the variable 

d @SQLStm         s               A   Len(6144) Varying(4)

Even when trying a portion of the SQL statement as a variable

@SQLStm = select * from XXLIB/XXFILE;

and then try: 

Exec SQL     
  Create table MyLib/MyFile as( :@SQLStm)    
  DATA INITIALLY DEFERRED REFRESH DEFERRED                       
  maintained by user;

I get the error Token : was not valid. Valid tokens: .

I expect the SQLRPLE to compile Instead of SQL precompile failed.

MSG ID SEV RECORD TEXT
SQL0104 30 236 Position 31 Token : was not valid. Valid tokens:
.

Message Summary
Total Info Warning Error Severe Terminal
1 0 0 0 1 0
30 level severity errors found in source

Upvotes: 1

Views: 1992

Answers (1)

Charles
Charles

Reputation: 23783

This is static SQL

Exec SQL                                                                
Create table MyLib/MyFile as(select * from XXLIB/XXFILE)   
DATA INITIALLY DEFERRED REFRESH DEFERRED                       
maintained by user;

What you want is dynamic SQL

wSqlStmt = 'Create table MyLib/MyFile as(select * from XXLIB/XXFILE)'
            + ' DATA INITIALLY DEFERRED REFRESH DEFERRED'
            + ' maintained by user'; 
exec SQL
  execute immediate :wSqlStmt;

Note that some statements can't be execute immediate instead you have to prepare then execute them.

more information can be found in the Embedded SQL programming manual.

Upvotes: 4

Related Questions