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

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.