OC Query exec

version 1.5

OC Query exec (connection_ID; sqlCommand; limit{; array}{; array2; ...; arrayN}) Integer

Parameter	Type		Description
connection_ID	Longint		Login_ID or Cursor_ID
sqlCommand	Text		SQL command to execute
limit	Integer		Maximum number of lines to return
array	String		Name of a 4D variable or array (N [[ 22)
Function result	Integer		Number of columns returned or
			-1 if an error occurs

The OC Execute SQL function allows you to send an SQL query and store results in 4th Dimension arrays.

connection_ID must be a valid login or cursor ID. If you use a valid login ID, 4D ODBC will automatically open and close a cursor for use with this function. If you pass a valid cursor ID, this function will use the specified cursor.

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 documentation of your ODBC driver for a complete description of SQL as supported by ODBC.

limit specifies the maximum number of result lines to return to 4th Dimension. If limit equals –1, OC Query exec loads all the 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 "OC Low level."

array1;…;array22 are names of 4th Dimension arrays that receive the results of the query. The array named by arrayN receives the results that come from column N, if it exists.

OC Query exec converts results so that they correspond to the data types of the destination array. It is not necessary for the number of arrayN parameters to correspond to the number of result columns returned. Extra parameters or columns are ignored.

OC Query exec returns the number of columns returned from the data source or -1 if an error occurs.

OC Query exec is the simplest way to execute an SQL query. This function is equivalent to the following sequence:

1. Create a cursor with OC Create cursor.

2. Send the SQL query with OC Set SQL in cursor.

3. Execute the query with OC Execute cursor.

4. Create binds for the purpose of bringing back results with OC BIND.

5. Load the results with OC Load row.

6. Drop the cursor with OC DROP CURSOR.

(1) The following method logs into the ODBC data source, inserts a row in the DEPT table, and logs out:

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

(2) The following method loads data from the EMP table into the arempno, arename, and arsal 4D arrays:

   $col:=OC Query exec(Login_ID;"SELECT empno, ename, sal FROM EMP";-1;"arEmpno";"arEName";"arSal")

OC Create Cursor, OC Execute SQL.