Re: Syntax for Insert into using an rpg data structure, 1 row

There are a two flavors of embedded SQL in RPG. Static and Dynamic. In Dynamic SQL you build the statement preferably with parameter markers, and the prepare and execute your statement (or you may be able to do that in one step using execute immediate). This is the only way to do some things like filtering a result set based on user input in multiple fields or selecting from different tables based on some varying criteria. Normally you will be able to use Static SQL. That is where you use host variables directly in the sql statement, and just execute it with exec sql as Darren suggested.

If your dynamic SQL looks like:

stmt = 'insert ...';
exec sql prepare s1 from :stmt;
exec sql execute s1 using :host-structure;

You should probably consider using static SQL like:

exec sql insert ...;

If you need a complex build for your sql statement:

exec sql declare s1 statement;
exec sql declare c1 cursor for s1;

stmt = 'select field1, field2, ... from table'
if someCondition;
stmt += ' where field3 = ?';
else;
stmt += ' where field4 = ?';
endif;
exec sql prepare s1 from :stmt;
exec sql open c1 using :host-structure;

Then you will likely need dynamic SQL unless you can adjust the statement so that it can all be written in a single string. In addition, some SQL statements, that contain neither variables nor parameter markers, can be prepared and executed in a single step with:

exec sql execute immediate :stmt;

But be sure you are not concatenating user input into a statement that is prepared in this manner (without parameter markers) as that is a vector for SQL injection. Actually this caution applies to execute immediate, and prepare and execute equally, though execute immediate sort of encourages this type of behavior.

Mark Murphy
STAR BASE Consulting, Inc.
mmurphy@xxxxxxxxxxxxxxx


-----JRusling@xxxxxxxxxxx wrote: -----
To: "RPG programming on the IBM i \(AS/400 and iSeries\)" <rpg400-l@xxxxxxxxxxxx>
From: JRusling@xxxxxxxxxxx
Date: 09/30/2015 02:06PM
Subject: Re: Syntax for Insert into using an rpg data structure, 1 row

Oh, i like that Darren.

Dont have to count question marks, as i had to. (below)

I did get this to work like this - the ?'s on the insert had to match the
number of fields in the data structure.
data struc -
myds ds
...field1
...field2 . . ... etc

the insert -
clear sql2;
sql2 = 'insert into qtemp/rpttossf ' +
'values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) with nc';
//ec sql insert into qtemp/rpttossf using :myds;
exec sql prepare stmt2 from :sql2;
exec sql execute stmt2 using :myds;

John Rusling
Senior Developer/Analyst
Quadion LLC
Minnesota Rubber & Plastics
952.927.1419
<br />
The information in this email is confidential and may be legally privileged.
It is intended solely for the addressee. Access to this email by anyone else is
unauthorized. If you are not the intended recipient, any disclosure, copying,
distribution or any action taken or omitted to be taken in reliance on it, is
prohibited and may be unlawful.