4D Doc Center : Receiving an SQL query result in a variable

Home

4D v14

Receiving an SQL query result in a variable

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:

 C_LONGINT($AllMovies)

 $AllMovies:=0

 ALL RECORDS([MOVIES])

 $AllMovies:=Records in selection([MOVIES])

 ALERT("The Video Library contains "+String($AllMovies)+" movies")

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:

C_LONGINT($AllMovies) $AllMovies:=0 Begin SQL SELECT COUNT(*) FROM MOVIES INTO <<$AllMovies>> End SQL ALERT("The Video Library contains "+String($AllMovies)+" movies")

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:

C_LONGINT($AllMovies) $AllMovies:=0 Begin SQL SELECT COUNT(*) FROM MOVIES INTO :$AllMovies End SQL ALERT("The Video Library contains "+String($AllMovies)+" movies")

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 "]":

C_LONGINT($AllMovies) $AllMovies:=0 Begin SQL SELECT COUNT(*) FROM MOVIES INTO <<$AllMovies>> End SQL ALERT("The Video Library contains "+String($AllMovies)+" movies")

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

C_LONGINT($AllMovies) $AllMovies:=0 ` Initialize a connection with the internal SQL engine SQL LOGIN(SQL_INTERNAL;"";"") ` Execute the query and return the result in the $AllMovies variable SQL EXECUTE("SELECT COUNT(*) FROM MOVIES";$AllMovies) ` Retrieve all the records found SQL LOAD RECORD(SQL all records) ` Close the connection SQL LOGOUT ALERT("The Video Library contains "+String($AllMovies)+" movies")

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:

C_LONGINT($AllMovies) $AllMovies:=0 QUERY BY SQL([MOVIES];"ID <> 0") $AllMovies:=Records in selection([MOVIES]) ALERT("The Video Library contains "+String($AllMovies)+" movies")

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

C_LONGINT(AllMovies) AllMovies:=0 C_TEXT(tQueryTxt) tQueryTxt:="SELECT COUNT(*) FROM MOVIES INTO :AllMovies" Begin SQL EXECUTE IMMEDIATE :tQueryTxt; End SQL ALERT("The Video Library contains "+String(AllMovies)+" movies")

Warning: You can see that in this last example, we use process variables. This is necessary when you want to use the database in compiled mode. In this context, in fact, it is not possible to use local variables with the EXECUTE IMMEDIATE command.

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:

Then press the SQL query results in variables button.