FMK2012: Mit SQL-Kommandos FileMaker Daten lesen - und schreiben von Nico Busch

FileMaker Konferenz 2012 Salzburg

www.filemaker-konferenz.com

Nicolaus Busch, N. Busch GmbHSQL-Kommandos

1

Mit SQL-Kommandos FileMaker Daten lesen - und schreiben

Integriertes SQL in FileMaker 12SQL mit Plugins nutzen

FileMaker Konferenz2010







2

Struktur

1. Was ist SQL?

2. Konzeptionelle Unterschiede SQL-FM

3. Neu in FileMaker 12: SQL-Select1. 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




3

Struktur

4. Mit Plugins SQL ausschöpfen1. 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 Smartpill4. Den Function-Maker nutzen

5. Funktionen zur Laufzeit laden

6. Update vie URL




4

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




5

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




6

Neu in FileMaker 12: SQL-Select

• SQL-Statements an FileMaker schicken

• Allgemeine Form:SQLQueryAusführen( "SELECT field(s) FROM table [WHERE Bedingung ]")

• Einfachstes BeispielSQLQueryAusfü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




7

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 AdressenWHERE LOWER( Nachname ) = 'sarasin' ANDLOWER( Vorname ) = 'philipp' "; "" ; "" )




8

Das macht Probleme

• Was geht: alle Arten von Abfragen mit SELECTWas 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!




9

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 )




10

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




11

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”; (…)

(…)




12

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




13

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




14

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




15

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]




16

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 AuWHERE Ti.d_Ort_t = ? AND Ti.\"_fk_Autor\"=Au.\"_pk_GUID\" 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 TiJOIN DAutor Au ON Ti.\"_fk_Autor\" = Au.\"_pk_GUID\" WHERE Ti.d_Ort_t = ? ORDER BY Au.d_Nachname_t ASC";"";"";"Dublin")

Join oder: One Night Stand statt Beziehung




17

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)




18

Mit Plugins SQL ausschöpfen

• Erweiterte Möglichkeiten durch INSERT, UPDATE & DELETE• Erweiterte Möglichkeiten durch PHP-Programmierung




19

Plugins im Markt• 2empower FM SQL Runner

www.dracoventions.comReines SQL-Plugin, kostenlos, FM-Daten lesen in Versionen pre-12

• myFMButler DoSQLhttp://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 Connectionhttp://www.monkeybreadsoftware.deUnterstützt SELECT, INSERT, UPDATE und DELETEUmfangreicher Befehlssatz

• Scodigo Smartpillwww.scodigo.comUnterstützt SELECT, INSERT, UPDATE und DELETEStellt Umgebung für PHP zur Verfügung

• 360Works ScriptMaster4www.360works.comUnterstützt SELECT, INSERT, UPDATE und DELETEStellt Umgebung für Groovy(Java) zur Verfügung

http://www.dracoventions.com/

http://www.myfmbutler.com/

http://www.monkeybreadsoftware.de/

http://www.scodigo.com/

http://www.scodigo.com/




20

CAVEAT!

Ab hier geht es Ihren Daten an den Kragen!Testen, testen und nochmal testenVarianten erprobenSave often, save early

fm_sql_Execute(" INSERT INTO Autor (Nachname,

Vorname) VALUES ('Sarasin','Philipp')");

")

fm_sql_Execute(" UPDATE Autor

SET \"ID\"='99ab23';")




21

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' )");




22

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'");




23

INSERT & UPDATE

fm_sql_Execute(" INSERT INTO Autor (ID) VALUES ('99ab23')");")

fm_sql_Execute(" UPDATE Autor SET \"Nachname\"='Sarasin', \"Vorname\"='Philipp' WHERE \"ID\" = '99ab23';")




24

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




25

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 []




26

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]




27

Funktionsbibliotheken am Beispiel Smartpill

Den Function-Maker nutzen Tests definieren Sets erstellen




28

Funktionen zur Laufzeit laden

Laden aus lokalen DateienSetzeVar[ $Result ; Value:PHP_LoadFunctions ]

Laden via URLSetzeVar[ $result; Value:PHP_LoadFunctionsFromURL (

"http://www.beispiel.ch/xml/PHPx_Functions.xml" ) ]




29

Further reading

• http://www.w3schools.com/sql/• filemakerhacks.com: FM 12 ExecuteSQL: Robust Coding, part 1• Wikibooks: Einführung in SQL

http://www.w3schools.com/sql/

http://www.w3schools.com/sql/

http://www.filemakerhacks.com/?p=4924



http://de.wikibooks.org/wiki/Einf%C3%BChrung_in_SQL

http://de.wikibooks.org/wiki/Einf%C3%BChrung_in_SQL




30

FileMaker Konferenz2010

Vielen Dank unseren Sponsoren

Danke für das Bewerten dieses Vortrages

http://http./www.filemaker-magazin.de

http://www.ingeno.ch/

http://www.herrfriedrich.com/

http://www.guenther-bs.de/

http://www.monkeybreadsoftware.de/

http://www.filemaker.de/

http://www.tao-solutions.de/

Documents

FMK2012: Mit SQL-Kommandos FileMaker Daten lesen - und schreiben von Nico Busch