Can you embed SQL statements in RPG program

20 pts.
Embedded SQL
I am new to AS/400 and we have a project wherein we have to display the records grouped by some fields. Is it possible to write sql statements within RPG program?? How???

Answer Wiki

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

Whether it’s possible or not depends on whether or not your system has the option to allow it — the 57xx-ST1 SQL Development Kit.

The SQL Dev Kit supplies the SQL pre-compiler that processes the SQL statements to convert them into the appropriate data structures and other RPG statements so that the RPG compiler can understand them.

The SQL Dev Kit is a chargeable option. It enables all of your installed HLLs to handle embedded SQL. It also installs the STRSQL interactive SQL command and the full Query Manager user and developer interfaces.

If the SQL Dev Kit is not installed, you can still use ILE RPG to process SQL CLI API calls (i.e., ODBC).



Yes it’s possible. you can use /exec sql and /end-exec pre compiler directives in the c- specs. In between the directives you can insert your SQL statements. The program becomes an object of type SQLRPG/SQLRPGLE depending on it’s compiling environment. The compilation should be done using CRTSQLRPG/CRTSQLRPGI command and not ordinary CRTRPGPGM or CRTBNDRPG.

here is an example how it can be done:
C/exec sql
C+ select CMCSNO, –customer number
C+ CMCSNM, –customer name
C+ CMDFSH –default shipto
C+ into :csCMCSNO,
C+ from CUSMS
C+ where CMCSNO=:customer


In addition, if you are on V5R4 or higher, you can use inline SQL statements in freeform RPG, like:


exec sql select CMCSNO,
into :csCMCSNO,
from CUSMS
where CMCSNO=:customer;


If you are not V5R4, then juggling between /free and fixed format just to do embedded SQL is a pain. Anyway, just make sure that you don’t forget to preface each RPG field with a colon (:). That’s how the program knows whether you are referencing fields in the database or in the :program. HTH. –Koohiisan

Discuss This Question: 1  Reply

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.
  • Jeffm
    Here is a good reference. Jeff
    135 pointsBadges:

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.

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


Share this item with your network: