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 selected PC file *--------------------------------------------------------------------- C Eval Rtn = SQL_PCFileCopy(SQL_Socket: C 'c:\TestBlankDB.mdb': C 'c:\testoutput\TestFilledDB.mdb')
*---------------------------------------------------------------------
* ** 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=;')
*--------------------------------------------------------------------- * ** 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)')
*--------------------------------------------------------------------- * ** Code to add a column *--------------------------------------------------------------------- C Eval Rtn = SQL_RunSQLExec(SQL_Socket: C 'ALTER TABLE NameAddress + C ADD COLUMN CellPhone STRING')
*--------------------------------------------------------------------- * ** Code to delete a column *--------------------------------------------------------------------- C Eval Rtn = SQL_RunSQLExec(SQL_Socket: C 'ALTER TABLE NameAddress + C DROP COLUMN Zip')
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')
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.