SQL EXECUTE

4D - Documentation   Français   English   German   Spanish   4D v11 SQL, Command Theme List   4D v11 SQL, Command Alphabetical List   4D v11 SQL, Constant Theme List   Back   Previous   Next

version 2004


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

ParameterTypeDescription
sqlStatementTextSQL command to execute
boundObjVariable | FieldReceives result (if necessary)

Description

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.

Examples

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

See Also

SQL LOAD RECORD.

System Variables or Sets

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


4D - Documentation   Français   English   German   Spanish   4D v11 SQL, Command Theme List   4D v11 SQL, Command Alphabetical List   4D v11 SQL, Constant Theme List   Back   Previous   Next