RE: SQL statement not working

I think the problem is that you should not be using host variables in a
Prepared statement, rather use parameter markers.

wsqlstm = 'select b0bvva into :hhopbal from ' +
%trim(wLib820)+ '/opbal '+
' where b0aqcd = ? and '+
' b0bycd = ? and '+
' b0c8cg = ?;
exsr PrepSQL;
exsr ExecSQL2;
.
.
.
CSR ExecSQL2 begsr
c/Exec SQL execute SqlStmt using :hhstock, :witecmd, :hhbrand
c/End-Exec
C endsr

HTH,
Rob
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of ewart.desouza@xxxxxxxxxxx
Sent: March 25, 2008 7:18 AM
To: RPG programming on the AS400 / iSeries
Subject: SQL statement not working

Hello All,

I am trying to use an SQL statement to get the opening & closing balance
from two separate members in a file. The statement does not seem to work.
When it is run in interactive SQL if works fine. I know there is something
to do with ':' but i just can't seem to find the fault.

I've only pasted the code for the opening balance as the Closing balance
code is similar.
// Get Opening Balance
wsqlstm = 'DROP ALIAS ' + %trim(wlib820)+'/'+ 'OpBal';
exsr PrepSQL;
exsr ExecSQL;
wsqlstm = 'CREATE ALIAS ' + %trim(wLib820)+ '/opbal FOR '+
%trim(wLibdta) + '/'+'STVATRP'+'(' + %trim(wMbrFr) +')';
exsr PrepSQL;
exsr ExecSQL;
wsqlstm = 'select b0bvva into :hhopbal from ' +
%trim(wLib820)+ '/opbal '+
' where b0aqcd = :hhstock and '+
' b0bycd = :witemcd and '+
' b0c8cg = :hhbrand';
exsr PrepSQL;
exsr ExecSQL;
-----------------

/end-free
CSR PrepSQL begsr
c/Exec SQL Prepare SqlStmt from :wsqlstm
c/End-Exec
C endsr
CSR ExecSQL begsr
c/Exec SQL execute SqlStmt
c/End-Exec
C endsr

-----------------

Is the statement in bold correct ??

Thanks & best regards
Ewart