4D v13.4

Receiving an SQL query result into arrays

Home

 
4D v13.4
4D SQL Reference
Tutorial
Receiving an SQL query result into arrays

Receiving an SQL query result into arrays  


 

 

Now we want to pass a variable to the SQL query containing the year (and not the year itself, hard-coded) and get all the movies released in 1960 or more recently. In addition, for each movie found, we also want information such as the year, title, director, media used and tickets sold. The solution is to receive this information in arrays or in a list box.

  • The initial query in 4D code would be:

     ARRAY LONGINT(aSoldTickets;0)
     ARRAY INTEGER(aMovieYear;0)
     ARRAY TEXT(aTitles;0)
     ARRAY TEXT(aDirectors;0)
     ARRAY TEXT(aMedias;0)
     C_LONGINT($MovieYear)
     
     REDUCE SELECTION([MOVIES];0)
     $MovieYear:=1960
     QUERY([MOVIES];[MOVIES]Year_of_Movie>=$MovieYear)
     SELECTION TO ARRAY([MOVIES]Year_of_Movie;aMovieYear;[MOVIES]Title;aTitles;[MOVIES]Director;aDirectors;
     [MOVIES]Media;aMedias;[MOVIES]Sold_Tickets;aSoldTickets)
      ` Initialize the rest of the list box columns in order to display the information
     ARRAY LONGINT(aNrActors;Size of array(aMovieYear))

  • Using SQL code, the above query becomes:

     ARRAY LONGINT(aSoldTickets;0)
     ARRAY INTEGER(aMovieYear;0)
     ARRAY TEXT(aTitles;0)
     ARRAY TEXT(aDirectors;0)
     ARRAY TEXT(aMedias;0)
     C_LONGINT($MovieYear)
     
     $MovieYear:=1960
     Begin SQL
        SELECT Year_of_Movie, Title, Director, Media, Sold_Tickets
        FROM MOVIES
        WHERE Year_of_Movie >= :$MovieYear
        INTO :aMovieYear, :aTitles, :aDirectors, :aMedias, :aSoldTickets;
     End SQL
      ` Initialize the rest of the list box columns in order to display the information
     ARRAY LONGINT(aNrActors;Size of array(aMovieYear))


As you can see:

  • We can pass a variable ($MovieYear) to the SQL query using the same notation as for receiving parameters.
  • The SQL query result is saved in the aMovieYear, aTitles, aDirectories, aMedias and aSoldTickets arrays. They are displayed in the main window in two ways:
    • Using a group of arrays:

    • Using a list box having columns with the same names:

  • Using the QUERY BY SQL command, the above query becomes:

     ARRAY LONGINT(aSoldTickets;0)
     ARRAY INTEGER(aMovieYear;0)
     ARRAY TEXT(aTitles;0)
     ARRAY TEXT(aDirectors;0)
     ARRAY TEXT(aMedias;0)
     C_LONGINT($MovieYear)
     
     REDUCE SELECTION([MOVIES];0)
     $MovieYear:=1960
     QUERY BY SQL([MOVIES];"Year_of_Movie >= :$MovieYear")
     SELECTION TO ARRAY([MOVIES]Year_of_Movie;aMovieYear;[MOVIES]Title;aTitles;[MOVIES]Director;aDirectors;
     [MOVIES]Media;aMedias;[MOVIES]Sold_Tickets;aSoldTickets)
      ` Initialize the rest of the list box columns in order to display the information
     ARRAY LONGINT(aNrActors;Size of array(aMovieYear))
  • Using the SQL EXECUTE IMMEDIATE command, the query above becomes:

     ARRAY LONGINT(aSoldTickets;0)
     ARRAY INTEGER(aMovieYear;0)
     ARRAY TEXT(aTitles;0)
     ARRAY TEXT(aDirectors;0)
     ARRAY TEXT(aMedias;0)
     C_LONGINT($MovieYear)
     C_TEXT($tQueryTxt)
     
     REDUCE SELECTION([MOVIES];0)
     $MovieYear:=1960
     $tQueryTxt:=""
     $tQueryTxt:=$tQueryTxt+"SELECT Year_of_Movie, Title, Director, Media, Sold_Tickets"
     $tQueryTxt:=$tQueryTxt+" FROM MOVIES"
     $tQueryTxt:=$tQueryTxt+" WHERE Year_of_Movie >= :$MovieYear"
     $tQueryTxt:=$tQueryTxt+" INTO :aMovieYear, :aTitles, :aDirectors, :aMedias, :aSoldTickets;"
     Begin SQL
        EXECUTE IMMEDIATE :$tQueryTxt;
     End SQL
      ` Initialize the rest of the list box columns in order to display the information
     ARRAY LONGINT(aNrActors;Size of array(aMovieYear))

To test all the above examples, launch the "4D SQL Code Samples" database and go to the main window. You can then choose the query mode and press the SQL query results in arrays button.

 
PROPERTIES 

Product: 4D
Theme: Tutorial