Re: Embedded SQL Dynamic Selection

[Michael Ryan <michaelrtr@xxxxxxxxx>]

Darn...was hoping you were on to something Kurt. Still receiving the same
error. This is frustrating.


On Thu, Nov 8, 2012 at 11:35 AM, Anderson, Kurt <KAnderson@xxxxxxxxxxxx>wrote:

> I should really keep track of all the RPG functions that the SQL
> pre-compiler does not support.  When I get a host variable not usable
> message, I tend to go through a number of iterations to finally end up with
> a variable that will work.
>
> A couple things I am aware of:
>   Host arrays can't have a dimension that's defined by a constant
> "variable."   Meaning Dim(10) will work, but Dim(MAX_ELEMENTS) will not
> work if MAX_ELEMENTS is defined as a constant.  I think it'll work if
> defined as a variable, but I can't remember for sure.
>   Host variables/structures cannot be defined LIKE a Template
> variable/structure (I might be leaping in logic here, the Templates I deal
> with are all qualified data structures, so it could be that the Template
> limitation has to do with qualified DS's only).
>   Host structures cannot be defined using LIKEREC.  I've had to use
> ExtName instead.  (The SQL police might show up and say "Don't do Select
> *!" but I do this in file encapsulated service programs to load a global
> data structure array that all of the Gets and Sets access.
>
> Someone can correct me if I'm wrong on any of these.  Or add to them.
>
> -Kurt Anderson
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx [mailto:
> midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Michael Ryan
> Sent: Thursday, November 08, 2012 10:21 AM
> To: Midrange Systems Technical Discussion
> Subject: Re: Embedded SQL Dynamic Selection
>
> Hi Bob...yup, the MODS WkInvMstP is defined in the D specs, well before
> any of the SQL statements. Thanks.
>
>
> On Thu, Nov 8, 2012 at 10:28 AM, Bob P. Roche <BRoche@xxxxxxxxxxxxxxxxx
> > wrote:
>
> > Make sure your variable is defined earlier in your code than the
> > placement of the SQL statement. Actually earlier in the code listing,
> > the fact that you program may hit the definition first while running
> > does not make a difference. Of course all your variables are defined
> > in the D specs so this may not be the issue.
> >
> >
> >
> >
> > From:
> > Michael Ryan <michaelrtr@xxxxxxxxx>
> > To:
> > Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>,
> > Date:
> > 11/08/2012 09:15 AM
> > Subject:
> > Re: Embedded SQL Dynamic Selection
> > Sent by:
> > midrange-l-bounces@xxxxxxxxxxxx
> >
> >
> >
> > Thanks for looking at this Birgitta. I 'fat fingered' the error
> > message...it's SQL5011:
> >
> > SQL5011: Position 55 Host structure array WKINVMSTP not defined or not
> > usable
> >
> > on this statement:
> >
> >            Fetch Next From C1 For :Maxrows Rows Into :Wkinvmstp;
> >
> > Thanks!
> >
> >
> > On Thu, Nov 8, 2012 at 9:59 AM, Birgitta Hauser
> > <Hauser@xxxxxxxxxxxxxxx>wrote:
> >
> > > Error Message means SQL0511 - Not allowed for update.
> > > Multiple row fetches cannot be used if you want to update your table
> > > directly using a cursor and specify the WHERE CURRENT OF Clause in
> > > the update statement.
> > >
> > > The error message has nothing to do with static and dynamic SQL  or
> > using
> > > host variables or parameter markers.
> > > Multiple row fetches are also not allowed for dynamic SQL with Update.
> > >
> > > 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 Michael Ryan
> > > Gesendet: Thursday, 08.11 2012 15:21
> > > An: Midrange Systems Technical Discussion
> > > Betreff: Embedded SQL Dynamic Selection
> > >
> > > So folks...sorry to be a pest...but is the reason I'm getting an
> > > SQL5011
> > on
> > > this statement:
> > >
> > > Fetch C1 For :Maxrows Rows Into :Wkinvmstp;
> > >
> > > Is because my Declare looks like this:
> > >
> > > Exec SQL
> > >              DECLARE C1 CURSOR FOR
> > >              Select * From INVMSTP
> > >              Where Imvnda Between :HostFld1  And :HostFld2   And ...
> > > (a long statement)
> > >
> > > and it needs to be parameter markers instead of host variables in
> > > the
> > WHERE
> > > clause?
> > > --
> > > This is the Midrange Systems Technical Discussion (MIDRANGE-L)
> > > mailing
> > list
> > > To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
> > > unsubscribe, or change list options,
> > > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > > or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please
> > > take a moment to review the archives at
> http://archive.midrange.com/midrange-l.
> > > --
> > > This is the Midrange Systems Technical Discussion (MIDRANGE-L)
> > > mailing
> > list
> > > To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
> > > unsubscribe, or change list options,
> > > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > > or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please
> > > take a moment to review the archives at
> > > http://archive.midrange.com/midrange-l.
> > --
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> > list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
> > unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
> > a moment to review the archives at
> > http://archive.midrange.com/midrange-l.
> >
> >
> >
> > --
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> > list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
> > unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
> > a moment to review the archives at
> > http://archive.midrange.com/midrange-l.
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
> unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
> moment to review the archives at http://archive.midrange.com/midrange-l.
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.