Using SQL in RPG/400

I've been reading the AS/400 manulas and it tells me I can use such things as

      C/EXEC SQL                                                    
      C+ UPDATE CAMBPPAA                                            
      C+ SET BPABAL = 1.0 WHERE ABAL > 5                            

in my RPG.

The RPG compiler won't accept this and the SEU complains as well.
In short it doesn't work (at all).  

LVL 18
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

An alternative for you may be to run SQL from the controlling CL program, either before or after ( or instead of ? ) the call to your RPG.

In the CL program, use
runsqlstm srcfile(lib/qsqlsrc) mbr(sample) commit(*none)

Member sample in file lib/qsqlsrc is the SQL statement, below,

Update fileA
 set field1 = (field2+100)
 where field1 < field2;
Update fileA
 set field2 = 0
 where field2 = (field1-100);

Repeat as required...

Sorry I can't test your RPG code until we recieve delivery of our new AS400 with RPG/400 and SQL installed.

deightonprogAuthor Commented:
I'm looking to embed it in RPG.  I know its possible 'cos its in the online manuals.

I assume you are trying to edit & compile via PDM (STRPDM or WRKMBRPDM)

The source type must be SQLRPG.

OR There is a different type for RPG ILE SQL (If you use the STRSEU command you can prompt for the different type in the source type field.)

The command to create an SQL RPG program is again different. Not CRTRPGPGM but CRTSQLRPG (or CRTRPGSQL I get them the wrong way round sometimes)Again ILE has a different type.

The Source editor will support valid SQL statements if the source type is correct and will even allow you to Prompt to syntax check the SQL statements.

If your source types were RPG or RPG38 or RPGLE then the compile would fail as it require an SQL precompiler. Remember when compiling AS400 SQL to revise the COMMIT parameter otherwise you'll get screwy error messages when you run it.

One last point - the above assumes that you have purchased and installed the relevant licensed products from IBM within your version of OS/400.

One furtehr point to bear in mind is such issues as Whenever and declare clauses.

Please refer to your manuals.  



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
IBM System i

From novice to tech pro — start learning today.