Overview of Low Level Commands

version 1.5

With low level commands, you execute SQL statements within cursors. A cursor is memory buffer used to reference and control SQL statements. Each cursor controls a single SQL query. You can create several cursors and use several statements concurrently in the same connection.

To control an operation using low level commands, you follow these basic steps:

This function returns a connection identifier, which is used when you create a cursor.

This function returns a cursor identifier, which is used in all subsequent commands dealing with the cursor.

Depending on the SQL statement, you must create binds that allow you to load Oracle data in 4th Dimension or send data from 4th Dimension to Oracle.

Unlike the two-way binds in context methods, the binds in low level methods work only in one direction. You use one bind to load results from Oracle. You use a different bind to send data to Oracle.

• If the SQL statement selects rows, use OD BIND TOWARDS 4D to create binds that allow 4th Dimension to load the results.

• If the SQL statement adds or updates rows on the server, use OD BIND TOWARDS SQL to create binds that allow 4th Dimension to send the data to Oracle.

The following figure shows the order of calls to execute SQL commands:

You create a cursor by calling the OD Create cursor function that returns an identifier for the cursor. The cursor identifier is used to identify the cursor in subsequent commands. When you have finished with the cursor, you can close it by using the OD DROP CURSOR command.

For instance, the following method connects to the server, creates two cursors, and then deletes the cursors.

   ID_login:=OD Login dialog
   ID_cursor1:=OD Create cursor (ID_login)
   ID_cursor2:=OD Create cursor (ID_login)
   OD DROP CURSOR (ID_cursor1)
   OD DROP CURSOR (ID_cursor2)

Creating a cursor takes time since resources are allocated at the level of the server. To optimize performance, you can create all of the cursors at one time and then delete them all when you quit your application.

Make sure that you keep the number of cursors below the maximum number that can be opened on the server. This number depends upon the server configuration, which is determined by the init.ora file on the Oracle server.

Note: The OD ROLLBACK and OD COMMIT commands validate or cancel all queries within the connection. They do not validate or cancel individual cursors.

When you disconnect from the server, all cursors in the connection are automatically closed.

You can select data by placing a SELECT statement in a cursor and then executing the cursor. Using the OD Set SQL in cursor function, you can specify any valid SELECT statement.

For instance, the following method defines a SELECT statement that selects all the data in the ename column.

   $query:="SELECT ename FROM EMP"
   $rc:=OD Set SQL in cursor (ID_cursor;$query)

Note: We use the $query variable to store the SQL statement to make the code more readable. You can pass the statement text as a parameter to the OD Set SQL in cursor command.

To load the selected data, you usually perform the following steps:

To specify the objects that should load the results, you bind each Oracle column to a 4th Dimension field, variable, or array using the OD BIND TOWARDS 4D command.

To execute the cursor, you use the OD EXECUTE CURSOR command.

You use the OD Load rows cursor function to load the rows. You can specify the number of rows you want to load, allowing you to display the results in a single record or in a series of records.

For example, the following method defines a SELECT statement that selects the data in the "ename" column. In preparing to load the data, the method binds the column to a 4th Dimension field. It then executes the cursor and loads a single result row.

   ID_Cursor:=OD Create cursor (vLogin)
   $query:="SELECT ename FROM EMP"
   $rc:=OD Set SQL in cursor (ID_cursor;$query)
   OD BIND TOWARDS 4D (ID_cursor;1;»Name)
   OD EXECUTE CURSOR (ID_cursor)
   $rc:=OD Load rows cursor (ID_cursor;1)

In this example, a pointer to a field designates the 4th Dimension object into which the data is loaded.

You have several options for specifying objects: you can specify a field, a variable or an array, or a pointer to a field, a variable, or an array.

If you bind a column with a simple variable and if several result lines are loaded, the contents of the variable will correspond to the last result line.

If you bind a column with an array, 4D for Oracle appends the result lines to the end of the array.

For more information on specifying a 4th Dimension object, refer to the section Specifying 4th Dimension Objects.

To send data to the server, you usually perform the following steps:

You place this SQL statement in an OD Set SQL in cursor statement, such as the following:

   $query:="INSERT INTO emp (empno,ename) VALUES (123,'Adams')"
   $rc:=OD Set SQL in cursor (ID_cursor;$query)

The query in this example inserts two values into the empno and ename columns in the emp table. In this case, the data to be inserted (123, 'Adams') is specified as constants. In the next section, you will learn how to construct more flexible statements by using 4th Dimension fields, variables, or arrays as the source of the data.

You can specify the 4th Dimension objects in the SQL statement defined with the OD Set SQL in cursor function. Or, you can define the source objects using the OD BIND TOWARDS SQL command.

Using the OD BIND TOWARDS SQL command, you can bind an Oracle column to a 4th Dimension object. For the 4th Dimension object, you can specify a pointer to a field, variable, or array.

For more information on specifying a 4th Dimension object, refer to the section Specifying 4th Dimension Objects.

To execute the cursor, you use the OD EXECUTE CURSOR command. When you execute the cursor, the corresponding rows on the server are updated.