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

OK, I am adding error checking now and it seems fairly straight forward.

But, where do I get a list of the error codes and their meaning?
Such as:
"row not found", "fatal exception", "success",
"how many rows did I read?" and the like.

John

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Stuart Rowe
Sent: Thursday, November 12, 2009 7:37 PM
To: RPG programming on the IBM i / System i
Subject: Re: Looking for BASIC example of using embedded SQL for retrieving
field value

Also, I'll reiterate what Rob said, you really really need to check for
error conditions when using embedded SQL. Embedded SQL statements will not
generate "errors' that can be caught with monitor ( you can see some of them
in the joblog ). They set various status codes in the sqlca (Communication
Area) that YOU need to pay attention to. The sqlca is placed into your
program by the SQL precompiler so you don't need to declare it. You just
need to know what's in it.

You can test for things like "row not found", "fatal exception", "success",
"how many rows did I read?" and the like. I prefer to do this myself rather
than using ON ERROR or ON ROW NOT FOUND since I use a lot of free form code
and goto bugs me.

As you said, your "program ran without error" and it did seem to didn't it?
It's only because you did not pay attention to the errors.

Keep at it, SQL can be really useful.

Stu

On Thu, Nov 12, 2009 at 18:28, Stuart Rowe <rowestu@xxxxxxxxx> wrote:

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

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

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.