These examples will show you some of the things you can do with RPG2SQL to create, manipulate or delete Microsoft Access database tables.

Unlike Excel where you can directly create the spread sheet, if you want to create a database you will have to start with a blank database already created and you can use the following command to copy the blank database in order to create a table, place data into and still allowing the blank database to remain unchanged for future use.

Copy Database

*---------------------------------------------------------------------
*                  ** Copy selected PC file                           
*---------------------------------------------------------------------
C                   Eval      Rtn = SQL_PCFileCopy(SQL_Socket:         
C                             'c:\TestBlankDB.mdb':                    
C                             'c:\testoutput\TestFilledDB.mdb')        

Connecting To Database

Once the blank database is created you can connect to it using the following code:
*---------------------------------------------------------------------
*                  ** Open ADO SQL Database Connection                
*                  ** for MS Access Database TestFilledDB.mdb         
*---------------------------------------------------------------------
C                   Eval      Rtn = SQL_DBOpenConn(SQL_Socket:         
C                             'Driver={Microsoft Access Driver ' +     
C                             '(*.mdb)}; ' +                           
C                             'Dbq=c:\testoutput\' +                   
C                             'TestFilledDB.mdb; ' +                   
C                             'Uid=admin;' +                           
C                             'Pwd=;')                                 

Creating Table

Once you have connected to the database you can create your table using the following example:
*---------------------------------------------------------------------
*                  ** Run SQL command to create NameAddress table     
*---------------------------------------------------------------------
C                   Eval      Rtn = SQL_RunSQLExec(SQL_Socket:         
C                             'CREATE TABLE NameAddress +              
C                             (ID COUNTER, First STRING, +             
C                             Last STRING, Address1 STRING, +          
C                             Address2 STRING, City STRING, +          
C                             State STRING, Zip STRING, +              
C                             Phone STRING, Fax STRING, +              
C                             Email STRING, Date1 DATETIME, +          
C                             Dollars NUMERIC)')                                  

Column Data Types

Text-Based Columns:

Text-Based Columns:
If the fields under a column would be used to hold any type of value, including regular text, such a column is treated as string-based. There are various data types you can apply to such a column. You can specify the data type as CHAR, STRING or VARCHAR.
Each one of the CHAR, STRING, or VARCHAR data types would produce the same effect. A column with the STRING, the CHAR, or the VARCHAR data type allows any type of value made of any character up to 255 symbols.
If you want the column to hold longer text, specify its data type as TEXT, MEMO, NOTE, or LONGTEXT. Such a column can hold any type of text, any combination of characters, and symbols, up to 64000 characters.
Boolean Columns:
If you want to create a column to hold only values as being true or being false, specify its data type as YESNO, BIT, or LOGICAL.
Byte and Integer1:
If you want a column to hold natural numbers, you can specify its data type as BYTE or INTEGER1. This is suited for a column that will hold small numeric values not to exceed 255.
Short and Integer2:
If you want the column to hold larger numbers that can exceed 255, specify its data type as SHORT or INTEGER2.
Long:
If the column will hold small to very large numbers, specify its data type as INT, INTEGER, INTEGER4 or LONG.
Floating-Point Value With Single Precision:
If you want to create a column that will hold regular decimal values without regards to precision on its value, specify its data type as SINGLE.
Floating-Point Value With Double Precision:
If the values of a column will require a good level of precision, specify its data type as DOUBLE. Alternatively, you can specify the data type as NUMERIC.
Money and Currency Columns:
If you want the values of a column to hold monetary values, specify its data type as MONEY or CURRENCY. Both MONEY and CURRENCY have the same effect.
Date and Time:
If you are creating a column whose values would consist of date, time, or both date and time, specify its data type as DATE or DATETIME. Both data types have the same effect in Microsoft Access.
Binary:
The Binary data type can let a column accept any type of data but it is equipped to interpret the value. For example, it can be used to receive hexadecimal numbers. To specify this when creating a column, specify its data type as either BINARY or VARBINARY.

Adding a New Column

After a table with one or more columns has been created, you can add a new column to it. To add a new column, after the ALTER TABLE statement and the name of the table, include an ADD COLUMN expression.
It is important to understand that the ADD COLUMN expression creates a new column at the end of the existing column(s). It cannot be used to insert a column in a table.
The column name must be a valid name for the new column and you must follow the rules for naming columns. The data type must be one of those we reviewed. The following code example adds a new string-based column named CellPhone to a table named NameAddress:
 *---------------------------------------------------------------------
 *                  ** Code to add a column                            
 *---------------------------------------------------------------------
C                   Eval      Rtn = SQL_RunSQLExec(SQL_Socket:         
C                             'ALTER TABLE NameAddress +               
C                             ADD COLUMN CellPhone STRING')            

Deleting a Column

To delete a column, start with the ALTER TABLE expression followed by the name of the table. After the ALTER TABLE TableName expression, follow it with a DROP COLUMN. The following code example deletes a column named Zip from the table named NameAddress:
 *---------------------------------------------------------------------
 *                  ** Code to delete a column                         
 *---------------------------------------------------------------------
C                   Eval      Rtn = SQL_RunSQLExec(SQL_Socket:         
C                             'ALTER TABLE NameAddress +               
C                             DROP COLUMN Zip')                        

Deleting a Table:

To delete a table, use the DROP TABLE. The following code example deletes a table named NameAddress:

 *---------------------------------------------------------------------
 *                  ** Code to delete a table                          
 *---------------------------------------------------------------------
C                   Eval      Rtn = SQL_RunSQLExec(SQL_Socket:         
C                             'DROP TABLE NameAddress')                

Complete Code Example

    100      H BNDDIR('RJSRPGSQL':'QC2LE') DFTACTGRP(*NO) ACTGRP(*NEW)                                                
    200       *********************************************************************
    300       * Program Name: TSTCRTDBR                                                                 
    400       *                                                                                                       
    500       * Purpose:                                                                                           
    600       * This sample program copies the TestBlankDB.mdb file and creates                
    700       * a new table and then inserts records into it.                                            
    800       *                                                                                                        
    900       * Processing Steps:                                                                               
  1000       *  1) Connects to a RPG2SQL server using specified IP address.                  
  1100       *  2) Copies TestBlankDB.mdb.                                                              
  1200       *  3) Opens a MS Access Database via ADO.                                           
  1300       *  4) Creates table NameAddress.                                                          
  1400       *  4) Inserts 10 duplicate records into the name and address table.             
  1500       *     **Note: Allowing duplicates is not standard coding practice,                
  1600       *     but for our example it works and you may have uses for                      
  1700       *     duplicate records as well in your own applications.                              
  1800       *  5) Closes ADO connection.                                                                 
  1900       *  6) Closes RPG2SQL server connection.                                                  
  2000       *                                                                                                       
  2100       *  Note: This sample does no error checking. In your own code you               
  2200       *        will need to check the return codes and last error by                         
  2300       *        using the last error return info returned by the                                  
  2400       *        SQL_LastErrNum, SQL_LastErrMsg or SQL_LastFullErr message.             
  2500       *                                                                                                        
  2600       *  DATE:       02/15/2007                                                                        
  2700       *  AUTHOR:     NATHAN A. KRAEMER                                                          
  2800       *  MOD LEVEL:  0000                                                                              
  2900       *  MOD DATE:   XX/XX/XX                                                                       
  3000       *  REASON:                                                                                           
  3100       *                                                                                                         
  3200       *********************************************************************     
  3300       *-----------------------------------------------------------------------------                            
  3400       * RPG2SQL Integrator API Includes                                                                        
  3500       *-----------------------------------------------------------------------------                            
  3600       /COPY SOURCE,RPGSQLH                                                                        
  3700      D quot            S              1         INZ(')                                                  
  3800                                                                                                                 
  3900       *-----------------------------------------------------------------------------                            
  4000       * Main Program Processing                                                                                
  4100       *-----------------------------------------------------------------------------                            
  4200      D DotIPAddr       S             15A                                                                
  4300      
       4400      C******************************************************************
       4500      C*  EXTERNAL PARAMETERS:                                           
  4600                                                                                                              
  4700      C     *ENTRY        PLIST                                                                       
  4800      C                   PARM                    IPADDR          255                                
  4900                                                                                                               
  5000       *-----------------------------------------------------------------------------                            
  5100       *                  ** Get dotted IP address from fully-qualified domain                                  
  5200       *                  ** name - not needed if using dotted IP address                                       
  5300       *-----------------------------------------------------------------------------                            
  5400      C                   Eval      Rtn = TCP_GetIPFromHost(IPAddr : DotIPAddr)           
  5500                                                                                                               
  5600       *                  ** Exit with Error Return - TCP Address Conversion                
  5700      C                   If        Rtn <> 0                                                               
  5800      C                   Eval      *INLR = *On                                                         
  5900      C                   Return                                                                             
  6000      C                   Endif                                                                               
  6100                                                                                                                
  6200       *-----------------------------------------------------------------------------                            
  6300       *                  ** Connect to RPG2SQL Server                                                           
  6400       *-----------------------------------------------------------------------------                            
  6500      C                   CallP     SQL_TCPMultBuff(1)                                            
  6600      C                   Eval      SQL_Socket = SQL_Connect(%trim(DotIPAddr))        
  6700                                                                                                              
  6800      C*                  ** Exit with Error Return - TCP Server Connect                    
  6900      C                   If        SQL_Socket = -999                                                
  7000      C                   Eval      Rtn = -1                                                              
  7100      C                   Eval      *INLR = *On                                                         
  7200      C                   Return                                                                             
  7300      C                   Endif                                                                               
  7400                                                                                                                
  7500       *---------------------------------------------------------------------       
  7600       *                  ** Copy selected PC file                                                     
  7700       *---------------------------------------------------------------------       
  7800      C                   Eval      Rtn = SQL_PCFileCopy(SQL_Socket:                           
  7900      C                             'c:\TestBlankDB.mdb':                                               
  8000      C                             'c:\testoutput\TestFilledDB.mdb')                                
  8100                                                                                                                 
  8200                                                                                                                 
  8300       *-----------------------------------------------------------------------------                            
  8400       *                  ** Open ADO SQL Database Connection                                                   
  8500       *                  ** for MS Access Database TestFilledDB.mdb                                            
  8600       *-----------------------------------------------------------------------------                            
  8700      C                   Eval      Rtn = SQL_DBOpenConn(SQL_Socket:                       
  8800      C                             'Driver={Microsoft Access Driver ' +                            
  8900      C                             '(*.mdb)}; ' +                                                         
  9000      C                             'Dbq=c:\testoutput\' +                                             
  9100      C                             'TestFilledDB.mdb; ' +                                               
  9200      C                             'Uid=admin;' +                                                         
  9300      C                             'Pwd=;')                                                                 
  9400                                                                                                                 
  9500       *-----------------------------------------------------------------------------                            
  9600       *                  ** Run SQL command to create NameAddress table                                         
  9700       *-----------------------------------------------------------------------------                            
  9800      C                   Eval      Rtn = SQL_RunSQLExec(SQL_Socket:                              
  9900      C                             'CREATE TABLE NameAddress +                                        
 10000      C                             (ID COUNTER, First STRING, +                                         
 10100      C                             Last STRING, Address1 STRING, +                                    
 10200      C                             Address2 STRING, City STRING, +                                     
 10300      C                             State STRING, Zip STRING, +                                           
 10400      C                             Phone STRING, Fax STRING, +                                          
 10500      C                             Email STRING, Date1 DATETIME, +                                     
 10600      C                             Dollars NUMERIC)')                                                          
 10700                                                                                                                 
 10800       *-----------------------------------------------------------------------------                            
 10900       *                  ** Loop and insert multiple records to NameAddress                                    
 11000       *-----------------------------------------------------------------------------                            
 11100      C                   Z-add     0             ct                5 0                                  
 11200                                                                                                                
 11300      C                   Dow       ct < 10                                                               
 11400                                                                                                                
 11500       *-----------------------------------------------------------------------------                            
 11600       *                  ** Run Insert SQL Query to Insert Record                                              
 11700       *-----------------------------------------------------------------------------                            
 11800      C                   Eval      Rtn = SQL_RunSQLExec(SQL_Socket:           
 11900      C                             'insert into NameAddress' +                          
 12000      C                             '(First,' +                                                  
 12100      C                             'Last,' +                                                    
 12200      C                             'Address1,' +                                              
 12300      C                             'Address2,' +                                              
 12400      C                             'City,' +                                                     
 12500      C                             'State,' +                                                   
 12600      C                             'Zip,' +                                                       
 12700      C                             'Phone,' +                                                           
 12800      C                             'Fax,' +                                                               
 12900      C                             'Email,' +                                                             
 13000      C                             'Date1,' +                                                            
 13100      C                             'Dollars) ' +                                                          
 13200      C                             'VALUES(' +                                                          
 13300      C                             quot + 'James' + quot + ',' +                                    
 13400      C                             quot + 'Johnson' + quot + ','  +                                 
 13500      C                             quot + '111 Main Street' + quot + ',' +                        
 13600      C                             quot + 'Apt 5' + quot + ',' +                                      
 13700      C                             quot + 'Mpls' + quot + ',' +                                        
 13800      C                             quot + 'MN' + quot + ',' +                                          
 13900      C                             quot + '55555' + quot + ',' +                                      
 14000      C                             quot + '111-111-1111' + quot + ',' +                           
 14100      C                             quot + '222-222-2222' + quot + ',' +                           
 14200      C                             quot + 'james@johnson.com' + quot + ',' +                   
 14300      C                             quot + '12/25/2002' + quot + ',' +                               
 14400      C                                    '123456.78' +                                                   
 14500      C                             ')')                                                                        
 14600                                                                                                                 
 14700      C                   Eval      ct = ct + 1                                                            
 14800                                                                                                                 
 14900      C                   Enddo                                                                              
 15000                                                                                                                 
 15100       *-----------------------------------------------------------------------------                            
 15200       *                  ** Code to add a column                                                               
 15300       *-----------------------------------------------------------------------------                            
 15400      C*                  Eval      Rtn = SQL_RunSQLExec(SQL_Socket:                       
 15500      C*                            'ALTER TABLE NameAddress +                                   
 15600      C*                            ADD COLUMN CellPhone STRING')                               
 15700                                                                                                                
 15800       *-----------------------------------------------------------------------------                            
 15900       *                  ** Code to delete a column                                                           
 16000       *-----------------------------------------------------------------------------                            
 16100      C*                  Eval      Rtn = SQL_RunSQLExec(SQL_Socket:                       
 16200      C*                            'ALTER TABLE NameAddress +                                   
 16300      C*                            DROP COLUMN Zip')                                                 
 16400                                                                                                                 
 16500       *-----------------------------------------------------------------------------                            
 16600       *                  ** Code to delete a table                                                              
 16700       *-----------------------------------------------------------------------------                            
 16800      C*                  Eval      Rtn = SQL_RunSQLExec(SQL_Socket:                       
 16900      C*                            'DROP TABLE NameAddress')                                     
 17000                                                                                                                
 17100       *-----------------------------------------------------------------------------                            
 17200       *                  ** Close ADO Database Connection. We're done.                                          
 17300       *-----------------------------------------------------------------------------                            
 17400      C                   callp     SQL_DBCloseConn(SQL_Socket)                                 
 17500                                                                                                                 
 17600       *-----------------------------------------------------------------------------                            
 17700      C*                  ** Disconnect from RPG2SQL server                                   
 17800       *----------------------------------------------------------------------------- 
 17900      C                   callp     SQL_Disconnect(SQL_Socket)                                        
 18000                                                                                                                
 18100      C                   SETON                                        LR

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

Last Modified On: December 10, 2016