Monkeybread Software - MBS FileMaker Plugin: SQL Export

Components	All	New	MacOS		Windows		Linux		iOS
Examples	Mac & Win		Server	Client	Guides	Statistic	FMM	Blog	Deprecated	Old

MBS FileMaker Plugin Example Databases

SQL Export

All examples are included with download of MBS FileMaker Plugin.

SQL Export.fmp12

Overview
Tables	1
Relationships	0
Layouts	1
Scripts	1
Value Lists	0
Custom Functions	0
Custom Menus	0

File Options
Default custom menu set	[Standard FileMaker Menus]
When opening file
Minimum allowed version	12.0
Login using	Account Name; Account= Admin
Allow user to save password	Off
Require iOS passcode	Off
Switch to layout	Off
Hide all toolbars	Off
Script triggers
OnFirstWindowOpen	Off
OnLastWindowClose	Off
OnWindowOpen	Off
OnWindowClose	Off
OnFileAVPlayerChange	Off
Thumbnail Settings
Generate Thumbnails	On; 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 Name	Type	Options	Comments	On Layouts	In Relationships	In Scripts	In Value Lists
Database File	Normal, Text	Auto-Enter: Allow editing Validation: Only during data entry Storage: Repetitions: 1 Indexing: None Automatically create indexes as needed Index Language: German		SQL Export		Export
Export folder	Normal, Text	Auto-Enter: Allow editing Validation: Only during data entry Storage: Repetitions: 1 Indexing: None Automatically create indexes as needed Index Language: German		SQL Export
SQL Destination Type	Normal, Text	Auto-Enter: Allow editing Validation: Only during data entry Storage: Repetitions: 1 Indexing: None Automatically create indexes as needed Index Language: German		SQL Export		Export
SQL Connection String	Normal, Text	Auto-Enter: Allow editing Validation: Only during data entry Storage: Repetitions: 1 Indexing: None Automatically create indexes as needed Index Language: German		SQL Export		Export
SQL Username	Normal, Text	Auto-Enter: Allow editing Validation: Only during data entry Storage: Repetitions: 1 Indexing: None Automatically create indexes as needed Index Language: German		SQL Export		Export
SQL Password	Normal, Text	Auto-Enter: Allow editing Validation: Only during data entry Storage: Repetitions: 1 Indexing: None Automatically create indexes as needed Index Language: German		SQL Export		Export
MySQL Library Path	Normal, Text	Auto-Enter: Allow editing Validation: Only during data entry Storage: Repetitions: 1 Indexing: None Automatically create indexes as needed Index Language: German		SQL Export

Layout Objects: SQL Export

Regular Fields

Field Name: SQL Export::Database File

Layout Name:

SQL Export

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

Layout Name:

SQL Export

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

Layout Name:

SQL Export

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

Layout Name:

SQL Export

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

Layout Name:

SQL Export

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

Layout Name:

SQL Export

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

Layout Name:

SQL Export

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 Name	Export
Run script with full access privileges	Off
Include In Menu	Yes
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 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" ) ] #Tell plugin we want to use SQLite Set Variable [ $result; Value:MBS("SQL.SetClient"; $Connection; SQL Export::SQL Destination Type) ] #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	SQL Export::SQL Destination Type SQL Export::SQL Connection String SQL Export::SQL Username SQL Export::SQL Password SQL Export::Database File
Scripts used in this script
Layouts used in this script
Tables used in this script	SQL Export
Table occurrences used by this script	SQL Export
Custom Functions used by this script
Custom menu set used by this script

Download example: SQL Export

Used functions: