Gespeichert von FileMaker Entwickler am Di, 04/28/2020 - 17:48

FileMaker SQL Shortcuts

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.

SQL Abfragen sind kontextunabhängig

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

SQL = reiner Text

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.

Custom Functions zum Schutz

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.

SQL Query Notierungen

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.

Bessere Organisation durch Shortcuts

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:

Textbaustein / Shortcut #sql_fm

 

//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 SQL

If ( result ≠ "?"; result)

) //Ende Let

In unserem Textbaustein sind gleich mehrere Elemente enthalten, die häufig nicht gebraucht werden, aber:

  • Es ist einfacher, Elemente zu markieren und zu entfernen, als etwas neu zu tippen
  • Die Vorlage enthält Abfragen für Text und Zahlenwerte, so dass man erinnert wird, wo die einfachen Anführungszeichen (') gesetzt werden (bei Textwerten) und wo nicht (bei Zahlenwerten)

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 / Shortcut #sql_datum

//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… }
)

Tipp: Suchabfrage mit aktuellem Datum

 

//Get (CurrentDate) in einfache Anführungszeichen:

WHERE
        " & _GFNQ ( TableA::DateFieldA1 ) & " = '" & Get (CurrentDate) & "'

 

Textbaustein / Shortcut #sql_concat

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 SQL

If ( 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).

 

Textbaustein / Shortcut #sql_join

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 SQL

If ( 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

  • Mit $value1 wird nach Werten aus einer Variablen gesucht
  • 'plain_text' ist ein Beispiel für ein feste Textkonstante
  • Der Wert in diesem Feld muss leer sein (nicht 0!), wenn nach IS NULL gesucht wird
  • BETWEEN ? AND ? ist für einen Datumsbereich, bei dem "echte FileMaker Parameter" an die ExecuteSQL  Funktion übergeben werden

 

Download

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