RE: PREPARE Statement Problem

["Bob Cozzi" <cozzi@xxxxxxxxx>]

Jerry,
You've got a host variable (actually a host structure) as a quoted value in your
string. This is not permitted. You need to use another technique or use the ?
substitution symbols when creating your SQL string.

-Bob Cozzi
www.iSeriesTV.com
Watch your industry news on-line.
Ask your friends to tune into iSeriesTV.com


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On
Behalf Of Jerry Adams
Sent: Thursday, July 27, 2006 9: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?