SQLコードから4Dメソッドを呼び出す

ここでは、映画ごとに出演者に関する何らかの情報を取得します。たとえば最低7人の出演者がいる映画を探すといったようなものです。結果は年で並び替えられます。
これを行うために、4Dメソッド (Find_Nr_Of_Actors) を使用します。このメソッドは映画のIDを受け取り、その映画の出演者数を返します: 

• 4Dコードは以下のようになります:
  
  
   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;>)

• 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
  
  
  ここで示すとおり、SQLコード内部で以下のシンタックスを使用することにより4Dメソッドを呼び出すことができます:
  
  
  {fn 4DFunctionName AS 4DFunctionResultType}

• 汎用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)
 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

• 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;>)

• 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

これらをテストするには"4D SQL Code Samples"データベースを起動してメインウィンドウを表示させます。クエリモードを選択して Calling 4D methodsボタンをクリックします。