« English MBS Plugin tr… | Home | FileMaker Custom func… »

Tip of the day: Connect to MySQL and run a query

With MBS Plugins you can connect to various databases from Xojo and FileMaker.
As you may know we support Centura SQLBase, DB2, Firebird, Informix, InterBase, MariaDB, Microsoft Access, Microsoft SQL Server, MySQL, ODBC, Oracle Database Server, PostgreSQL, SQL Anywhere, SQLite, SQLCipher and Sybase. 
 
Here an example script for FileMaker using MySQL: 
 

#Start a new connection

Set Variable [$Connection; Value:MBS("SQL.NewConnection")]

#Tell plugin where MySQL library is (put it where you like)

Set Variable [$result; Value:MBS("SQL.SetConnectionOption"; $Connection; "MYSQL.LIBS"; "/Users/cs/Desktop/libmysqlclient.dylib")]

#Connect to a mysql database:

Set Variable [$result; Value:MBS("SQL.Connect"; $Connection; "192.168.11.51@Server_Config"; "user"; "password"; "MySQL")]

If [$result  ≠  "OK"]

#Connection failed

Show Custom Dialog ["Error: " & $result]

Set Variable [$result; Value:MBS("SQL.FreeConnection"; $Connection)]

Halt Script

Else

#Create a query:

Set Variable [$Command; Value:MBS("SQL.NewCommand"; $Connection; "SELECT * FROM Server_Config where ServerName=:Name")]

#If you use parameters, you can fill them here

Set Variable [$r; Value:MBS("SQL.SetParamAsText"; $Command; "Name"; "MacMini")]

#Execute it

Set Variable [$result; Value:MBS("SQL.Execute"; $Command)]

If [$result  ≠ "OK"]

Set Field [MySQL Query::Result; $result]

Show Custom Dialog ["Error: " & $result]

Else

Set Variable [$lines; Value:""]

Set Variable [$fieldcount; Value:MBS("SQL.FieldCount"; $command)]

Loop

#call FetchNext to get the next record

Set Variable [$result; Value:MBS("SQL.FetchNext"; $Command)]

Exit Loop If [$result  ≠ 1]

Set Variable [$line; Value:""]

Set Variable [$i; Value:1]

Loop

#We query field names and values to show them later

Set Variable [$v; Value:MBS("SQL.GetFieldAsText"; $command; $i)]

Set Variable [$n; Value:MBS("SQL.GetFieldName"; $command; $i)]

Set Variable [$line; Value:$line &  $n & ": " & $v & ¶]

Set Variable [$i; Value:$i+1]

Exit Loop If [$i > $fieldCount]

End Loop

Set Variable [$lines; Value:$lines & ($line & ¶)]

End Loop

Set Variable [$lines; Value:$lines & ¶]

Show Custom Dialog ["Result from Query:"; $lines]

End If

#Cleanup

Set Variable [$result2; Value:MBS("SQL.FreeCommand"; $Command)]

End If

Set Variable [$result2; Value:MBS("SQL.FreeConnection"; $Connection)]

 
As you notice some database types like MySQL, PostgreSQL, DB2, Oracle, Firebird and others need a client library. We point the plugin to load the client library which must match the bit number from FileMaker or Xojo. Once we are connected, we can run several queries over the connnection and usually keep it open while the application does its work.
Claris FileMaker Plugin
07 02 17 - 09:22