Using 4D for Oracle to Manage Data

version 1.5

In a typical work session, the user connects to the server from a 4th Dimension database. The user may then request data, which the server locates and returns. For instance, the user might request data on all employees who make more than $50,000 per year.

At this point, the user can manipulate, add to, or modify the data using 4th Dimension. For instance, the user might modify an employee's address or add a new employee. When data is added or modified, it must be updated in the Oracle database.

4th Dimension and Oracle store data using different data structures. 4th Dimension organizes data into tables and fields. Oracle organizes data into tables and columns.

For instance, the following 4th Dimension structure table:

is equivalent to the following definition of an Oracle table:

Column Name	Type
First Name	Char(15)
Last Name	Char(15)
EmployeeNum	Char(8)
Address1	Char(30)
Address2	Char(30)
City	Char(20)
State	Char(2)
Zip	Char(15)
Salary	Number

Each 4th Dimension field has a specific data type. Each Oracle column has a different data type. When exchanging data between 4th Dimension and Oracle, 4D for Oracle performs any necessary conversions between differing data types. For more information on data type conversions, refer to Appendix A, Type Conversions.

In addition to having a data type, columns can be either NULL or not NULL. If a column value cannot be NULL, a value must be entered for the column before the row can be updated.

If a column value can be NULL, a value does not have to be entered for that column. For instance, in the following Oracle table, the Address2 column is not mandatory because some employees may not have a second line in their address.

Column Name	NULL?	Type
First Name	Not NULL	Char(15)
Last Name	Not NULL	Char(15)
EmployeeNum	Not NULL	Char(8)
Address1	Not NULL	Char(30)
Address2	NULL	Char(30)
City	Not NULL	Char(20)
State	Not NULL	Char(2)
Zip	Not NULL	Char(15)
Salary	Not NULL	Number

If the Address2 column in a row contains a NULL value, you may want to specify how the NULL value should be displayed in 4th Dimension. For instance, you might want "N/A" to be displayed in the 4th Dimension field to indicate that there is no second line in the employee's address. By the same token, if "N/A" is entered into the 4th Dimension field, you want the Oracle row to be updated with the NULL value.

Using 4D for Oracle's OD SET NULL VALUE command, you can specify a NULL value equivalent for each data type. In the example discussed here, the NULL value equivalent for the alphanumeric field Address2 is "N/A". If you do not specify a NULL value equivalent, 4th Dimension uses the default NULL value equivalent for the data type.

4th Dimension stores data in records. Each record belongs to a table and can have entries for each field in the table. For example, the following record contains entries for the fields in the [Employees] table:

Oracle stores data in rows. Each row belongs to a table and can have entries for each column in the table. For example, the following row contains entries for the columns in the Employees table:

First Name	Last Name	EmployeeNum	Salary
Deborah	Blaney	88	55000

When you use 4D for Oracle to retrieve or update data, you must identify

• The rows you want to retrieve or update

• The columns you want to retrieve or update in each row

• The table to which the rows belong

When you use 4D for Oracle commands to perform operations, you must supply this information. How this information is supplied depends on whether you are using context commands or low level commands.

You can use contexts to bind 4th Dimension fields, variables, and arrays, with Oracle columns. When you use context commands, you can retrieve data and perform operations on that data without using SQL statements. The 4D for Oracle context commands allow you to write statements in terms of your 4th Dimension tables and fields. When 4D for Oracle interprets the statements, it generates the appropriate SQL statements.

You can use low level commands to execute SQL statements. In this case, you use SQL statements to load data and perform operations on that data. In your SQL statements, you specify the rows and columns you want to display or update.

In Oracle, the primary key consists of the column or combination of columns that can be used to uniquely identify a row in a table.

For instance, suppose that you have a table in which customers are listed.

First Name	Last Name	Address
Beth	Adams	222 Cherry Blossom Lane
David	Carnswell	448 Simpson Street
Barney	Smith	11 North 1st Street
Ernie	Smith	9931 Park Place

You cannot make the Last Name column the primary key since it is possible for several customers to have the same last name. In the example table shown above, there are two customers with the last name "Smith".

You must therefore define a primary key using several columns. Since it is improbable that two customers will have the same name and address, you could define a primary key across the Last Name and Address columns. In the previous table, there are two customers with the last name "Smith", but there is only one "Smith" that lives at 9931 Park Place.

On the 4th Dimension side, the database structure should be created with the following rules in mind.

• If the primary key consists of only one column, the field that corresponds to the primary key should be indexed and unique.

• If the primary key consists of two or more columns, you must concatenate the corresponding field values in an alphanumeric field created for that purpose. The field containing the concatenation should be indexed and unique.

When you define a context linking 4th Dimension fields, variables, and arrays with Oracle columns, you need to know the primary key. You must then specify the same primary key in the context definition. Using the previous example, your context definition must include the two components of the primary key, Last Name and Address.