1 / 30

Mit SQL-Kommandos FileMaker Daten lesen - und schreiben

Mit SQL-Kommandos FileMaker Daten lesen - und schreiben. FileMaker Konferenz2010. Integriertes SQL in FileMaker 12 SQL mit Plugins nutzen. Nicolaus Busch, N. Busch GmbH SQL-Kommandos. FileMaker Konferenz 2012 Salzburg www.filemaker-konferenz.com. Struktur. Was ist SQL?

slone
Download Presentation

Mit SQL-Kommandos FileMaker Daten lesen - und schreiben

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Mit SQL-Kommandos FileMaker Daten lesen - und schreiben FileMaker Konferenz2010 • Integriertes SQL in FileMaker 12 • SQL mit Plugins nutzen Nicolaus Busch, N. Busch GmbH SQL-Kommandos FileMaker Konferenz 2012 Salzburg www.filemaker-konferenz.com

  2. Struktur • Was ist SQL? • KonzeptionelleUnterschiede SQL-FM • Neu in FileMaker 12: SQL-Select • Aufbaueiner SQL-Abfrage • Was geht, was gehtnicht? • Caveat: Das machtProbleme • Was kann man damitanstellen? Anwendung an Beispielen • Vorhandensein eines Datensatzes prüfen (SELECT BY ID) • Einschub: Das macht Probleme • Einschub: Dynamische vs. statischeArgumente • Werte aus einem Datensatz holen ohne Beziehung oder Suche (SELECT fieldnames) • Liste verschiedener Werte innerhalb Suchbereich holen (SELECT DISTINCT) • Array gruppierter Summen holen (SELECT COUNT GROUP BY) • Join oder: OneNight Stand statt Beziehung

  3. Struktur • Mit Plugins SQL ausschöpfen • Plugins imMarkt • Vorsichtbitte! • ErweiterteMöglichkeitendurch CREATE, UPDATE & DELETE • Datensatz in beliebigeTabelleeinfügen (INSERT INTO) • Datensatz in beliebigerTabelleaktualisieren (UPDATE … WHERE …) • DatensätzelöschenaufgrundBedingung (DELETE FROM … WHERE) • Exkurs: Erweiterte Möglichkeiten durch PHP-Programmierung (oder andere Sprache) • Auchhier: Umbenennungs-Probleme • Funktionsbibliotheken am BeispielSmartpill • Den Function-Maker nutzen • FunktionenzurLaufzeit laden • 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 DAutorWHERE 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 DISTINCTd_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 DTitelGROUP 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)] • EigenesDialogfeldanzeigen ["Unique values of " & $Feldname; $Result]

  16. Join oder: OneNight 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 DTitelTi • 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 DTitelTi, DAutor Au WHERE Ti.d_Ort_t = ? AND Ti.\"_fk_Autor\"=Au.\"_pk_GUID\" ORDER BY Au.d_Nachname_t ASC";"";""; "Dublin")

  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 DTitelTi • JOIN DAutor Au ON Ti.\"_fk_Autor\" = Au.\"_pk_GUID\" ANDTi.\"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 • myFMButlerDoSQLhttp://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 • ScodigoSmartpillwww.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

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

  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:"echofm_sql_select(\"Select COUNT( DISTINCT " & $Feldname & ") FROM " & $Tabelle & "\");"] • Variable setzen [$Result; Wert:PHP_Execute ( $Query )] • Variable setzen [$Error; Wert:PHP_GetLastError] • EigenesDialogfeldanzeigen ["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 Dateien • SetzeVar[ $Result ; Value:PHP_LoadFunctions ] • Laden via URL • SetzeVar[ $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

  30. FileMaker Konferenz2010 Vielen Dank unseren Sponsoren Danke für das Bewerten dieses Vortrages

More Related