FileMaker Konferenz 2010

FileMaker Konferenz2010
Mit SQL-Kommandos FileMaker
Daten lesen - und schreiben
Integriertes SQL in FileMaker 12
SQL mit Plugins nutzen
Nicolaus Busch, N. Busch GmbH
SQL-Kommandos
1
FileMaker Konferenz 2012 Salzburg
www.filemaker-konferenz.com
Struktur
1. Was ist SQL?
2. Konzeptionelle Unterschiede SQL-FM
3. Neu in FileMaker 12: SQL-Select
1. Aufbau einer SQL-Abfrage
2. Was geht, was geht nicht?
3. Caveat: Das macht Probleme
4. Was kann man damit anstellen? Anwendung an Beispielen
1.
Vorhandensein eines Datensatzes prüfen (SELECT BY ID)
2.
Einschub: Das macht Probleme
3.
Einschub: Dynamische vs. statische Argumente
4.
Werte aus einem Datensatz holen ohne Beziehung oder Suche (SELECT fieldnames)
5.
Liste verschiedener Werte innerhalb Suchbereich holen (SELECT DISTINCT)
6.
Array gruppierter Summen holen (SELECT COUNT GROUP BY)
7.
Join oder: One Night Stand statt Beziehung
Nicolaus Busch, N. Busch GmbH
SQL-Kommandos
2
FileMaker Konferenz 2012 Salzburg
www.filemaker-konferenz.com
Struktur
4. Mit Plugins SQL ausschöpfen
1. Plugins im Markt
2. Vorsicht bitte!
3. Erweiterte Möglichkeiten durch CREATE, UPDATE & DELETE
1.
Datensatz in beliebige Tabelle einfügen (INSERT INTO)
2.
Datensatz in beliebiger Tabelle aktualisieren (UPDATE … WHERE …)
3.
Datensätze löschen aufgrund Bedingung (DELETE FROM … WHERE)
4. Exkurs: Erweiterte Möglichkeiten durch PHP-Programmierung (oder andere
Sprache)
5. Auch hier: Umbenennungs-Probleme
5. Funktionsbibliotheken am Beispiel Smartpill
4. Den Function-Maker nutzen
5. Funktionen zur Laufzeit laden
6. Update vie URL
Nicolaus Busch, N. Busch GmbH
SQL-Kommandos
3
FileMaker Konferenz 2012 Salzburg
www.filemaker-konferenz.com
Was ist SQL?
•
•
•
•
SQL ist keine Datenbank
SQL ist eine Sprache (Structured English Query Language)
Entwickelt Anfang der 1970er Jahre bei IBM
Wenige Sprachelemente zur Abfrage und Manipulation von Daten und
Datenbankstruktur
• Von zahlreichen DBMS unterstützt (MySQL, MS SQL Server, Oracle,
DB/2, FileMaker,…)
• Sprache im Wesentlichen unabhängig von verwendeter Engine
Nicolaus Busch, N. Busch GmbH
SQL-Kommandos
4
FileMaker Konferenz 2012 Salzburg
www.filemaker-konferenz.com
Konzeptionelle Unterschiede SQL-FM
• FileMaker: Suche selektiert Datensätze für weitere Bearbeitung
• Befehle beziehen sich auf die Fundmenge
• SQL: Jedes Statement steht für sich
• Suche und Datenausgabe/Manipulation im selben Befehl
• Erstellen, Editieren, Löschen mit Bedingungsangabe
Nicolaus Busch, N. Busch GmbH
SQL-Kommandos
5
FileMaker Konferenz 2012 Salzburg
www.filemaker-konferenz.com
Neu in FileMaker 12: SQL-Select
• SQL-Statements an FileMaker schicken
• Allgemeine Form:
SQLQueryAusführen( "SELECT field(s) FROM table [WHERE Bedingung ]")
• Einfachstes Beispiel
SQLQueryAusführen( "SELECT * FROM Adressen" ; "";"")




156231,Müller,DE,Walter,Helmut Kohl Allee 12,55116,Mainz
2312,Meier,AT,Irene,Erzherzog-Johann-Strasse 5,1172,Wien
12156,Ganter,CH,Hansueli,Bankenplatz 1,8000,Zürich
15612,Zehnder,,Rudolf,Paradeplatz 12,1100,Wien
 Unsortierte Liste, Feldreihenfolge wie erstellt
Nicolaus Busch, N. Busch GmbH
SQL-Kommandos
6
FileMaker Konferenz 2012 Salzburg
www.filemaker-konferenz.com
Datensatz prüfen
• Gibt es dich schon?
IstLeer( SQLQueryAusführen( "
SELECT id FROM Adressen
WHERE Nachname = 'Sarasin' AND Vorname = 'Philipp' ";
"" ; "" ))
•Ergebnis: Leer oder ID-Liste
• Weichere Abfrage:
SQLQueryAusführen( "
SELECT id FROM Adressen
WHERE LOWER( Nachname ) = 'sarasin' AND
LOWER( Vorname ) = 'philipp' ";
"" ; "" )
Nicolaus Busch, N. Busch GmbH
SQL-Kommandos
7
FileMaker Konferenz 2012 Salzburg
www.filemaker-konferenz.com
Das macht Probleme
• Was geht: alle Arten von Abfragen mit SELECT
Was geht nicht: alle Arten von Manipulation der Daten oder der Struktur
• Unterstützung durch FileMaker bei Fehlern: keine
• gross- und KLEINschreiBunG von Argumenten
• Umbenennungs-Probleme
• _Sonderzeichen in Feldname ⇒ \" benutzen
• Keine SQL-Abfragen in gespeicherten Berechnungen!
• Kein SQL-Abfragen im Data Viewer stehen lassen!
Nicolaus Busch, N. Busch GmbH
SQL-Kommandos
8
FileMaker Konferenz 2012 Salzburg
www.filemaker-konferenz.com
Dynamische vs. statische Argumente
 SQLQueryAusführen( "SELECT id FROM Adressen
WHERE Nachname = 'Sarasin' AND Vorname = 'Philipp' ";
"" ; "" )
 SQLQueryAusführen( "SELECT id FROM Adressen
WHERE Nachname = '" & Adressen::Nachname & "' AND Vorname = '" &
Adressen::Vorname & "' ";
"" ; "" )
 SQLQueryAusführen( "SELECT id FROM Adressen
WHERE Nachname = ? AND Vorname = ? ";
"" ; "" ;
Adressen::Nachname ;
Adressen::Vorname )
Nicolaus Busch, N. Busch GmbH
SQL-Kommandos
9
FileMaker Konferenz 2012 Salzburg
www.filemaker-konferenz.com
Werte aus einem Datensatz holen
SQLQueryAusführen(
"SELECT plz, ort, land, strasse
FROM Adressen
WHERE NOT land = ? ";
"" ; "" ;
"AT"
) //end SQLQuery
 55116,Mainz,DE, Helmut Kohl Allee 12
 8000,Zürich,CH,Bankenplatz 1
 1100,Wien,, Paradeplatz 12
Nicolaus Busch, N. Busch GmbH
SQL-Kommandos
10
FileMaker Konferenz 2012 Salzburg
www.filemaker-konferenz.com
Einsatz im FileMaker-Script
Beispiel: Neue Person anlegen
Script ausführen[ fn.TroiDL_Input["Title =" & "Neue Person" & ¶ &
"Labels =Name#Vorname " & ¶ &)" Aus Datei:“LL4_Toolbox”; (…)
Variable setzen [ $Selection; Wert:HoleWert( Hole( ScriptErgebnis ) ; 1) ]
Wenn [ $Selection = 1 ]
Variable setzen [ $Nachname; Wert:HoleWert( Hole( ScriptErgebnis ) ; 2) ]
Variable setzen [ $Vorname; Wert:HoleWert( Hole( ScriptErgebnis ) ; 3) ]
Variable setzen [ $Matches; Wert:SQLQueryAusführen(
"SELECT \"_pk_GUID\", \"d_Nachname_t\", \"d_Vorname_t\", \"d_Geburtsort_t\",
\"d_Geburtsjahr_n\" FROM DAutor
WHERE LOWER( d_Nachname_t ) = ?"
& Falls( NICHT IstLeer( $Vorname) ; " AND LOWER( d_Vorname_t )= ?")
& " ORDER BY d_Nachname_t, d_Vorname_t";
", " ; "" ; Kleinbuchstaben( $Nachname ); Kleinbuchstaben( $Vorname )) ]
Wenn [ NICHT IstLeer( $Matches ) ]
Script ausführen[ "fn.TroiDL_SelectFromList["Text =" & _::AuswahlDatensatzOderNeu ;
& "¶" & "B1 =OK ¶B2 =Neu ¶B3 =NeueSuche ¶B4 =Abbruch ¶" Aus Datei:
“LL4_Toolbox”; (…)
(…)
Nicolaus Busch, N. Busch GmbH
SQL-Kommandos
11
FileMaker Konferenz 2012 Salzburg
www.filemaker-konferenz.com
Liste verschiedener Werte holen
SQLQueryAusführen("SELECT d_AutorNachname_t , d_AutorVorname_t
FROM DTitel WHERE LOWER( d_ort_t ) = ?" ; ", ";"dublin")













Connolly, James
Connolly, James
Mitchell, Arthur
Martin, F.X.
Martin, F.X.
Nowlan, Kevin B.
O'Broin, Leon
O'Broin, Leon
Nevin, Donal
Cronin, James
Cronin, James
Lalor, Fintan
Nowlan, Kieran
Nicolaus Busch, N. Busch GmbH
SQL-Kommandos
12
FileMaker Konferenz 2012 Salzburg
www.filemaker-konferenz.com
Liste verschiedener Werte holen II
SQLQueryAusführen("SELECT DISTINCT d_AutorNachname_t,
d_AutorVorname_t FROM DTitel WHERE LOWER(d_ort_t) = ? ORDER
BY d_AutorNachname_t, d_AutorVorname_t" ; ", " ; "dublin")









Connolly, James
Cronin, James
Lalor, Fintan
Martin, F.X.
Mitchell, Arthur
Nevin, Donal
Nowlan, Kevin B.
Nowlan, Kieran
O'Broin, Leon
Nicolaus Busch, N. Busch GmbH
SQL-Kommandos
13
FileMaker Konferenz 2012 Salzburg
www.filemaker-konferenz.com
Array gruppierter Summen holen
SQLQueryAusführen( "SELECT d_Ort_t, COUNT(d_Titel_t) FROM DTitel
GROUP BY d_Ort_t";
"" ; "")








,24
Dublin,1
Frankfurt,1
Frankfurt a.M,1
Frankfurt am Main,2
Hamburg,2
Stuttgart,1
Zürich,2
Nicolaus Busch, N. Busch GmbH
SQL-Kommandos
14
FileMaker Konferenz 2012 Salzburg
www.filemaker-konferenz.com
Array im Script einsetzen
Beispiel: Show count of distinct Values
Variable setzen [$Feldname; Wert: Get( ActiveFieldName)]
Variable setzen [$Tabelle; Wert: Get( LayoutTableName)]
Variable setzen [$Query;
Wert: "echo fm_sql_select(\"Select COUNT( DISTINCT " & $Feldname & ")
FROM " & $Tabelle & "\");"]
Variable setzen [$Result; Wert:PHP_Execute ($Query)]
Eigenes Dialogfeld anzeigen ["Unique values of " & $Feldname; $Result]
Nicolaus Busch, N. Busch GmbH
SQL-Kommandos
15
FileMaker Konferenz 2012 Salzburg
www.filemaker-konferenz.com
Join oder: One Night Stand statt
Beziehung
ExecuteSQL ("SELECT Ti.d_Titel_t , Ti.d_Ort_t , Ti.d_Datierung_t,
Au.d_Nachname_t , Au.d_Vorname_t , Au.d_Geburtsort_t ,
Au.d_Geburtsjahr_n
FROM DTitel Ti
JOIN DAutor Au ON Ti.\"_fk_Autor\" = Au.\"_pk_GUID\"
WHERE Ti.d_Ort_t = ?
ORDER BY Au.d_Nachname_t ASC";"";"";
"Dublin")
ExecuteSQL ("SELECT Ti.d_Titel_t , Ti.d_Ort_t , Ti.d_Datierung_t,
Au.d_Nachname_t , Au.d_Vorname_t , Au.d_Geburtsort_t ,
Au.d_Geburtsjahr_n
FROM DTitel Ti, DAutor Au
WHERE Ti.d_Ort_t = ? AND Ti.\"_fk_Autor\"=Au.\"_pk_GUID\"
ORDER BY Au.d_Nachname_t ASC";"";"";
"Dublin")
Nicolaus Busch, N. Busch GmbH
SQL-Kommandos
16
FileMaker Konferenz 2012 Salzburg
www.filemaker-konferenz.com
Script mit Join
#Ruft die Titel auf, bei denen Publikationsort gleich Geburtsort des Autors ist
Variable setzen [ $Result ; Wert:SQLQueryAusführen ( "
SELECT Ti.\"_pk_GUID\"
FROM DTitel Ti
JOIN DAutor Au ON Ti.\"_fk_Autor\" = Au.\"_pk_GUID\" AND
Ti.\"d_Ort_t\" = Au.\"d_Geburtsort_t\"
ORDER BY Au.\"d_Nachname_t\" ASC";
"";"") ]
Wenn [ NICHT IstLeer( $Result ) ]
Feldwert setzen [ DGlobals::v_TitelID_tg ; $Result ]
Fenster fixieren
Gehe zu Layout [ “DGlobals” (DGlobals) ]
Gehe zu Bezugsdatensatz[ Aus Tabelle: “DGlobals.Titel” ; Mit Layout:
“lst.Titel” (DTitel) , Nur Bezugsdatensätze zeigen ]
Ende (wenn)
Nicolaus Busch, N. Busch GmbH
SQL-Kommandos
17
FileMaker Konferenz 2012 Salzburg
www.filemaker-konferenz.com
Mit Plugins SQL ausschöpfen
• Erweiterte Möglichkeiten durch INSERT, UPDATE & DELETE
• Erweiterte Möglichkeiten durch PHP-Programmierung
Nicolaus Busch, N. Busch GmbH
SQL-Kommandos
18
FileMaker Konferenz 2012 Salzburg
www.filemaker-konferenz.com
Plugins im Markt
• 2empower FM SQL Runner
www.dracoventions.com
Reines SQL-Plugin, kostenlos, FM-Daten lesen in Versionen pre-12
• myFMButler DoSQL
http://www.myfmbutler.com/
Lesen und schreiben in FM via SQL, unterstützt neben SELECT, INSERT,
UPDATE und DELETE auch CREATE, ALTER und DROP
• Monkeybread MBS SQL Connection
http://www.monkeybreadsoftware.de
Unterstützt SELECT, INSERT, UPDATE und DELETE
Umfangreicher Befehlssatz
• Scodigo Smartpill
www.scodigo.com
Unterstützt SELECT, INSERT, UPDATE und DELETE
Stellt Umgebung für PHP zur Verfügung
• 360Works ScriptMaster4
www.360works.com
Unterstützt SELECT, INSERT, UPDATE und DELETE
Stellt Umgebung für Groovy(Java) zur Verfügung
Nicolaus Busch, N. Busch GmbH
SQL-Kommandos
19
FileMaker Konferenz 2012 Salzburg
www.filemaker-konferenz.com
CAVEAT!
 Ab hier geht es Ihren Daten an den Kragen!
 Testen, testen und nochmal testen
 Varianten erproben
 Save often, save early
fm_sql_Execute("
INSERT INTO Autor (Nachname,
Vorname) VALUES
('Sarasin','Philipp')");
")
fm_sql_Execute("
UPDATE Autor SET \"ID\"='99ab23';
")
Nicolaus Busch, N. Busch GmbH
SQL-Kommandos
20
FileMaker Konferenz 2012 Salzburg
www.filemaker-konferenz.com
Datensatz einfügen (INSERT INTO)
Allgemeine Form:
fm_sql_Execute( \"INSERT INTO " & $Database & " (" & $Names & ") "
& " VALUES (" & $Values & ")\");"
Beispiel:
echo fm_sql_Execute( "INSERT INTO DTitel (
_fk_Autor, _pk_GUID, d_AusgabeSeite_t, d_AutorNachname_t, d_AutorVorname_t,
d_CoAutoren_t, d_Datentyp_t, d_Datentyp2_t, d_Datierung_t, d_Kurztitel_t, d_Reihe_t,
d_Titel_t, s_AenderungName_t, s_AenderungZeit_ts, s_ErstellungName_t,
s_ErstellungZeit_ts )
VALUES (
'A_WFMDAABX8JWWKLAKJ.MN.120210.112303',
'HPYWWJVG5FP92U5VC.MN.120210.115013',
'8-14', 'Dyk', 'Silke', 'Stephan Lessenich', 't', 'Aufsatz', '2010',
'Potentiale des Alters', 'Mittelweg 36, 19. Jg.', 'Die Potentiale des Alters und die
Soziologie', 'nb', '19.02.2012 13:45:05', 'ps', '10.02.2012 11:50:13' )
");
Nicolaus Busch, N. Busch GmbH
SQL-Kommandos
21
FileMaker Konferenz 2012 Salzburg
www.filemaker-konferenz.com
Datensatz aktualisieren (UPDATE)
Allgemeine Form:
fm_sql_Execute( \"UPDATE " & $Database & " SET " & $NamesValues & "
WHERE ID = '" & $ID & "'\");"
Beispiel::
echo fm_sql_Execute( "
UPDATE Titel SET \"_fk_Autor\"='A_WFMDAABX8JWWKLAKJ.MN.120210.112303',
\"_fk_Bibliothek\"='25634',
\"d_Titel_t\"=' Diskursanalyse meets Gouvernementalitätsforschung : Perspektiven auf
das Verhältnis von Subjekt, Sprache, Macht und Wissen',
(…)
\"s_ZoteroKey_t\"='17F2YA89',
WHERE _pk_GUID = 'HPYWWJVG5FP92U5VC.MN.120210.115013'
");
Nicolaus Busch, N. Busch GmbH
SQL-Kommandos
22
FileMaker Konferenz 2012 Salzburg
www.filemaker-konferenz.com
INSERT & UPDATE
fm_sql_Execute("
INSERT INTO Autor (ID) VALUES ('99ab23')");
")
fm_sql_Execute("
UPDATE Autor
SET \"Nachname\"='Sarasin',
\"Vorname\"='Philipp'
WHERE \"ID\" = '99ab23';
")
Nicolaus Busch, N. Busch GmbH
SQL-Kommandos
23
FileMaker Konferenz 2012 Salzburg
www.filemaker-konferenz.com
Datensatz löschen (DELETE)
Let(
$Command = "echo fm_sql_execute( \"
DELETE FROM " & table & "
WHERE \\\"_pk_GUID\\\"='" & id & "'
\");";
PHP_Execute ($Command)
)
LETZTE WARNUNG: NICHT SO
Let(
$Command = "echo fm_sql_execute( \"
DELETE FROM " & table
\");";
PHP_Execute ($Command)
)
Nicolaus Busch, N. Busch GmbH
SQL-Kommandos
24
FileMaker Konferenz 2012 Salzburg
www.filemaker-konferenz.com
Exkurs: Erweiterte Möglichkeiten
durch PHP- Programmierung
Beispiel: Daten verschlüsseln
Variable setzen [$PW; Wert:"IchBinGeheim"]
Variable setzen [$Text; Wert:Get ( ActiveFieldContents )]
Variable setzen [$Query; Wert:"$cipher = MCRYPT_RIJNDAEL_128;$key = hash('md5', '" &
$PW & "');$iv_size = mcrypt_get_iv_size($cipher, MCRYPT_MODE_ECB);$iv =
mcrypt_create_iv($iv_size, MCRYPT_RAND);$crypt_text = mcrypt_encrypt($cipher,
$key, '" & $Text & "', MCRYPT_MODE_ECB, $iv);$Result =
base64_encode($crypt_text);echo $Result;"]
Variable setzen [$Result; Wert:PHP_Execute ( $Query )]
Variable setzen [$Error; Wert:PHP_GetLastError]
Feldwert setzen [; $Result]
Gehe zu Feld []
Nicolaus Busch, N. Busch GmbH
SQL-Kommandos
25
FileMaker Konferenz 2012 Salzburg
www.filemaker-konferenz.com
Exkurs: Erweiterte Möglichkeiten
durch PHP- Programmierung
Beispiel: Anzahl Werte ausgeben
Variable setzen [$Feldname; Wert:Get( ActiveFieldName)]
Variable setzen [$Tabelle; Wert:Get( LayoutTableName)]
Variable setzen [$Query; Wert:"echo fm_sql_select(\"Select COUNT( DISTINCT " &
$Feldname & ") FROM " & $Tabelle & "\");"]
Variable setzen [$Result; Wert:PHP_Execute ( $Query )]
Variable setzen [$Error; Wert:PHP_GetLastError]
Eigenes Dialogfeld anzeigen ["Unique values of " & $Feldname; $Number]
Nicolaus Busch, N. Busch GmbH
SQL-Kommandos
26
FileMaker Konferenz 2012 Salzburg
www.filemaker-konferenz.com
Funktionsbibliotheken am Beispiel
Smartpill
 Den Function-Maker nutzen
 Tests definieren
 Sets erstellen
Nicolaus Busch, N. Busch GmbH
SQL-Kommandos
27
FileMaker Konferenz 2012 Salzburg
www.filemaker-konferenz.com
Funktionen zur Laufzeit laden
Laden aus lokalen Dateien
SetzeVar[ $Result ; Value:PHP_LoadFunctions ]
Laden via URL
SetzeVar[ $result; Value:PHP_LoadFunctionsFromURL (
"http://www.beispiel.ch/xml/PHPx_Functions.xml" ) ]
Nicolaus Busch, N. Busch GmbH
SQL-Kommandos
28
FileMaker Konferenz 2012 Salzburg
www.filemaker-konferenz.com
Further reading
• http://www.w3schools.com/sql/
• filemakerhacks.com: FM 12 ExecuteSQL: Robust Coding, part 1
• Wikibooks: Einführung in SQL
Nicolaus Busch, N. Busch GmbH
SQL-Kommandos
29
FileMaker Konferenz 2012 Salzburg
www.filemaker-konferenz.com
FileMaker Konferenz2010
Vielen Dank unseren Sponsoren
Danke für das Bewerten dieses Vortrages
Nicolaus Busch, N. Busch GmbH
SQL-Kommandos
30
FileMaker Konferenz 2012 Salzburg
www.filemaker-konferenz.com