Hi,
Just a minor comment:
You don't need dynamic SQL.
With dynamic SQL several steps must be performed at runtime, that may slow
down the performance:
1. The Character String containing the SQL-Statement must be syntax checked
and converted into an executable SQL-Statement. (PREPARE).
2. The access plan neccessary to execute the query must be built from
scratch (if not stored in the SQE plan cache when executing with SQE or in
the actual job's scope). When executing static SQL the access plan will be
stored in the program object itself and can be directly validated and used.
To use static SQL, just add two host variables holding the from and to
values for Variable. If Company is *Zeros, just fill *LoVal and *HiVal in
the From/To Variables. If company is not *Zeros, just fill the value you
need in both, from and To Variable.
D FromVar S like(MyField)
D ToVar S like(MyField)
*--------------------------------------------------------
/Free
If Company <> *Zeros;
FromCompany = MyValue;
ToCompany = MyValue;
Else;
FromCompany = *LoVal;
ToCompany = *HiVal;
EndIf;
/End-Free
C/Exec SQL
C+ Declare Cursor C1 For
C+ Select ....
C+ From ....
C+ Where (PENAME LIKE :csgrpkwda)
C+ (PECO# between :FromVar and :ToVar)
C/End-Free
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: rpg400-l-bounces@xxxxxxxxxxxx [
mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von Michael Daly
Gesendet: Wednesday, July 11, 2007 03:29
An: rpg400-l@xxxxxxxxxxxx
Betreff: Re: Dynamic sql embedded
Dquery S 1024A
...
C if (company <> 0)
C eval query = 'SELECT ' +
C 'PENAME,PEADR1,PESS#,PEEMP# ' +
C 'FROM PRMSTP ' +
C 'WHERE (PENAME LIKE ''' + csgrpkwda +
C ''') AND (PECO# = ''' + co#0 +
C ''') ORDER BY PENAME'
C else
C eval query = 'SELECT ' +
C 'PENAME,PEADR1,PESS#,PEEMP# ' +
C 'FROM PRMSTP ' +
C 'WHERE (PENAME LIKE ''' + csgrpkwda +
C ''') ORDER BY PENAME'
C endif
C/Exec Sql
C+ Declare C1 Cursor For DynSqlStmt
C/End-Exec
C/Exec Sql
C+ Prepare DynSqlStmt From :Query
C/End-Exec
C/Exec Sql
C+ Open C1
C/End-Exec
C/Exec Sql
C+ Fetch C1 into :InName, :InSub
C/End-Exec
*
C Dow SQLSTT < '02000'
...
C/Exec Sql
C+ Fetch C1 into :InName, :InSub
C/End-Exec
C ENDDO
C/Exec Sql
C+ Close C1
C/End-Exec
Hello James,
Hi everyone,
I've been struggling with embedded SQL in RPG. I need to create
dynamic statements. Here's what I'm trying to get to work:
Dquery S 1024A
...
C if (company <> 0)
C eval query = 'DECLARE C1 CURSOR FOR SELECT '
+
C 'PENAME,PEADR1,PESS#,PEEMP# ' +
C 'FROM PRMSTP ' +
C 'WHERE (PENAME LIKE :csgrpkwda) AND ' +
C '(PECO# = :co#0) ' +
C 'ORDER BY PENAME'
C else
C eval query = 'DECLARE C1 CURSOR FOR SELECT '
+
C 'PENAME,PEADR1,PESS#,PEEMP# ' +
C 'FROM PRMSTP ' +
C 'WHERE (PENAME LIKE :csgrpkwda) ' +
C 'ORDER BY PENAME'
C endif
C/exec sql
C+ PREPARE DYNSQLSTMT FROM :query
C+ EXECUTE DYNSQLSTMT
C/end-exec
C eval query = 'OPEN C1'
C/exec sql
C+ PREPARE DYNSQLSTMT FROM :query
C+ EXECUTE DYNSQLSTMT
C/end-exec
I can't even get this (or any other incantation I've tried) to
compile. Does someone have some sample code of using a dynamic SQL
statement to populate a subfile?
James Rich
It's not the software that's free; it's you.
- billyskank on Groklaw
midrange.com
