To create an ODBC data source for reading from a fixed flat text file:
- Create a new fixed flat PC text file named TEST1.TXT in the C:\ directory.
- Launch the ODBC Administrator: Go to Start > Run, type ODBCAD32.EXE, and click OK.
- Click Add.
- Choose the Microsoft Text Driver and click Finish.
- Set up the data source:
- In Data Source Name, type RJSTEXT.
- Click Select Directory and choose C:\.
- Click Options to expand the text file options.
- Click Define Format and choose the following settings for the output format for TEST1.TXT:
Note: Set the width parameter to the width of the flat file record.
- Set the text format setting 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
Last Modified On:
No, open a new Support Case
