Accessing the 4D SQL Engine

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:

• Using the integrated SQL commands of 4D, found in the “SQL” theme (SQL SET PARAMETER, SQL EXECUTE, etc.). These commands can work with an ODBC data source or 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 (or by another engine, if set by the SQL LOGIN command).

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 symbols as shown here “<<” and “>>”
• Place a colon “:” in front of the reference.

Examples:

or:

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

Im kompilierten Modus können Sie unter bestimmten Bedingungen in SQL Statements lokale Variablenreferenzen (mit vorangestelltem Zeichen $) verwenden:

• Sie können lokale Variablen innerhalb einer Sequenz Begin SQL / End SQL einsetzen, außer für den Befehl EXECUTE IMMEDIATE;
• Sie können lokale Variablen mit dem Befehl SQL EXECUTE einsetzen, wenn diese Variablen direkt im Parameter der SQL Anfrage und nicht über Referenzen verwendet werden.
  So funktioniert z.B. folgender Code im kompilierten Modus:
  
   SQL EXECUTE("select * from t1 into :$myvar") // funktioniert im kompilierten Modus
  
  während folgender Code einen Fehler im kompilierten Modus generiert:
  
   C_TEXT(tRequest)
 tRequest:="select * from t1 into :$myvar"
 SQL EXECUTE(tRequest) // Fehler im kompilierten Modus

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 "SQL" 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 SQL EXECUTE command, you can also use the additional parameters:
  
   SQL 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 SQL 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 SQL LOAD RECORD.

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

• Using 4D generic SQL commands:
  
   ARRAY INTEGER(aBirthYear;0)
 C_TEXT(vName)
 vName:="Smith"
 $SQLStm:="SELECT Birth_Year FROM PERSONS WHERE ename= <<vName>>"
 SQL EXECUTE($SQLStm;aBirthYear)
 While(Not(SQL End selection))
    SQL 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:

• Using Begin SQL/End SQL tags:
  
   ARRAY INTEGER(aBirthYear;0)
 C_TEXT(vName)
 vName:="Smith"
 Begin SQL
    SELECT Birth_Year FROM PERSONS 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 4D generic SQL commands:
  
   C_TEXT(vName)
 vName:="Smith"
 $SQLStm:="SELECT Birth_Year FROM PERSONS WHERE ename= <<vName>>"
 SQL EXECUTE($SQLStm;[MYTABLE]Birth_Year)
 While(Not(SQL End selection))
    SQL 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_TEXT(vName)
 vName:="Smith"
 Begin SQL
    SELECT Birth_Year FROM PERSONS 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:

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

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