SIMPLE SQL EXAMPLE 2

[rstitzel]

I've decided to start to try using SQL in my RPG programs as I'm reading that there are some advantages and I also want to broaden my skills.

I have a lot of questions and an example probably would answer most. One question is. Assuming the SELECT statement is like a READ (one record at a time) I would have to "wrap" the SELECT statement in a loop? Not sure how this would be coded. Can I do the select, dow not %eof(WMCUST), do some calcs, perform the select again, enddo
And cursors? What and how are they used.

I've read some of SQL posts in this forum but only see snippets of code and don't understand all of it. If someone could please post a small complete RPG program using embedded SQL statements that would be great.

The program that I'm trying to create as a test is a simple Customer Address Listing.

C/exec sql
C+ SELECT CustNum, CustName, CustAddr, CustCity, CustState, CustZip from CUSTMAST
C+ WHERE CustDel <> 'D' and <> 'I'
C+ ORDER BY CustNum
C/end-exec

Is as far as I got :-(

Thank you very much!

 

[rstitzel]

THIS IS WHAT I CAME UP WITH ... BUT IT GETS STUCK IN A LOOP. ITS READING THE FIRST RECORD OF THE CUSTOMER MASTER OVER AND OVER. NOT SURE HOW TO MAKE IT GO TO THE NEXT RECORD HELP !!

F**********************************************************************************************
F*** Program to Practice & Test Embedded SQL Statements
F**********************************************************************************************
F
F*** Program will read through the customer master file and print a report
F
FWMCUST IF E K DISK
FSQLTESTPF O E PRINTER oflind(*in20)
F
C
C/Exec SQL
C+ Set Option Commit = *none
C/End-Exec
C
C/Exec SQL
C+ Select WCCUST, WCNAME, WCADD1, WCCITY, WCSTAT, WCZIPC
C+ Into :RNum, :RName, :RAddr, :RCity, :RState, :RZip
C+ From WMCUST
C+ Where WCDELE <> 'D'
C+ Order By WCCUST
C/End-Exec
C
C write Header
C
C dow not %eof(WMCUST)
C
C if *in20 =*on
C write Header
C eval *in20 =*off
C endif
C
C Write Detail
C
C/Exec SQL
C+ Select WCCUST, WCNAME, WCADD1, WCCITY, WCSTAT, WCZIPC
C+ Into :RNum, :RName, :RAddr, :RCity, :RState, :RZip
C+ From WMCUST
C+ Where WCDELE <> 'D'
C+ Order By WCCUST
C/End-Exec
C
C enddo
C
C eval *inlr =*on
C return

 

[LearningFox]

First thing is that you do not need the f-spec for the PF.

Second, when you want to loop through a set of records, you load a cursor ( temporary result set) and then fetch one record at a time from the cusor for your report processing.

I will post a short example later.

 

[rstitzel]

LearningFox,

Thanks for the reply and I would very much appreciate the example.

Thank you...

 

[LearningFox]

<code>
*****************************************************************
* Program name: EAf20R150
*
* Program objective: send email to sale reps for previously faxed
* EA Select customer welcome letter
*
* Creation date: 10/23/2003
*
* Created by: *
* Parameters:
*
* Action | Description | Attribute
* --------------|-----------------|-------------
*
* Programming Notes:
*
* Error Codes:
*
**=================================================================**
** H - S P E C S **
**=================================================================**

hOption(*NoDebugIO : *SrcStmt : *ShowCpy)
hnomain

* Include prototypes

d sendSaleEml pr

d sendEmail pr extpgm('EAG20C164')
d Account# 7

p sendSaleEml b export

* procedure interface definition

d sendSaleEml pi

*
* Declare parms to use
*

d strAccount s 7
d numAccount s 7 0

d QT c ''''
d OK c 0
d EOF c 100
d editAcct c '0 '

d count s 4s 0

/free
/end-free

c/exec sql
c+ set option datfmt=*ISO, commit=*NONE, closqlcsr=*ENDMOD
c/end-exec

c/exec sql
c+ declare c1 cursor for
c+ select eacust from aircustom/eag20p150
c+ join traklibdb/csta1
c+ on eacust = cst#9b
c+ where int19b = 'FAX' and eadate <> '2003-10-23'
c/end-exec

c/exec sql
c+ open c1
c/end-exec

/free
dou sqlcod <> OK;
/end-free

c/exec sql
c+ fetch c1 into :numAccount
c/end-exec

* do something with the data now before it's overwritten!

/free
if sqlcod = OK;
exsr look_up;
else;
if sqlcod = EOF;
leave;
endif;
endif;
/end-free

/free
count = count + 1;
enddo;
/end-free

c/exec sql
c+ close c1
c/end-exec

/free
*inlr = '1';
return;
/end-free

*
* Subroutines
*

/free
begsr look_up;
evalr strAccount = %editw(numAccount :editAcct);
callp sendEmail(strAccount);
endsr;
/end-free

p sendSaleEml e
<code>

Take a look at thi and if you have questions, fire away.

 

[thelearner]

Hi,

Try these 2 web sites.

http://www.partner400.com/SQLIntro+Embed.pdf

http://www.midrangeserver.com/fhg/fhg011404-story01.html

 

[rstitzel]

Thanks Rapeek, I'll look over those links.

From Learningfox's example I was able to create my first program with embedded sql statements. A simple Customer Address listing, but now I understand the basics. Thank you ...

 

[sanduke]

hi
can embedded SQL be done in RPG III or only in RPG IV?
thanks

 

[flapeyre]

I answered this in thread317-805253, but for the benefit of those who have not looked at that thread, the short answer is Yes.


&quot;When once you have tasted flight, you will forever walk the Earth with your eyes turned skyward, for here you have been, and there you will always long to return.&quot;

--Leonardo da Vinci

 

[Ronze55]

I'm a bit late on this thread but I would like to move data from one table to another table using embedded SQL and the FETCH phrase

LearnFox gave a great example but it is sending the cursor inforation to a program ds. Instead I would like to put it into a file

here is basically what i have
======================================================
foutpf o e disk
c/exec sql
c*
c+ DECLARE get_data CURSOR FOR
c+ SELECT Field1, Field2
c+ from table
c/end-exec

c/exec sql
c+ open get_data
c/end-exec

c/exec sql
c+ fetch from get_data
c+ into :field1, :field2
c/end-exec

close
commit

seton lr
===============================================
what should I be doing

thanks
Ronze

 

[LearningFox]

If I understand what you want to do (update file01 from a value found in file02) you don't really need a cursor.

Try a single sql statement like this:

INSERT INTO FILE01 (MYCHAR)
SELECT MYCHAR FROM
FILE02 WHERE MYCHAR LIKE 'M%'

If you type "strsql" from a command line and type "insert" and prompt it (F4) you can figure out how to make your statement.

 

[Ronze55]

LearnFox,

Thanks for your reply. Unfortunately I need to do this with a RPGLE and on multiple fields. I have about 44 fields that need to be moved from table1 to table2.

I am making a table that will be attached to via OCBC. the table itself has errors in it that need to be corrected and the RPGLE is really a QC step. The program will eventually go to a scheduler and be run at night.

You brought up Insert, which I would like to use. I thought the insert form you suggested was only for one record field at a time?

thanks!
Ronze

 

[Ronze55]

Here is a message I am getting

SQL0312 30 56 Position 31 Variable FIELD1 not defined or not usable.

as always thanks for your responce
ronze

 

[Mercury2]

Ronze55,
When you say "move", you really mean move records out from table1 and add them to table2 ?

 

[Ronze55]

I have started to try this statement

Insert into table1
select field1, field2 from table2

commit
seton lr


the programs now compile however, no data gets updated to table1. any reasons why?

thanks for your feedback!
ronze

 

[LearningFox]

Ronze, I did not mean to imply that you not use RPGLE. I was suggesting that you work out the format of your sql statement prior to putting it in your program.

Having said that, perhaps I did not fully understand your situation. If you need to retrieve one record at a time from file01 and validate or edit it then add that record to file02, that would change my recommnedations. In that case, I would suggest that you use record=level access as it will be more efficient.

SQL is very good for handling sets of records as a group; for a single record at a time, I think record-level access would be better.

Having said that, if you want to use SQL, you can select a single record into a data structure, edit the DS, insert into file02 and loop until SQLCOD = EOF(100).

If you chosse this route, I could probably find an example.

Kyle

 

[Ronze55]

Kyle,

thanks for your reply, I started doing what you suggested yesterday.

cc