Re: AW: PREPARE Statement Problem

Birgitta,

Thanks.


        * Jerry C. Adams
*IBM System i Programmer/Analyst
B&W Wholesale Distributors, Inc.* *
voice
        615.995.7024
fax
        615.995.1201
email
        jerry@xxxxxxxxxxxxxxx <mailto:jerry@xxxxxxxxxxxxxxx>



HauserSSS wrote:

Hi,

just a question do you really need to prepare your SQL statement at runtime
or do only want to insert rows into the same table?
If so, you can use static SQL and specifying a datastructure that holds the
record information. With dynamic SQL you may get problems with packed
numeric values.

The following example works in one of my programs:
D DSAKOPP       E DS                  ExtName(LLAKOPP) inz
*----------------------------------------------------------------
C/EXEC SQL  declare global temporary table TempTable
C+          like LLAKOPP
C/End-Exec

 //Do some other statements, i.e. fill the data structure with new values

 //Insert Row into the temporary table
C/Exec SQL  Insert into TempTable Values(:DSAKOPP)
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)

-----Ursprungliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]Im Auftrag von Jerry Adams
Gesendet: Freitag, 28. Juli 2006 14:26
An: RPG programming on the AS400 / iSeries
Betreff: Re: PREPARE Statement Problem


Gentlemen (and Tommy),


Not wanting to omit anything, I may have been excessive in my notes
(below).  So, if you're really bored, read on or go grab a copy of
"Crime and Punishment."


I thought that I had tried using the parameter marker before, but
without success.  One of my early stabs at this had:


HdrString = 'INSERT Into ' + %trim(Library) + '/' + %trim(HdrFile) +
' Values(?)';


Later the PREPARE and then the EXECUTE HeaderSQL using :dsheader


Let me explain that the only reason for using an externally described
data structure (which, incidentally, is based upon the actual input
file) was because there are 67 fields in the file.  Just thought (hoped)
I could INSERT Into using it instead of all of the individual field
names.  The code sample (above) compiled but didn't work; same error
pointing to the PREPARE statement.


Just got through trying something else.  Since the E DS "trick" (above)
didn't work, I figured there wasn't a shortcut; that I would have to
define a parameter marker for each field name.


Since I couldn't figure out the applicable continuation rule (if any)
when setting up HdrString with 67 parameter markers in the VALUE clause,
I got a little "creative" and did it this way:


D @Values         S            150A   INZ('
VALUES+
D
(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?+
D
,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?+
D
,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?+
D
,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)')


In Calcs:


HdrString = 'INSERT Into ' + %trim(Library) + '/' + %trim(HdrFile) +
@Values;


When I ran it under STRDBG I captured the value (below):


EVAL HdrString
HDRSTRING =
         ....5...10...15...20...25...30...35...40...45...50...55...60
    1   'INSERT Into SERVOHOLD/HLTKON VALUES(?,?,?,?,?,?,?,?,?,?,?,?,'
   61   '?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,'
  121   '?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)          '


which, to me, looked accurate.


The relevant execution is:


C/EXEC SQL    EXECUTE HeaderSQL using :bncode,
C+                                    :bnco,
C+                                    :bndv,
C+                                    :bndp,   (etc. 63 more names)


Cute, but no cigars.


Then tried just passing the first ten field names (so I could get the
Values clause entirely on one line of Calcs), thus:


HdrString = 'INSERT Into ' + %trim(Library) + '/' + %trim(HdrFile) +
' Values(?,?,?,?,?,?,?,?,?,?)';


C/Exec SQL  Prepare HeaderSQL from :HdrString
C/End-Exec


C/EXEC SQL    EXECUTE HeaderSQL using :bncode,
C+                                    :bnco,
C+                                    :bndv,
C+                                    :bndp,
C+                                    :bnglac,
C+                                    :bnglsb,
C+                                    :bncust,
C+                                    :bnspto,
C+                                    :bndate,
C+                                    :bnref#
C/End-Exec


In debug it looks like this:


EVAL HdrString
HDRSTRING =
         ....5...10...15...20...25...30...35...40...45...50...55...60
    1   'INSERT Into SERVOHOLD/HLTKON Values(?,?,?,?,?,?,?,?,?,?)    '


Same error as reported earlier:  SQL0518 - Prepared statement *N not found.
No data on the receiving end.  Debug won't actually show the PREPARE or
EXECUTE statements being generated when one steps through, but I know
the program is issuing the error on the EXECUTE because I stepped
through and, in another session, displayed the job log after each step.


The suggestion of coding the string

HdrString = 'INSERT Into ' + %trim(Library) + '/' + %trim(HdrFile) +
' Values('''+%Trim(dsheader)+''')';

didn't work either.

> EVAL HdrString
 HDRSTRING =
           ....5...10...15...20...25...30...35...40...45...50...55...60
      1   'INSERT Into SERVOHOLD/HLTKON Values('')                     '


Now, I know that eschewing the PREPARE statement and just using the
following works:


C/EXEC SQL    INSERT Into SERVOHOLD/HLTKON Values(:DsHeader)
C/End-Exec


Since the process didn't work with either one [1], ten [10], or
sixty-seven [67] parameter markers, there most likely, it seems to me,
to be an error during the PREPARE phase; either the string that I am
building is messed up somehow, or something else is going awry.


After getting the feedback from Charles, Bob, and Tommy, I spent all day
working various scenarios on those suggestions, as illustrated above.
I, at least, triple-checked my syntax against both the Redbook and The
SQL Programming and Reference manuals.


By the way, there are two [2] other files in the program (LINES and
SHIPTO) that work, but only because I eschewed the PREPARE statement and
did on in-line INSERT using the data structure.  I only mention this
because it means that, at least, the CONNECT (and DISCONNECT) worked.
The job log, also, shows this to be so.


Thanks.
        * Jerry C. Adams
*IBM System i Programmer/Analyst
B&W Wholesale Distributors, Inc.* *
voice
        615.995.7024
fax
        615.995.1201
email
        jerry@xxxxxxxxxxxxxxx <mailto:jerry@xxxxxxxxxxxxxxx>



Wilt, Charles wrote:

Jerry,

You'll find lots of information about this in the archives.  But
basically, you can't use RPG variables in the prepare statement like you
are trying to do.

Instead of:

HdrString = 'INSERT Into SERVOHOLD/HLTKON Values(:DsHeader)'

You need

HdrString = 'INSERT Into SERVOHOLD/HLTKON Values (' + dsheader.fld1 +
',' dsheader.fld2 + ')';

You already figure this out for part of the SQL statement, see how you
specified the library and file name originally?
HdrString = 'INSERT Into ' + %trim(Library) + '/' + %trim(HdrFile) +
' Values(:dsheader)';


Now with dynamic SQL you can use parameter markers.  That's probably you
best solution, it would look like so:
HdrString = 'INSERT Into ' + %trim(Library) + '/' + %trim(HdrFile) +
' Values(?,?,?,?)';

C/Exec SQL  Prepare HeaderSQL from :HdrString
C/End-Exec

C/EXEC SQL    EXECUTE HeaderSQL using :dsHeader.fld1,
C+                                    :dsHeader.fld2,
C+                                    :dsHeader.fld3,
C+                                    :dsHeader.fld4,
C/End-Exec

By using parameter markers this way, you can prepare the statement once
using a variable file name and library and execute the statement
multiple times to insert multiple rows.

HTH,

Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Jerry Adams
Sent: Thursday, July 27, 2006 10:06 AM
To: RPG Midrange
Subject: PREPARE Statement Problem

I have been writing my first SQLRPGLE program and, while I have used
interactive SQL for quite awhile, the experience has been,
shall we say,
illuminating.  But I'm puzzled about a few seemingly
elementary problems.

The scenario is that there are two systems here.  The idea is to
programmatically push transactions from A to B.  Simple
enough - I thought.

As I interpreted the SQL manual and Birgitta, et al's
Redbook, the best
method was to use the PREPARE statement to create the INSERT
statement.
Below is, I think, the relevant code.  It compiles and
creates the SQL
package on System B when compiled on System A.


D DsHeader      E DS                  ExtName(Header)

HdrString = 'INSERT Into SERVOHOLD/HLTKON Values(:DsHeader)';

C/Exec SQL  Prepare HeaderSQL from :HdrString
C/End-Exec

C/EXEC SQL    EXECUTE HeaderSQL
C/End-Exec


However, nothing got pushed.  The job log had: Prepared
statement *N not
found.
Lower level message included:

Cause . . . . . :   An EXECUTE statement referred to the
statement *N.
*N is
not a valid prepared statement.  The statement has one of the
following

conditions:

  -- The statement has never been
prepared.
  -- The statement identifies a prepared SELECT or DECLARE
PROCEDURE

statement.

  -- The statement was in error at prepare or bind
time.

and a few other things, but the error seemed to be the last one.

STRDBG didn't tell me much (if anything) and a DUMP after the PREPARE
yielded as much (nada).

After I replaced the EXECUTE statement with the hard-coded
INSERT statement:

  C/Exec SQL
C+          INSERT Into SERVOHOLD/HLTKON Values(:dsheader)
C/End-Exec

It worked.

Originally, the HdrString line looked like this:

HdrString = 'INSERT Into ' + %trim(Library) + '/' + %trim(HdrFile) +
' Values(:dsheader)';

Which was the whole reason for using the PREPARE statement:
To make the
program variable-driven, not hard-coded.

Any idea what my faux pas is (other than trying to sound
classy by using
French)?


Another concern I have is with the CONNECT statement.  Until
I added my
user id and password, the connection failed.  Hard-coding, or even
passing, user ids and passwords is not, in my estimation, good
practice.  I'm working on alternatives, but is there a way to
configure
System B to accept the connection without the user id and
password, at
least, in this context?

--
        * Jerry C. Adams
*IBM System i Programmer/Analyst
B&W Wholesale Distributors, Inc.* *
voice
        615.995.7024
fax
        615.995.1201
email
        jerry@xxxxxxxxxxxxxxx <mailto:jerry@xxxxxxxxxxxxxxx>


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