|
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 >
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.
