Llamar los métodos 4D desde el código SQL

Ahora queremos efectuar búsquedas relativas a los actores de cada película: más especificamente, estamos interesados en encontrar todas las películas con al menos 7 actores. El resultado se ordenará por año.
Para hacer esto, utilizamos una función 4D (Find_Nr_Of_Actors) que recibe el ID de la película y devuelve el número de actores de la película:

• El código 4D inicial sería:
  
  
   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;>)

• Utilizando código SQL:
  
  
   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
  
  
  Puede ver que podemos llamar a un función 4D dentro del código SQL utilizando la sintaxis:
  
  
  {fn 4DFunctionName AS 4DFunctionResultType}

• Utilizando comandos SQL genéricos:
  
  
   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

• Utilizando el comando QUERY BY SQL:
  
  
   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
 QUERY BY SQL([MOVIES];"{fn Find_Nr_Of_Actors(ID) AS NUMERIC} >= :$NrOfActors")
 For($i;1;Records in selection([MOVIES]))
    $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}:=Find_Nr_Of_Actors([MOVIES]ID)
    NEXT RECORD([MOVIES])
 End for
 SORT ARRAY(aMovieYear;aTitles;aDirectors;aMedias;aSoldTickets;aNrActors;>)

• Utilizando el comando SQL EXECUTE IMMEDIATE:
  
  
   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

Para probar todos los ejemplos anteriores, lance la base "4D SQL Code Samples" y vaya a la ventana principal. A continuación, puede elegir el modo de búsqueda y presionar el botón Calling 4D methods.