Problem:
Accessing an Excel 97 Spreadsheet with Embedded SQL
Resolution:
Create a spreadsheet that will be used with embedded SQL by creating one that contains
name/titles at the top of each column. These titles will be the column names used in the embedded SQL statements.
Highlight the whole table or the applicable piece of the worksheet and assign a name to this group of data. This is accomplished by selecting INSERT > NAME > DEFINE function. This name will be the table name assigned to this data.
The Save As function must be used to save this spreadsheet.
Now you can create a datasource using an appropriate ODBC driver for Excel, and point it to this .xls file.
Below is an example of a spreadsheet. The group of data is named tsttable.
Table tsttable has 4 columns - field1, field2, field3 and field4.
In the attached COBOL program, generated by the OpenEsql Assistant, you will see the columns identified in the SELECT statement as field1,field2, field3,and field4 while the FROM clause
identifies the tsttable table.
<table CELLSPACING="0" BORDER="0" CELLPADDING="2" WIDTH="336">
<tr>
<td WIDTH="25%" VALIGN="TOP" HEIGHT="21"><p
ALIGN="CENTER">field1</td>
<td WIDTH="25%" VALIGN="TOP" HEIGHT="21"><p
ALIGN="CENTER">field2</td>
<td WIDTH="25%" VALIGN="TOP" HEIGHT="21"><p
ALIGN="CENTER">field3</td>
<td WIDTH="25%" VALIGN="TOP" HEIGHT="21"><p
ALIGN="CENTER">field4</td>
</tr>
<tr>
<td WIDTH="25%" VALIGN="TOP" HEIGHT="21"><p
ALIGN="RIGHT">10</td>
<td WIDTH="25%" VALIGN="TOP" HEIGHT="21"><p
ALIGN="RIGHT">20</td>
<td WIDTH="25%" VALIGN="TOP" HEIGHT="21"><p
ALIGN="RIGHT">30</td>
<td WIDTH="25%" VALIGN="TOP" HEIGHT="21"><p
ALIGN="RIGHT">40</td>
</tr>
<tr>
<td WIDTH="25%" VALIGN="TOP" HEIGHT="21"><p
ALIGN="RIGHT">100</td>
<td WIDTH="25%" VALIGN="TOP" HEIGHT="21"><p
ALIGN="RIGHT">200</td>
<td WIDTH="25%" VALIGN="TOP" HEIGHT="21"><p
ALIGN="RIGHT">300</td>
<td WIDTH="25%" VALIGN="TOP" HEIGHT="21"><p
ALIGN="RIGHT">400</td>
</tr>
</table>
$SET SQL(DBMAN=ODBC)
Data Division.
01 dfield1 pic zzzzzzzzz9.99.
01 dfield2 pic zzzzzzzzz9.99.
01 dfield3 pic zzzzzzzzz9.99.
01 dfield4 pic zzzzzzzzz9.99.
EXEC SQL INCLUDE tsttable END-EXEC
*****************tsttable copybook************************************
EXEC SQL DECLARE 'tsttable' TABLE
( 'field1' NUMBER
,'field2' NUMBER
,'field3' NUMBER
,'field4' NUMBER
) END-EXEC.
******************************************************************
* COBOL DECLARATION FOR TABLE tsttable *
******************************************************************
01 DCLtsttable.
03 A-field1 COMP-2.
03 A-field2 COMP-2.
03 A-field3 COMP-2.
03 A-field4 COMP-2.
******************************************************************
* COBOL INDICATOR VARIABLES FOR TABLE *
******************************************************************
01 DCLtsttable-NULL.
03 A-field1-NULL PIC S9(04) COMP-5.
03 A-field2-NULL PIC S9(04) COMP-5.
03 A-field3-NULL PIC S9(04) COMP-5.
03 A-field4-NULL PIC S9(04) COMP-5.
*****************end tsttable copybook********************************
EXEC SQL INCLUDE SQLCA END-EXEC
*****************sqlca copybook**************************************
01 SQLCA.
05 SQLCAID PIC X(8) VALUE 'SQLCA '.
05 SQLCABC PIC S9(9) COMP-5 VALUE 136.
05 SQLCODE PIC S9(9) COMP-5 VALUE 0.
05 SQLERRM.
49 SQLERRML PIC S9(4) COMP-5.
49 SQLERRMC PIC X(70).
05 SQLERRP PIC X(8).
05 SQLERRD PIC S9(9) COMP-5 OCCURS 6 VALUE 0.
05 SQLWARN.
10 SQLWARN0 PIC X.
10 SQLWARN1 PIC X.
10 SQLWARN2 PIC X.
10 SQLWARN3 PIC X.
10 SQLWARN4 PIC X.
10 SQLWARN5 PIC X.
10 SQLWARN6 PIC X.
10 SQLWARN7 PIC X.
10 SQLWARN8 PIC X.
10 SQLWARN9 PIC X.
10 SQLWARN10 PIC X.
10 SQLWARNA PIC X REDEFINES SQLWARN10.
05 SQLSTATE PIC X(5).
*****************end sqlca copybook************************************
Procedure Division.
EXEC SQL
CONNECT TO 'INT_EXCEL'
END-EXEC
EXEC SQL
DECLARE CSR52 CURSOR FOR SELECT
