|
Hmm, I thought attachments were supposed to be blocked? Ron Power Programmer Information Services City Of St. John's, NL P.O. Box 908 St. John's, NL A1C 5M2 Tel: 709-576-8132 Email: rpower@xxxxxxxxxx Website: http://www.stjohns.ca/ ___________________________________________________________________________ Success is going from failure to failure without a loss of enthusiasm. - Sir Winston Churchill Holden Tommy <Tommy.Holden@xxxxxxxxxxxxxxxxx> Sent by: rpg400-l-bounces@xxxxxxxxxxxx 12/11/2004 01:20 PM Please respond to RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx> To "Rpg400-L (E-mail)" <rpg400-l@xxxxxxxxxxxx> cc Subject FW: if SQL is an option for your utility.... Thanks, Tommy Holden > -----Original Message----- > From: Holden Tommy > Sent: Friday, November 12, 2004 9:59 AM > To: 'jonathan.mason@xxxxxxxxxxxxxxxx' > Subject: if SQL is an option for your utility.... > > This is a sample program I use.... > > <<TESTINGDA.txt>> > > Thanks, > Tommy Holden > -- 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 e-mail communication and accompanying documents is intended only for the individual or entity to which it is addressed and may contain information that is confidential, privileged or exempt from disclosure under applicable law. Any use of this information by individuals or entities other than the intended recipient is strictly prohibited. If you have received this in error, please notify the sender and delete all the copies (electronic or otherwise) immediately.
midrange.com
H BNDDIR('EMOBNDDIR' : 'MVBNDDIR')
Dc2n PR 30p 9 opdesc
Dc 32 const options(*varsize)
D p@select S 1024A
D p@From S 1024A
D p@Where S 1024A
D p@OrderBy S 1024A
D p@SQLStmt S 4096A
D p@VarIndex S 10I 0
D p@VarValue S 100A BASED(p@ValuePtr)
D p@NullValue S 4B 0 BASED(p@NullIndPtr)
D p@ValuePtr S *
D p@NullIndPtr S *
D w@ReportId S 10A
D w@ReportDS S 40A
D w@RptLine S 220A
D w@length S 10I 0
D w@Dummy S 10A
D w@FirstTime S N INZ(*ON)
*
D SQL_NUM C CONST(10)
* This is the expected number of
columns
* that the user might select. You can
use
* it in the SQLDA. SQL_NUM is not a
* reserved word.
D SQLDA DS
D SQLDAID 1 8A
* Used for storage dumps.
D SQLDABC 9 12B 0
* Total length of the SQLDA. The
* value is calculated using the
* formula SQLN * Length(SQLVAR)+16
D SQLN 13 14B 0
* Total number of occurrences of
SQLVAR
D SQLOCCUR 13 14I 0
* define as an integer.
D SQLD 15 16B 0
D SQLCols 15 16I 0
* Total pertinent number of
occurrences.
D SQLVAR 80A DIM(100)
* Instead of defining DIM(100), we can
* use SQL_NUM. SQL_NUM is not a
reserved
* word. Eg.- DIM(SQL_NUM)
D 17 18B 0
* Type of column.
D 19 20B 0
* Length of the column
D 21 32A
* Reserved by IBM to align boundaries.
D 33 48*
* Pointer to data (value of the
column)
D 49 64*
* Pointer to null indicator of the
column
D 65 66B 0
* Length of the column name.
D 67 96A
* Actual Column Name.
* The following data structure is used to fill
* pointers, for data and retrieve values.
D SQLVAR1 DS
D SQLTYPE 1 2B 0
D Sql_Type 1 2I 0
D SQLLEN 3 4B 0
D Precision 3 3I 0
D Scale 4 4I 0
D Length 3 4I 0
D SQLRES 5 16A
D SQLDATA 17 32*
D SQLIND 33 48*
D SQLNAMELEN 49 50B 0
D SQLNAME 51 80A
*
D w@returnValue S 20S 5
D w@Char S 32A
D w@NumValue S 30P 9
*
C EVAL SQLDABC = 8016
C EVAL SQLN = 100
C EVAL SQLD = 4
*
C EVAL p@varIndex = 1
C EVAL p@SqlStmt = 'SELECT JOBID, JOBNAME, JOBRUNCM-
C d, FREQUENCY FROM JBT100 '
**
* Prepare the SQL Statement.
**
C/EXEC SQL
C+ PREPARE SQLSTMT FROM :p@SQLStmt
C/END-EXEC
**
* Describe the SQL Statement. By doing a describe, SQL will
* will return the column attributes back in SQLDA. We then
* use the column attributes to allocate storage.
**
C/EXEC SQL
C+ DESCRIBE SQLSTMT INTO :SQLDA
C/END-EXEC
C EVAL p@VarIndex = 1
C DOW P@VarIndex <= SQLCOLS
C EVAL SqlVar1 = SQLVAR(p@VarIndex)
* The ALLOC function allocates memory in the heap. Factor 2
* contains the length of the storage to be allocated.
* The result field will return a pointer to that location and
* This pointer will be stored in the SQLDA.
C ALLOC SQLLEN SQLDATA
C ALLOC 1 SQLIND
C EVAL SQLVAR(p@VarIndex) = SQLVAR1
C EVAL p@VarIndex = p@varIndex + 1
C ENDDO
c/EXEC SQL
C+ DECLARE DYN_CURSOR SCROLL CURSOR FOR SQLSTMT
C/END-EXEC
C/EXEC SQL
C+ OPEN DYN_CURSOR
C/END-EXEC
**
* We do not use host variables. SQLDA points us to the values of
* columns using two pointers that we have allocated previously after
* describing the SQL Statement.
**
C/EXEC SQL
C+ FETCH FROM DYN_CURSOR USING DESCRIPTOR :SQLDA
C/END-EXEC
**
* Retrieve the field names.
**
C EVAL p@VarIndex = 1
C EVAL w@firsttime = *ON
C DOW SQLCOD = *ZEROS
C EVAL p@VarIndex = 1
C DOW p@VarIndex <= SQLCOLS
C EVAL SqlVar1 = SQLVAR(p@VarIndex)
**
* SQLDATA and SQLIND are pointers to the column values. SQLDATA points to
* the actual column value and SQLIND tells us if the value is a null
value.
**
C EVAL p@ValuePtr = SQLDATA
C EVAL p@NullIndPtr = SQLIND
C/EXEC SQL
C+ FETCH NEXT FROM DYN_CURSOR USING DESCRIPTOR :SQLDA
C/END-EXEC
C ENDDO
C ENDDO
C
* Close the cursor.
C/EXEC SQL
C+ CLOSE DYN_CURSOR
C/END-EXEC
C EVAL *INLR = *ON
C RETURN
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.
