ODBC EXECUTE

version 2004

ODBC EXECUTE (sqlStatement{; boundObj}{; boundObj2; ...; boundObjN})

The ODBC EXECUTE command is used to execute an SQL command and to bind the result to 4D objects (arrays, variables or fields).

A valid connection is required in the current process in order to execute this command.

The sqlStatement parameter contains the SQL command to execute. boundObj receives the results. Variables are bound in the column sequence order, which means that any remaining remote columns are discarded.

If 4D fields are passed as parameters in boundObj, the command will create records and save them automatically. 4D fields must come from the same table (a field from table 1 and a field from table 2 cannot be passed in the same call). If fields from more than one table are passed, an error is generated.

If you pass 4D arrays in the boundObj parameter(s), it is advisable to declare them before calling the command in order to check the type of data processed. Arrays are automatically resized when necessary.

With a 4D variable, one record is fetched at a time. The other results are ignored.

1. In this example, we will get the ename column of the emp table of the external data source. The result is stored in the [Employee]Name 4D field. 4D records will be created automatically:

   SQLStmt:="SELECT ename FROM emp"
   ODBC EXECUTE(SQLStmt;[Employee]Name)
   ODBC LOAD RECORD(ODBC All Records)

2. To check the creation of records, it is possible to include code within a transaction and to validate it only if the operation proves to be satisfactory:

   ODBC LOGIN("mysql";"root";"")
   SQLStmt:="SELECT alpha_field FROM app_testTable"
   START TRANSACTION
   ODBC EXECUTE(SQLStmt;[Table 2]Field1)
   While(Not(ODBC End Selection))
      ODBC LOAD RECORD
      ...   `Place the data validation code here
   End while
   VALIDATE TRANSACTION   `Validation of the transaction

3. In this example, we want to get the ename column of the emp table of the external data source. The result will be stored in an aName array. We fetch records 10 at a time.

   ARRAY STRING(30;aName;20)
   SQLStmt:="SELECT ename FROM emp"
   ODBC EXECUTE(SQLStmt;aName)
   While(Not(ODBC End Selection))
      ODBC LOAD RECORD(10)
   End while

4. In this example, we want to get the ename and job of the emp table for a specific ID (WHERE clause) of the external data source. The result will be stored in the vName and vJob 4D variables. Only the first record is fetched.

   SQLStmt:="SELECT ename, job FROM emp WHERE id = 3"
   ODBC EXECUTE(SQLStmt;vName;vJob)
   ODBC LOAD RECORD

ODBC LOAD RECORD.

If the command has been executed correctly, the system variable OK returns 1. Otherwise, it returns 0.