4D v13.4

Calling 4D methods inside the SQL code

Home

 
4D v13.4
4D SQL Reference
Tutorial
Calling 4D methods inside the SQL code

Calling 4D methods inside the SQL code  


 

 

We would now like to know something about the actors for each movie: more specifically, we are interested in finding all the movies with at least 7 actors. The result will be sorted by year.
To do this, we will use a 4D function (Find_Nr_Of_Actors) that receives the movie ID as unique parameter and returns the number of actors that played in that movie:

  `(F) Find_Nr_Of_Actors
 C_LONGINT($0;$1;$vMovie_ID)
 $vMovie_ID:=$1
 
 QUERY([MOVIE_ACTOR];[MOVIE_ACTOR]Movie_ID=$vMovie_ID)
 $0:=Records in selection([MOVIE_ACTOR])

  • The initial query in 4D code would be:

     ARRAY LONGINT(aSoldTickets;0)
     ARRAY INTEGER(aMovieYear;0)
     ARRAY LONGINT(aNrActors;0)
     ARRAY TEXT(aTitles;0)
     ARRAY TEXT(aDirectors;0)
     ARRAY TEXT(aMedias;0)
     C_LONGINT($NrOfActors;$i;$vInd)
     
     $vInd:=0
     $NrOfActors:=7
     ALL RECORDS([MOVIES])
     For($i;1;Records in selection([MOVIES]))
        $vCrtActors:=Find_Nr_Of_Actors([MOVIES]ID)
        If($vCrtActors>=$NrOfActors)
           $vInd:=$vInd+1
           INSERT IN ARRAY(aMovieYear;$vInd;1)
           aMovieYear{$vInd}:=[MOVIES]Year_of_Movie
           INSERT IN ARRAY(aTitles;$vInd;1)
           aTitles{$vInd}:=[MOVIES]Title
           INSERT IN ARRAY(aDirectors;$vInd;1)
           aDirectors{$vInd}:=[MOVIES]Director
           INSERT IN ARRAY(aMedias;$vInd;1)
           aMedias{$vInd}:=[MOVIES]Media
           INSERT IN ARRAY(aSoldTickets;$vInd;1)
           aSoldTickets{$vInd}:=[MOVIES]Sold_Tickets
           INSERT IN ARRAY(aNrActors;$vInd;1)
           aNrActors{$vInd}:=$vCrtActors
        End if
        NEXT RECORD([MOVIES])
     End for
     SORT ARRAY(aMovieYear;aTitles;aDirectors;aMedias;aSoldTickets;aNrActors;>)
  • Using SQL code, the above query becomes:

     ARRAY LONGINT(aSoldTickets;0)
     ARRAY INTEGER(aMovieYear;0)
     ARRAY LONGINT(aNrActors;0)
     ARRAY TEXT(aTitles;0)
     ARRAY TEXT(aDirectors;0)
     ARRAY TEXT(aMedias;0)
     C_LONGINT($NrOfActors;$i;$vInd)
     
     $vInd:=0
     $NrOfActors:=7
     Begin SQL
        SELECT Year_of_Movie, Title, Director, Media, Sold_Tickets, {fn Find_Nr_Of_Actors(ID) AS NUMERIC}
        FROM MOVIES
        WHERE {fn Find_Nr_Of_Actors(ID) AS NUMERIC} >= :$NrOfActors
        ORDER BY 1
        INTO :aMovieYear, :aTitles, :aDirectors, :aMedias, :aSoldTickets, :aNrActors;
     End SQL


    You can see that we are able to call a 4D function inside SQL code using the syntax:

    {fn 4DFunctionName AS 4DFunctionResultType}
  • Using generic SQL commands, the above query becomes:

     ARRAY LONGINT(aSoldTickets;0)
     ARRAY INTEGER(aMovieYear;0)
     ARRAY LONGINT(aNrActors;0)
     ARRAY TEXT(aTitles;0)
     ARRAY TEXT(aDirectors;0)
     ARRAY TEXT(aMedias;0)
     C_LONGINT($NrOfActors;$i;$vInd)
     C_TEXT($tQueryTxt)
     
     $vInd:=0
     $NrOfActors:=7
     SQL LOGIN(SQL_INTERNAL;"";"")
     $tQueryTxt:=""
     $tQueryTxt:=$tQueryTxt+"SELECT Year_of_Movie, Title, Director, Media, Sold_Tickets, {fn Find_Nr_Of_Actors(ID) AS NUMERIC}"
     $tQueryTxt:=$tQueryTxt+" FROM MOVIES"
     $tQueryTxt:=$tQueryTxt+" WHERE {fn Find_Nr_Of_Actors(ID) AS NUMERIC} >= :$NrOfActors"
     $tQueryTxt:=$tQueryTxt+" ORDER BY 1"
     SQL EXECUTE($tQueryTxt;aMovieYear;aTitles;aDirectors;aMedias;aSoldTickets;aNrActors)
     SQL LOAD RECORD(SQL All Records)
     SQL LOGOUT
  • Using the SQL EXECUTE IMMEDIATE command, the query above becomes:

     ARRAY LONGINT(aSoldTickets;0)
     ARRAY INTEGER(aMovieYear;0)
     ARRAY LONGINT(aNrActors;0)
     ARRAY TEXT(aTitles;0)
     ARRAY TEXT(aDirectors;0)
     ARRAY TEXT(aMedias;0)
     C_LONGINT($NrOfActors;$i;$vInd)
     C_TEXT($tQueryTxt)
     
     $vInd:=0
     $NrOfActors:=7
     $tQueryTxt:=""
     $tQueryTxt:=$tQueryTxt+"SELECT Year_of_Movie, Title, Director, Media, Sold_Tickets, {fn Find_Nr_Of_Actors(ID) AS NUMERIC}"
     $tQueryTxt:=$tQueryTxt+" FROM MOVIES"
     $tQueryTxt:=$tQueryTxt+" WHERE {fn Find_Nr_Of_Actors(ID) AS NUMERIC} >= :$NrOfActors"
     $tQueryTxt:=$tQueryTxt+" ORDER BY 1"
     $tQueryTxt:=$tQueryTxt+" INTO :aMovieYear, :aTitles, :aDirectors, :aMedias, :aSoldTickets,"+" :aNrActors;"
     Begin SQL
        EXECUTE IMMEDIATE :$tQueryTxt;
     End SQL

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 Calling 4D methods button.

 
PROPERTIES 

Product: 4D
Theme: Tutorial