OD Execute SQL

version 1.5

OD Execute SQL (login_ID; sqlCommand; limit{; pointer}{; pointer2; ...; pointerN}) Integer

Parameter	Type		Description
login_ID	Longint		Connection Identifier
sqlCommand	Text		SQL command to execute
limit	Longint		Maximum number of lines to return
pointer	Pointer		Pointer to a 4D field, variable or array (N [[ 22)
Function result	Integer		Number of columns of results or
			-1 if an error occurs

The OD Execute SQL function enables you to send a SQL query and store results in 4th Dimension fields, variables, or arrays, by specifying a login ID.

Because Oracle needs a cursor for each query, 4D for Oracle manages a set of internal cursors for this purpose.

You can specify a cursor ID instead of a login ID to OD Execute SQL. In this way, OD Execute SQL uses your own cursor and thus eliminates the overhead of managing internal cursors when you make multiple calls to OD Execute SQL.

login_ID must be a valid connection identifier.

sqlCommand is the text of the SQL query to execute. All SQL commands are accepted as long as their syntax is valid. Refer to the SQL*Language Reference manual for a complete description of SQL as supported by Oracle.

limit specifies the maximum number of result lines to return to 4th Dimension. If limit equals -1, OD Execute SQL loads all results. If a number other than -1 is specified, the remaining rows cannot be loaded. To set a limit and later load the remaining rows, use the cursor commands described in the chapter "OD Low Level."

pointer1;…;pointer22 are pointers to fields, variables, or arrays that receive the results of the query. The object referenced by pointerN receives the results that come from column N, if it exists.

When you work with 4th Dimension arrays, 4D for Oracle takes advantage of array processing (sending or receiving data by blocks of a number of rows), which significantly increases its performance.

To export a selection of 4th Dimension records to an Oracle database, you can write the following method:

   APPLY TO SELECTION (rc:=OD Execute SQL (login;"insert into emp (ename)
                                       values(<<[Employees]Name>>)"))

The advantage of this method is that it consists of only one line of code, however it does not use array processing. Therefore the execution time may be restrictive because the 4th Dimension records is sent to Oracle one by one.

To take advantage of array processing, you can write the following method:

   SELECTION TO ARRAY ([Employees]Name;tName)
   rc:=OD Execute SQL (login;"insert into emp (ename) values(<<tName>>)")
   CLEAR VARIABLE (tName)

This method uses an intermediary 4th Dimension array, and thus needs more memory than the first method. Remember to allocate enough memory to 4th Dimension or 4D Client; otherwise, export your selection of records in separate pieces.

4D for Oracle uses array processing performing any of the following routines:

• OD Execute SQL

• OD EXECUTE CURSOR

• OD Load rows cursor

• OD Load rows context

The following example uses the same cursor to send two queries with OD Execute SQL:

   ID_login:= OD Login dialog
   ID_Cursor:=OD Create cursor (ID_login)
   $rc:=OD Execute SQL (ID_Cursor;"delete from emp")
   $rc:=OD Execute SQL (ID_Cursor;"insert into emp (empno, ename) values (42,'johns')")
   OD DROP CURSOR (ID_Cursor)
   OD LOGOUT (ID_login)

If you specify a cursor_ID to OD Execute SQL along with an empty query, 4D for Oracle loads remaining rows without re-executing the query. This allows you to retrieve results row by row as in the following example.

This example issues a SELECT statement using OD Execute SQL and retrieves results 10 rows by 10 rows:

   ID_login:=OD Login dialog
   ID_Cursor:=OD Create cursor (ID_login)
            `Send the query without retrieving any rows
   $rc:=OD Execute SQL (ID_Cursor;"select ename from emp";0;->tEname)
   Repeat
         `Retrieve the next 10 rows
      $rc:=OD Execute SQL (ID_Cursor;"";10)
         `Until there are no more rows to retrieve
   Until (OD Cursor state (ID_Cursor)=2)
   OD DROP CURSOR (ID_Cursor)
   OD LOGOUT (ID_login)

When you pass a null string as the query to retrieve the remaining rows, the parameters of type Pointer provided are ignored. The 4th Dimension objects that receive the results are those specified when you call OD Execute SQL by sending the text of the query.

Low-level commands that are applied to cursors remain available for a cursor being used by OD Execute SQL. In the previous example, OD Cursor state is used to test to see if you are at the end of the results to be read.

If a query returns results, or if SQLCommand is of the SELECT… type, OD Execute SQL behaves in the following manner according to the nature of the objects to which pointerN points:

• If pointerN points to a field, OD Execute SQL creates as many records as there are loaded rows. If limit=1, OD Execute SQL creates a record in memory to receive the result row but does not save it. The SAVE RECORD command is not called, allowing you to decide if you want to keep the results in 4th Dimension. When results are loaded into 4th Dimension records, the current selection of records is set to the records created by 4D for Oracle.

• If pointerN points to a variable, the variable receives the value of column N of the last row of the returned results. Remember that a variable can hold only one row at a time.

• If pointerN points to an array, the array receives the values of the different columns.

OD Execute SQL converts results so they correspond to the types of objects referenced by pointerN. It is not necessary for the number of pointerN parameters to correspond to the number of results columns returned. Extra parameters or columns are ignored.

OD Execute SQL is the simplest way to execute a SQL query. This function is equivalent to the following sequence:

• Create a cursor with OD Create cursor.

• Send the SQL query with OD Set SQL in cursor.

• Create links for the purpose of bringing back results with OD BIND TOWARDS 4D.

• Execute the query with OD EXECUTE CURSOR.

• Load the results with OD Load rows cursor.

• Drop the cursor with OD DROP CURSOR.

The following method logs into the Oracle server, inserts a row in the DEPT table, and logs out:

   Login_ID:=OD Login dialog
   If (Login_ID>0)
      $col:=OD Execute SQL (Login_ID;"INSERT INTO DEPT VALUES (50,'PRODUCTS','PARIS')";-1)
      OD LOGOUT (Login_ID)
   End if

The following method loads data from the EMP table into the 4th Dimension table called [Employees]:

   $col:=OD Execute SQL (Login_ID;"SELECT empno, ename, sal FROM EMP"; -1;->[Employees]Nb;
                                    ->[Employees]Name;->[Employees]Salary)

OD Create cursor, OD Cursor state, OD Login, OD Login dialog.