| Components | All | New | MacOS | Windows | Linux | iOS | ||||
| Examples | Mac & Win | Server | Client | Guides | Statistic | FMM | Blog | Deprecated | Old | |
SQL.SetConnectionOption
Sets a string value of a specific connection or command option.
| Component | Version | macOS | Windows | Linux | Server | iOS SDK |
| SQL | 2.6 | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes |
Parameters
| Parameter | Description | Example |
|---|---|---|
| Connection | The connection reference number gained with SQL.NewConnection. | $Connection |
| Key | A string key of the connection or command option. | "" |
| Value | The new option value. | "" |
Result
Returns "OK" or error message.
Description
Sets a string value of a specific connection or command option.Call this method to set value(s) of server specific connection or command options. See Server specific notes.
SetOption method is safe to use in cross-server applications. An option related to a particular server does not affect others.
see also:
https://www.sqlapi.com/ApiDoc/class_s_a_connection.html
We have a collection of library files here:
https://www.monkeybreadsoftware.com/filemaker/files/Libs/
Examples
All: Requests 100 rows to be prefetched:
MBS( "SQL.SetConnectionOption"; $Connection; "PreFetchRows"; "100" )
# will propagate down to all commands on that connection.
CubeSQL: Set the path to DLL for Windows:
MBS( "SQL.SetConnectionOption"; $Connection; "CUBESQL.LIBS"; "c:\CubeSQL\cubesql.dll" )
CubeSQL: Enable SSL connection:
MBS( "SQL.SetConnectionOption"; $Connection; "ConnectionEncryption"; "SSL_AES256" ) &
MBS( "SQL.SetConnectionOption"; $Connection; "SSLCertificatePath"; $path )
CubeSQL: Set connection timeout for CubeSQL:
MBS( "SQL.SetConnectionOption"; $Connection; "ConnectionTimeout"; "30" )
DB2: Set path for libraries:
MBS( "SQL.SetConnectionOption"; $Connection; "DB2CLI.LIBS"; "c:\DB2\db2cli.dll" )
Informix: Set path for libraries on Windows:
MBS( "SQL.SetConnectionOption"; $Connection; "INFCLI.LIBS"; "c:\Informix\ICLIT09B.DLL" )
InterBase: Set Firebird libraries:
MBS( "SQL.SetConnectionOption"; $Connection; "IBASE.LIBS"; "c:\Firebird\fbclient.dll" )
MySQL: Set path to library:
# Windows
Set Variable [ $r; MBS( "SQL.SetConnectionOption"; $Connection; "MYSQL.LIBS"; "c:\MySQL\libmySQL.dll" ) ]
# macOS
Set Variable [ $r; MBS( "SQL.SetConnectionOption"; $Connection; "MYSQL.LIBS"; "/test/libmysqlclient.16.dylib" ) ]
MySQL: Set path for Ubuntu 22:
Set Variable [ $r; MBS( "SQL.SetConnectionOption"; $Connection;
"MYSQL.LIBS"; "/usr/lib/aarch64-linux-gnu/libmysqlclient.so.21" ) ]
# needs mysql client library package installed. Your version may not be 21 for the library!
MySQL: Enable multi statements and results explicitly for MySQL:
MBS( "SQL.SetConnectionOption"; $Connection; "CLIENT_MULTI_STATEMENTS"; "true" ) &
MBS( "SQL.SetConnectionOption"; $Connection; "CLIENT_MULTI_RESULTS"; "true" )
MySQL: Set Timeout options:
MBS( "SQL.SetConnectionOption"; $Connection; "MYSQL_OPT_CONNECT_TIMEOUT"; 10 )
MBS( "SQL.SetConnectionOption"; $Connection; "MYSQL_OPT_READ_TIMEOUT"; 10 )
MBS( "SQL.SetConnectionOption"; $Connection; "MYSQL_OPT_WRITE_TIMEOUT"; 10 )
MySQL: Store RecordSet locally:
MBS( "SQL.SetConnectionOption"; $Connection; "HandleResult"; "Store" )
MySQL: Set SSL Cipher:
MBS( "SQL.SetConnectionOption"; $Connection; "MYSQL_SSL_CIPHER"; "DHE-RSA-AES256-SHA" )
MySQL: Enable SSL:
# all options optional, but you need at least one set to activate SSL.
MBS( "SQL.SetConnectionOption"; $Connection; "MYSQL_SSL_KEY"; "/Users/cs/Desktop/key.pem" )
MBS( "SQL.SetConnectionOption"; $Connection; "MYSQL_SSL_CERT"; "/Users/cs/Desktop/cert.pem" )
MBS( "SQL.SetConnectionOption"; $Connection; "MYSQL_SSL_CA"; "/Users/cs/Desktop/cacert.pem" )
MBS( "SQL.SetConnectionOption"; $Connection; "MYSQL_SSL_CAPATH"; "" )
MBS( "SQL.SetConnectionOption"; $Connection; "MYSQL_SSL_CIPHER"; "DHE-RSA-AES256-SHA" )
MySQL: Enable SSL for MySQL or MariaDB:
# file with root certificates
Set Variable [ $sslPath ; Value: MBS( "Path.AddPathComponent"; $appPath ; "cacert.pem") ]
Set Variable [ $result ; Value: MBS("SQL.SetConnectionOption"; $$Connection; "MYSQL_SSL_CA"; $sslPath) ]
# cipher to use
Set Variable [ $result ; Value: MBS("SQL.SetConnectionOption"; $$Connection; "MYSQL_SSL_CIPHER"; "DHE-RSA-AES256-SHA") ]
# require SSL
Set Variable [ $result ; Value: MBS("SQL.SetConnectionOption"; $$Connection; "MYSQL_OPT_SSL_MODE"; "SSL_MODE_REQUIRED") ]
MySQL: Write library to disk from container and initialize:
# your license key if you have one
Set Variable [$SerialNumber; Value:""]
# where we store the library file
Set Variable [$tempFolder; Value:MBS( "Folders.UserTemporary" )]
If [MBS( "IsMacOS" )]
# library for Mac from Container
Set Variable [$container; Value:MySQL::MySQLLibMacContainer]
Set Variable [$filename; Value:"libmysqlclient.dylib"]
Else If [MBS( "IsWindows" )]
# library for Win from Container
Set Variable [$container; Value:MySQL::MySQLLibWinContainer]
Set Variable [$filename; Value:"libmysqlclient.dll"]
Else
Exit Script []
End If
Set Variable [$filepath; Value:MBS( "Path.AddPathComponent"; $tempFolder; $filename)]
# write file
Set Variable [$r; Value:MBS( "Files.WriteFile"; $container; $filepath)]
If [$r ≠ "OK"]
Show Custom Dialog ["Writing file failed"; $r]
End If
# now initialize
Set Variable [$r; Value:MBS( "SQL.SetConnectionOption"; $connection; "MYSQL.LIBS"; $filepath)]
If [$r ≠ "OK"]
Show Custom Dialog ["SQL initialization failed"; $r]
End If
MySQL: Set SSL Mode:
MBS( "SQL.SetConnectionOption"; $Connection; "MYSQL_OPT_SSL_MODE"; "SSL_MODE_REQUIRED" )
// Valid values: "SSL_MODE_DISABLED" , "SSL_MODE_PREFERRED" , "SSL_MODE_REQUIRED" , "SSL_MODE_VERIFY_CA" , "SSL_MODE_VERIFY_IDENTITY"
MySQL: Set TLS Version:
MBS( "SQL.SetConnectionOption"; $Connection; "MYSQL_OPT_TLS_VERSION"; "TLSv1.2" )
MySQL: Enable reconnect:
MBS( "SQL.SetConnectionOption"; $Connection; "MYSQL_OPT_RECONNECT"; "true" )
ODBC: Set name of DLL for Windows:
MBS( "SQL.SetConnectionOption"; $Connection; "ODBC.LIBS"; "odbc32.dll" )
ODBC: Set library on MacOS:
MBS( "SQL.SetConnectionOption"; $Connection; "ODBC.LIBS"; "/usr/lib/libiodbc.dylib" )
ODBC: Set an option we need for connecting ODBC to FileMaker:
MBS( "SQL.SetConnectionOption"; $Connection; "ODBCAddLongTextBufferSpace"; "false" )
// newer plugins automatically set it right when connecting to FileMaker
ODBC: Set timeout property:
MBS( "SQL.SetConnectionOption"; $Connection; "SQL_ATTR_CONNECTION_TIMEOUT"; "10" )
Oracle: Set client library path:
MBS( "SQL.SetConnectionOption"; $Connection; "OCI8.LIBS"; "c:\Oracle\oci.dll" )
PostgreSQL: Set path to library on macOS:
MBS( "SQL.SetConnectionOption"; $Connection; "LIBPQ.LIBS"; "/usr/lib/libpq.dylib" )
PostgreSQL: Set path to library on Linux with Intel CPU:
MBS( "SQL.SetConnectionOption"; $Connection; "LIBPQ.LIBS"; "/usr/lib/x86_64-linux-gnu/libpq.so.5" )
# may need installation via Terminal: sudo apt-get install libpq5
SQL Server: Set client database provider for Microsoft SQL Server:
MBS( "SQL.SetConnectionOption"; $Connection; "UseAPI"; "OLEDB" )
MBS( "SQL.SetConnectionOption"; $Connection; "SQLNCLI.LIBS"; "sqlsrv32.dll" )
SQL Server: 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
SQLBase: Set path to libraries:
MBS( "SQL.SetConnectionOption"; $Connection; "SQLBASE.LIBS"; "c:\SQLBase\sqlwntm.dll" )
SQLite: Set library path for macOS:
MBS( "SQL.SetConnectionOption"; $Connection; "SQLITE.LIBS"; "/usr/lib/libsqlite3.dylib" )
SQLite: Set flag to open database file read only:
MBS( "SQL.SetConnectionOption"; $Connection; "SQLiteVFSFlags"; "1")
Sybase: Set database libraries:
MBS( "SQL.SetConnectionOption"; $Connection; "SYBINTL.LIBS"; "/sybase/libsybintl.so" )
MBS( "SQL.SetConnectionOption"; $Connection; "SYBCOMN.LIBS"; "/sybase/libsybcomn.so" )
MBS( "SQL.SetConnectionOption"; $Connection; "SYBTCL.LIBS"; "/sybase/libsybtcl.so" )
MBS( "SQL.SetConnectionOption"; $Connection; "SYBCT.LIBS"; "/sybase/libsybct.so" )
MBS( "SQL.SetConnectionOption"; $Connection; "SYBCS.LIBSS"; "/sybase/libsybcs.so" )
Set ODBC libraryies to load Actual tech connection library:
MBS( "SQL.SetConnectionOption"; $Connection; "ODBC.LIBS"; "/Library/ODBC/Actual Open Source Databases.bundle/Contents/MacOS/Actual Open Source Databases" )
See also
- IsMacOS
- Path.AddPathComponent
- SQL.Connect
- SQL.FreeConnection
- SQL.GetConnectionOption
- SQL.GetConnectionOptions
- SQL.GetFieldAsText
- SQL.NewConnection
- SQL.SetClient
- SSH.Tunnel.Run
Release notes
- Version 9.2
- Changed SQL.SetConnectionOption to pass more options to commands for MS SQL: SQL_ATTR_CURSOR_TYPE, SQL_ATTR_QUERY_TIMEOUT, SQL_ATTR_CONCURRENCY, SQL_ATTR_CURSOR_SCROLLABLE and SQL_ATTR_CURSOR_SENSITIVITY.
Example Databases
- SQL to other databases/Firebird Query
- 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/MySQL Query
- SQL to other databases/ODBC Query
- SQL to other databases/PostgreSQL Query
- SQL to other databases/SQLite blob
- SQL to other databases/SQLite fun
- Third Party/FileMaker Snippet Storage
Blog Entries
- iODBC update needed
- Transactions with SQL functions
- Connect to an external database server in FileMaker
- Using TLS when connecting to MySQL server
- ODBC driver and MBS SQL Functions in FileMaker
- CubeSQL Library for Mac
- MBS FileMaker Plugin, version 9.2pr1
- Prefetching records from databases
- Tip of the day: Connect to MySQL and run a query
- SQL Connectivity for FileMaker
FileMaker Magazin
This function is free to use.
Created 18th August 2014, last changed 10th February 2024
