Re: Dumb SQL question

Just a note...
using select * from ....  is not a good practice.

Always specify the list of fields in select. like:

select emp_name, emp_id, emp_num from employee.

This way you program will work even if a new field is added later on the
employee file(table)



On 3/21/06, HauserSSS <Hauser@xxxxxxxxxxxxxxx> wrote:
>
> 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.
>
>
>
> --
> 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.
>
>