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:
- 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
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:
![](http://doc.4d.com/picture/147961/pict147961.en.png)
- Using a list box having columns with the same names:
![](http://doc.4d.com/picture/147962/pict147962.en.png)
- Using generic SQL commands, the above query becomes:
- 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
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.