× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.





As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.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 on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.