Can you embed SQL statements in RPG program

20 pts.
Tags:
Embedded SQL
RPG
SQL
SQLRPGLE
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???
ASKED: October 14, 2008  4:48 AM
UPDATED: August 22, 2010  10:33 PM

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).

Tom

=========================================================

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+ :csCMCSNM,
C+ :csCMDFSH :inCMDFSH
C+ from CUSMS
C+ where CMCSNO=:customer
C/end-exec

regards

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

/free

exec sql select CMCSNO,
CMCSNM,
CMDFSH,
into :csCMCSNO,
:csCMCSNM,
:csCMCFSH
from CUSMS
where CMCSNO=:customer;

/end-free

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.

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
  • Jeffm
    Here is a good reference. http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/rzajp/rzajprpg.htm Jeff
    125 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