Eine wesentliche Neuerung, die uns FileMaker Entwicklern eine Menge Möglichkeiten gibt, war die Einführung von SQL in FileMaker 12. Zwar unterstützt ExecuteSQL nur einen einzigen SQL Befehl (SELECT), gleichzeitig ist dieser aber der wichtigste.
Der orginale Befel lautet:
//sqlQuery ist der Teil der FileMaker Funktion, der den SQL Abfrage String enthält
ExecuteSQL ( sqlQuery ; fieldSeparator ; rowSeparator { ; arguments... } )
//fieldSeparator und rowSeparator sind optionale Parameter für die Ausgabe des Ergebnisses. Beitde Felder können leer bleiben.
Mit ExecuteSQL werden einfache oder komplexe Abfragen ermöglich, ohne das spezielle Verknüpfungen erforderlich sind. D.h. wenn der FileMaker Entwickler zwei Tabellen miteinander verknüpft, für bestimmte Suchabfragen aber eigentlich weitere Verknüpfungen derselben Tabellen erforderlich wären, kann er sich die zusätzlichen Verknüpfungen sparen. Das erhöht die Übersichtlichkeit und belastet nicht die Geschwindigkeit der Datenbank.
Während der FileMaker Entwickler bei Suchabfragen immer den Kontext beachten muss (in welchem Layout bin ich, d.h. welche TableOccurence liegt meinen Suchabfragen zugrunde), sind die SQL Abfragen kontextunabhängig.
//Dies ist eine einfache Abfrage nach den ID Nummern aller Datensätze
ExecuteSQL ("SELECT ID FROM KnowledbaseItems"; "";"")
//die sqlQuery ein ein reiner String
Und noch etwas gilt es zu beachten: Während der FileMaker Entwickler sich bei "normalen" Tabellenabfragen auf die "Unterstützung" von FileMaker verlassen kann (Tabellen- und Feldnamen werden ausgewählt, bei Änderungen der Namen werden diese automatisch in allen Formelfeldern und Scripten automatisch nachgeführt, ist dies bei der ExecuteSQL Funktion nicht der Fall.
Wenn im obigen Beispiel das Feld ID oder Tabellenname KnowledbaseItems geändert wird, funktioniert die Abfrage nicht mehr, da FileMaker nicht den Tabellen und Feldnamen in der sqlQuery speichert, da es sich um reinen Text handelt.
Als FileMaker Entwickler kann man die sqlQuery durch die Kombination aus statischem Text und der Verknüpfung mit "echten" FileMaker Tabellen- und Feldnamen lösen.
//Die gleiche Abfrage mit gekapselten "echten" FileMaker Tabellen- und Feldnamen
ExecuteSQL ("SELECT " & _GFNQ ( KnowledbaseItems::ID ) & " FROM " & _GTNQ ( KnowledbaseItems::ID ) & ""; "";"")
Die beiden Custom Functions _GFNQ (GetFieldname quoted) und _GTNQ (GetTablename quoted) (siehe Download Datei) fügen dem zuvor starren Text die Tabellen- und Feldnamen hinzu, die auf diese Weise auch bei Änderungen von FileMaker aktualisiert werden.
Bei SQL Abfragen muss die SQL Syntax beachtet werden. Wenn eine Abfrage nach Parametern erfolgt, müssen Abfragen nach Textwerten in zwei einfache Anfühungszeichen ('Parameter') gesetzt werden.
Außerdem möchte der FileMaker Entwickler sicherlich auch die Vorteile der SQL-Operatoren (BETWEEN, LIKE, IN) nutzen und muss daher um deren Besonderheiten wissen.
Natürlich muss der FileMaker die SQL Syntax, die er einsetzenn will beherrschen. Aber er braucht nicht jede Kleinigkeit im Kopf zu behalten. Gerade beim neu Erlernen der SQL Syntax ist es hilfreich, 1x grundlegend die Funktionsweise und die dazugehörige Syntax zu verstehen und in einer jederzeit abrufbaren Form zu notieren.
Hierfür bieten sich Programme mit Textbausteinen (Mac: Typinator, Windows: FastKeys oder ähnliche) an.
Textbaustein können in der FileMaker Entwicklung besonders bei SQL Standard Abfragen nützlich sein. Denn neben der eigentlichen Syntax können verschiedene Standardvarianten (Mehrfach-Abfragen, spezielle Datum oder Berechnungs-Querys) als Textbaustein angelegt werden. Dazu kann dann die "eigene SQL Funktion" nach einem eigenen gewünchten Shema "formatiert" werden:
//Beispiel Textbaustein #sql_fm. Dieser enthält neben der SQL Abfrage noch ein Let Statement, um mögliche Fehler im Ergebnis abzufangen.
Let (
result =ExecuteSQL ("
SELECT
" & _GFNQ ( TableA::TextFieldA1 ) & "," & _GFNQ ( TableA::TextFieldA2 ) & "FROM
" & _GTNQ ( TableA::TextFieldA1 ) & "WHERE
" & _GFNQ ( TableA::NumberFieldA1) & " = " & TableB::NumberFieldB1 & "
AND
" & _GFNQ ( TableA::TextFieldA2 ) & " = '" & TableB::TextFieldB2 & "'
AND
" & _GFNQ ( TableA::TextFieldA3) & " = '" & TableB::TextFieldB3 & "'
";
"|"; //fieldSeparator
"" //rowSeparator { ; arguments }
); //Ende SQLIf ( result "?"; result)
) //Ende Let
In unserem Textbaustein sind gleich mehrere Elemente enthalten, die häufig nicht gebraucht werden, aber:
Durch Tippen des Textbaustein-Trigger-Codes #sql_fm fügt das Shortcut Programm den gesamten Text in den FileMaker Formeleditor und wir entfernen die Elemente, die nicht gebraucht werden. In 90% der Fälle, ist die Abfrage nach einem zweiten Feld _GFNQ ( TableA::TextFieldA2 ) und auch die Angabe von mehr als 1 Suchparameter (ab ...AND...) nicht erforderlich und können einfach gelöscht werden.
Textbausteine vereinfachen nicht nur die aufwendige Eingabe der SQL Syntax, man spart auch viel Zeit, die oftmals für die Fehlersuche aufgrund inkorrekter SQL Syntax verloren geht.
//Textbaustein #sql_datum
ExecuteSQL ("
SELECT
" & _GFNQ ( TableA::TextFieldA1) & ",
ROUND (" & _GFNQ ( TableA::NumberFieldA1 ) & ",2)FROM
" & _GTNQ ( TableA::TextFieldA1 ) & "WHERE
" & _GFNQ ( TableA::DateFieldA1 ) & " = " & _GFNQ ( TableB::DateFieldB1 ) & "";
"|"; //fieldSeparator
"" //rowSeparator { ; arguments }
)
//Get (CurrentDate) in einfache Anführungszeichen:
WHERE
" & _GFNQ ( TableA::DateFieldA1 ) & " = '" & Get (CurrentDate) & "'
Mit concatenate werden Ergebniswerte zu einem Textstring verknüpft. In diesem Beispiel werden die Werte aus Feld TableA::TextFieldA1 und TableA::TextFieldA2 verbunden um als Ergebnis einen Scriptparameter für die CustomFunction _sp zusammenzubauen (Informationen zu dieser cf befindet sich hier)
//Textbaustein #sql_concat
Let (
result =ExecuteSQL ("
SELECT
'_sp (\"'||" & _GFNQ ( TableA::TextFieldA1 ) & "||'\";\"'||" & _GFNQ ( TableA::TextFieldA2 ) & "||'\")'FROM
" & _GTNQ ( TableA::TextFieldA1 ) & "WHERE
" & _GFNQ ( TableA::NumberFieldA1 ) & " = " & TableB::NumberFieldB1 & "
";
"|"; //fieldSeparator
"" //rowSeparator { ; arguments }
); //Ende SQLIf ( result "?"; result)
) //Ende Let
Hinweis: Das Ergebnis dieser Abfrage ist beispielsweise: _sp (user_change_record#allowed)
_sp ist in dem obigen Beispiel Teil des Textes, der als Ergebnis ausgegeben wird. Der zusammengesetzte Text (inkl. des Namens einer CustomFunction ) dient als Scriptaufruf mit zusammengesetzten Parametern (der Scriptaufruf ist nicht Teil des Beispiels).
Will man z.B. einen Report mit Daten aus verschiedenen Tabellen per SQl Abfrage erzeugen, ist dafür keine besondere Relation auf FileMaker Ebene dafür erforderlich.
//Textbaustein #sql _join
Let (
result = ExecuteSQL ("
SELECT
SUM (a." & _GFNQ ( TableA::TextFieldA1 ) & ")FROM
" & _GTNQ (TableA::TextFieldA1 ) & " a
JOIN
" & _GTNQ ( TableB:JoinFieldB1 ) & " b ON a." & _GFNQ (TableA::JoinFieldA1) & " = b." & _GFNQ ( TableB:JoinFieldB1) & "
WHERE
a." & _GFNQ ( TableA::TextFieldA2 ) & " = " & $value1 & "
AND
a." & _GFNQ ( TableA::TextFieldA3 ) & " = '" & TableA::TextFieldA3 & "'
AND
b." & _GFNQ ( TableB:TextFieldB2 ) & " = 'plain_text'
AND
b." & _GFNQ ( TableB:TextFieldB3 ) & " IS NULL
AND
b." & _GFNQ ( TableB:DateFieldB4 ) & " BETWEEN ? AND ?
";
"|"; //fieldSeparator
"" //rowSeparator
); //Ende SQLIf ( result "?"; result)
) //Ende Let
Hinweis: Wichtig im obigen Beispiel sind die rosafarbenen Buchstaben a und b. Mit JOIN und ON werden die Tabellen miteinander für die Dauer der Abfrage verknüpft.
Im unteren Teil des Textbausteins sind verschiedene Varianten für die Übergabe von Suchparametern aufgeführt
Hier können Sie die Bespiel Datei runterladen
Mamtemic GmbH:
FileMaker Entwicklung, FileMaker Programmierung, Individuelle Datenbanklösungen, Speziallösungen, Web Applications, Auswertungen & Berichte, Außendienst Steuerung, Spezial Kalkulationen, Datenkonvertierung, FileMaker Entwickler für die Anpassung und Wartung Ihrer Inhouse Datenbank
FileMaker Development in Hamburg, Deutschland und Europa, FileMaker Consulting in Norddeutschland