RE: SQL statement not working

Hello all you lovely people,

Thank you. I have tried all the suggestions & Charles suggestion seems to
work fine.

Thanks again for all your suggestions - I learnt a ton.
Ewart










"Wilt, Charles" <WiltC@xxxxxxxxxx>
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
03/25/2008 07:23 PM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>

To
"RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx>
cc

Subject
RE: SQL statement not working





A cursor is _NOT_ required.

While SELECT INTO is not valid for prepared statements, you can use the
VALUES INTO.

Note QT defined as constant (assumes your hhstock, witemcd, and hhbrand
variables are character. If
they aren't you don't need the QT.)

D QT c ''''

wsqlstm = 'values (select b0bvva from ' +
%trim(wLib820)+ '/opbal '+
' where b0aqcd = '+ QT + hhstock + QT +' and '+
' b0bycd = '+ QT + witemcd + QT +' and '+
' b0c8cg = '+ QT + hhbrand + QT ) into ?'

exsr PrepSQL;

/exec SQL
Exec SQL execute SqlStmt using :hhopbal;
/end-exec




Charles Wilt
Software Engineer
CINTAS Corporation - IT 92B
513.701.1307

wiltc@xxxxxxxxxx

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx

[mailto:rpg400-l-bounces@xxxxxxxxxxxx]

On Behalf Of Robert Rogerson
Sent: Tuesday, March 25, 2008 8:48 AM
To: RPG programming on the AS400 / iSeries
Subject: RE: SQL statement not working

Correct Rick, a cursor must be used. Sorry for the misinformation.

Rob

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx

[mailto:rpg400-l-bounces@xxxxxxxxxxxx]

On Behalf Of rick baird
Sent: March 25, 2008 8:17 AM
To: RPG programming on the AS400 / iSeries
Subject: Re: SQL statement not working

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.

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

This e-mail transmission contains information that is intended to be
confidential and privileged. If you receive this e-mail and you are not a
named addressee you are hereby notified that you are not authorized to
read, print, retain, copy or disseminate this communication without the
consent of the sender and that doing so is prohibited and may be unlawful.
Please reply to the message immediately by informing the sender that the
message was misdirected. After replying, please delete and otherwise
erase it and any attachments from your computer system. Your assistance
in correcting this error is appreciated.
--
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.