Re: Looking for BASIC example of using embedded SQL for retrieving field value -- RPG400-L

Typically we'd allow passing in a "where clause" to do selection -- for
example, what if the file had 7 keys? Anyway, you'll have to use a cursor
in this case. I will not be a fab performer so don't rely on this type of
access heavily.

d aStatement s 8192a varying
d p1Where s 2048a varying (a parm from somewhere)

/free

aStatement = 'select ' + %trim( PARM5 ) + ' +
from ' + %trim( PARM2 ) + '/' +
%trim( PARM1 ) + ' +
where ' + p1Where;
// or with your parm
where ' + %trim( PARM3 ) +
' = ''' + %trim( PARM4 ) + ''';

exec sql prepare $stmt from :aStatement;
exec sql declare $curs cursor for $stmt;
exec sql open $curs;

// minimal error handler -- there are better ways.
if sqlcode <> *zero
// I usually send an escape message to the caller here using the message
id
// and data in the sqlca. At any rate, do not continue.
endif;

exec sql fetch from curs into :wrkfld;

// minimal error handler -- there are better ways.
if sqlcode <> *zero;
clear wrkFld;
endif;

exec sql close $curs;

/end-free

Stu

On Thu, Nov 12, 2009 at 17:15, <rob@xxxxxxxxx> wrote:

Just to repeat, you should check SQLSTT after every exec sql statement.
See my sample earlier.

Also, since you cannot do a 'select into' on a prepared statement you will
have to use a cursor.

There are some who say if you are using a cursor in SQL you are using it
wrong. I am NOT in that camp.

Under normal circumstances I do think it's rather silly to use a cursor
for a single row fetch. But if you really have a need for a generic
utility like you are trying to do then you'll have to go that route.

Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com

From: Alan Campin <alan0307d@xxxxxxxxx>
To: "RPG programming on the IBM i / System i" <rpg400-l@xxxxxxxxxxxx>
Date: 11/12/2009 06:09 PM
Subject: Re: Looking for BASIC example of using embedded SQL for
retrieving field value
Sent by: rpg400-l-bounces@xxxxxxxxxxxx

Your sql is error out and your are not check the value of SQLSTATE. Cannot
do a Select Into with a prepared statement.

On Thu, Nov 12, 2009 at 3:55 PM, John Allen <jallen@xxxxxxxxxxx> wrote:

I got the basic program working and I now inderstand the basics!!

Thanks to everyone that replied, the suggestions were are very helpful

and

simple.

I have even been able to add some more non-basic to it and keep it

working.

(ie Build the select screen with variables and execute it)

I do have one question:
I am hoping to make this a program that can retrieve a field value from

any

file (or almost any file)
The incoming parms will be
1) The physical file name
2) The physical file library name
3) The field name of the key field in the physical file
4) The key field value
5) The name of the field I want the value of returned

C *ENTRY PLIST
C PARM PARM1
C PARM PARM2
C PARM PARM3
C PARM PARM4
C PARM PARM5
Don't yell at me for the programming technique and naming conventions
this is only a simple test program not for production

So I want to pass:
Parm1 = VNDMAST
Parm2 = *LIBL
Parm3 = VENDORNBR
Parm4 = 00100
Parm5 = VENDORNM
If I build the select statement dynamically it does not seem to work.
Is what I am trying to do possible?
If yes, what do I have coded incorrectly?
(Select VENDORNM into a work field (WRKFLD)
from VNDMAST Where VENDORNBR is 00100)
C eval W$SQLCmd = 'Select '
C + PARM5
C + ' into :'
C + 'WRKFLD'
C + ' from '
C + PARM1
C + ' Where '
C + PARM3
C + ' = :'
C + ''''
C + PARM4
C + ''''
C/exec SQL
C+ Set option Commit = *NONE
C/End-Exec
*
C/exec SQL
C+ execute immediate :W$SQLCmd
C/End-Exec

This program runs without an error but does not retrieve any value

I was hoping it would put the vendor name into the work field WrkVndNm

Thanks

John

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

mailto:rpg400-l-bounces@xxxxxxxxxxxx]

On Behalf Of Stuart Rowe
Sent: Thursday, November 12, 2009 4:58 PM
To: RPG programming on the IBM i / System i
Subject: Re: Looking for BASIC example of using embedded SQL for

retrieving

field value

/free

exec sql select ifnull(ComanyName,'') into :workvariable from FILENAME
where CompanyNbr = 'keyvalue'

if sqlcode <> *zero;
clear workvariable;
endif;

/end-free

ought to do it just fine. Nothing fancy. No error handling per se.
Nothing else needed really. If you need to qualify the filename, use a
slash like LIB/FILE.

If you want the "variable length-ness" to come in as well then declare
workvariable as varying.

Stu

On Thu, Nov 12, 2009 at 10:59, <rob@xxxxxxxxx> wrote:

Should be on the rpg list.

H ActGrp(*CALLER)
H DftActGrp(*NO)
*ENTRY PLIST
D JohnAllen PR extpgm('JOHNALLEN')
D CompanyNbr like(cmpny)
D JohnAllen PI
D CompanyNbr like(cmpny)
D RCO E DS extname(RCO)

/free
*inlr=*on;

// SQL's version of the H spec
EXEC SQL
Set Option
Naming = *Sys,
Commit = *None,
UsrPrf = *User,
DynUsrPrf = *User,
Datfmt = *iso,
CloSqlCsr = *EndMod;
exec sql
select cmpnam into :cmpnam
from rco
where cmpny = :CompanyNbr;
// Did it work?
Select;
When SqlStt='00000';
dsply cmpnam;
Other;
dsply SqlStt;
EndSl;
return;
/end-free

Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com

From: "John Allen" <jallen@xxxxxxxxxxx>
To: <MIDRANGE-L@xxxxxxxxxxxx>
Date: 11/12/2009 11:38 AM
Subject: Looking for BASIC example of using embedded SQL for
retrieving field value
Sent by: midrange-l-bounces@xxxxxxxxxxxx

I have finally got the opportunity to learn embedded SQL in RPG ILE.

I have been searching on the web for the most basic example I can find

for

my application
but everything I find has more then I need (I think) and it is

confusing.

I find I can learn much quicker by seeing an example.

Does anyone have or can anyone point me to an example that does the
following (No more, no extra stuff that will confuse me)

I have a physical file with 10 fields (COMPANY)

The unique key field (Company Number) is two character field (Alpha),
The 5th field in the company file is a variable length field (Company
Name).

The only parameter I have coming in is the key value.

I would like to use SQL to retrieve the record for the key value

(Company

Nbr) and get the value in the 5th field (Company Name) and place it in

a

work field.

No updating, No deleting, just take the key value retrieve the company
name
and place it in a work field

I know this is very easy to do with a simple chain, but I believe we

could

do a lot more with embedded SQL in other programs
so I want to learn how to do this with SQL. Then hopefully all the

other

examples on the web will make sense to me.

Thanks in advance

John

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the RPG programming on the IBM i / System i (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 IBM i / System i (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 IBM i / System i (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 IBM i / System i (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 IBM i / System i (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.