|
|
|
|
|
|
|
|
|
version 1.5
OC Execute SQL (connection_ID; sqlCommand{; limit{; arrayList}}) 
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 |
| arrayList | Array String | | Array of array names |
| Function result | Integer |  | Number of arrays which have been filled or |
| -1 if an error occurs |
Description
The OC Execute SQL function allows you to send an SQL query and store results in 4th Dimension arrays.
OC Execute SQL is similar to the OC Query exec function. It allows you to send and execute an SQL command on the data source and retrieve the results of the request in 4D arrays. Unlike OC Query exec, which requires the destination arrays to be passed as parameters, OC Execute SQL expects a text or string array that contains the names of the destination arrays. Therefore, with this command, the limit of 22 arrays is eliminated.
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.
The limit and arrayList optional parameters should be passed only if the command returns data.
• limit specifies the maximum number of result lines to return to 4th Dimension. If limit equals –1, OC 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 "OC Low level."
• arrayList is the 4th Dimension string array that contains the names of arrays that are to receive the results of the query. The object named by arrayList{n} receives the results that come from column N, if it exists.
If an error occurs during execution, OC Execute SQL returns -1. Otherwise, it returns a value greater than or equal to zero that represents the number of arrays which have been filled.
Example
The following example retrieves three columns from the "Sales" table and populates the results into three 4D arrays using the OC Execute SQL function. The names of the destination arrays are passed to the function as elements in the arArrays array.
ARRAY STRING(30;arRep;0)
ARRAY STRING(30;arCust;0)
ARRAY STRING(30;arProduct;0)
ARRAY STRING(30;arArrays;3)
cur:=OC Create cursor (login)
arArrays{1}:="arRep"
arArrays{2}:="arCust"
arArrays{3}:="arProduct"
OC OPEN DEBUG WINDOW
$sql:="select REPID, CUSTID, PRODNAME from sales"
$res:=OC Execute SQL (cur;$sql;-1;arArrays)
|
|
|
|
|
|
|
|
|