Monkeybread Software - MBS FileMaker Plugin: Component: SQL

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.4 11.5 12.0 12.1 12.2 12.3 12.4 12.5 13.0 13.1 Statistic FMM Blog

Component: SQL

Connect to SQL databases and query data or run SQL commands.

Use this functions to connect to foreign databases. You can run queries, execute SQL statements and work with stored procedures.
We support this database types: CubeSQL, Centura SQLBase, DB2, DuckDB, Firebird, Informix, InterBase, MariaDB, Microsoft Access, Microsoft SQL Server, MySQL, ODBC, Oracle Database Server, PostgreSQL, SQL Anywhere, SQLite, SQLCipher and Sybase.

If you look for SQL functions within FileMaker database, please check our FM SQL functions.

Connect to Microsoft Access, Claris FileMaker Server (or Pro), Microsoft Visual FoxPro and others via ODBC. Connect to MongoDB via the mysql interface of MongoDB Connector for BI or check out our MongoDB functions for direct connection.

Version	macOS	Windows	Linux	Server	iOS SDK
2.6 / 11.4	✅ Yes	✅ Yes	✅ Yes	✅ Yes	✅ Yes

Item	Details
FM.SQL.InsertRecordsToSQL Inserts records in SQL database from records in memory.	All 6.4
SQL.Cancel Attempts to cancel the pending result set, or current statement execution.	All 2.6
SQL.ClientVersion Gets the DBMS client API version number.	All 2.6
SQL.Close Closes the specified command object.	All 2.6
SQL.CommandText Gets the command text associated with the command object.	All 2.6
SQL.CommandType Gets the command type currently associated with the command object.	All 2.6
SQL.Commit Saves any changes and ends the current transaction.	All 2.6
SQL.Connect Opens the connection to a data source.	All 2.6
SQL.ConvertDateTimeFromSQL Converts a date or timestamp to FileMaker format.	All 11.2
SQL.ConvertDateTimeToSQL Converts a date/time/timestamp to SQL text.	All 11.2
SQL.Disconnect Disconnects the connection from the database.	All 2.6
SQL.Execute Executes the current command.	All 2.6
SQL.FetchFirst Fetches the first row from a result set.	All 2.6
SQL.FetchLast Fetches the last row from a result set.	All 2.6
SQL.FetchNext Fetches next row from a result set.	All 2.6
SQL.FetchPos Fetches a row from a result set.	All 5.1
SQL.FetchPrior Fetches previous row from a result set.	All 2.6
SQL.FieldCount Returns the number of fields (columns) in a result set.	All 2.6
SQL.FieldExists Checks whether a field exists.	All 11.4
SQL.FreeAllConnections Frees all SQL connections and commands.	All 6.3
SQL.FreeCommand Frees the command and releases all memory associated with it.	All 2.6
SQL.FreeConnection Call this method if you are done with a connection.	All 2.6
SQL.GetAutoCommit Checks whether autocommit is enabled or disabled for the current connection.	All 2.6
SQL.GetCommandOption Gets a string value of a specific command option.	All 2.6
SQL.GetCommandOptions Queries list of options for command.	All 8.4
SQL.GetCommandTag Queries tag for command.	All 6.4
SQL.GetConnectionOption Gets a string value of a specific connection option.	All 2.6
SQL.GetConnectionOptions Queries list of options for connection.	All 8.4
SQL.GetConnectionTag Queries tag for connection.	All 6.4
SQL.GetFieldAsBoolean Returns field's value as boolean value; converts if needed.	All 2.6
SQL.GetFieldAsContainer Queries field value as container.	All 6.2
SQL.GetFieldAsDate Returns field's value as date value; converts if needed.	All 2.6
SQL.GetFieldAsDateTime Returns field's value as timestamp value.-	All 2.6
SQL.GetFieldAsFloat Returns field's value as floating point number value; converts if needed.	All 2.6
SQL.GetFieldAsJPEG Returns field's value as JPEG value.	All 3.0
SQL.GetFieldAsNumber Returns field's value as integer number value; converts if needed.	All 2.6
SQL.GetFieldAsPDF Returns field's value as PDF value.	All 4.3
SQL.GetFieldAsPNG Returns field's value as PNG value.	All 3.0
SQL.GetFieldAsText Returns field's value as text value; converts if needed.	All 2.6
SQL.GetFieldAsTime Returns field's value as time value; converts if needed.	All 2.6
SQL.GetFieldDataType Returns field data type.	All 2.6
SQL.GetFieldIsNull Whether the field value is NULL.	All 2.6
SQL.GetFieldName Returns name of the field.	All 2.6
SQL.GetFieldNameList Queries list of field names.	All 5.1
SQL.GetFieldOption Gets a string value of a specific field option.	All 2.6
SQL.GetFieldOptions Queries list of options for field.	All 8.4
SQL.GetFieldValue Returns field's value.	All 5.1
SQL.GetFieldValueList Queries list of field values.	All 5.1
SQL.GetFieldsAsJSON Queries fields values current record as JSON object.	All 10.2
SQL.GetIsolationLevel Gets the transaction isolation level.	All 2.6
SQL.GetParamAsBoolean Returns param's value as boolean value; converts if needed.	All 2.6
SQL.GetParamAsContainer Queries parameter value as container.	All 6.2
SQL.GetParamAsDate Returns param's value as date; converts if needed.	All 2.6
SQL.GetParamAsDateTime Returns param's value as timestamp value; converts if needed.	All 2.6
SQL.GetParamAsFloat Returns param's value as floating point number; converts if needed.	All 2.6
SQL.GetParamAsJPEG Returns parameters's value as JPEG value.	All 3.0
SQL.GetParamAsNumber Returns param's value as integer; converts if needed.	All 2.6
SQL.GetParamAsPDF Returns parameters's value as PDF value.	All 4.3
SQL.GetParamAsPNG Returns parameters's value as PNG value.	All 3.0
SQL.GetParamAsText Returns param's value as text; converts if needed.	All 2.6
SQL.GetParamAsTime Returns param's value as time value; converts if needed.	All 2.6
SQL.GetParamDataType Returns parameter data type.	All 2.6
SQL.GetParamIsDefault Checks whether parameter is default value.	All 2.6
SQL.GetParamIsNull Whether the parameter value is NULL.	All 2.6
SQL.GetParamName Returns the name of the parameter.	All 2.6
SQL.GetParamNameList Queries list of parameter names.	All 5.1
SQL.GetParamOption Gets a string value of a specific parameter option.	All 8.4
SQL.GetParamOptions Queries list of options for parameter.	All 8.4
SQL.GetParamValue Returns param's value.	All 5.1
SQL.GetParamValueList Queries list of parameter values.	All 5.1
SQL.GetParamsAsJSON Queries parameters as JSON object.	All 10.2
SQL.GetRecordsAsJSON Queries records of current result set as JSON array.	All 10.2
SQL.GetRecordsAsText Queries all records as text.	All 5.3
SQL.InsertOrUpdateRecords Creates or updates records in FileMaker database for current recordset.	All 7.3
SQL.InsertRecords Creates records in FileMaker database for current recordset.	All 5.2
SQL.InternalSQLiteLibrary.Activate Activates the internal SQLite library.	All 5.3
SQL.InternalSQLiteLibrary.Activated Checks if internal SQLite library is used.	All 5.3
SQL.InternalSQLiteLibrary.Deactivate Deactivates the use of internal SQLite library.	All 5.3
SQL.InternalSQLiteLibrary.ICUEnabled Queries whether ICU libraries are enabled.	All 11.2
SQL.InternalSQLiteLibrary.ICULoaded Queries whether ICU libraries are loaded.	All 11.2
SQL.InternalSQLiteLibrary.ICUUsed Queries whether ICU libraries have been used.	All 11.2
SQL.InternalSQLiteLibrary.LoadICU Loads ICU libraries now.	All 11.2
SQL.InternalSQLiteLibrary.SetICUEnabled Sets whether to use ICU libraries.	All 11.2
SQL.InternalSQLiteLibrary.SourceID Returns the source code ID.	All 5.3
SQL.InternalSQLiteLibrary.Version Returns the SQLite version number.	All 5.3
SQL.InternalSQLiteLibrary.VersionNumber Returns the SQLite version number.	All 5.3
SQL.ListCommands Returns a list of all SQL Commands.	All 6.4
SQL.ListConnections Returns a list of all SQL Connections.	All 6.4
SQL.MySQL.InsertID For MySQL Connection queries the last insert ID.	All 2.9
SQL.NewCommand Creates a new command.	All 2.6
SQL.NewConnection Initialize a new SAConnection object and returns Connection Reference.	All 2.6
SQL.Open Opens the specified command object.	All 2.6
SQL.ParamCount Returns the number of parameters associated with the command object.	All 2.6
SQL.Prepare Validates and prepares the current command for execution.	All 2.6
SQL.ResetCommand Clears all DBMS related handles and sturctures but doesn't destroy them (mostly for Unix fork/exec based applications)	All 2.6
SQL.ResetConnection Clears all DBMS related handles and structures but doesn't destroy them.	All 2.6
SQL.Rollback Cancels any changes made during the current transaction and ends the transaction.	All 2.6
SQL.RowsAffected Queries the number of rows affected.	All 2.6
SQL.SQLite3.EnableLoadExtension Enables extensions on SQLite 3.	All 4.4
SQL.SQLite3.LastInsertRowID For SQLite3, returns the last row ID used.	All 2.9
SQL.SQLite3.MemoryHighwater Queries maximum memory usage so far.	All 7.4
SQL.SQLite3.MemoryUsed Queries memory in use by SQLite.	All 7.4
SQL.SQLite3.ReKey You can change the key on a database using the Rekey Function.	All 5.3
SQL.SQLite3.SetKey Sets the encryption key to use.	All 5.3
SQL.ServerVersion The long number of the currently connected server version.	All 2.6
SQL.ServerVersionString Gets the currently connected DBMS server version string.	All 2.6
SQL.SetAutoCommit Enables or disables autocommit for the current connection, that is, automatic commit of every SQL command.	All 2.6
SQL.SetClient Assigns an DBMS client for the connection.	All 2.6
SQL.SetCommandOption Sets a string value of a specific command option.	All 2.6
SQL.SetCommandTag Sets the tag for the command.	All 6.4
SQL.SetCommandText Sets the command text.	All 2.6
SQL.SetConnectionOption Sets a string value of a specific connection or command option.	All 2.6
SQL.SetConnectionTag Sets the tag for the connection.	All 6.4
SQL.SetFieldOption Sets a string value of a specific field option.	All 2.6
SQL.SetIsolationLevel Sets the transaction isolation level.	All 2.6
SQL.SetParamAsBoolean Sets parameter's value as bool data.	All 2.6
SQL.SetParamAsContainer Sets parameter's value as BLOB value with data from a container.	All 3.0
SQL.SetParamAsDate Sets parameter's value as Date data.	All 2.6
SQL.SetParamAsDateTime Sets parameter's value as DateTime data.	All 2.6
SQL.SetParamAsFloat Sets parameter's value as floating point number.	All 2.6
SQL.SetParamAsNumber Sets parameter's value as 32-bit integer number.	All 2.6
SQL.SetParamAsText Sets parameter's value as character string data.	All 2.6
SQL.SetParamAsTime Sets parameter's value as Time data.	All 2.6
SQL.SetParamDataType Sets parameter's data type.	All 2.6
SQL.SetParamDefault Sets the default value for this parameter.	All 2.6
SQL.SetParamNull Sets parameter's value as null.	All 2.6
SQL.SetParamOption Sets a string value of a specific parameter option.	All 8.4
SQL.SetParamTypesValues Sets all parameters for a query in one call.	All 5.1
SQL.SetParamUnknown Sets parameter's type as unknown.	All 2.6
SQL.SetParamValue Sets parameter's value.	All 5.1
SQL.SetParamValues Sets all parameters for a query in one call.	All 5.1
SQL.SetParamsWithJSON Sets parameters based on values in JSON.	All 10.2
SQL.isAlive Returns the database server connection status for a particular connection object.	All 2.6
SQL.isConnected Returns the connection state for a particular connection object.	All 2.6
SQL.isExecuted Whether command was executed.	All 2.6
SQL.isOpened Tests whether the specified command object is opened.	All 2.6
SQL.isResultSet Tests whether a result set exists after the command execution.	All 2.6

137 functions shown.

9 functions require a paid license (7%) and 128 functions are free to use.

Release notes

Version 13.1
- Changed SQL functions to log SQL statements when trace is active.
Version 13.0
- Fixed a problem with SQL.InsertOrUpdateRecords not finding the primary key field.
Version 12.3
- Added MariaDB as option for SQL.SetClient function.
Version 12.2
- Improved exception handling for SQL functions.
Version 12.0
- Added SQL.GetFieldAsNumber, SQL.GetParamAsNumber and SQL.SetParamAsNumber to better handle numeric values.
- Changed how we handles numbers in SQL functions to better handle numeric values and avoid conversion of numeric to floating pointer numbers.
- Fixed a potential problem in SQL.InsertOrUpdateRecords function where it could crash with too many primary keys.
Version 11.5
- Fixed a problem with decimal data type for Firebird in our SQL functions.
Version 11.4
- Added SQL.FieldExists function.
Version 11.2
- Added code to use ICU libraries for SQLite. See SQL.InternalSQLiteLibrary.ICUEnabled, SQL.InternalSQLiteLibrary.ICULoaded, SQL.InternalSQLiteLibrary.ICUUsed and SQL.InternalSQLiteLibrary.SetICUEnabled functions.
- Added milliseconds for SQL.ConvertDateTimeFromSQL and SQL.ConvertDateTimeToSQL functions.
- Added SQL.ConvertDateTimeFromSQL and SQL.ConvertDateTimeToSQL functions.
- Enabled math and geopoly functions for internal SQLite library. see SQL.InternalSQLiteLibrary.Activate
Version 11.0
- Changed SQL functions to automatically use CLOB/BLOB type for strings for SQL Server if length is >8000.
- Fixed an issue with SQL.Connect function connecting to MySQL when server doesn't know utf8mb4 text encoding.

Blog Entries

FileMaker Magazin

Example Databases

💬 Ask a question or report a problem