To create an ODBC data source for reading from a fixed flat text file:
 
  1. Create a new fixed flat PC text file named TEST1.TXT in the C:\ directory.
  2. Launch the ODBC Administrator: Go to Start > Run, type ODBCAD32.EXE, and click OK.

  3. Click Add.
  4. Choose the Microsoft Text Driver and click Finish.

  5. Set up the data source:

    1. In Data Source Name, type RJSTEXT.
    2. Click Select Directory and choose C:\
  6. Click Options to expand the text file options.

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

  8. 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                                               

 


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

Last Modified On: December 10, 2016