Sample Program to Read a Fixed Flat PC Text File using RPG2SQL
From Support
Create ODBC Data Source for Reading from a fixed flat Text File
1.) Create the fixed flat PC text file TEST1.TXT file in the C:\ directory
2.) From Windows Select Start/Run, type ODBCAD32.EXE and click OK to launch ODBC Administrator.
3.) Click Add to create the RJSTEXT ODBC Data Source.
4.) Select the Microsoft Text Driver and click Finish.
5.) Enter RJSTEXT for the data source. Select C:\ as the default directory.
6.) Click Options to expand the text file options.
7.) Click Define Format to define the output format for TEST1.TXT.
Enter the settings as listed above and save the settings. The width parameter can be however wide the flat file record is.
A text format setting must be set up for each PC file where custom formatting is required.
Sample RPG Program to Create Fixed Text PC File
*********************************************************************
* Program Name: SQTEST13R
* Purpose:
* 1) Connects to a RPG2SQL server using specified IP address
* 2) Connects to ODBC Text Driver
* (Data Source Must be Manually Setup via ODBCAD32.EXE)
* 3) Creates a New Single Record Flat Text File via ADO/ODBC.
* 4) Reads TEXTFILE1 flat file and inserts all records into TEST1.TXT
* TEST1.TXT is a fixed length flat file text file.
* 5) Closes ADO connection.
* 6) Closes RPGSQL server connection.
*
* Note: This sample does no error checking. In your own code you
* will need to check the return codes and last error by
* using the last error return info.
*
*********************************************************************
FTEXTFILE1 IF E DISK
/COPY SOURCE,RPGSQLH
D quot S 1 INZ('''')
*---------------------------------------------------------------------
* Main Program Processing
*---------------------------------------------------------------------
C *ENTRY PLIST
C PARM IPADDR 100
*---------------------------------------------------------------------
* Connect to RPG/SQL Server
*---------------------------------------------------------------------
C* ** Connect to RPG SQL Server
C Eval SQL_Socket = SQL_Connect(%TRIM(IPADDR))
C* ** Exit with Error Return - TCP Server Connect
C If SQL_Socket = -999
C Eval Rtn = -1
C Eval *INLR = *On
C Return
C Endif
*---------------------------------------------------------------------
* Open ADO SQL database connection to ODBC Text Driver - RJSTEXT
* (This ODBC Data Source must be manually configured by user.)
* (in order to create a fixed text output file.)
*---------------------------------------------------------------------
* ** Open text file ODBC connection
C Eval Rtn = SQL_DBOpenConn(SQL_Socket:
C 'DSN=RJSTEXT;' +
C 'Uid=;' +
C 'Pwd=;')
*
* Exit After Error Return
*
C If Rtn <> 0
C Eval *Inlr = *On
C Return
C EndIf
*
* Open Recordset From Sunin.Txt
*
C Eval Sql = 'SELECT * FROM TEST1.TXT'
C Eval Rtn = Sql_Runsqlsel(Sql_Socket:Sql)
*
* Go To Firstrecord
*
*
* Go To Previous
*
C Eval Rtnrecord=Sql_Moveprevbuf(Sql_Socket)
C Eval Rtnrecord=Sql_Movefirsbuf(Sql_Socket)
*
* Reset Control Variables
*
C Eval Eof1 = *Off
*
* Read All Records
*
*
C Dow Not Eof1
*
* If Errors, Assume Eof Reached
*
C If Rtnrecord = '*ERROR*'
C Eval Eof1 = *On
C EndIf
*
* If Not Eof, Get Record Data
*
C If Not Eof1
*
* Extract Field Data From Record Buffer
*
C Eval @Data = Sql_Getfldchrb(
C Rtnrecord:'~':1)
*
* Header Record
*
C If %Subst(@Data:1:1) = 'H'
C Eval Headerfound = *On
C Eval Count = Count + 1
C EndIf
*
* Detail Record
*
C If %Subst(@Data:1:1) <> 'H'
C And %Subst(@Data:1:1) <> 'T'
C Eval Count = Count + 1
C EndIf
*
* Trailor Record
*
C If %Subst(@Data:1:1) = 'T'
C Eval Trailorfound = *On
C Eval Count = Count + 1
C EndIf
*
* Go To Next Record
*
C Eval Rtnrecord=Sql_Movenextbuf(Sql_Socket)
*
C EndIf
C EndDo
*
* Close Database Conection
*
C Callp Sql_Dbcloseconn(Sql_Socket)
*
* Disconnect
*
C Callp Sql_Disconnect(Sql_Socket)
*
* Set Return Value
*
C Eval @Return = Trailorfound
*
C Eval *Inlr = *On
C Return
