MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » November 2012

RE: Embedded SQL Dynamic Selection



fixed

Michael,

There is a DDS option to define a field as nullable: ALWNULL
This option is seldom used, it is primarily an SQL concept.
DSPFFD will identify nullable fields with " Allows the null value"
(prints just below "Field text")

Ok, so I think null values is not your problem, so I hope
I did not distract you too much.

If you are still getting the >Host structure array< error, then
focus on the DS definition. As indicated by others, there are
several different details that can upset the compiler.

Your plan of getting a simple working program and then adding
fields and "features" is a good approach: best of luck!

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Michael Ryan
Sent: Thursday, November 08, 2012 11:50 AM
To: Midrange Systems Technical Discussion
Subject: Re: Embedded SQL Dynamic Selection

I've taken the program and the file and drastically cut it down...and now it compiles. I don't believe I have any null capable fields in the file - it's a DDS created file, so I don't think there's an option to make a field null capable, right? I think I'll try and add fields back to the file and see if at some point that breaks it.


On Thu, Nov 8, 2012 at 1:28 PM, Gary Thompson <gthompson@xxxxxxxxxxx> wrote:

Michael,

First thing to check, of course, position 55 of your DS must be a
compatible data type and length.

For some reason, the phrase:

Host structure array WKINVMSTP not defined or not usable<

makes me think of what can happen when a sql statement returns a
field(s) that can have a null value.

In that case, the sql statement must be able to return a separate null
indicator field for each null capable field, so your program would
need to be designed for that.

Here is an example where field names starting with # are the "data"
field and field names starting with $ are the null indicator for that
field:

C/EXEC SQL FETCH C1 INTO :#FLD1,
C+ :#FLD2,
C+ :#FLD3 : $FLD3,
C+ :#FLD4 : $FLD4
C/END-EXEC

Hope this helps.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:
midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Michael Ryan
Sent: Thursday, November 08, 2012 10:41 AM
To: Midrange Systems Technical Discussion
Subject: Re: Embedded SQL Dynamic Selection

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.


--
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.







Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact