RE: RPG embeded SQL insert statement

Thanks Rob.

The code that I included is not a completed code.  I understand that you
must declare and open the cursor before fetch.  My main concern is on
the insert statment.  If the sql open, insert, close on each record, the
process will be every slow, I would think, compare to a regular RPG code
below:

C                    read                  FILEA        
C                dow             not %eof

C                if              chkSomething()
C                    write         FILEBfmt 
C                endif

C                    read                  FILEA         
C                enddo




-----Original Message-----
From: rob@xxxxxxxxx [mailto:rob@xxxxxxxxx]
Sent: Tuesday, May 06, 2003 3:17 PM
To: Midrange Systems Technical Discussion
Subject: Re: RPG embeded SQL insert statement


Lim,

(Sorry if I have your names backward)

The fetch and the insert are totally unrelated.

That fetch would not execute.  You would either:
1)  Replace the fetch with a simple SELECT INTO.
2)  Set up a loop like the following:

C/exec sql 
C+ Declare FILEAcursor for 
C+ Select ....
C+ From FILEA
C+ Where ...
C/end-exec 

C/exec sql 
C+ Open FILEAcursor
C/end-exec 

C                dow             sqlCod = 0

C/exec sql 
C+ fetch from FILEAcursor into: field1, field2 
C/end-exec 

C                if              sqlCod <> 0
C                leave
C                endif 

...
 
C                enddo

C/exec sql 
C+ Close FILEAcursor
C/end-exec 

And depending on a host of things, including, but not limited to, the 
phase of the moon, your FILEB may close.  Mostly it's related to what
you 
have in:
     C/EXEC SQL
     C+ Set Option
     C+     CloSqlCsr = *EndMod
     C/END-EXEC
But even that has to be intensely studied.
Because, even though FILEB wasn't handled via a cursor, it seems to
think 
it was.  Well, at least on a SELECT INTO.  Pray that a INSERT is more 
reasonable.


Rob Berendt
-- 
"They that can give up essential liberty to obtain a little temporary 
safety deserve neither liberty nor safety." 
Benjamin Franklin 




"Lim Hock-Chai" <Lim.Hock-Chai@xxxxxxxx>
Sent by: midrange-l-bounces@xxxxxxxxxxxx
05/06/2003 02:58 PM
Please respond to Midrange Systems Technical Discussion
 
        To:     <midrange-l@xxxxxxxxxxxx>
        cc: 
        Fax to: 
        Subject:        RPG embeded SQL insert statement


does embeded SQL insert statement open, insert the record, and then
close 
the file for each insert?

example

C                dou             sqlCod <> 0
C/exec sql 
C+ fetch from FILEAcursor into: field1, field2 
C/end-exec 

C                if              sqlCod <> 0
C                leave
C                endif 

C                if              chkSomething()
C/exec sql 
C+ insert into FILEB (field1, field2) values(: field1, :field2) 
C/end-exec 
C                endif
 
C                enddo


does the sql open FILEB, insert record to FILEB, and then close FIELEB
for 
each insert?

_______________________________________________
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.cgi/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 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.cgi/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.