AW: SQL0312

Hi,

just to make it clear:
With static SQL, the SQL statement is known at compile time. Host variables
can be used at runtime to execute the same statement with different values.

With dynamic SQL the SQL statement is not known at all at compile time, but
completely built at runtime. Working with parameter markers allows you to
prepare your statement only once and execute it severalt times with
different values. Parameter markers can be compared with host variables in
static SQL.

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: Mittwoch, 26. Juli 2006 23:51
An: 'RPG programming on the AS400 / iSeries'
Betreff: RE: SQL0312


No.  <g>

According to my reading I thought it was still static SQL.  Dynamic SQL is
for when the host variable _name_ is not known at compile time.  What you're
saying is I can't use the Prepare statement?

--
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 rob@xxxxxxxxx
Sent: Wednesday, July 26, 2006 4:24 PM
To: RPG programming on the AS400 / iSeries
Subject: Re: SQL0312

DSPMSGD RANGE(SQL0312) MSGF(QSQLMSG)

One of two things:
1 - Does WCUSNR show up as a valid RPG variable in your dump?
2 - Youre using a prepare statement.  translation:  You are
using dynamic sql.  Using a host variable in dynamic sql is
forbidden according to SQL0312.  Therefore you have to do
something like MySql = 'Select c.*, i.* From NXCHNBIL C Left
Outer Join NXITMMST I On c.ITNBR = i.ITNBR Where c.ACREC =
'''A''' and i.NFDCD = 1 and ((c.CUSNR = '
+ ''' + char(wcusnr) + ... (you get the rest of it, right?)
Then do your prepare statement.  Basically your converting
the host variable into a literal and appending that into your
sql statement in MySql.  Understand?

Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





"Jeff Crosby" <jlcrosby@xxxxxxxxxxxxxxxx>
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
07/26/2006 03:59 PM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
"'RPG programming on the AS400 / iSeries'" <rpg400-l@xxxxxxxxxxxx>
cc

Subject
SQL0312






I'm getting more complex in my embedded SQL and am getting
the following
error:

SQL0312 Variable WCUSNR not defined or not usable.

This is not happening at compile time, but at execution time.
 This is the
SQL statement:

Select c.*, i.* From NXCHNBIL C
  Left Outer Join NXITMMST I
  On c.ITNBR = i.ITNBR
  Where c.ACREC = 'A' and i.NFDCD = 1 and
    ((c.CUSNR = :wcusnr and c.CHAUT <> ' ') or
    (c.CUSNR = :rchmst and c.CHAUT in :autsel))
  Order By i.MBGROP, i.MBSBGP, i.MBLINE


Debugging and a dump show the above is in variable MySQL.

I do a:

Exec SQL  Prepare Stmt1 from :MySQL;

So far I have tried 3 variables there:  1) tcusnr defined in
a DS as zoned
4,0, 2) cusnr defined in a DS as packed 4,0, and now 3) wcusnr defined
standalone as packed 4,0.  All give me this same error and I
do not know
why.  All show up on the dump with the expected value.

The only SWAG I have is that the host variable autsel is
where the problem
actually is, but things are being reported incorrectly.
Autsel is 500
chars
long and contains:

( '@', '$', '&', '1', 'B', 'C', 'D', 'E', 'G', 'O', 'P', 'Q',
'T', 'U',
'W',
'Y' )

with a lot of blanks at the end.  But if those blanks were a
problem -
well
I just don't know why it would tell me wcusnr was the problem
if the real
culprint was autsel.  As I said, it's just a SWAG.

Can someone tell me where to look next?  Thanks.


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


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