|
Are you checking the SQLCOD or SQLSTT after each SQL statement? After the prepare statement you should be able to check the SQLCOD (or SQLSTT) to see why it's failing...I generally check the SQLCOD so I can look it up in the msgf QSQLMSG.... Thanks, Tommy Holden -----Original Message----- From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Jerry Adams Sent: Friday, July 28, 2006 7:26 AM To: RPG programming on the AS400 / iSeries Subject: 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
midrange.com
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 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.
