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

SQL.Connect

Opens the connection to a data source.

Component Version macOS Windows Linux Server iOS SDK
SQL 2.6 ✅ Yes ✅ Yes ✅ Yes ✅ Yes ✅ Yes
MBS( "SQL.Connect"; Connection; DBString { ; UserName; Password; Client } )   More

Parameters

Parameter Description Example Flags
Connection The connection reference number gained with SQL.NewConnection. $Connection
DBString Database connection string.
Please specify port with comma after IP, not double colon.		 "/test/database.db"
UserName The user name to use when establishing the connection. "" Optional
Password The password to use when establishing the connection. "" Optional
Client Optional, one of the following client values: CubeSQL, SQLAnywhere, ODBC, Oracle, SQLServer, Firebird, InterBase, SQLBase, DB2, DuckDB, Informix, Sybase, MySQL, PostgreSQL or SQLite. "SQLite" Optional

Result

Returns "OK" or error message.

Description

Opens the connection to a data source.
Be aware that for most database servers, you need to specify client library with SQL.SetConnectionOption.

For server scripting, please use one connection for each script running on server, so multiple scripts running at the same data don't access the same connection or command objects in memory. And each connection has its own transaction and error state.

see also
https://www.sqlapi.com/ApiDoc/class_s_a_connection.html

If you get a crash here on MacOS, please check if crash report says Code Signature Invalid. Then you may need to code sign the dylib yourself to match the code signature of your runtime application.

Connect to Microsoft Access, FileMaker Server (or Pro), Microsoft Visual FoxPro and others via ODBC.

For SQLite, you can use SQL.SQLite3.SetKey to set encryption key to use for encrypted databases. Use SQL.InternalSQLiteLibrary.Activate to activate the internal SQLite library.

We have a collection of library files here:
https://www.monkeybreadsoftware.com/filemaker/files/Libs/

If you have trouble with DLLs for databases not loading on Windows, please check if you have new Visual Studio Runtime libraries installed. If you need more than one DLL, please use Process.SetDllDirectory to point to the folder to find the DLL files. It can help to put the DLL in the same folder as the FileMaker (server) application.

Examples

Sybase: Connect to a database:

MBS("SQL.Connect"; $Connection; "MyServer@MyDatabase"; $name; $pass; "Sybase")

DB2: Connect to a database:

MBS("SQL.Connect"; $Connection; "Server=myAddress:myPortNumber;Database=myDataBase;UID=myUsername;PWD=myPassword; CurrentSchema=mySchema;"; ""; ""; "DB2")

Informix: Connect to a database:

MBS("SQL.Connect"; $Connection; "Database=myDataBase;Host=192.168.10.10;Server=db_engine_tcp;Service=1492;"; $user; $pass; "Informix")

InterBase: Connect to a database:

MBS("SQL.Connect"; $Connection; "MyServer:MyDatabase"; $name; $pass; "InterBase")

SQLBase: Connect to a database:

MBS("SQL.Connect"; $Connection; "MyDatabase"; $name; $pass; "SQLBase")

MySQL: Connect to a database:

MBS("SQL.Connect"; $Connection; "MyServer@MyDatabase"; $name; $pass; "MySQL")

MySQL: Connect and run a query:

#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)]

MySQL: Connect to a database via path:

MBS("SQL.Connect"; $Connection; "/Applications/MAMP/tmp/mysql/mysql.sock@MyDatabase"; $name; $pass; "MySQL")

MySQL: Run SSH Tunnel to connect to MySQL:

# some settings like local IP/Port
Set Variable [ $LocalIP ; Value: "127.0.0.1" ]
Set Variable [ $LocalPort ; Value: 3306 ]

# the SSH server to connect through
Set Variable [ $SSHServerIP ; Value: "myserver.test" ]

# database to connect
Set Variable [ $DatabaseIP ; Value: "db.myserver.test" ]
Set Variable [ $DatabasePort ; Value: 3306 ]

# create connection
Set Variable [ $ssh ; Value: MBS( "SSH.New" ) ]
Set Variable [ $r ; Value: MBS( "SSH.Connect"; $ssh; $SSHServerIP ) ]
Set Variable [ $r ; Value: MBS( "SSH.SessionHandshake"; $ssh) ]

# using private key file here, but username+password is also possible
Set Variable [ $r ; Value: MBS( "SSH.UserAuthPublicKeyFile"; $ssh; "username"; "" ; $keypair_path ; "password" ) ]

Set Variable [ $auth ; Value: MBS( "SSH.IsAuthenticated"; $ssh ) ]
# must be 1

Set Variable [ $r ; Value: MBS( "SSH.Tunnel.Run"; $ssh; $DatabaseIP; $DatabasePort; $LocalIP; $LocalPort) ]
Pause/Resume Script [ Duration (seconds): .5 ]

Set Variable [ $tunnel_mess ; Value: MBS( "SSH.Tunnel.Messages"; $ssh ) ]
# now this should show that tunnel is waiting.
 
Set Variable [ $Connection ; Value: MBS("SQL.NewConnection") ]
Set Variable [ $r ; Value: MBS("SQL.SetConnectionOption"; $Connection; "MYSQL.LIBS"; $mysql_path ) ]

Set Variable [ $r ; Value: MBS("SQL.SetClient"; $Connection; "MySQL") ]
Set Variable [ $r ; Value: MBS("SQL.Connect"; $Connection; $LocalIP & "," & $LocalPort & "@DatabaseName"; "UserName"; "password"; "MySQL") ]

Set Variable [ $tunnel_mess ; Value: MBS( "SSH.Tunnel.Messages"; $ssh ) ]
Show Custom Dialog [ $tunnel_mess & ¶ & $r ]
# should show OK for connect and for tunnel that it's forwarding data

# do query here

# disconnect
Set Variable [ $r ; Value: MBS("SQL.FreeConnection"; $Connection) ]
Set Variable [ $r ; Value: MBS( "SSH.Tunnel.Cancel"; $ssh ) ]
Pause/Resume Script [ Duration (seconds): .1 ]
Set Variable [ $r ; Value: MBS( "SSH.Disconnect"; $ssh ) ]
Set Variable [ $r ; Value: MBS( "SSH.Release"; $ssh ) ]

MySQL: Configure MariaDB or MySQL to use standard quotes around table and field names:

Set Variable [ $cmd ; Value: MBS("SQL.NewCommand"; $Connection; "SET SESSION SQL_MODE=ANSI_QUOTES;") ]
Set Variable [ $cmd ; Value: MBS( "SQL.Execute"; $cmd ) ]
Set Variable [ $r ; Value: MBS( "SQL.FreeCommand"; $cmd) ]

PostgreSQL: Connect to a database:

MBS("SQL.Connect"; $Connection; "MyServer@MyDatabase"; $name; $pass; "PostgreSQL")

PostgreSQL: Connect with a script:

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

# Tell plugin where PostgreSQL library is
Set Variable [ $path ; Value: "/Users/cs/Library/Application Support/FileMaker/Extensions/libpq.5.dylib" ]
Set Variable [ $r ; Value: MBS("SQL.SetConnectionOption"; $Connection; "LIBPQ.LIBS"; $path) ]

# you can include an app name to show in server administration views
Set Variable [ $r ; Value: MBS("SQL.SetConnectionOption"; $Connection; "APPNAME"; "FileMaker") ]

# Connection string in the following format:
# "" or "@" - empty string or '@' character, connects to a local server
<database_name> or @<database_name> - connects to a database with the specified name on local server
<serve_name>@ - connects to the specified server
<server_name>@<database_name> - connects to a database with the specified name on the specified server
Set Variable [ $result ; Value: MBS("SQL.Connect"; $Connection; "server@database"; "user"; "password"; "PostgreSQL") ]

If [ $result ≠ "OK" ]
    Show Custom Dialog [ "Error: " & $result ]
    Set Variable [ $result ; Value: MBS("SQL.FreeConnection"; $Connection) ]
    Halt Script
End If

PostgreSQL: Connect with timeout and ssl:

MBS("SQL.Connect"; $Connection; "127.0.0.1,5432@dbname=postgres connect_timeout=10 sslmode=require"; $name; $pass; "PostgreSQL")

ODBC: Connect to a database over ODBC using the data source name:

MBS("SQL.Connect"; $Connection; "DSN"; $name; $pass; "ODBC")

ODBC: Connect to a Microsoft Access database:

MBS("SQL.Connect"; $Connection; "Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\mydatabase.accdb;Uid=Admin;Pwd=;"; $name; $pass; "ODBC")

ODBC: Connect to Visual FoxPro:

MBS("SQL.Connect"; $Connection; "Driver={Microsoft Visual FoxPro Driver};SourceType=DBC;SourceDB=c:\myvfpdb.dbc;Exclusive=No;NULL=NO;Collate=Machine;BACKGROUNDFETCH=NO;DELETED=NO;"; ""; ""; "ODBC")

SQLite: Connect to a local database:

MBS("SQL.Connect"; $Connection; "/Users/cs/Desktop/test.db"; ""; ""; "SQLite")

SQLite: Connect to a new in memory database:

MBS("SQL.Connect"; $Connection; ":memory:"; ""; ""; "SQLite")

Firebird: Connect to a database:

# start a new connection
Set Variable [$Connection; Value:MBS( "SQL.NewConnection" )]
# if you like set where to find the libraries
Set Variable [$r; Value:MBS( "SQL.SetConnectionOption"; $Connection; "IBASE.LIBS"; "c:\Firebird\fbclient.dll" )]
# connect to database
Set Variable [$r; Value:MBS("SQL.Connect"; $Connection; "MyDatabase"; "MyName"; "MyPass"; "Firebird")]

Firebird: Connect with custom port:

// if the service name is gds_db and the port number is 3051:
MBS( "SQL.Connect"; $Connection; "alice/3051:/data/teaparty.fdb"; $Username; $Password; "FireBird" )

SQL Server: Connect to a Microsoft SQL Server Database:

MBS("SQL.Connect"; $Connection; "MyServer\SQLEXPRESS@pubs;MARS_Connection=yes"; ""; ""; "SQLServer")
// MARS_Connection=yes option for multiple recordsets on a connection.

SQL Server: Connect from Mac via FreeTDS library:

Set Variable [$Connection; Value:MBS("SQL.NewConnection")]
#Tell plugin we want to use Microsoft SQL Server via ODBC
Set Variable [$result; Value:MBS("SQL.SetClient"; $Connection; "ODBC")]
#Tell plugin where freetds library is
Set Variable [$path; Value: "/Users/cs/Desktop/libtdsodbc.dylib"]
#Connect to database
Set Variable [$result; Value:MBS("SQL.Connect"; $Connection; "DRIVER={" & $path & "};Server=192.168.2.32;UId=SA;PWD=test;Database=test;TDS_VERSION=7.2;Port=1433")]
# or TDS_VERSION=8.0;
If [$result  ≠  "OK"]
    Show Custom Dialog ["Error: " & $result]
    Set Variable [$result; Value:MBS("SQL.FreeConnection"; $Connection)]
    Halt Script
End If

SQL Server: Connect from Mac via FreeTDS library and point to library in connection string:

MBS("SQL.Connect"; $connection; "DRIVER=/Applications/FileMaker Pro 16 Advanced/libtdsodbc.dylib;Server=135.68.6.82;UId=FlavorShip;PWD=xxx;Database=myDB;TDS_VERSION=7.2;Port=1433"; "ODBC")

Oracle: Connect to a database:

MBS("SQL.Connect"; $Connection; "MyDatabase"; "MyName"; "MyPass"; "Oracle")

Oracle: Connect with SID:

MBS("SQL.Connect";$Connection;"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.128.12.150)(PORT=1521))(CONNECT_DATA=(SID=STRMTL)))";"User”;"Password";"Oracle") ]

Oracle: Connect as SysDBA

Set Variable [ $sql; Value: MBS("SQL.NewConnection") ]
Set Variable [ $r; Value: MBS("SQL.SetConnectionOption"; $sql; "OCI8.LIBS"; "C:\Oracle\bin\oci.dll") ]
Set Variable [ $r; Value: MBS("SQL.SetConnectionOption"; $sql; "ConnectAs"; "SYSDBA") ]
Set Variable [ $r; Value: MBS("SQL.Connect"; $sql; "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=LOCALHOST)(PORT=1521))(CONNECT_DATA=(SID=orcl)))"; "sys"; "xxx"; "Oracle") ]

CubeSQL: Connect to via script:

# start a new connection
Set Variable [$Connection; Value:MBS( "SQL.NewConnection" )]

# Point to where we can find the library
Set Variable [$r; Value: MBS( "SQL.SetConnectionOption"; $Connection; "CUBESQL.LIBS"; "c:\CubeSQL\cubesql.dll" ) ]

# you can put in an application name for monitoring who connects
Set Variable [$r; Value: MBS( "SQL.SetConnectionOption"; $Connection; "APPNAME"; "FileMaker Test") ]

// we use a short timeout to not block too long if the server is not reachable
Set Variable [$r; Value: MBS( "SQL.SetConnectionOption"; $Connection; "ConnectionTimeout"; "5") ]

// optionally enable encryption
Set Variable [$r; Value: MBS( "SQL.SetConnectionOption"; $Connection; "ConnectionEncryption"; "AES128") // or "AES192" or "AES256" ]
   
# connect to database
Set Variable [$r; Value: MBS("SQL.Connect"; $Connection; "localhost@mydatabase.sqlite"; "admin"; "admin"; "CubeSQL")]

DuckDB: Connect to local database:

Set Variable [ $Connection ; Value: MBS("SQL.NewConnection") ]
# Tell plugin where PostgreSQL library is
Set Variable [ $result ; Value: MBS("SQL.SetConnectionOption"; $Connection; "DUCKDB.LIBS"; "/opt/homebrew/Cellar/duckdb/1.2.1/lib/libduckdb.dylib") ]
# Connect to database
Set Variable [ $result ; Value: MBS("SQL.Connect"; $Connection; "/Users/cs/Desktop/test.duckdb"; ""; ""; "DuckDB") ]
If [ $result ≠ "OK" ]
    Show Custom Dialog [ "Error: " & $result ]
    Set Variable [ $result ; Value: MBS("SQL.FreeConnection"; $Connection) ]
Else
    # do something here
End If
# Cleanup
Set Variable [ $result2 ; Value: MBS("SQL.FreeConnection"; $Connection) ]

See also

Release notes

  • Version 11.0
    • Fixed an issue with SQL.Connect function connecting to MySQL when server doesn't know utf8mb4 text encoding.

Example Databases

Blog Entries

This function is free to use.

Created 18th August 2014, last changed 6th April 2025


SQL.Commit - SQL.ConvertDateTimeFromSQL