4D Doc Center : Receiving an SQL query result into arrays

Home

4D v14

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 generic SQL commands, 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) C_TEXT($tQueryTxt) REDUCE SELECTION([MOVIES];0) $MovieYear:=1960 SQL LOGIN(SQL_INTERNAL;"";"") $tQueryTxt:="" $tQueryTxt:=$tQueryTxt+"SELECT Year_of_Movie, Title, Director, Media, Sold_Tickets" $tQueryTxt:=$tQueryTxt+" FROM MOVIES" $tQueryTxt:=$tQueryTxt+" WHERE Year_of_Movie >= :$MovieYear" SQL EXECUTE($tQueryTxt;aMovieYear;aTitles;aDirectors;aMedias;aSoldTickets) SQL LOAD RECORD(SQL all records) SQL LOGOUT ` Initialize the rest of the list box columns in order to display the information ARRAY LONGINT(aNrActors;Size of array(aMovieYear))

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.