Specifying 4th Dimension Objects

version 1.5

This section starts with the simplest methods and proceeds to the most versatile and efficient methods of specifying a 4th Dimension object in a low level command.

The object you are specifying could be the source of the data you want to send to the server or the object that should receive data from the server.

For example, consider the following SQL statement:

   INSERT INTO emp (empno,ename) VALUES (123,'Adams')

This is not a query, so you do not need to recover a result. The values to be inserted (123,'Adams') are included in the command text, so there is no need to designate a source in 4th Dimension for these values. The 4D for Oracle command sequence is as follows:

   ID_login:=OD Login dialog               
   ID_cursor:=OD Create cursor (ID_login)
   $query:="INSERT INTO emp (empno,ename) VALUES (123,'Adams')"
   $rc:=OD Set SQL in cursor (ID_cursor;$query)
   OD EXECUTE CURSOR (ID_cursor)
   OD DROP CURSOR (ID_cursor)
   OD LOGOUT (ID_login)

Although simple, this code is inflexible because you must redefine the SQL statement each time the value you want to add changes. This can be cumbersome when parameters come from a dialog box or when there are many parameters.

Instead of specifying constant values, you can use 4th Dimension variables in your SQL statements. For example, the following method uses variables to define the data that should be inserted on the server:

   vEmpno:=123
   vEname:="Adams"
   $query:="INSERT INTO emp (empno,ename) VALUES (<<vEmpno>>,<<vEname>>)"
   $rc:=OD Set SQL in cursor (ID_cursor;$query)
   OD EXECUTE CURSOR (ID_cursor)

The vEmpno and vEname variables replace the 123 and 'Adams' constants. These variables are called substitution variables. The substitution variables in SQL statements must be surrounded by the "<<" (option-\) and ">>" (shift-option-\) characters.

Two advantages of substitution variables are the following:

• A SQL command may be constructed before you know its parameters.

• A SQL command using substitution variables does not need to be parsed again by OD Set SQL in cursor when you change the values.

To insert the three employees (123,'Adams'), (124,'Adamo') and (125,'Adam'), you could use the following method:

   vEmpno:=123
   vEname:="Adams"
   $query:="INSERT INTO emp (empno,ename) VALUES (<<vEmpno>>,<<vEname>>)"
   $rc:=OD Set SQL in cursor (ID_cursor;$query)
   OD EXECUTE CURSOR (ID_cursor)
   vEmpno:=124
   vEname:="Adamo"
   OD EXECUTE CURSOR (ID_cursor)
   vEmpno:=125
   vEname:="Adam"
   OD EXECUTE CURSOR (ID_cursor)

You can replace the lines that assign the vEmpno and vEname variables with a dialog box asking the user to input values.

If you are going to compile your database, be sure to declare the substitution variables using a compiler directive such as C_INTEGER, or by explicitly assigning them values.

Local variables prefixed by the $ sign cannot be used as substitution variables.

You can use fields in place of constants in SQL statements. Substitution fields must be surrounded by the "<<" and ">>" characters and specified in the format "<<[TableName]FieldName>>".

For example, suppose that an [Employees] table contains the Number and Name fields. You can insert the values of this table's current selection into the emp table using the following method:

   $query:="INSERT INTO emp (empno,ename) VALUES (<<[Employees]Number>>,<<[Employees]Name>>)"
   $rc:=OD Set SQL in cursor (ID_cursor;$query)
   APPLY TO SELECTION ([Employees];OD EXECUTE CURSOR (ID_cursor))

4D for Oracle uses the values of the current record's fields at the time of execution of OD EXECUTE CURSOR. If there is no current record, OD EXECUTE CURSOR does nothing.

You can use arrays in place of constants in SQL statements. If you use arrays, 4D for Oracle executes the SQL statement as many times as there are elements in the array. As with substitution fields and variables, the substitution arrays in SQL statements must be surrounded by the "<<" and ">>" characters.

For example, the following method inserts three rows on the server based on the values in the tEmpno and tEname arrays:

   ARRAY STRING (30;tEname;3)
   ARRAY INTEGER (tEmpno;3)
   $query:="INSERT INTO emp (empno,ename) VALUES (<<tEmpno>>,<<tEname>>)"
   $rc:=OD Set SQL in cursor (ID_cursor;$query)
   tEname{1}:="ADAMS"
   tEname{2}:="ADAMO"
   tEname{3}:="ADAM"
   tEmpno{1}:=123
   tEmpno{2}:=124
   tEmpno{3}:=125
   OD EXECUTE CURSOR (ID_cursor)

If a variable or a field is used with an array, the OD EXECUTE CURSOR command executes only once using the first row of the array.

If several arrays are used as substitution variables, they must have the same number of lines.