RE: PREPARE Statement Problem

["Holden Tommy" <Tommy.Holden@xxxxxxxxxxxxxxxxx>]

First thing:

HdrString = 'INSERT Into ' + %trim(Library) + '/' + %trim(HdrFile) +
 ' Values(:dsheader)'; 

Should be either:
HdrString = 'INSERT Into ' + %trim(Library) + '/' + %trim(HdrFile) +
 ' Values(?)';

Or

HdrString = 'INSERT Into ' + %trim(Library) + '/' + %trim(HdrFile) +
 ' Values('''+%Trim(dsheader)+''')';   

When using the ? Placeholder on the EXECUTE statement it would look
like:

EXECUTE HeaderSQL Using :dsheader

Also for pushing data across systems there are some initial setup steps
that have to be done.

ADDRDBDIRE for each system


Run this on system1:
ADDRDBDIRE RDB(SYSTEM2) RMTLOCNAME(system2.dns.com *IP) TEXT('RDB Entry
For system2')

Run this on system2:
ADDRDBDIRE RDB(SYSTEM1) RMTLOCNAME(system1.dns.com *IP) TEXT('RDB Entry
For system1')
                        
Then you can use the CONNECT statement to access remote data.  The big
issue is that you can't access both systems at the same time using
RDB...
                                             

Thanks,
Tommy Holden


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