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: 11.0   11.1   11.2   11.3   11.4   11.5   12.0   12.1   12.2   12.3    Statistic    FMM    Blog  


Sets a string value of a specific connection or command option.

Component Version macOS Windows Linux Server iOS SDK License
SQL 2.6 ✅ Yes ✅ Yes ✅ Yes ✅ Yes ✅ Yes Free
MBS( "SQL.SetConnectionOption"; Connection; Key; Value )   More


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. ""


Returns "OK" or error message.


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:

We have a collection of library files here:


Set Oracle client library path:

MBS( "SQL.SetConnectionOption"; $Connection; "OCI8.LIBS"; "c:\Oracle\oci.dll" )

Set Microsoft SQL Server client database provider:

MBS( "SQL.SetConnectionOption"; $Connection; "UseAPI"; "OLEDB" )
MBS( "SQL.SetConnectionOption"; $Connection; "SQLNCLI.LIBS"; "sqlsrv32.dll" )

Set Sybase Database libraries:

MBS( "SQL.SetConnectionOption"; $Connection; "SYBINTL.LIBS"; "/sybase/" )
MBS( "SQL.SetConnectionOption"; $Connection; "SYBCOMN.LIBS"; "/sybase/" )
MBS( "SQL.SetConnectionOption"; $Connection; "SYBTCL.LIBS"; "/sybase/" )
MBS( "SQL.SetConnectionOption"; $Connection; "SYBCT.LIBS"; "/sybase/" )
MBS( "SQL.SetConnectionOption"; $Connection; "SYBCS.LIBSS"; "/sybase/" )

Set MySQL 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" ) ]

Set DB2 libraries:

MBS( "SQL.SetConnectionOption"; $Connection; "DB2CLI.LIBS"; "c:\DB2\db2cli.dll" )

Set Informix libraries:

MBS( "SQL.SetConnectionOption"; $Connection; "INFCLI.LIBS"; "c:\Informix\ICLIT09B.DLL" )

Set InterBase/Firebird libraries:

MBS( "SQL.SetConnectionOption"; $Connection; "IBASE.LIBS"; "c:\Firebird\fbclient.dll" )

Set SQLBase libraries:

MBS( "SQL.SetConnectionOption"; $Connection; "SQLBASE.LIBS"; "c:\SQLBase\sqlwntm.dll" )

Set PostgreSQL libraries:

MBS( "SQL.SetConnectionOption"; $Connection; "LIBPQ.LIBS"; "/usr/lib/libpq.dylib" )

Set ODBC libraries:

MBS( "SQL.SetConnectionOption"; $Connection; "ODBC.LIBS"; "odbc32.dll" )

Set SQLite libraries:

MBS( "SQL.SetConnectionOption"; $Connection; "SQLITE.LIBS"; "/usr/lib/libsqlite3.dylib" )

For SQLite, set flag to open database file read only:

MBS( "SQL.SetConnectionOption"; $Connection; "SQLiteVFSFlags"; "1")

Set MySQL Timeouts:

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 )

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=;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

Set an option we need for ODBC to FileMaker:

MBS( "SQL.SetConnectionOption"; $Connection; "ODBCAddLongTextBufferSpace"; "false" )

// newer plugins automatically set it right when connecting to FileMaker

The MySQL SSL Cipher:

MBS( "SQL.SetConnectionOption"; $Connection; "MYSQL_SSL_CIPHER"; "DHE-RSA-AES256-SHA" )

Requests 100 rows to be prefetched:

MBS( "SQL.SetConnectionOption"; $Connection; "PreFetchRows"; "100" )
# will propagate down to all commands on that connection.

Store MySQL RecordSet locally:

MBS( "SQL.SetConnectionOption"; $Connection; "HandleResult"; "Store" )

Set ODBC timeout property:

MBS( "SQL.SetConnectionOption"; $Connection; "SQL_ATTR_CONNECTION_TIMEOUT"; "10" )

Enable SSL for MySQL:

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

Set connection timeout for CubeSQL:

MBS( "SQL.SetConnectionOption"; $Connection; "ConnectionTimeout"; "30" )

Set CubeSQL libraries:

MBS( "SQL.SetConnectionOption"; $Connection; "CUBESQL.LIBS"; "c:\CubeSQL\cubesql.dll" )

Set ODBC libraries on MacOS:

MBS( "SQL.SetConnectionOption"; $Connection; "ODBC.LIBS"; "/usr/lib/libiodbc.dylib" )

Enable SSL for CubeSQL connection:

MBS( "SQL.SetConnectionOption"; $Connection; "ConnectionEncryption"; "SSL+AES256" ) &
MBS( "SQL.SetConnectionOption"; $Connection; "SSLCertificatePath"; $path )

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

Write MySQL 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"]
        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

See also

Release notes

  • Version 9.2

Example Databases

Blog Entries

Created 18th August 2014, last changed 10th March 2022

SQL.SetCommandText - SQL.SetConnectionTag

Feedback: Report problem or ask question.

Start Chat