Monkeybread Software - MBS FileMaker Plugin: MySQL example

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

MBS FileMaker Plugin Example Databases

MySQL example

All examples are included with download of MBS FileMaker Plugin.

MySQL example.fmp12

Overview
Tables	2
Relationships	0
Layouts	2
Scripts	6
Value Lists	0
Custom Functions	0
Custom Menus	33

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
tbl_SQL	10 fields defined, 3 records	tbl_SQL
tbl_customer	5 fields defined, 5 records	tbl_customer

Fields

Table Name: tbl_SQL - 10 Fields
Field Name	Type	Options	Comments	On Layouts	In Relationships	In Scripts	In Value Lists
FirstName	Normal, Text	Auto-Enter: Allow editing Validation: Only during data entry Storage: Repetitions: 1 Indexing: None Automatically create indexes as needed Index Language: German
LastName	Normal, Text	Auto-Enter: Allow editing Validation: Only during data entry Storage: Repetitions: 1 Indexing: None Automatically create indexes as needed Index Language: German
Birthday	Normal, Date	Auto-Enter: Allow editing Validation: Only during data entry Storage: Repetitions: 1 Indexing: None Automatically create indexes as needed Index Language: German
NumberOfOrders	Normal, Number	Auto-Enter: Allow editing Validation: Only during data entry Storage: Repetitions: 1 Indexing: None Automatically create indexes as needed Index Language: German
TotalSales	Normal, Number	Auto-Enter: Allow editing Validation: Only during data entry Storage: Repetitions: 1 Indexing: None Automatically create indexes as needed Index Language: German
PathToMySQLLibrary	Normal, Text	Auto-Enter: Allow editing Validation: Only during data entry Storage: Global Repetitions: 1 Index Language: German	old _PathToSQLiteLibrary	SQL header and customer list		OpenDatabase
Username	Normal, Text	Auto-Enter: Allow editing Validation: Only during data entry Storage: Global Repetitions: 1 Index Language: German		SQL header and customer list		OpenDatabase
Password	Normal, Text	Auto-Enter: Allow editing Validation: Only during data entry Storage: Global Repetitions: 1 Index Language: German		SQL header and customer list		OpenDatabase
Server_and_Database_name	Normal, Text	Auto-Enter: Allow editing Validation: Only during data entry Storage: Global Repetitions: 1 Index Language: German		SQL header and customer list		OpenDatabase
sSQL	Normal, Text	Auto-Enter: Allow editing Validation: Only during data entry Storage: Global Repetitions: 1 Index Language: German		SQL header and customer list		Excecute SQL

Table Name: tbl_customer - 5 Fields
Field Name	Type	Options	Comments	On Layouts	In Relationships	In Scripts	In Value Lists
FirstName	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 header and customer list tbl_customer		ExportRecord Import
LastName	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 header and customer list tbl_customer		ExportRecord Import
Birthday	Normal, Date	Auto-Enter: Allow editing Validation: Only during data entry Storage: Repetitions: 1 Indexing: None Automatically create indexes as needed Index Language: German		SQL header and customer list tbl_customer		ExportRecord Import
NumberOfOrders	Normal, Number	Auto-Enter: Allow editing Validation: Only during data entry Storage: Repetitions: 1 Indexing: None Automatically create indexes as needed Index Language: German		SQL header and customer list tbl_customer		ExportRecord Import
TotalSales	Normal, Number	Auto-Enter: Allow editing Validation: Only during data entry Storage: Repetitions: 1 Indexing: None Automatically create indexes as needed Index Language: German		SQL header and customer list tbl_customer		ExportRecord Import

Layout Objects: SQL header and customer list

Regular Fields

Field Name: tbl_customer::FirstName

Layout Name:

SQL header and customer list

Top: 240 pt
Left: 180 pt
Bottom: 257 pt
Right: 433 pt

Anchoring: Left, Top

Field Format:

Edit Box

Field Behavior:

Allow field to be entered: In Find mode, In Browse mode
Touch keyboard type: System Default
Go to next field using: Tab key

Yes

Field Name: tbl_customer::LastName

Layout Name:

SQL header and customer list

Top: 260 pt
Left: 180 pt
Bottom: 277 pt
Right: 433 pt

Anchoring: Left, Top

Field Format:

Edit Box

Field Behavior:

Allow field to be entered: In Find mode, In Browse mode
Touch keyboard type: System Default
Go to next field using: Tab key

Yes

Field Name: tbl_customer::Birthday

Layout Name:

SQL header and customer list

Top: 278 pt
Left: 180 pt
Bottom: 295 pt
Right: 433 pt

Anchoring: Left, Top

Field Format:

Edit Box

Field Behavior:

Allow field to be entered: In Find mode, In Browse mode
Touch keyboard type: System Default
Go to next field using: Tab key

Yes

Field Name: tbl_customer::NumberOfOrders

Layout Name:

SQL header and customer list

Top: 298 pt
Left: 180 pt
Bottom: 315 pt
Right: 433 pt

Anchoring: Left, Top

Field Format:

Edit Box

Field Behavior:

Allow field to be entered: In Find mode, In Browse mode
Touch keyboard type: System Default
Go to next field using: Tab key

Yes

Field Name: tbl_customer::TotalSales

Layout Name:

SQL header and customer list

Top: 316 pt
Left: 180 pt
Bottom: 333 pt
Right: 433 pt

Anchoring: Left, Top

Field Format:

Edit Box

Field Behavior:

Allow field to be entered: In Find mode, In Browse mode
Touch keyboard type: System Default
Go to next field using: Tab key

Yes

Field Name: tbl_SQL::PathToMySQLLibrary

Layout Name:

SQL header and customer list

Top: 56 pt
Left: 180 pt
Bottom: 76 pt
Right: 433 pt

Anchoring: Left, Top

Field Format:

Edit Box

Field Behavior:

Allow field to be entered: In Find mode, In Browse mode
Touch keyboard type: System Default
Go to next field using: Tab key

Field Name: tbl_SQL::Server_and_Database_name

Layout Name:

SQL header and customer list

Top: 94 pt
Left: 180 pt
Bottom: 114 pt
Right: 433 pt

Anchoring: Left, Top

Field Format:

Edit Box

Field Behavior:

Allow field to be entered: In Find mode, In Browse mode
Touch keyboard type: System Default
Go to next field using: Tab key

Field Name: tbl_SQL::Username

Layout Name:

SQL header and customer list

Top: 116 pt
Left: 180 pt
Bottom: 136 pt
Right: 433 pt

Anchoring: Left, Top

Field Format:

Edit Box

Field Behavior:

Allow field to be entered: In Find mode, In Browse mode
Touch keyboard type: System Default
Go to next field using: Tab key

Field Name: tbl_SQL::Password

Layout Name:

SQL header and customer list

Top: 137 pt
Left: 180 pt
Bottom: 157 pt
Right: 433 pt

Anchoring: Left, Top

Field Format:

Edit Box

Field Behavior:

Allow field to be entered: In Find mode, In Browse mode
Touch keyboard type: System Default
Go to next field using: Tab key

Field Name: tbl_SQL::sSQL

Layout Name:

SQL header and customer list

Top: 168 pt
Left: 180 pt
Bottom: 230 pt
Right: 775 pt

Anchoring: Left, Top

Field Format:

Edit Box

Field Behavior:

Allow field to be entered: In Find mode, In Browse mode
Touch keyboard type: System Default
Go to next field using: Tab key

Type:

Text: Create MySQL Database

Layout Name:

SQL header and customer list

Additional Properties:

Change to hand cursor over button
Rectangular

Top: 12 pt
Left: 12 pt
Bottom: 38 pt
Right: 163 pt

Anchoring: Left, Top

Perform Script [ “CreateDatabase”; Parameter: tbl_SQL::sSQL ]

Scripts:

CreateDatabase

Fields:

tbl_SQL::sSQL

Button Properties

Coordinates

Script/Script Step

Type:

Text: Export selected record to MySQL database

Layout Name:

SQL header and customer list

Additional Properties:

Change to hand cursor over button
Rectangular

Top: 12 pt
Left: 175 pt
Bottom: 38 pt
Right: 444 pt

Anchoring: Left, Top

Perform Script [ “ExportRecord” ]

Scripts:

ExportRecord

Button Properties

Coordinates

Script/Script Step

Type:

Text: Import Records from MySQL Database

Layout Name:

SQL header and customer list

Additional Properties:

Change to hand cursor over button
Rectangular

Top: 12 pt
Left: 454 pt
Bottom: 38 pt
Right: 687 pt

Anchoring: Left, Top

Perform Script [ “Import” ]

Scripts:

Import

Button Properties

Coordinates

Script/Script Step

Type:

Text: Trace on

Layout Name:

SQL header and customer list

Additional Properties:

Change to hand cursor over button
Rectangular

Top: 12 pt
Left: 696 pt
Bottom: 38 pt
Right: 787 pt

Anchoring: Left, Top

Set Variable [ $result; Value:MBS("Trace") ]

Button Properties

Coordinates

Script/Script Step

Type:

Text: Excecute SQL

Layout Name:

SQL header and customer list

Additional Properties:

Change to hand cursor over button
Rectangular

Top: 168 pt
Left: 12 pt
Bottom: 194 pt
Right: 127 pt

Anchoring: Left, Top

Perform Script [ “Excecute SQL”; Parameter: //Let ($s = tbl_SQL::sSQL;$s ) tbl_SQL::sSQL ]

Scripts:

Excecute SQL

Fields:

tbl_SQL::sSQL

Layout Objects: tbl_customer

Regular Fields

Field Name: tbl_customer::FirstName

Layout Name:

tbl_customer

Top: 24 pt
Left: 138 pt
Bottom: 41 pt
Right: 391 pt

Anchoring: Left, Top

Field Format:

Edit Box

Field Behavior:

Allow field to be entered: In Find mode, In Browse mode
Touch keyboard type: System Default
Go to next field using: Tab key

Yes

Field Name: tbl_customer::LastName

Layout Name:

tbl_customer

Top: 42 pt
Left: 138 pt
Bottom: 59 pt
Right: 391 pt

Anchoring: Left, Top

Field Format:

Edit Box

Field Behavior:

Allow field to be entered: In Find mode, In Browse mode
Touch keyboard type: System Default
Go to next field using: Tab key

Yes

Field Name: tbl_customer::Birthday

Layout Name:

tbl_customer

Top: 60 pt
Left: 138 pt
Bottom: 77 pt
Right: 259 pt

Anchoring: Left, Top

Field Format:

Edit Box

Field Behavior:

Allow field to be entered: In Find mode, In Browse mode
Touch keyboard type: System Default
Go to next field using: Tab key

Yes

Field Name: tbl_customer::NumberOfOrders

Layout Name:

tbl_customer

Top: 78 pt
Left: 138 pt
Bottom: 95 pt
Right: 217 pt

Anchoring: Left, Top

Field Format:

Edit Box

Field Behavior:

Allow field to be entered: In Find mode, In Browse mode
Touch keyboard type: System Default
Go to next field using: Tab key

Yes

Field Name: tbl_customer::TotalSales

Layout Name:

tbl_customer

Top: 96 pt
Left: 138 pt
Bottom: 113 pt
Right: 217 pt

Anchoring: Left, Top

Field Format:

Edit Box

Field Behavior:

Allow field to be entered: In Find mode, In Browse mode
Touch keyboard type: System Default
Go to next field using: Tab key

Next Script: [Excecute SQL]

Script Name	CreateDatabase
Run script with full access privileges	Off
Include In Menu	Yes
Layouts that use this script	SQL header and customer list
Scripts that use this script
Script Definition
Script Steps	Perform Script [ “OpenDatabase” ] Set Variable [ $Connection; Value:Get (ScriptResult) ] #Create a table Set Variable [ $Command; Value:MBS( "SQL.NewCommand"; $Connection; "CREATE TABLE tbl_test(id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), FirstName VARCHAR (32), LastName VARCHAR (32), Birthday DATE, NumberOfOrders INTEGER, TotalSales DOUBLE)" ) ] Set Variable [ $result; Value:MBS("SQL.Execute"; $Command) ] Set Variable [ $result2; Value:MBS("SQL.Commit"; $Connection) ] Set Variable [ $result2; Value:MBS("SQL.FreeCommand"; $Command) ] If [ $result ≠ "OK" ] Show Custom Dialog [ Message: "Error: " & $result; Default Button: “OK”, Commit: “Yes”; Button 2: “Abbrechen”, Commit: “No” ] Else Show Custom Dialog [ Message: "Database created."; Default Button: “OK”, Commit: “Yes”; Button 2: “Abbrechen”, Commit: “No” ] End If Set Variable [ $result2; Value:MBS("SQL.FreeConnection"; $Connection) ]
Fields used in this script
Scripts used in this script	OpenDatabase
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

Previous Script: [CreateDatabase]

Next Script: [ExportRecord]

Script Name	Excecute SQL
Run script with full access privileges	Off
Include In Menu	Yes
Layouts that use this script	SQL header and customer list
Scripts that use this script
Script Definition
Script Steps	Perform Script [ “OpenDatabase” ] Set Variable [ $Connection; Value:Get (ScriptResult) ] #Create a table Set Variable [ $SQL_string; Value:tbl_SQL::sSQL ] Show Custom Dialog [ Title: "SP"; Message: Get (ScriptParameter); Default Button: “OK”, Commit: “Yes”; Button 2: “Cancel”, Commit: “No” ] Set Variable [ $Command; Value:MBS( "SQL.NewCommand"; $Connection; $SQL_string; ) ] Set Variable [ $result; Value:MBS("SQL.Execute"; $Command) ] Set Variable [ $result2; Value:MBS("SQL.Commit"; $Connection) ] Set Variable [ $result2; Value:MBS("SQL.FreeCommand"; $Command) ] If [ $result ≠ "OK" ] Show Custom Dialog [ Message: "Error: " & $result; Default Button: “OK”, Commit: “Yes”; Button 2: “Abbrechen”, Commit: “No” ] Else Show Custom Dialog [ Message: "Database created."; Default Button: “OK”, Commit: “Yes”; Button 2: “Abbrechen”, Commit: “No” ] End If Set Variable [ $result2; Value:MBS("SQL.FreeConnection"; $Connection) ]
Fields used in this script	tbl_SQL::sSQL
Scripts used in this script	OpenDatabase
Layouts used in this script
Tables used in this script	tbl_SQL
Table occurrences used by this script	tbl_SQL
Custom Functions used by this script
Custom menu set used by this script

Previous Script: [Excecute SQL]

Next Script: [Import]

Script Name	ExportRecord
Run script with full access privileges	Off
Include In Menu	Yes
Layouts that use this script	SQL header and customer list
Scripts that use this script
Script Definition
Script Steps	Perform Script [ “OpenDatabase” ] Set Variable [ $Connection; Value:Get (ScriptResult) ] #Create insert command with setting values Set Variable [ $Command; Value:MBS("SQL.NewCommand"; $Connection; "INSERT INTO tbl_test (FirstName, LastName, Birthday, NumberOfOrders, TotalSales) VALUES (:1,:2,:3,:4,:5)") ] Set Variable [ $result; Value:MBS("SQL.SetParamAsText"; $Command; 1; tbl_customer::FirstName) ] Set Variable [ $result; Value:MBS("SQL.SetParamAsText"; $Command; 2; tbl_customer::LastName) ] Set Variable [ $result; Value:MBS("SQL.SetParamAsDate"; $Command; 3; tbl_customer::Birthday) ] Set Variable [ $result; Value:MBS("SQL.SetParamAsNumber"; $Command; 4; tbl_customer::NumberOfOrders) ] Set Variable [ $result; Value:MBS("SQL.SetParamAsFloat"; $Command; 5; tbl_customer::TotalSales) ] #Run it Set Variable [ $result; Value:MBS("SQL.Execute"; $Command) ] #Cleanup Set Variable [ $result2; Value:MBS("SQL.Commit"; $Connection) ] Set Variable [ $result2; Value:MBS("SQL.FreeCommand"; $Command) ] If [ $result ≠ "OK" ] Show Custom Dialog [ Message: "Error: " & $result; Default Button: “OK”, Commit: “No”; Button 2: “Abbrechen”, Commit: “No” ] Else Show Custom Dialog [ Message: "Record exported."; Default Button: “OK”, Commit: “Yes”; Button 2: “Abbrechen”, Commit: “No” ] End If Set Variable [ $result2; Value:MBS("SQL.FreeConnection"; $Connection) ]
Fields used in this script	tbl_customer::FirstName tbl_customer::LastName tbl_customer::Birthday tbl_customer::NumberOfOrders tbl_customer::TotalSales
Scripts used in this script	OpenDatabase
Layouts used in this script
Tables used in this script	tbl_customer
Table occurrences used by this script	tbl_customer
Custom Functions used by this script
Custom menu set used by this script

Previous Script: [ExportRecord]

Next Script: [OpenDatabase]

Script Name	Import
Run script with full access privileges	Off
Include In Menu	Yes
Layouts that use this script	SQL header and customer list
Scripts that use this script
Script Definition
Script Steps	Perform Script [ “OpenDatabase” ] Set Variable [ $Connection; Value:Get (ScriptResult) ] #Create select and run it Set Variable [ $Command; Value:MBS("SQL.NewCommand"; $Connection; "SELECT * FROM tbl_test") ] #Run it Set Variable [ $result; Value:MBS("SQL.Execute"; $Command) ] If [ $result ≠ "OK" ] Show Custom Dialog [ Message: "Error: " & $result; Default Button: “OK”, Commit: “No”; Button 2: “Abbrechen”, Commit: “No” ] Else Loop Set Variable [ $result; Value:MBS("SQL.FetchNext"; $Command) ] Exit Loop If [ $result ≠ 1 ] New Record/Request Set Field [ tbl_customer::FirstName; MBS("SQL.GetFieldAsText"; $command; "FirstName") ] Set Field [ tbl_customer::LastName; MBS("SQL.GetFieldAsText"; $command; "LastName") ] Set Field [ tbl_customer::Birthday; MBS("SQL.GetFieldAsDate"; $command; "Birthday") ] Set Field [ tbl_customer::TotalSales; MBS("SQL.GetFieldAsFloat"; $command; "TotalSales") ] Set Field [ tbl_customer::NumberOfOrders; MBS("SQL.GetFieldAsNumber"; $command; "NumberOfOrders") ] Commit Records/Requests [ No dialog ] End Loop Show Custom Dialog [ Message: "Records imported."; Default Button: “OK”, Commit: “Yes”; Button 2: “Abbrechen”, Commit: “No” ] End If #Cleanup Set Variable [ $result2; Value:MBS("SQL.FreeCommand"; $Command) ] // Set Variable [ $result2; Value:MBS("SQL.Commit"; $Connection) ] Set Variable [ $result2; Value:MBS("SQL.FreeConnection"; $Connection) ]
Fields used in this script	tbl_customer::FirstName tbl_customer::LastName tbl_customer::Birthday tbl_customer::TotalSales tbl_customer::NumberOfOrders
Scripts used in this script	OpenDatabase
Layouts used in this script
Tables used in this script	tbl_customer
Table occurrences used by this script	tbl_customer
Custom Functions used by this script
Custom menu set used by this script

Previous Script: [Import]

Next Script: [RegisterMBS]

Script Name	OpenDatabase
Run script with full access privileges	Off
Include In Menu	Yes
Layouts that use this script
Scripts that use this script	CreateDatabase Excecute SQL ExportRecord Import
Script Definition
Script Steps	Set Variable [ $Connection; Value:MBS("SQL.NewConnection") ] Set Variable [ $db; Value:tbl_SQL::Server_and_Database_name ] Set Variable [ $name; Value:tbl_SQL::Username ] Set Variable [ $pass; Value:tbl_SQL::Password ] #Tell plugin where SQLite library is Set Variable [ $result; Value:MBS("SQL.SetConnectionOption"; $Connection; "MYSQL.LIBS"; tbl_SQL::PathToMySQLLibrary) ] #Tell plugin we want to use MySQL Set Variable [ $result; Value:MBS("SQL.SetClient"; $Connection; "MySQL") ] #Connect to database in read/write/create mode. Creates new file if none exists. // Set Variable [ $result; Value:MBS("SQL.Connect"; $Connection; tbl_SQL::PathToMySQLLibrary & "@" & tbl_SQL::Server_and_Database_name & ";" & tbl_SQL::Username & ";" & tbl_SQL::Password & ";" & "MySQL") //Original string: //MBS("SQL.Connect"; $Connection; SQLite fun::Servername & "\@" & SQLite fun::Database_name;SQLite fun::Username; SQLite //fun::Password; "MySQL") ] Set Variable [ $result; Value:MBS("SQL.Connect"; $Connection;$db; $name; $pass; "MySQL") ] If [ $result = "OK" ] // Show Custom Dialog [ Title: "OK :)"; Message: "OK :)"; Default Button: “OK”, Commit: “Yes”; Button 2: “Cancel”, Commit: “No” ] Exit Script [ Result: $Connection ] Else Show Custom Dialog [ Message: "Error: " & $result; Default Button: “OK”, Commit: “Yes”; Button 2: “Abbrechen”, Commit: “No” ] Set Variable [ $result; Value:MBS("SQL.FreeConnection"; $Connection) ] Halt Script End If
Fields used in this script	tbl_SQL::Server_and_Database_name tbl_SQL::Username tbl_SQL::Password tbl_SQL::PathToMySQLLibrary
Scripts used in this script
Layouts used in this script
Tables used in this script	tbl_SQL
Table occurrences used by this script	tbl_SQL
Custom Functions used by this script
Custom menu set used by this script

Previous Script: [OpenDatabase]

Script Name	RegisterMBS
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	#/** This script registers the plug-in. You need to place you registation function into the script step below */ #Your registeration function goes in the next script step You can find your registration function in the email response that came when you purchaed the function It should look something like this. MBS("Register"; "Your Name"; "Window"; "5 seats"; 200803; 0123456789) Set Variable [ $result; Value:MBS("Register"; "Your Name"; "Window"; "5 seats"; 200803; 123456789) ] #If the code is correct it the $result variable with equal "OK" If [ $result = "OK" ] // Show Custom Dialog [ Title: "Sucess"; Message: "The plug-in was registered succesfully."; Default Button: “OK”, Commit: “No” ] Else Show Custom Dialog [ Title: "Error"; Message: "The plug-in was NOT registered correctly?"; Default Button: “OK”, Commit: “No” ] End If
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: MySQL example

Used functions: