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 + '[email protected]' + 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.