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!"


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

I have a relatively simple SQL statement that works fine in interactive SQL
and iNavigator.



However, in a RPG program (free form) that is using a prepare and EXECUTE,
the EXECUTE fails with error -518.



This error is used for a number of errors by IBM and I cannot find a
specific error code indicating the problem.



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



Here is the code:



Exec Sql Declare SQLRRN scroll cursor for SQLRRN;

Exec Sql Prepare SQLRRN from :SqlRRNPrep;



If SqlCod < 0;

ErrorStatus = '1003';

p1SqlCmd = sqlRRNPrep;

logACTerr ('1003' : SqlCod : p1SqlCmd );

ReturnCode = *off ;// Failed

Return ReturnCode;

Else;

Exec Sql Execute SQLRRN ;

If SqlCod < 0;

ErrorStatus = '1004';

ENDIF;



I get error 1004 with SqlCod = -518.



The code is all in one program/module.



I am using V5R3.



Darryl Freinkel

Assignment 400 Group, Inc.

Tel: 770.321.8562 ext 111 | Fax 770.321.8562 | 2247 La Salle Dr, Marietta
GA, 30062, USA | PO Box 72556, Marietta, GA 30007-2556