|
Hi,if you are already on release V5R3 or higher, you can define and use an array data structure:
D Array1 DS DIM(999) Qualified D A1Code 3AWhen using an array data structure you can get direct access to the elements:
midrange.com
For example:
C/Exec SQL Fetch next from C1 For 10 rows into :Array1
C/End-Exec
/Free
If SQLER3 <> *Zeros // At least 1 row returned
For Index = 1 to SQLER3;
Fld2 = Array1(Index).A1Code;
Dsply Fld2;
EndFor;
EndIf;
/End-Free
If you are not yet on release V5R3, you have to define and use a multi
occurence data structure.
D Array1 DS OCCURS(999) D A1Code 3ATo get access to the elements, you first have to read the appropriate data structure by either using the opcode occur or the built-in-function %OCCUR.
For example:
C/Exec SQL Fetch next from C1 For 10 rows into :Array1
C/End-Exec
/Free
If SQLER3 <> *Zeros // At least 1 row returned
For Index = 1 to SQLER3;
%Occur(Array1) = Index;
Fld2 = A1Code;
Dsply Fld2;
EndFor;
EndIf;
/End-Free
Just some more tips:
1. Never use Host variables that start with 'SQL', 'SQ', 'RDI' or 'DSN'.
These variables are reserved for the database manager. Even if these
variables can be used today, they can cause problems with one of the next
releases.
2. Replace dynamic SQL through static SQL whenever possible. In your example you do not need to built the SQL-Statement dynamically. The variable where conditions can be replaced by using a Host-Variable.
C/EXEC SQL C+ Declare ... For c+ Select max(SVCD26) From Carrier C+ Where SCVD26 <> :HstVar C+ Group By SCVD26 C/END-EXEC(Even though I don't understand what you want to archieve with this statement.)
DB2 UDB for iSeries is optimized for static SQL. That means access plans will be stored in the programm objects, so they have only to be validated and not to be rebuilt each time the SQL-Statement gets called. Access plans are necessary to built or better to open the data path (ODP) to get access to the data.
3. And avoid special signs, such as @, #, $, §. They are not international. I added the §-sign willfully, because we must use it instead of the @-sign. If we get sources from an american machine, we always have to replace @ through §.
Mit freunlichen Grüßen / Best regards Birgitta Hauser"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown)
"If you think education is expensive, try ignorance." (Derek Bok)----- Original Message ----- From: <fkany@xxxxxxxxxxxxxxxxxx>
To: "RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx>
Sent: Friday, October 20, 2006 22:51
Subject: Re: fill Array using SQL
Hi Birgitta,
I would like to return multiple rows with a single fetch. I setup an array
data structure, but I get the following error when trying to compile the
new program:
"Position 36 Host structure array A1CODE not defined or not usable."
D @SQLCMD S 70A
D Q C ''''
*
* Vendor Service Code Array
D Array1 DS
D A1Code 3A DIM(999)
*
* Embedded SQL Initializations
C/EXEC SQL
C+ Set Option
C+ Naming = *Sys,
C+ Commit = *None,
C+ UsrPrf = *User,
C+ DynUsrPrf = *User,
C+ Datfmt = *Iso,
C+ CloSqlCsr = *EndMod,
C+ Commit = *NONE
C/END-EXEC
*
* Build Vendor Service Code Array
C EVAL @SQLCMD = 'select max(SVCD26) ' +
C 'from CARRIER ' +
C 'where SVCD26 <> ' +
C Q + ' ' + Q +
C ' group by SVCD26'
C/EXEC SQL
C+ PREPARE SQL1 FROM :@SQLCMD
C/END-EXEC
C/EXEC SQL
C+ DECLARE C1 CURSOR FOR SQL1
C/END-EXEC
C/EXEC SQL
C+ OPEN C1
C/END-EXEC
C IF SQLCOD >= 0
C/EXEC SQL
C+ FETCH C1 FOR 10 ROWS INTO :A1Code
C/END-EXEC
C ENDIF
C/EXEC SQL
C+ CLOSE C1
C/END-EXEC
*
C EVAL *INLR = *ON
C RETURN
"HauserBirgitta"
<Hauser@SSS-Softw
are.de> To
Sent by: "RPG programming on the AS400 /
rpg400-l-bounces@ iSeries" <rpg400-l@xxxxxxxxxxxx>
midrange.com cc
Subject
10/20/2006 03:30 Re: fill Array using SQL
PM
Please respond to
RPG programming
on the AS400 /
iSeries
<rpg400-l@midrang
e.com>
Hi,
you have to fetch the single value per row into a workfield and after move
this workfield into the appropriate array element.
In your example you always fill A1CODE (or the first array element because
it's overlayed by the array).
C/EXEC SQL
C+ FETCH C1 INTO :WrkField
C/END-EXEC
C EVAL Array(i) = WrkField
C EVAL i = i + 1
If you want to return multiple rows with a single fetch, you either have to
define a multi occurrence data structure or an array data structure, but
not
an array.
Mit freunlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
----- Original Message -----
From: <fkany@xxxxxxxxxxxxxxxxxx>
To: <rpg400-l@xxxxxxxxxxxx>
Sent: Friday, October 20, 2006 21:32
Subject: fill Array using SQL
Happy Friday everbody! I'd appreciate any suggestions on why my program isn't filling an array using SQL. I've created the test program below to see how to populate an Array with SQL. At first I set my break point at the end of the program so that I could go check the values in the array. I found that the array was
blank.
I reset my break point at the FETCH(C Z-ADD -4
SQLER6) statement and re-ran the program.
The following line of code populates the array element:
C EVAL A1CODE = SQL_00011
The next line of code clears it out:
C EVAL VNDSVCCDE = SQL_00012
Debug View:
======================================
C*EXEC SQL
C* FETCH C1 INTO :Array1:i
C*END-EXEC
C Z-ADD -4 SQLER6
C CALL SQLROUTE
C PARM SQLCA
C PARM SQL_00006
C SQL_00009 IFEQ '1'
C EVAL A1CODE = SQL_00011
C EVAL VNDSVCCDE = SQL_00012
C EVAL I = SQL_00013
C END
C EVAL i = i + 1
C IF i = 999
C LEAVE
C ENDIF
C ENDDO
C ENDIF
Source Code:
=================================
D i S 5i 0
D @SQLCMD S 47A
*
* Vendor Service Code Array
D Array1 DS
D A1Code 3A
D VndSvcCde LIKE(A1Code) OVERLAY(Array1)
DIM(999)
*
* Embedded SQL Initializations
C/EXEC SQL
C+ Set Option
C+ Naming = *Sys,
C+ Commit = *None,
C+ UsrPrf = *User,
C+ DynUsrPrf = *User,
C+ Datfmt = *Iso,
C+ CloSqlCsr = *EndMod,
C+ Commit = *NONE
C/END-EXEC
*
* Build Vendor Service Code Array
C EVAL @SQLCMD = 'select max(SVCD26) ' +
C 'from CARRIER ' +
C 'group by SVCD26'
C/EXEC SQL
C+ PREPARE SQL1 FROM :@SQLCMD
C/END-EXEC
C/EXEC SQL
C+ DECLARE C1 CURSOR FOR SQL1
C/END-EXEC
C/EXEC SQL
C+ OPEN C1
C/END-EXEC
C IF SQLCOD >= 0
C EVAL i = 1
C DOU SQLCOD < 0
C/EXEC SQL
C+ FETCH C1 INTO :Array1:i
C/END-EXEC
C EVAL i = i + 1
C IF i = 999
C LEAVE
C ENDIF
C ENDDO
C ENDIF
C/EXEC SQL
C+ CLOSE C1
C/END-EXEC
*
C EVAL *INLR = *ON
C RETURN
--
This is the RPG programming on the AS400 / iSeries (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 AS400 / iSeries (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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
