Accessing the 4D SQL Engine

version 11

The 4D built-in SQL engine can be called in three different ways:

• Using the QUERY BY SQL command. Simply pass the WHERE clause of an SQL SELECT statement as a query parameter. Example:

   QUERY BY SQL([OFFICES];"SALES > 100")

• Using the integrated ODBC commands of 4D, found in the "External Data Source" theme (ODBC SET PARAMETER, ODBC EXECUTE, etc.). These commands have been modified to work with the 4D SQL engine of the current database.

• Using the standard Method editor of 4D. SQL statements can be written directly in the standard 4D Method editor. You simply need to insert the SQL query between the tags: Begin SQL and End SQL. The code placed between these tags will not be parsed by the 4D interpreter and will be exe-cuted by the SQL engine.

It is possible to reference any type of valid 4D expression (variable, field, array, expression...) within WHERE and INTO clauses of SQL expressions. To indicate a 4D reference, you can use either of the following notations:

• Place the reference between double less-than and greater-than sym-bols as shown here "<<" and ">>"

• Place a colon ":" in front of the reference.

Examples:

   C_STRING(80;vName)
   vName:=Request("Name:")
   ODBC EXECUTE("SELECT age FROM PEOPLE WHERE name=<<vName>>")

or:

   C_STRING(80;vName)
   vName:=Request("Name:")
   Begin SQL
      SELECT age FROM PEOPLE WHERE name= :vName
   End SQL 

Note: The use of brackets [] is required when you work with interprocess variables (for example, <<[<>myvar]>> or :[<>myvar]).

The data retrieval in a SELECT statement will be managed either inside Begin SQL/End SQL tags using the INTO clause of the SELECT command or by the "External Data Source" (ODBC) language commands.

• In the case of Begin SQL/End SQL tags, you can use the INTO clause in the SQL query and refer to any valid 4D expression (field, variable, array) to get the value:

   Begin SQL
      SELECT ename FROM emp INTO <<[Employees]Name>>
   End SQL

• With the ODBC EXECUTE command, you can also use the additional parameters:

   ODBC EXECUTE("SELECT ename FROM emp";[Employees]Name)

The main difference between these two ways of getting data from SQL (Begin SQL/End SQL tags and ODBC commands) is that in the first case all the information is sent back to 4D in one step, while in the second case the records must be loaded explicitly using ODBC LOAD RECORD.

For example, supposing that in the PEOPLE table there are 100 records:

• Using ODBC commands:

   ARRAY INTEGER(aBirthYear;0) 
   C_STRING(40;vName) 
   vName:="Smith" 
   $SQLStm:="SELECT Birth_Year FROM PEOPLE WHERE ename= <<vName>>" 
   ODBC EXECUTE($SQLStm;aBirthYear) 
   While (Not (ODBC End Selection)) 
      ODBC LOAD RECORD(10) 
   End while 

Here we have to loop 10 times to retrieve all 100 records. If we want to load all the records in one step we should use:

   ODBC LOAD RECORD(ODBC All Records) 

• Using Begin SQL/End SQL tags:

   ARRAY INTEGER(aBirthYear;0) 
   C_STRING(40;vName) 
   vName:="Smith" 
   Begin SQL 
      SELECT Birth_Year FROM PEOPLE WHERE ename= <<vName>> INTO <<aBirthYear>> 
   End SQL 

In this situation, after the execution of the SELECT statement, the aBirthYear array size becomes 100 and its elements are filled with all the birth years from all 100 records.

If, instead of an array, we want to store the retrieved data in a column (i.e., a 4D field), then 4D will automatically create as many records as necessary to save all the data. In our preceding example, supposing that in the PEOPLE table there are 100 records:

• Using ODBC commands:

   C_STRING(40;vName) 
   vName:="Smith" 
   $SQLStm:="SELECT Birth_Year FROM PEOPLE WHERE ename= <<vName>>" 
   ODBC EXECUTE($SQLStm;[MYTABLE]Birth_Year) 
   While (Not (ODBC End Selection)) 
      ODBC LOAD RECORD(10) 
   End while

Here we have to loop 10 times to retrieve all the 100 records. Every step will create 10 records in the [MYTABLE] table and store each retrieved Birth_Year value from the PEOPLE table in the Birth_Year field.

• Using Begin SQL/End SQL tags:

   C_STRING(40;vName) 
   vName:="Smith" 
   Begin SQL 
      SELECT Birth_Year FROM PEOPLE WHERE ename= <<vName>> INTO <<[MYTABLE]Birth_Year>> 
   End SQL

In this case, during the execution of the SELECT statement, there will be 100 records created in the [MYTABLE] table and each Birth_Year field will contain the corresponding data from the PEOPLE table, Birth_Year column.

4D includes a specific automatic functioning (LISTBOX keyword) that can be used for placing data from SELECT queries into a listbox. For more information, please refer to the Design Reference manual.

For optimization purposes, it is preferable to use 4D expressions rather than SQL functions in queries. 4D expressions will be calculated once before the execution of the query whereas SQL functions are evaluated for each record found.

For example, with the following statement:

   ODBC EXECUTE("SELECT FullName FROM PEOPLE WHERE FullName=<<vLastName+vFirstName>>")

... the vLastName+vFirstName expression is calculated once, before query execution. With the following statement:

   ODBC EXECUTE("SELECT FullName FROM PEOPLE WHERE FullName=CONCAT(<<vLastName>>,<<vFirstName>>)")

... the CONCAT(<<vLastName>>,<<vFirstName>>) function is called for each record of the table; in other words, the expression is evaluated for each record.