Need a sample code for Sql fetching multiple rows

130 pts.
Tags:
SQL
SQLCBLLE
In Native cobol we use read PF file in a loop until EOF to fetch all records.Now i need to do the same by using Sql in cobol,eg: select * from File and show it in subfile.Can anyone help me how to do this.

How to move recods from curson to screen.Thankx in advance for ur help.

 

supriyo B



Software/Hardware used:
as400
ASKED: April 15, 2010  8:55 AM
UPDATED: April 19, 2010  10:20 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

I’ve noticed this has been unanswered for a while. I have some expertise in RPG, not COBOL (sorry), but maybe this will help since RPG and COBOL on the iSeries are similar. I’m assuming the embedded SQL is the same.

In the sample code below I move SQL results into RPG (program) variables. Those program variables could easily be SFL fields. Program variables are designated in SQL EXECs by preceding them with “:” (colon) . In this example :sID is a program variable used to determine which records to select. The flow is:
<ol>
<li>In my main routine, I first call the ‘Open’ routine</li><li>The first bit of RPG code in this subroutine makes sure the cursor is closed</li><li>Embedded SQL then declares a cursor for an SQL Statement</li><li>Embedded SQL opens the cursor</li><li>The next bit of RPG checks for an open cursor, and if so, calls a fetch routine</li><li>The fetch routine is all embedded SQL in an RPG subroutine wrapper. It grabs one record and places the results into program variables ORORD#, ORINV, and CoOCDESC</li><li>In my main routine, I keep calling the fetch routine (not the Open routine) to fill my SFL until the EOF (coEof=True).</li>
</ol>

Hopefully someone much smarter than I with COBOL skills will improve this answer. In the mean time, I hope this helps.
<pre>
* Open SQL
C CoSQLOpen BegSR
* Close the SQL Cursor if it’s open
C CoOpen Ifeq True
C Exsr CoSQLClose
C Endif
* Create the SQL statement and cursor
C/EXEC SQL
C+ Declare CoCursor Cursor
C+ For
C+ Select C.OCORD#, B.ORINV, C.OCDESC
C+ From COMMENTLY C, ORDBILL B
C+ Where C.OCORD# = B.ORODR#
C+ And C.OCTYP = ‘Y’
C+ And C.OCDLT <>’D’
C+ And C.OCDESC Like :sID
C/END-EXEC
* Open Cursor
C/EXEC SQL
C+ Open CoCursor
C/END-EXEC
* Check for a resultset. If we got one fetch first record. Set flags
/Free
If (SQLCod <> 0) Or (SQLWn0 <> *BLANK);
Eval CoOpen = False;
Eval CoEof = True;
Else;
Eval CoOpen = True;
Exsr CoSQLFetch;
EndIf;
/End-Free
C EndSR

* Fetch next record
C CoSQLFetch Begsr
C/EXEC SQL
C+ Fetch Next
C+ From CoCursor
C+ Into :OCORD#,
C+ :ORINV,
C+ :CoOCDESC
C/END-EXEC
/Free
Eval CoEof = SqlCod = SqCdNoRow;
/End-Free
C EndSr

</pre>
============================================================

Since the above RPG is here for comparison, I’ll add some ILE SQL CBL code that’s extracted (and modified) out of a larger program. The code shown shows a possible way of filling a 200 element array that could then be the basis of a SUBFILE that can hold 200 rows:<pre>
Identification Division.
Program-ID. SetupSFAry.
/
Environment Division.

Configuration Section.
Source-computer. IBM-AS400.
Object-computer. IBM-AS400.
INPUT-OUTPUT SECTION.

FILE-CONTROL.
DATA DIVISION.
FILE SECTION.

WORKING-STORAGE SECTION.

EXEC SQL
INCLUDE SQLCA
END-EXEC.

01 SubFile-Table.
05 SF-Row occurs 200.
10 SF-SomeF1 PIC X(10).
10 SF-SomeF2 PIC X(10).
10 SF-SomeOpt PIC X.

01 C1-Status pic 9 value 0.
88 C1-not-open value 0.
88 C1-Open value 1.

Procedure Division.

00-Main-driver.

if C1-not-open
perform 100-load-table
end-if.

goback.

00-Main-driver-exit. exit.

100-load-table.

Set C1-Open to true.

EXEC SQL
Declare C1 cursor for
Select SomeF1, SomeF2, SomeOpt from SFTABLE
order by SomeF1, SomeF2
END-EXEC.

EXEC SQL open C1
END-EXEC.

EXEC SQL
Fetch C1 for 200 rows
Into :SF-Row
END-EXEC.

display ‘SomeF1 SomeF2 ‘ SF-SomeF1(1) SF-SomeF2(1)
display ‘SomeF1 SomeF2 ‘ SF-SomeF1(2) SF-SomeF2(2)
display ‘SomeF1 SomeF2 ‘ SF-SomeF1(3) SF-SomeF2(3)
display ‘SomeF1 SomeF2 ‘ SF-SomeF1(4) SF-SomeF2(4)
display ‘SomeF1 SomeF2 ‘ SF-SomeF1(5) SF-SomeF2(5)
display ‘SomeF1 SomeF2 ‘ SF-SomeF1(6) SF-SomeF2(6)
100-load-table-exit. exit.</pre>
Of course, the DISPLAY statements are simply for sample testing.

The FETCH grabs 200 rows into the array. The program would use those to WRITE subfile rows, maybe all 200 or maybe a page at a time.

Depending on how the program should work, different variations of the FETCH might be used. E.g., the subfile attributes of the DSPF might be queried to see how many rows are on each subfile page. The FETCH might then only fetch that many rows. Or the FETCH might be controlled ‘relative’ to the current cursor position, sometimes fetching the previous set of rows, and other times continuing forward for the next set of rows.

Tom

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Supriyoas400
    Hi .. Thanks a lot for the answer.but i am working in CBLLE .My main concern in how to move multiple row data into the screen of subfile. can anyone write a snap-shot of the code of fetching and moving to screen.
    130 pointsBadges:
    report
  • TomLiotta
    The only thing left to do to populate a subfile in the CBLLE code that I supplied is the MOVE statement that copies from array elements into the subfile fields. I could code that, but I don't know your subfile field names nor the names of the fields you'd be FETCHing. You'd simply PERFORM the MOVE however many times you want to fill subfile records. The array index would be incremented by 1 each time. A subfile record would be written each time. Practically speaking, that's all there is to it. What exactly are you needing to know? Tom
    125,585 pointsBadges:
    report
  • Supriyoas400
    [...] Learning Server-Side JavaScript with Node.js | Blancer.com Tutorials and projects Need a sample code for Sql fetching multiple rows Using C++ templates for startup validation · Technology Articles Programming exercise: [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following