File Maker Konferenz 2010 Mit SQLKommandos File Maker

File. Maker Konferenz 2010 Mit SQL-Kommandos File. Maker Daten lesen - und schreiben Integriertes SQL in File. Maker 12 SQL mit Plugins nutzen Nicolaus Busch, N. Busch Gmb. H SQL-Kommandos 1 File. Maker Konferenz 2012 Salzburg www. filemaker-konferenz. com

Struktur 1. Was ist SQL? 2. Konzeptionelle Unterschiede SQL-FM 3. Neu in File. Maker 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 Gmb. H SQL-Kommandos 2 File. Maker 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 Gmb. H SQL-Kommandos 3 File. Maker 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 1970 er Jahre bei IBM Wenige Sprachelemente zur Abfrage und Manipulation von Daten und Datenbankstruktur • Von zahlreichen DBMS unterstützt (My. SQL, MS SQL Server, Oracle, DB/2, File. Maker, …) • Sprache im Wesentlichen unabhängig von verwendeter Engine Nicolaus Busch, N. Busch Gmb. H SQL-Kommandos 4 File. Maker Konferenz 2012 Salzburg www. filemaker-konferenz. com

Konzeptionelle Unterschiede SQL-FM • File. Maker: 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 Gmb. H SQL-Kommandos 5 File. Maker Konferenz 2012 Salzburg www. filemaker-konferenz. com

Neu in File. Maker 12: SQL-Select • SQL-Statements an File. Maker schicken • Allgemeine Form: SQLQuery. Ausführen( "SELECT field(s) FROM table [WHERE Bedingung ]") • Einfachstes Beispiel SQLQuery. Ausfü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 Gmb. H SQL-Kommandos 6 File. Maker Konferenz 2012 Salzburg www. filemaker-konferenz. com

Datensatz prüfen • Gibt es dich schon? Ist. Leer( SQLQuery. Ausführen( " SELECT id FROM Adressen WHERE Nachname = 'Sarasin' AND Vorname = 'Philipp' "; "" )) • Ergebnis: Leer oder ID-Liste • Weichere Abfrage: SQLQuery. Ausführen( " SELECT id FROM Adressen WHERE LOWER( Nachname ) = 'sarasin' AND LOWER( Vorname ) = 'philipp' "; "" ) Nicolaus Busch, N. Busch Gmb. H SQL-Kommandos 7 File. Maker 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 Struktur • Unterstützung durch File. Maker bei Fehlern: keine • gross- und KLEINschrei. Bun. G 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 Gmb. H SQL-Kommandos 8 File. Maker Konferenz 2012 Salzburg www. filemaker-konferenz. com

Dynamische vs. statische Argumente v SQLQuery. Ausführen( "SELECT id FROM Adressen WHERE Nachname = 'Sarasin' AND Vorname = 'Philipp' "; "" ) v SQLQuery. Ausführen( "SELECT id FROM Adressen WHERE Nachname = '" & Adressen: : Nachname & "' AND Vorname = '" & Adressen: : Vorname & "' "; "" ) v SQLQuery. Ausführen( "SELECT id FROM Adressen WHERE Nachname = ? AND Vorname = ? "; "" ; Adressen: : Nachname ; Adressen: : Vorname ) Nicolaus Busch, N. Busch Gmb. H SQL-Kommandos 9 File. Maker Konferenz 2012 Salzburg www. filemaker-konferenz. com

Werte aus einem Datensatz holen SQLQuery. Ausfü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 Gmb. H SQL-Kommandos 10 File. Maker Konferenz 2012 Salzburg www. filemaker-konferenz. com

Einsatz im File. Maker-Script Beispiel: Neue Person anlegen Script ausführen[ fn. Troi. DL_Input["Title =" & "Neue Person" & ¶ & "Labels =Name#Vorname " & ¶ &)" Aus Datei: “LL 4_Toolbox”; (…) Variable setzen [ $Selection; Wert: Hole. Wert( Hole( Script. Ergebnis ) ; 1) ] Wenn [ $Selection = 1 ] Variable setzen [ $Nachname; Wert: Hole. Wert( Hole( Script. Ergebnis ) ; 2) ] Variable setzen [ $Vorname; Wert: Hole. Wert( Hole( Script. Ergebnis ) ; 3) ] Variable setzen [ $Matches; Wert: SQLQuery. Ausfü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 Ist. Leer( $Vorname) ; " AND LOWER( d_Vorname_t )= ? ") & " ORDER BY d_Nachname_t, d_Vorname_t"; ", " ; "" ; Kleinbuchstaben( $Nachname ); Kleinbuchstaben( $Vorname )) ] Wenn [ NICHT Ist. Leer( $Matches ) ] Script ausführen[ "fn. Troi. DL_Select. From. List["Text =" & _: : Auswahl. Datensatz. Oder. Neu ; & "¶" & "B 1 =OK ¶B 2 =Neu ¶B 3 =Neue. Suche ¶B 4 =Abbruch ¶" Aus Datei: “LL 4_Toolbox”; (…) Nicolaus Busch, N. Busch Gmb. H SQL-Kommandos 11 File. Maker Konferenz 2012 Salzburg www. filemaker-konferenz. com

Liste verschiedener Werte holen SQLQuery. Ausführen("SELECT d_Autor. Nachname_t , d_Autor. Vorname_t FROM DTitel WHERE LOWER( d_ort_t ) = ? " ; ", "; "dublin") Ø Ø Ø Ø Connolly, James Mitchell, Arthur Martin, F. X. Nowlan, Kevin B. O'Broin, Leon Nevin, Donal Cronin, James Lalor, Fintan Nowlan, Kieran Nicolaus Busch, N. Busch Gmb. H SQL-Kommandos 12 File. Maker Konferenz 2012 Salzburg www. filemaker-konferenz. com

Liste verschiedener Werte holen II SQLQuery. Ausführen("SELECT DISTINCT d_Autor. Nachname_t, d_Autor. Vorname_t FROM DTitel WHERE LOWER(d_ort_t) = ? ORDER BY d_Autor. Nachname_t, d_Autor. Vorname_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 Gmb. H SQL-Kommandos 13 File. Maker Konferenz 2012 Salzburg www. filemaker-konferenz. com

Array gruppierter Summen holen SQLQuery. Ausführen( "SELECT d_Ort_t, COUNT(d_Titel_t) FROM DTitel GROUP BY d_Ort_t"; "") Ø Ø Ø Ø , 24 Dublin, 1 Frankfurt a. M, 1 Frankfurt am Main, 2 Hamburg, 2 Stuttgart, 1 Zürich, 2 Nicolaus Busch, N. Busch Gmb. H SQL-Kommandos 14 File. Maker Konferenz 2012 Salzburg www. filemaker-konferenz. com

Array im Script einsetzen Beispiel: Show count of distinct Values Variable setzen [$Feldname; Wert: Get( Active. Field. Name)] Variable setzen [$Tabelle; Wert: Get( Layout. Table. Name)] 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 Gmb. H SQL-Kommandos 15 File. Maker Konferenz 2012 Salzburg www. filemaker-konferenz. com

Join oder: One Night Stand statt Beziehung Execute. SQL ("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") Execute. SQL ("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 Gmb. H SQL-Kommandos 16 File. Maker 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: SQLQuery. Ausfü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 Ist. Leer( $Result ) ] Feldwert setzen [ DGlobals: : v_Titel. ID_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 Gmb. H SQL-Kommandos 17 File. Maker 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 Gmb. H SQL-Kommandos 18 File. Maker Konferenz 2012 Salzburg www. filemaker-konferenz. com

Plugins im Markt • 2 empower FM SQL Runner www. dracoventions. com Reines SQL-Plugin, kostenlos, FM-Daten lesen in Versionen pre-12 • my. FMButler Do. SQL 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 • 360 Works Script. Master 4 www. 360 works. com Unterstützt SELECT, INSERT, UPDATE und DELETE Stellt Umgebung für Groovy(Java) zur Verfügung Nicolaus Busch, N. Busch Gmb. H SQL-Kommandos 19 File. Maker 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"='99 ab 23'; ") Nicolaus Busch, N. Busch Gmb. H SQL-Kommandos 20 File. Maker 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_Ausgabe. Seite_t, d_Autor. Nachname_t, d_Autor. Vorname_t, d_Co. Autoren_t, d_Datentyp 2_t, d_Datierung_t, d_Kurztitel_t, d_Reihe_t, d_Titel_t, s_Aenderung. Name_t, s_Aenderung. Zeit_ts, s_Erstellung. Name_t, s_Erstellung. Zeit_ts ) VALUES ( 'A_WFMDAABX 8 JWWKLAKJ. MN. 120210. 112303', 'HPYWWJVG 5 FP 92 U 5 VC. 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 Gmb. H SQL-Kommandos 21 File. Maker Konferenz 2012 Salzburg www. filemaker-konferenz. com

Datensatz aktualisieren (UPDATE) Allgemeine Form: fm_sql_Execute( "UPDATE " & $Database & " SET " & $Names. Values & " WHERE ID = '" & $ID & "'"); " Beispiel: : echo fm_sql_Execute( " UPDATE Titel SET "_fk_Autor"='A_WFMDAABX 8 JWWKLAKJ. 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_Zotero. Key_t"='17 F 2 YA 89', WHERE _pk_GUID = 'HPYWWJVG 5 FP 92 U 5 VC. MN. 120210. 115013' "); Nicolaus Busch, N. Busch Gmb. H SQL-Kommandos 22 File. Maker Konferenz 2012 Salzburg www. filemaker-konferenz. com

INSERT & UPDATE fm_sql_Execute(" INSERT INTO Autor (ID) VALUES ('99 ab 23')"); ") fm_sql_Execute(" UPDATE Autor SET "Nachname"='Sarasin', "Vorname"='Philipp' WHERE "ID" = '99 ab 23'; ") Nicolaus Busch, N. Busch Gmb. H SQL-Kommandos 23 File. Maker 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 Gmb. H SQL-Kommandos 24 File. Maker Konferenz 2012 Salzburg www. filemaker-konferenz. com

Exkurs: Erweiterte Möglichkeiten durch PHP- Programmierung Beispiel: Daten verschlüsseln Variable setzen [$PW; Wert: "Ich. Bin. Geheim"] Variable setzen [$Text; Wert: Get ( Active. Field. Contents )] Variable setzen [$Query; Wert: "$cipher = MCRYPT_RIJNDAEL_128; $key = hash('md 5', '" & $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 = base 64_encode($crypt_text); echo $Result; "] Variable setzen [$Result; Wert: PHP_Execute ( $Query )] Variable setzen [$Error; Wert: PHP_Get. Last. Error] Feldwert setzen [; $Result] Gehe zu Feld [] Nicolaus Busch, N. Busch Gmb. H SQL-Kommandos 25 File. Maker Konferenz 2012 Salzburg www. filemaker-konferenz. com

Exkurs: Erweiterte Möglichkeiten durch PHP- Programmierung Beispiel: Anzahl Werte ausgeben Variable setzen [$Feldname; Wert: Get( Active. Field. Name)] Variable setzen [$Tabelle; Wert: Get( Layout. Table. Name)] 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_Get. Last. Error] Eigenes Dialogfeld anzeigen ["Unique values of " & $Feldname; $Number] Nicolaus Busch, N. Busch Gmb. H SQL-Kommandos 26 File. Maker Konferenz 2012 Salzburg www. filemaker-konferenz. com

Funktionsbibliotheken am Beispiel Smartpill Ø Den Function-Maker nutzen Ø Tests definieren Ø Sets erstellen Nicolaus Busch, N. Busch Gmb. H SQL-Kommandos 27 File. Maker Konferenz 2012 Salzburg www. filemaker-konferenz. com

Funktionen zur Laufzeit laden Laden aus lokalen Dateien Setze. Var[ $Result ; Value: PHP_Load. Functions ] Laden via URL Setze. Var[ $result; Value: PHP_Load. Functions. From. URL ( "http: //www. beispiel. ch/xml/PHPx_Functions. xml" ) ] Nicolaus Busch, N. Busch Gmb. H SQL-Kommandos 28 File. Maker Konferenz 2012 Salzburg www. filemaker-konferenz. com

Further reading • http: //www. w 3 schools. com/sql/ • filemakerhacks. com: FM 12 Execute. SQL: Robust Coding, part 1 • Wikibooks: Einführung in SQL Nicolaus Busch, N. Busch Gmb. H SQL-Kommandos 29 File. Maker Konferenz 2012 Salzburg www. filemaker-konferenz. com

File. Maker Konferenz 2010 Vielen Dank unseren Sponsoren Danke für das Bewerten dieses Vortrages Nicolaus Busch, N. Busch Gmb. H SQL-Kommandos 30 File. Maker Konferenz 2012 Salzburg www. filemaker-konferenz. com