5769PW1 V4R2M0 980228 SEU SOURCE LISTING 09/16/03 15:42:07 PAGE 1
SOURCE FILE . . . . . . . RJSLIB/RJSRPGSQL
MEMBER . . . . . . . . . TSTSQL02R
SEQNBR*...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 ...+... 9 ...+... 0
100 ********************************************************************* 10/09/02
200 * Program Name: TSTSQL02R 07/25/03
300 * 03/02/03
400 * Purpose: 03/02/03
500 * This sample program reads an AS/400 table and writes the data 07/25/03
600 * to a MS Excel Spreadsheet. 07/25/03
700 * 03/02/03
800 * Note: This sample does no error checking. In your own code you 10/09/02
900 * will need to check the return codes and last error by 10/09/02
1000 * using the last error return info returned by the 03/02/03
1100 * SQL_LastErrNum, SQL_LastErrMsg or SQL_LastFullErr message. 03/02/03
1200 * 03/02/03
1300 * DATE: 08/10/2002 03/02/03
1400 * AUTHOR: RICHARD J. SCHOEN. 03/02/03
1500 * MOD LEVEL: 0000 03/02/03
1600 * MOD DATE: XX/XX/XX 03/02/03
1700 * REASON: 03/02/03
1800 * 03/02/03
1900 ********************************************************************* 10/09/02
2000 *----------------------------------------------------------------------------- 03/02/03
2100 * RPG2SQL API Includes 03/02/03
2200 *----------------------------------------------------------------------------- 03/02/03
2300 FCUSTOMER IF E DISK 07/25/03
2400 /COPY SOURCE,RPGSQLH 10/09/02
2500 D qt S 1 INZ('''') 07/21/03
2600 D SQL S 3800 INZ('') 07/21/03
2700 D ExcelFile S 255A 07/22/03
2800 D ExcelTemplate S 255A 07/22/03
2900 D ExcelSheet S 255A VARYING 07/22/03
3000 D DefaultDir S 255A 07/22/03
3100 D StartCol S 5A VARYING 07/22/03
3200 D EndCol S 5A VARYING 07/22/03
3300 D TblRow S 255A VARYING 07/22/03
3400 D CurRow S 5P 0 07/22/03
3500 D EndRow S 5P 0 07/22/03
3600 10/09/02
3700 *----------------------------------------------------------------------------- 10/04/00
3800 * Main Program Processing 10/04/00
3900 *----------------------------------------------------------------------------- 10/04/00
4000 C MOVEL '125.1.1.169' IPADDR 100 07/22/03
4100 12/23/00
4200 *----------------------------------------------------------------------------- 10/03/00
4300 * ** Connect to RPG/SQL Server 03/02/03
4400 *----------------------------------------------------------------------------- 10/09/02
4500 C Eval SQL_Socket = SQL_Connect(%TRIM(IPADDR)) 10/09/02
4600 08/30/02
4700 C* ** Exit with Error Return - TCP Server Connect 08/30/02
4800 C If SQL_Socket = -999 08/30/02
4900 C Eval Rtn = -1 08/30/02
5000 C Eval *INLR = *On 08/30/02
5100 C Return 08/30/02
5200 C Endif 08/30/02
5300 *----------------------------------------------------------------------------- 07/22/03
5769PW1 V4R2M0 980228 SEU SOURCE LISTING 09/16/03 15:42:07 PAGE 2
SOURCE FILE . . . . . . . RJSLIB/RJSRPGSQL
MEMBER . . . . . . . . . TSTSQL02R
SEQNBR*...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 ...+... 9 ...+... 0
5400 * Set Excel File and Sheet Name we will be working with 07/25/03
5500 *----------------------------------------------------------------------------- 07/22/03
5600 C Eval ExcelFile = 'c:\margin3.xls' 07/22/03
5700 C Eval ExcelSheet = 'July$' 07/22/03
5800 07/22/03
5900 *----------------------------------------------------------------------------- 07/25/03
6000 * Open ADO SQL database connection to spreadsheet 07/25/03
6100 *----------------------------------------------------------------------------- 07/25/03
6200 * ** Open spreadsheet connection 07/22/03
6300 C Eval Rtn = SQL_DBOpenConn(SQL_Socket: 07/22/03
6400 C 'Provider=Microsoft.Jet.OLEDB.4.0;' + 07/22/03
6500 C 'Data Source=' + 07/22/03
6600 C %trimr(ExcelFile) + ';' + 07/22/03
6700 C 'Extended Properties=' + 07/22/03
6800 C '"Excel 8.0;HDR=No;IMEX=2;";') 07/22/03
6900 09/08/02
7000 *----------------------------------------------------------------------------- 10/09/02
7100 * ** Run Update Query to Update the Date 07/22/03
7200 * ** Headers in the selected spreadsheet. 07/22/03
7300 *----------------------------------------------------------------------------- 10/09/02
7400 07/22/03
7500 * ** Determine which current row we're updating 07/22/03
7600 C Eval StartCol = 'B' 07/22/03
7700 C Eval EndCol = 'J' 07/22/03
7800 C Eval CurRow = 2 07/22/03
7900 C Eval EndRow = 2 07/22/03
8000 C Eval TBlRow='['+ExcelSheet+StartCol+ 07/22/03
8100 C %trim(%editc(CurRow:'Z'))+':'+ 07/22/03
8200 C EndCol+ 07/22/03
8300 C %trim(%editc(EndRow:'Z'))+ 07/22/03
8400 C ']' 07/22/03
8500 07/22/03
8600 C Eval SQL = 'UPDATE ' + TblRow + ' SET ' + 07/22/03
8700 C 'F1 = ' + 07/22/03
8800 C QT + '2/1/2000' + QT + ',' + 07/22/03
8900 C 'F3 = ' + 07/22/03
9000 C QT + '2/5/2000' + QT + ',' + 07/22/03
9100 C 'F5 = ' + 07/22/03
9200 C QT + '1/15/2000' + QT + ',' + 07/22/03
9300 C 'F7 = ' + 07/22/03
9400 C QT + '1/20/2000' + QT + ',' + 07/22/03
9500 C 'F9 = ' + 07/22/03
9600 C QT + '1/25/2000' + QT 07/22/03
9700 07/22/03
9800 C Eval Rtn = SQL_RunSQLExec(SQL_Socket:SQL) 07/21/03
9900 07/22/03
10000 *----------------------------------------------------------------------------- 07/25/03
10100 * ** Read all records from CUSTOMER table 07/25/03
10200 * ** and write to specified Excel spreadsheet 07/25/03
10300 *----------------------------------------------------------------------------- 07/25/03
10400 C Eval CurRow = 5 07/25/03
10500 C Eval StartCol = 'A' 07/25/03
10600 C Eval EndCol = 'M' 07/25/03
5769PW1 V4R2M0 980228 SEU SOURCE LISTING 09/16/03 15:42:07 PAGE 3
SOURCE FILE . . . . . . . RJSLIB/RJSRPGSQL
MEMBER . . . . . . . . . TSTSQL02R
SEQNBR*...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 ...+... 9 ...+... 0
10700 07/25/03
10800 C DOW EOF1 = 0 07/25/03
10900 07/25/03
11000 C READ CUSTOMER 73 07/25/03
11100 C 73 Z-ADD 1 EOF1 1 0 07/25/03
11200 07/25/03
11300 C* ** If not EOF, write record to Excel 07/25/03
11400 C If EOF1 = 0 07/25/03
11500 07/25/03
11600 * ** Determine which current row we're updating 07/25/03
11700 C Eval EndRow = CurRow 07/25/03
11800 C Eval TBlRow='['+ExcelSheet+StartCol+ 07/25/03
11900 C %trim(%editc(CurRow:'Z'))+':'+ 07/25/03
12000 C EndCol+ 07/25/03
12100 C %trim(%editc(EndRow:'Z'))+ 07/25/03
12200 C ']' 07/25/03
12300 07/25/03
12400 * ** Update the fields in current spreadsheet 07/25/03
12500 * ** detail row. Each column is referred to as a 07/25/03
12600 * ** field name starting with F. The fields stay 07/25/03
12700 * ** in the same relative order as follows. 07/25/03
12800 * ** Ex: If the start and end column selected 07/25/03
12900 * ** were A-M, the field names would be as follows: 07/25/03
13000 * ** Field name: Culumn A = F1, B = F2, etc. 07/25/03
13100 * ** Ex: If the start and end column selected 07/25/03
13200 * ** were AA-AM, the field names would be as follows: 07/25/03
13300 * ** Field name: Column AM = F1, BM = F2, etc. 07/25/03
13400 07/25/03
13500 C Eval SQL = 'UPDATE ' + TblRow + ' SET ' + 07/25/03
13600 C 'F1 = ' + QT + 'Test' + QT + ',' + 07/25/03
13700 C 'F2='+%trim(%EDITC(DOLLAR1:'P'))+',' + 07/25/03
13800 C 'F3='+%trim(%EDITC(DOLLAR1:'P'))+',' + 07/25/03
13900 C 'F4='+%trim(%EDITC(DOLLAR1:'P'))+',' + 07/25/03
14000 C 'F5='+%trim(%EDITC(DOLLAR1:'P'))+',' + 07/25/03
14100 C 'F6='+%trim(%EDITC(DOLLAR1:'P'))+',' + 07/25/03
14200 C 'F7='+%trim(%EDITC(DOLLAR1:'P'))+',' + 07/25/03
14300 C 'F8='+%trim(%EDITC(DOLLAR1:'P'))+',' + 07/25/03
14400 C 'F9='+%trim(%EDITC(DOLLAR1:'P'))+',' + 07/25/03
14500 C 'F10='+%trim(%EDITC(DOLLAR1:'P'))+',' + 07/25/03
14600 C 'F11='+%trim(%EDITC(DOLLAR1:'P'))+',' + 07/25/03
14700 C 'F12='+%trim(%EDITC(DOLLAR1:'P'))+',' + 07/25/03
14800 C 'F13='+%trim(%EDITC(DOLLAR1:'P')) 07/25/03
14900 C Eval Rtn = SQL_RunSQLExec(SQL_Socket:SQL) 07/25/03
15000 07/25/03
15100 C Eval CurRow = CurRow + 1 07/25/03
15200 07/25/03
15300 C Endif 07/25/03
15400 07/25/03
15500 C Enddo 07/25/03
15600 09/08/02
15700 *----------------------------------------------------------------------------- 10/09/02
15800 * ** Close ADO Database Connection. We're done. 03/02/03
15900 *----------------------------------------------------------------------------- 10/09/02
5769PW1 V4R2M0 980228 SEU SOURCE LISTING 09/16/03 15:42:07 PAGE 4
SOURCE FILE . . . . . . . RJSLIB/RJSRPGSQL
MEMBER . . . . . . . . . TSTSQL02R
SEQNBR*...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 ...+... 9 ...+... 0
16000 C callp SQL_DBCloseConn(SQL_Socket) 08/30/02
16100 08/30/02
16200 *----------------------------------------------------------------------------- 10/09/02
16300 C* ** Disconnect from RPG2SQL server 03/02/03
16400 *----------------------------------------------------------------------------- 10/09/02
16500 C callp SQL_Disconnect(SQL_Socket) 08/30/02
16600 08/30/02
16700 C SETON LR 08/30/02
* * * * E N D O F S O U R C E * * * *
Last Modified On:
No, open a new Support Case
