AW: SQL0518 - What does RPG not like that the run time produces this error?

Parameter Markers can be used for dynamic SQL (but are not allowed for
tables or schemas).
When using a cursor the variables that hold the information are passed with
the open statement
When using no cursor the variables are passed with the execute statement:

MySQL = 'Select ... +
From ... +
Where Fld1 = ? +
And Fld2 = ? +
...'

Exec SQL Declare Csr ...;
Exec SQL Prepare DynSQL from Csr;
Exec SQL Open Csr using :Var1, :Var2, ... VarN;

...

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Darryl Freinkel
Gesendet: Tuesday, 22. June 2010 15:02
An: midrange-l@xxxxxxxxxxxx
Betreff: SQL0518 - What does RPG not like that the run time produces this
error?

My code is somewhat complicated by the fact that the code is dynamic and is
a sub procedure. It does not know what the table names are until they are
passed in to the sub procedure. Table names cannot be hard coded. So dynamic
SQL is the only answer that I am aware of.

I need some help to create a prepare statement that can use a parameter
marker. So far I have used

Declare
Prepare
Open
Fetch
Close

for every record processed.

The process works but it is slow.

My program is event based and updating is done in a service program,
processing 1 record at a time. When I have a batch of records (say 1000) to
process, the service program does the above for every record. I would like
to do the DECLARE, PREPARE and OPEN once only to save the cycles. The
service program does monitor for changed file names and automatically closes
out and initializes for each new file to be processed.

Is there a way to use parameter markers (?) in the PREPARE once in the code
executing Declare, Prepare and Open once and then simply executing the FETCH
repeatedly for each call?


Darryl Freinkel


----------------------------------------------------------------------

message: 1
date: Tue, 22 Jun 2010 07:06:53 +0200
from: "Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>
subject: AW: SQL0518 - What does RPG not like that the run time
produces this error?

Hi,

when working with a cursor in dynamic SQL, you need:

Prepare
Declare
Open
Fetch
Close

Prepare and Execute (or Execute Immed) are only allowed for SQL statements
where no cursor is used (for example if you want to dynamically create a
table)

... And don't try to use SELECT ... INTO with dynamic SQL, because it is not
supported.
If you only want to get a single row returned you may use VALUES ... INTO
(which can be used with dynamic SQL, contrary to SELECT ... INTO).

MySQLStmt = 'VALUES (Select RRN(A1) +
from OEDTL01P A1 right outer join AA0000219 B1 +
on A1.ODORD = B1.LORD +
and A1.ODLINE = B1.LLINE +
where RRN(B1) = 55243) +
INTO ?';

Exec SQL Prepare DynSQLStmt from :MySQLSTMT;
Exec SQL Execute DynSQLStmt Using :MyRRN;

But in either way if I have a look at your SQL Statement is it really
necessary to use dynamic SQL?
If you only want to get the relative record no from A1 based on a relative
record no in B1 (which may change), static SQL can be used:

Exec SQL Select RRN(A1) into :MyRRN
from OEDTL01P A1 right outer join AA0000219 B1
on A1.ODORD = B1.LORD
and A1.ODLINE = B1.LLINE
where RRN(B1) = :B1RRN;


Just to additional comments:
1. When selecting records based on a relative record no either a table scan
or at least a table probe is performed which will result in a very bad
performance for large physical files or tables.
2. Never start your variables with SQL, IBM may create additional variables
(for embedded SQL) in future releases and those variables will always start
with SQL or SQ. In this way you may get troubles in a future release when
IBM decides for example to name a variable SQLRRN.

Mit freundlichen Gr??en / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"