RE: PREPARE Statement Problem

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.