Topics
All
MacOS
(Only)
Windows
(Only)
Linux
(Only, Not)
iOS
(Only, Not)
Components
Crossplatform Mac & Win
Server
Client
Old
Deprecated
Guides
Examples
Videos
New in version:
10.2
10.3
10.4
10.5
11.0
11.1
11.2
11.3
11.4
11.5
Statistic
FMM
Blog
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 |
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, 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.
Examples
Connect to an Oracle Database:
MBS("SQL.Connect"; $Connection; "MyDatabase"; "MyName"; "MyPass"; "Oracle")
Connect to a Microsoft SQL Server Database:
MBS("SQL.Connect"; $Connection; "MyServer\SQLEXPRESS@pubs"; ""; ""; "SQLServer")
Connect to a Sybase Database:
MBS("SQL.Connect"; $Connection; "MyServer@MyDatabase"; $name; $pass; "Sybase")
Connect to a DB2 Database:
MBS("SQL.Connect"; $Connection; "Server=myAddress:myPortNumber;Database=myDataBase;UID=myUsername;PWD=myPassword; CurrentSchema=mySchema;"; ""; ""; "DB2")
Connect to a Informix Database:
MBS("SQL.Connect"; $Connection; "Database=myDataBase;Host=192.168.10.10;Server=db_engine_tcp;Service=1492;"; $user; $pass; "Informix")
Connect to a InterBase Database:
MBS("SQL.Connect"; $Connection; "MyServer:MyDatabase"; $name; $pass; "InterBase")
Connect to a SQLBase Database:
MBS("SQL.Connect"; $Connection; "MyDatabase"; $name; $pass; "SQLBase")
Connect to a MySQL Database:
MBS("SQL.Connect"; $Connection; "MyServer@MyDatabase"; $name; $pass; "MySQL")
Connect to a PostgreSQL Database:
MBS("SQL.Connect"; $Connection; "MyServer@MyDatabase"; $name; $pass; "PostgreSQL")
Connect to a ODBC Database (e.g. Microsoft Access):
MBS("SQL.Connect"; $Connection; "Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\mydatabase.accdb;Uid=Admin;Pwd=;"; $name; $pass; "ODBC")
Connect to a local SQLite Database:
MBS("SQL.Connect"; $Connection; "/Users/cs/Desktop/test.db"; ""; ""; "SQLite")
Connect to Visual FoxPro via ODBC:
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")
Connect to a new in memory SQLite Database:
MBS("SQL.Connect"; $Connection; ":memory:"; ""; ""; "SQLite")
Connect to a MySQL Database via path:
MBS("SQL.Connect"; $Connection; "/Applications/MAMP/tmp/mysql/mysql.sock@MyDatabase"; $name; $pass; "MySQL")
Connect to Firebird 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")]
Connect to PostgreSQL with timeout and ssl:
MBS("SQL.Connect"; $Connection; "127.0.0.1,5432@dbname=postgres connect_timeout=10 sslmode=require"; $name; $pass; "PostgreSQL")
Connect to Microsoft SQL Server 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
Connect to ODBC via data source name:
MBS("SQL.Connect"; $Connection; "DSN"; $name; $pass; "ODBC")
Connect to MySQL 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)]
Connect to Microsoft SQL Server 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")
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 ) ]
Connect to Firebird 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" )
See also
- SQL.SetClient
- SQL.SetConnectionOption
- SSH.Connect
- SSH.Disconnect
- SSH.IsAuthenticated
- SSH.New
- SSH.SessionHandshake
- SSH.Tunnel.Messages
- SSH.Tunnel.Run
- SSH.UserAuthPublicKey
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
- SQL to other databases/Firebird Query
- SQL to other databases/Microsoft Access Execute
- SQL to other databases/Microsoft SQL Execute
- SQL to other databases/Microsoft SQL Server Connect
- SQL to other databases/Microsoft SQL Server Query
- SQL to other databases/MySQL example
- SQL to other databases/ODBC Query
- SQL to other databases/SQLite blob
- SQL to other databases/SQLite fun
- Third Party/FileMaker Snippet Storage
Blog Entries
- MBS FileMaker Plugin, version 11.0pr6
- Using TLS when connecting to MySQL server
- ODBC driver and MBS SQL Functions in FileMaker
- Can FileMaker connect to a Microsoft Access database?
- Tip of the day: Connect to MySQL and run a query
- SQLite in memory databases
- Connecting to Microsoft Visual FoxPro
FileMaker Magazin
Created 18th August 2014, last changed 17th August 2021
SQL.Commit - SQL.ConvertDateTimeFromSQL
Feedback: Report problem or ask question.
Links
MBS Xojo PDF Plugins
