Thanks
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Haas, Matt (CL Tech Sv)
Sent: Friday, November 13, 2009 10:17 AM
To: RPG programming on the IBM i / System i
Subject: RE: Looking for BASIC example of using embedded
SQLforretrievingfield value
John,
SQLCOD and SQLSTT (SQLCODE and SQLSTATE were introduced in more recent
releases) both return status information about the statement that was just
executed.
SQLSTT is the preferred variable to check because the values are an actual
standard, there is only one SQLSTT value for a given error (some SQLCOD's
are issued for multiple errors), and the values don't change between
releases (this has happened with SQLCOD a couple of times, I got bit by it
once so I don't use it anymore).
Have you looked at the SQL programming books in the InfoCenter yet? Error
handling is covered pretty well in them.
Matt
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of John Allen
Sent: Friday, November 13, 2009 10:08 AM
To: 'RPG programming on the IBM i / System i'
Subject: RE: Looking for BASIC example of using embedded SQL
forretrievingfield value
Also, while looking for the error codes, I see references to a field SQLCODE
My program does not have a field SQLCODE (looking in debug)
I have a field SQLCOD but nothing named SQLCODE
I am compiling on .......(Don't holler at me) V5R1 if that make a difference
What is SQLCODE and why would I not have it?
What field should contain the error codes
SQLCODE
SQLCOD
SQLSTT
Other??
John
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of John Allen
Sent: Friday, November 13, 2009 9:40 AM
To: 'RPG programming on the IBM i / System i'
Subject: RE: Looking for BASIC example of using embedded SQL
forretrievingfield value
Will download this today,
Thanks
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Jerry Adams
Sent: Friday, November 13, 2009 7:16 AM
To: RPG programming on the IBM i / System i
Subject: RE: Looking for BASIC example of using embedded SQL for
retrievingfield value
John,
May I suggest that you download the Redbook "Modernizing iSeries Application
Data Access" (SG24-6393-00). It is basic, well organized, and well written.
I often use it as a reference, and I re-read chapters periodically since I
am not an SQL expert.
Jerry C. Adams
IBM System i Programmer/Analyst
--
B&W Wholesale
office: 615-995-7024
email: jerry@xxxxxxxxxxxxxxx
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of John Allen
Sent: Thursday, November 12, 2009 4:55 PM
To: 'RPG programming on the IBM i / System i'
Subject: RE: Looking for BASIC example of using embedded SQL for retrieving
field value
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
midrange.com
