4D v13.4

Receiving an SQL query result in a variable

Home

 
4D v13.4
4D SQL Reference
Tutorial
Receiving an SQL query result in a variable

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.

 
PROPERTIES 

Product: 4D
Theme: Tutorial