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 + '[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
