Re: SQL statement not working

["rick baird" <rick.baird@xxxxxxxxx>]

and what about this?

into :hhopbal

that's not valid in a prepare / excecute either is it?

On Tue, Mar 25, 2008 at 8:11 AM, Robert Rogerson
<robertrogerson@xxxxxxxxxx> wrote:
> 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
>  --
>  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.
>  --
>  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.