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

Still have questions? We can help. Submit a case to Technical Support.

Last Modified On: December 10, 2016