SQL in CLP

320 pts.
Tags:
RPG
SQL
Hi, I have to create a command that permits a user to copy records from one file to another and then remove those records from the first file. We've been doing it all manually using CPYF and INCREL and then deleting the records using an SQL Delete statement. I got the idea to create a command and put it all in a CL CPP but is it possible to use the Delete SQL statement in a CL? I went through the Embedded SQL Programming book but it only talks about C, C++ and other languages, all I know is CLP, RPG and a little RPGLE.
ASKED: July 28, 2009  2:05 PM
UPDATED: January 28, 2011  1:48 AM

Answer Wiki

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

Hi,

You can do a RUNSQL command in CL. I usually load a variable with the sql statement, then run it:

CHGVAR VAR(&DELRECS) VALUE(‘DELETE FROM xxx WHERE blah.blah’)
RUNSQL STMT(&DELRECS)

Steve

Discuss This Question: 8  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
  • Satsho
    You can also use the RUNSQLSTM command. Store the SQL statements in a file and use this command to run the SQL. I prefer using this since we can alter the SQL statements without affecting the CL program. Link
    1,245 pointsBadges:
    report
  • Satsho
    Sorry, the link was missing Link
    report
  • Floyd02
    Thanks, Either way will get the job done.
    320 pointsBadges:
    report
  • Fmcely
    Hi: You donĀ“t need sql statements. CLP Program: CPYF FROMFILE(FILE1) TOFILE(FILE2) MBROPT(*ADD) CLRPFM FILE1
    10 pointsBadges:
    report
  • TomLiotta
    CPYF FROMFILE(FILE1) TOFILE(FILE2) MBROPT(*ADD) CLRPFM FILE1 A couple possible problems -- your CPYF doesn't include the mentioned INCREL() parameter, and CLRPFM doesn't allow record selection at all. If INCREL() or other selection criteria isn't needed, then CPYF/CLRPFM is totally valid. Tom
    125,585 pointsBadges:
    report
  • bluesky2
    What about using a variable to select the records to delete?  How does that work with the RUNSQL command to execute the SQL Statement from a  Text Source Member?
    10 pointsBadges:
    report
  • TomLiotta
    How does that work with the RUNSQL command ...   Since RUNSQL is not a native command and there is an unknown number of versions floating around, you'll have to tell us everything about how your version of it works before we can give responses with certainty.   Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    Since RUNSQL is not a native command... Unless you're asking about the new RUNSQL command in i 7.1 (and perhaps i 6.1 via PTF)... If that's the case, please confirm that this is the new IBM-supplied version.   Tom
    125,585 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