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


  1.  Home
  2. RPG400-L
  3. July 2006

AW: AW: PREPARE Statement Problem

Hi,

why not simply overriding the table/file with OVRDBF?

C/Free
    CLCmd = 'OVRDBF FILE(X)+
                    TOFILE(' + %Trim(MYSCHEMA)+ '/' +
                               %Trim(MYTABLE) + ') +
                    OVRSCOPE(*CALLLVL)'
    QCmdExc(CLCmd: %Len(CLCMD));
 /End-Free
C/Exec SQL  Insert into X Values(:MyDS)

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 19:49
An: RPG programming on the AS400 / iSeries
Betreff: Re: AW: PREPARE Statement Problem


Birgitta,

No, it won't always be the same table into which I need to INSERT
records.  We have two companies segregated by library so, depending upon
which library is passed in will dictate which library I push to on the
second system.  The push-to files will always be the same, though.


Even with that, I could have hard-coded it to work as you suggested.
I've been trying to push them here to use SQL more, especially in the
iSeries stuff.  So I figured I'd need to get this down sooner or later.

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.







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