AW: Dumb SQL question

Hi,

>>Oooh.  You mean it will automagically stick the host variables where the
"?"
>>are?

Yes!

>>OTOH the Select isn't going to change other than the date range, so static
>>sounds good too.

Static is always better than dynamic, because it provides a better
performance.
With static SQL syntax checking is done at compile time. Additionally with
static SQL access plans get stored in the program objects and can be
validated each time the SQL statements are executed.

With dynamic SQL syntax checking and converting the character string into an
executable SQL statement are performed at run time. An access plan is
created each time the SQL statement gets executed, but it gets not stored in
the program object and subsequentely cannot be validated the next time the
SQL statement gets executed.

Mit freundlichen Gruessen / Best regards

Birgitta

"Shoot for the moon, even if you miss, you'll land among the stars."
(Les Brown)

-----Ursprungliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]Im Auftrag von Jeff Crosby
Gesendet: Dienstag, 21. Marz 2006 21:12
An: 'RPG programming on the AS400 / iSeries'
Betreff: RE: Dumb SQL question


>    The parameter markers get replaced in the open statement
> by specifying USING :HostVariable

Oooh.  You mean it will automagically stick the host variables where the "?"
are?

OTOH the Select isn't going to change other than the date range, so static
sounds good too.

--
Jeff Crosby
Dilgard Frozen Foods, Inc.
P.O. Box 13369
Ft. Wayne, IN 46868-3369
260-422-7531

The opinions expressed are my own and not necessarily the opinion of my
company.  Unless I say so.


> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of HauserSSS
> Sent: Tuesday, March 21, 2006 1:57 PM
> To: RPG programming on the AS400 / iSeries
> Subject: AW: Dumb SQL question
>
> Hi Jeff,
>
> it's not possible to use hostvariables (leading :) in the
> Command-String.
>
> You have the following options:
> 1. If the SQL statement never changes and only uses different
> dates, I'd suggest to use static SQL:
> C/EXEC SQL
> C+ Declare MyCursor Cursor
> C+     for Select * from dbmstf/ordtlhst
> C+            where onrcu IN (SELECT   onrcu
> C+                               from  dbmstf/orhdrhst
> C+                               where shpdtiso between :BgnDate
> C+                                                  and :EndDate)
> C+            Order By NAMEK10, DATEKISO, TIMEKISO'
> C/END-EXEC
>
> C/Exec SQL  Open MyCursor
> C/End-Exec
>
> C/EXEC SQL  Fetch ...
> C/End-Exec
>
> C/EXEC SQL  Close MyCursor
> C/End-Exec
>
> 2. If the SQL-Statement must be built dynamically, you have
> to use parameter markers (?) instead of Host-Variables.
>    The parameter markers get replaced in the open statement
> by specifying USING :HostVariable
>
> D FetchSQL        C    'SELECT * FROM dbmstf/ordtlhst +
> D                      WHERE onrcu IN (SELECT onrcu FROM +
> D                      dbmstf/orhdrhst WHERE +
> D                      shpdtiso >= DATE(?) +
> D                      AND +
> D                      shpdtiso <= DATE(?)) +
> D                      ORDER BY NAMEK10, DATEKISO, TIMEKISO'
>  *------------------------------------------------------------------
>  /Free
>    MySQL = FetchSQL;
>  /End-Free
> C/exec sql
> C+ prepare mainStatement
> C+    from :MySQL
> C/end-exec
>
> C/exec sql
> C+ declare MyCursor cursor
> C+     for mainStatement
> C/end-exec
>
> C/EXEC SQL Open MyCursor Using :BgnDate, :EndDate C/End-Exec
>
> C/EXEC SQL Fetch ....
> C/End-Exec
>
> C/EXEC SQL Close MyCursor
> C/End-Exec
>
>
> Mit freundlichen Gruessen / Best regards
>
> Birgitta
>
> "Shoot for the moon, even if you miss, you'll land among the stars."
> (Les Brown)
>
> --
> This is the RPG programming on the AS400 / iSeries (RPG400-L)
> mailing list To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
>
>


--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.