Receiving an SQL query result in a variable

version 11

To start with a very simple query: we would like to know how many movies are in the Video Library. In the 4D language, the code would be:

• The first way to interact in a similar manner with the SQL engine is by placing the query between the Begin SQL and End SQL tags. Thus, the simple query above becomes:

As you can see, you can receive the result of the query in a variable (in our case $AllMovies) that is enclosed between "<<" and ">>".

Another way to reference any type of valid 4D expression (variable, field, array, "expression…") is to place a colon ":" in front of it:

Special attention should be paid to inter-process variables, where the notation is a little bit different: you must place an inter-process variable between "[" and "]":

• The second way to interact with the SQL engine is using integrated generic SQL (ODBC compatible) commands. Thus the simple query above becomes:

For more information concerning generic SQL commands, please refer to "SQL" section of the 4D Language Reference manual.

• The third way to interact with the new SQL engine is using the 4D QUERY BY SQL command. In this situation, the simple query above becomes:

In fact, the QUERY BY SQL command can be used to execute a simple SELECT query that can be written as follows:

   SELECT *
      FROM myTable
      WHERE <SQL_Formula>
myTable is the name of the table passed in the first parameter and SQL_Formula is the query string passed as the second parameter:
	
QUERY BY SQL(myTable;SQL_Formula)
In our case there is no WHERE clause, so we forced one: "ID <> 0". The equivalent in SQL for the whole query would be:
   SELECT * 
      FROM MOVIES
       WHERE ID <> 0

• The fourth way to interact with the new SQL Engine is using the dynamic SQL EXECUTE IMMEDIATE command. The query above becomes:




To test all the above examples, launch the "4D SQL Code Samples" database and go to the main dialog box. On the left side of the dialog, you can choose the query mode: using standard 4D code, SQL code, ODBC commands, the QUERY BY SQL command or dynamic SQL: 



Then press the SQL query results in variables button.