MBS FileMaker Plugin Example Databases

SQL Export

All examples are included with download of MBS FileMaker Plugin.

SQL Export.fmp12

Overview
Tables1
Relationships0
Layouts1
Scripts1
Value Lists0
Custom Functions0
Custom Menus0
File Options
Default custom menu set[Standard FileMaker Menus]
When opening file
Minimum allowed version12.0
Login usingAccount Name; Account= Admin
Allow user to save passwordOff
Require iOS passcodeOff
Switch to layoutOff
Hide all toolbarsOff
Script triggers
OnFirstWindowOpenOff
OnLastWindowCloseOff
OnWindowOpenOff
OnWindowCloseOff
OnFileAVPlayerChangeOff
Thumbnail Settings
Generate ThumbnailsOn; Temporary

 

Tables

Table Name
Statistics
Occurrences in Relationship Graph
SQL Export
7 fields defined, 2 records
SQL Export

Fields

Table Name: SQL Export - 7 Fields
Field NameTypeOptionsCommentsOn LayoutsIn RelationshipsIn ScriptsIn Value Lists
Database FileNormal, TextAuto-Enter:
  • Allow editing
Validation:
  • Only during data entry
Storage:
  • Repetitions: 1
  • Indexing: None
  • Automatically create indexes as needed
  • Index Language: German
      Export folderNormal, TextAuto-Enter:
      • Allow editing
      Validation:
      • Only during data entry
      Storage:
      • Repetitions: 1
      • Indexing: None
      • Automatically create indexes as needed
      • Index Language: German
            SQL Destination TypeNormal, TextAuto-Enter:
            • Allow editing
            Validation:
            • Only during data entry
            Storage:
            • Repetitions: 1
            • Indexing: None
            • Automatically create indexes as needed
            • Index Language: German
                SQL Connection StringNormal, TextAuto-Enter:
                • Allow editing
                Validation:
                • Only during data entry
                Storage:
                • Repetitions: 1
                • Indexing: None
                • Automatically create indexes as needed
                • Index Language: German
                    SQL UsernameNormal, TextAuto-Enter:
                    • Allow editing
                    Validation:
                    • Only during data entry
                    Storage:
                    • Repetitions: 1
                    • Indexing: None
                    • Automatically create indexes as needed
                    • Index Language: German
                        SQL PasswordNormal, TextAuto-Enter:
                        • Allow editing
                        Validation:
                        • Only during data entry
                        Storage:
                        • Repetitions: 1
                        • Indexing: None
                        • Automatically create indexes as needed
                        • Index Language: German
                            MySQL Library PathNormal, TextAuto-Enter:
                            • Allow editing
                            Validation:
                            • Only during data entry
                            Storage:
                            • Repetitions: 1
                            • Indexing: None
                            • Automatically create indexes as needed
                            • Index Language: German

                                  Layout Objects: SQL Export

                                  Regular Fields

                                  Field Name: SQL Export::Database File
                                  Field PropertiesCoordinatesField FormatField BehaviorQuick Find
                                  • Top: 57 pt
                                  • Left: 150 pt
                                  • Bottom: 78 pt
                                  • Right: 403 pt
                                  • Anchoring: Left, Top
                                  Field Format:
                                  • Edit Box
                                  Field Behavior:
                                  • Allow field to be entered: In Find mode, In Browse mode
                                  • Touch keyboard type: Default for Data Type
                                  • Go to next field using: Tab key
                                  Yes

                                  Field Name: SQL Export::Export folder
                                  Field PropertiesCoordinatesField FormatField BehaviorQuick Find
                                  • Top: 84 pt
                                  • Left: 150 pt
                                  • Bottom: 105 pt
                                  • Right: 403 pt
                                  • Anchoring: Left, Top
                                  Field Format:
                                  • Edit Box
                                  Field Behavior:
                                  • Allow field to be entered: In Find mode, In Browse mode
                                  • Touch keyboard type: Default for Data Type
                                  • Go to next field using: Tab key
                                  Yes

                                  Field Name: SQL Export::SQL Destination Type
                                  Field PropertiesCoordinatesField FormatField BehaviorQuick Find
                                  • Top: 130 pt
                                  • Left: 150 pt
                                  • Bottom: 151 pt
                                  • Right: 403 pt
                                  • Anchoring: Left, Top
                                  Field Format:
                                  • Edit Box
                                  Field Behavior:
                                  • Allow field to be entered: In Find mode, In Browse mode
                                  • Touch keyboard type: Default for Data Type
                                  • Go to next field using: Tab key
                                  Yes

                                  Field Name: SQL Export::SQL Connection String
                                  Field PropertiesCoordinatesField FormatField BehaviorQuick Find
                                  • Top: 153 pt
                                  • Left: 150 pt
                                  • Bottom: 174 pt
                                  • Right: 403 pt
                                  • Anchoring: Left, Top
                                  Field Format:
                                  • Edit Box
                                  Field Behavior:
                                  • Allow field to be entered: In Find mode, In Browse mode
                                  • Touch keyboard type: Default for Data Type
                                  • Go to next field using: Tab key
                                  Yes

                                  Field Name: SQL Export::SQL Username
                                  Field PropertiesCoordinatesField FormatField BehaviorQuick Find
                                  • Top: 176 pt
                                  • Left: 150 pt
                                  • Bottom: 197 pt
                                  • Right: 403 pt
                                  • Anchoring: Left, Top
                                  Field Format:
                                  • Edit Box
                                  Field Behavior:
                                  • Allow field to be entered: In Find mode, In Browse mode
                                  • Touch keyboard type: Default for Data Type
                                  • Go to next field using: Tab key
                                  Yes

                                  Field Name: SQL Export::SQL Password
                                  Field PropertiesCoordinatesField FormatField BehaviorQuick Find
                                  • Top: 199 pt
                                  • Left: 150 pt
                                  • Bottom: 220 pt
                                  • Right: 403 pt
                                  • Anchoring: Left, Top
                                  Field Format:
                                  • Edit Box
                                  Field Behavior:
                                  • Allow field to be entered: In Find mode, In Browse mode
                                  • Touch keyboard type: Default for Data Type
                                  • Go to next field using: Tab key
                                  Yes

                                  Field Name: SQL Export::MySQL Library Path
                                  Field PropertiesCoordinatesField FormatField BehaviorQuick Find
                                  • Top: 225 pt
                                  • Left: 150 pt
                                  • Bottom: 246 pt
                                  • Right: 403 pt
                                  • Anchoring: Left, Top
                                  Field Format:
                                  • Edit Box
                                  Field Behavior:
                                  • Allow field to be entered: In Find mode, In Browse mode
                                  • Touch keyboard type: Default for Data Type
                                  • Go to next field using: Tab key
                                  Yes

                                  Script Hierarchy

                                  Export

                                  Script NameExport
                                  Run script with full access privilegesOff
                                  Include In MenuYes
                                  Layouts that use this script
                                    Scripts that use this script
                                      Script Definition
                                      Script Steps
                                      • #Copy all tables of one file into a SQL database
                                      • #does not copy global fields.
                                      • #and containers loose their file names, just the data is copied.
                                      • #Connect to database
                                      • If [ 1 ]
                                      • Set Variable [ $Connection; Value:MBS("SQL.NewConnection") ]
                                      • #Tell plugin we want to use SQLite
                                      • Set Variable [ $result; Value:MBS("SQL.SetClient"; $Connection; SQL Export::SQL Destination Type) ]
                                      • #Tell plugin where MySQL or SQLite library is
                                      • Set Variable [ $result; Value:MBS("SQL.InternalSQLiteLibrary.Activate") ]
                                      • Set Variable [ $result; Value:MBS( "SQL.SetConnectionOption"; $Connection; "MYSQL.LIBS"; "/test/libmysqlclient.16.dylib" ) ]
                                      • #Connect to database in read/write/create mode. Creates new file if none exists.
                                      • Set Variable [ $result; Value:MBS("SQL.Connect"; $Connection; SQL Export::SQL Connection String; SQL Export::SQL Username; SQL Export::SQL Password) ]
                                      • If [ $result ≠ "OK" ]
                                      • Set Variable [ $result; Value:MBS("SQL.FreeConnection"; $Connection) ]
                                      • Show Custom Dialog [ Title: "Error"; Message: $result; Default Button: “OK”, Commit: “Yes”; Button 2: “Abbrechen”, Commit: “No” ]
                                      • Halt Script
                                      • End If
                                      • End If
                                      • #Set encoding to be UTF-8 for SQLite
                                      • If [ SQL Export::SQL Destination Type = "SQLite" ]
                                      • Set Variable [ $Command; Value:MBS("SQL.NewCommand"; $Connection; "PRAGMA encoding = \"UTF-8\";") ]
                                      • Set Variable [ $result; Value:MBS("SQL.Execute"; $Command) ]
                                      • Set Variable [ $result2; Value:MBS("SQL.FreeCommand"; $Command) ]
                                      • End If
                                      • #loop over all tables
                                      • If [ 1 ]
                                      • Set Variable [ $TableList; Value:MBS( "FM.SQL.Execute"; SQL Export::Database File; "SELECT DISTINCT BaseTableName from FILEMAKER_TABLES") ]
                                      • If [ MBS("IsError") ]
                                      • Set Variable [ $result; Value:MBS("SQL.FreeConnection"; $Connection) ]
                                      • Show Custom Dialog [ Title: "Error"; Message: $result; Default Button: “OK”, Commit: “Yes”; Button 2: “Abbrechen”, Commit: “No” ]
                                      • Halt Script
                                      • End If
                                      • Set Variable [ $TableCount; Value:MBS( "FM.SQL.RowCount"; $TableList) ]
                                      • Set Variable [ $TableIndex; Value:0 ]
                                      • Loop
                                      • Set Variable [ $FMBaseTableName; Value:MBS( "FM.SQL.Field"; $TableList; $TableIndex; 0 ) ]
                                      • Set Variable [ $BaseTableName; Value:$FMBaseTableName ]
                                      • #adjust name
                                      • Set Variable [ $BaseTableName; Value:Substitute($BaseTableName; " "; "_") ]
                                      • Set Variable [ $BaseTableName; Value:Substitute($BaseTableName; "|"; "_") ]
                                      • #create table
                                      • Set Variable [ $Command; Value:MBS("SQL.NewCommand"; $Connection; "CREATE TABLE \"" & $BaseTableName & "\" (_RowID INTEGER PRIMARY KEY AUTOINCREMENT) ") ]
                                      • Set Variable [ $result; Value:MBS("SQL.Execute"; $Command) ]
                                      • Set Variable [ $result2; Value:MBS("SQL.FreeCommand"; $Command) ]
                                      • If [ ($result ≠ "OK") and (Position($result; "already exists"; 1; 1) = 0) ]
                                      • Set Variable [ $result; Value:MBS("SQL.FreeConnection"; $Connection) ]
                                      • Set Variable [ $result; Value:MBS("FM.SQL.Release"; $TableList) ]
                                      • Show Custom Dialog [ Title: "Error"; Message: $result; Default Button: “OK”, Commit: “Yes”; Button 2: “Abbrechen”, Commit: “No” ]
                                      • Halt Script
                                      • End If
                                      • #loop over all fields
                                      • If [ 1 ]
                                      • Set Variable [ $FieldList; Value:MBS( "FM.SQL.Execute"; SQL Export::Database File; "SELECT DISTINCT BaseTableName, FieldName, FieldType, FieldID, FieldClass, FieldReps from FILEMAKER_TABLES, FILEMAKER_FIELDS WHERE FILEMAKER_TABLES.TableName = ? AND FILEMAKER_TABLES.TableName = FILEMAKER_FIELDS.TableName"; $BaseTableName) ]
                                      • If [ MBS("IsError") ]
                                      • Set Variable [ $result; Value:MBS("SQL.FreeConnection"; $Connection) ]
                                      • Set Variable [ $result; Value:MBS("FM.SQL.Release"; $TableList) ]
                                      • Show Custom Dialog [ Title: "Error"; Message: $FieldList; Default Button: “OK”, Commit: “Yes”; Button 2: “Abbrechen”, Commit: “No” ]
                                      • Halt Script
                                      • End If
                                      • Set Variable [ $FieldCount; Value:MBS( "FM.SQL.RowCount"; $FieldList) ]
                                      • Set Variable [ $FieldIndex; Value:0 ]
                                      • Set Variable [ $FieldNames; Value:"" ]
                                      • If [ $FieldCount > 0 ]
                                      • Set Variable [ $FMFieldNames; Value:"" ]
                                      • Loop
                                      • Set Variable [ $BaseTableName; Value:MBS( "FM.SQL.Field"; $FieldList; $FieldIndex; 0 ) ]
                                      • Set Variable [ $FMFieldName; Value:MBS( "FM.SQL.Field"; $FieldList; $FieldIndex; 1 ) ]
                                      • Set Variable [ $FieldName; Value:$FMFieldName ]
                                      • Set Variable [ $FieldType; Value:MBS( "FM.SQL.Field"; $FieldList; $FieldIndex; 2 ) ]
                                      • If [ Position ( $FieldType ; "global" ; 1 ; 1 ) = 0 ]
                                      • Set Variable [ $FieldID; Value:MBS( "FM.SQL.Field"; $FieldList; $FieldIndex; 3 ) ]
                                      • Set Variable [ $FieldClass; Value:MBS( "FM.SQL.Field"; $FieldList; $FieldIndex; 4 ) ]
                                      • Set Variable [ $FieldReps; Value:MBS( "FM.SQL.Field"; $FieldList; $FieldIndex; 5 ) ]
                                      • #adjust type and name
                                      • If [ $FieldType = "binary" ]
                                      • Set Variable [ $FieldType; Value:"BLOB" ]
                                      • Else
                                      • Set Variable [ $FieldType; Value:"varchar" ]
                                      • End If
                                      • Set Variable [ $FieldName; Value:Substitute($FieldName; " "; "_") ]
                                      • Set Variable [ $FieldName; Value:Substitute($FieldName; "|"; "_") ]
                                      • Set Variable [ $BaseTableName; Value:Substitute($BaseTableName; " "; "_") ]
                                      • Set Variable [ $BaseTableName; Value:Substitute($BaseTableName; "|"; "_") ]
                                      • #add field
                                      • Set Variable [ $Command; Value:MBS("SQL.NewCommand"; $Connection; "ALTER TABLE \"" & $BaseTableName & "\" ADD COLUMN " & $FieldName & " " & $FieldType & " DEFAULT NULL" ) ]
                                      • Set Variable [ $result; Value:MBS("SQL.Execute"; $Command) ]
                                      • Set Variable [ $result2; Value:MBS("SQL.FreeCommand"; $Command) ]
                                      • If [ ($result ≠ "OK") and (Position($result; "duplicate column name"; 1; 1) = 0) ]
                                      • Set Variable [ $result; Value:MBS("SQL.FreeConnection"; $Connection) ]
                                      • Set Variable [ $result; Value:MBS("FM.SQL.Release"; $TableList) ]
                                      • Set Variable [ $result; Value:MBS("FM.SQL.Release"; $FieldList) ]
                                      • Show Custom Dialog [ Title: "Error"; Message: $result; Default Button: “OK”, Commit: “Yes”; Button 2: “Abbrechen”, Commit: “No” ]
                                      • Halt Script
                                      • End If
                                      • Set Variable [ $FieldNames; Value:$FieldNames & $FieldName & ¶ ]
                                      • Set Variable [ $FMFieldNames; Value:If(Length($FMFieldNames) = 0; $FMFieldName; $FMFieldNames & "\", \"" & $FMFieldName ) ]
                                      • End If
                                      • #next
                                      • Set Variable [ $FieldIndex; Value:$FieldIndex + 1 ]
                                      • Exit Loop If [ $FieldIndex ≥ $FieldCount ]
                                      • End Loop
                                      • #move values
                                      • Set Variable [ $Records; Value:MBS( "FM.SQL.Execute"; SQL Export::Database File; "SELECT \"" & $FMFieldNames & "\" FROM \"" & $BaseTableName & "\""; $BaseTableName) ]
                                      • If [ MBS("IsError") ]
                                      • Set Variable [ $result; Value:MBS("SQL.FreeConnection"; $Connection) ]
                                      • Set Variable [ $result; Value:MBS("FM.SQL.Release"; $TableList) ]
                                      • Set Variable [ $result; Value:MBS("FM.SQL.Release"; $FieldList) ]
                                      • Show Custom Dialog [ Message: "Error: " & $Records; Default Button: “OK”, Commit: “Yes”; Button 2: “Abbrechen”, Commit: “No” ]
                                      • Halt Script
                                      • End If
                                      • Set Variable [ $RecordIndex; Value:0 ]
                                      • Set Variable [ $RecordCount; Value:MBS( "FM.SQL.RowCount"; $Records) ]
                                      • If [ $RecordCount > 0 ]
                                      • #copy values
                                      • Set Variable [ $InsertResult; Value:MBS( "FM.SQL.InsertRecordsToSQL"; $Records; $connection; $BaseTableName; $FieldNames) ]
                                      • If [ MBS("IsError") ]
                                      • Set Variable [ $result; Value:MBS("SQL.FreeCommand"; $Command) ]
                                      • Set Variable [ $result; Value:MBS("SQL.FreeConnection"; $Connection) ]
                                      • Set Variable [ $result; Value:MBS("FM.SQL.Release"; $TableList) ]
                                      • Set Variable [ $result; Value:MBS("FM.SQL.Release"; $FieldList) ]
                                      • Show Custom Dialog [ Title: "Error"; Message: $InsertResult; Default Button: “OK”, Commit: “Yes”; Button 2: “Abbrechen”, Commit: “No” ]
                                      • Halt Script
                                      • End If
                                      • End If
                                      • End If
                                      • Set Variable [ $result; Value:MBS("FM.SQL.Release"; $FieldList) ]
                                      • End If
                                      • Set Variable [ $result2; Value:MBS("SQL.Commit"; $Connection) ]
                                      • #next
                                      • Set Variable [ $TableIndex; Value:$TableIndex + 1 ]
                                      • Exit Loop If [ $TableIndex ≥ $TableCount ]
                                      • End Loop
                                      • Set Variable [ $result; Value:MBS("FM.SQL.Release"; $TableList) ]
                                      • Set Variable [ $result; Value:MBS("FM.SQL.Release"; $Records) ]
                                      • End If
                                      • Set Variable [ $result2; Value:MBS("SQL.Commit"; $Connection) ]
                                      • Set Variable [ $result; Value:MBS("SQL.FreeConnection"; $Connection) ]
                                      Fields used in this script
                                      Scripts used in this script
                                        Layouts used in this script
                                          Tables used in this script
                                          Table occurrences used by this script
                                          Custom Functions used by this script
                                            Custom menu set used by this script

                                              Download example: SQL Export

                                              Used functions:




                                              Links
                                              MBS FileMaker Plugins