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:
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:
For more information concerning generic SQL commands, please refer to SQL section of the 4D Language Reference manual.
- 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:
![](../../picture/147924/pict147924.en.png)
Then press the SQL query results in variables button.