PREPARE Statement Problem

[Jerry Adams <jerry@xxxxxxxxxxxxxxx>]

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?