SQL EXECUTE

version 2004

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

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

A valid connection must be specified 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.

Note: For more information about referencing 4D expressions in SQL queries, please refer to the SQL Commands section.

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

   SQLStmt:="SELECT ename FROM emp"
   SQL EXECUTE(SQLStmt;[Employee]Name)
   SQL LOAD RECORD(SQL 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:

   SQL LOGIN("mysql";"root";"")
   SQLStmt:="SELECT alpha_field FROM app_testTable"
   START TRANSACTION
   SQL EXECUTE(SQLStmt;[Table 2]Field1)
   While(Not(SQL End Selection))
      SQL 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 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"
   SQL EXECUTE(SQLStmt;aName)
   While(Not(SQL End Selection))
      SQL 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 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"
   SQL EXECUTE(SQLStmt;vName;vJob)
   SQL LOAD RECORD

5. In this example, we want to get the Blob_Field column of the Test table in the data source. The result will be stored in a BLOB variable whose value is updated each time a record is loaded.

   C_BLOB(MyBlob)
   SQL LOGIN
   SQL EXECUTE("SELECT Champ_Blob FROM Test";MonBlob)
   While(Not(SQL End selection))
         `We look through the results
      SQL LOAD RECORD
         `The value of MyBlob is updated on each call
   End while

SQL LOAD RECORD.

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