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:
9.5
10.0
10.1
10.2
10.3
10.4
10.5
11.0
11.1
11.2
Statistic
FMM
Blog
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 |
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.
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/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 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=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
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" )
Droz Consulting
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") ]
Created 18th August 2014, last changed 20th August 2020
SQL.SetCommandText - SQL.SetConnectionTag
Feedback: Report problem or ask question.