Passing variables in SQL

pts.
Tags:
Application development
DB2 Universal Database
I am writting a CL program that will create and update a table. I am using the command 'runsqlstm'. I am able to build the table but when I try to populate it I am having a problem. I am using the following statement 'insert into qtemp/rrtemp values (&rec). SQL will not process a CL variable. It gets hungup on the '&'. Does anyone have an idea for a work around or how to fix my problem?

Answer Wiki

Thanks. We'll let you know when a new response is added.

Of course, SQL will not accept your statement, because it can not relate to your CL defined variable. You have to pass a completely setup SQL expression. Example:

dcl &rec *char 10
dcl &sql *char 128

chgvar &rec value(‘TEST’)
chgvar &sql value(‘Insert into qtemp/rrtemp values(‘ *CAT &rec *CAT ‘)’)

Then hand over the string contained in &sql to be processed by RUNSQLSTM. Note that in RPG/RPGLE you can work with parameter markers to pass program-defined variables to SQL!

Good luck

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
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
  • astradyne
    As far as I'm aware (up to v5r3 at least) you cannot use RUNSQLSTM to execute a statement directly as hinted at in the previous answer - you can only use RUNSQLSTM to execute statements in a source member. If you want to execute an SQL statement directly from a CL routine then you have a couple of options. 1) Write an embedded SQL RPG program to accept the statement and execute it, or 2) Use the EXCSQLSTM which uses QMQRY as the means for executing the statement. EXCSQLSTM can be found on the AS400 Pro website at http://www.as400pro.com/servlet/sql.tipView?key=1434&category=SQL All the best Jonathan www.astradyne-uk.com
    370 pointsBadges:
    report
  • RREXPST
    Your Ideas have helped me thank you for your time. Scott
    0 pointsBadges:
    report
  • JPLamontre
    I have write some tools to solve this kind of problem : EXECSQL runs SQL in a source text + transmitted parameters EXECUTESQL runs SQL "on the fly" there are at http://jplamontre.free.fr/jpltools.htm
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
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

Thanks! We'll email you when relevant content is added and updated.

Following