Components | All | New | MacOS | Windows | Linux | iOS | ||||
Examples | Mac & Win | Server | Client | Guides | Statistic | FMM | Blog | Deprecated | Old |
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 FMSQL 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.
Release notes
- Version 15.0
- Fixed crash in DuckDB client for SQL functions.
- Version 14.5
- Added MYSQL_OPT_LOCAL_INFILE option for SQL.SetConnectionOption for use with MySQL connections.
- Version 14.2
- Improved SQL functions in FileMaker to better provide an error message.
- Version 14.1
- Version 14.0
- Version 13.5
- Changed SQL functions, so you can load Actual Tech ODBC drivers directly without using iODBC.
- Version 13.4
- Fixed a bug in setting numeric values in SQL functions with floating numbers, caused by a bug fix in version 13.3. Values showed up as zero incorrectly.
- Version 13.3
- Fixed a crash with SQL.SetParamAsNumber when parsing a string.
- Version 13.1
- Changed SQL functions to log SQL statements when trace is active.
Blog Entries
- Insert and return new record ID
- Can FileMaker do that?
- Moving data from ODBC to FileMaker via script
- CSV in FileMaker with MBS FileMaker Plugin
- MBS @ FMTraining.TV - SQL Connections
- MBS Plugin 12.3 for Claris FileMaker
- Transactions with SQL functions
- MBS FileMaker Plugin 12.0 - More than 6700 Functions In One Plugin
- MBS FileMaker Plugin, June 2021 News
- New in MBS FileMaker Plugin 11.2
FileMaker Magazin
- Ausgabe 2/2022, Seite 27 bis 29
- Ausgabe 3/2018, Seite 34
- Ausgabe 4/2016, Seite 28
- Ausgabe 3/2016, Seite 29 bis 30
- Ausgabe 2/2016, Seite 14
- Ausgabe 1/2016, Seite 28 bis 30
- Ausgabe 4/2015, Seite 15 bis 16
Example Databases
- SQL to other databases/Firebird Query
- SQL to other databases/Microsoft Access Execute
- SQL to other databases/Microsoft SQL Server Connect
- SQL to other databases/Microsoft SQL Server Query
- SQL to other databases/MySQL Query
- SQL to other databases/ODBC Query
- SQL to other databases/ODBC to FIleMaker/ODBC Test
- SQL to other databases/PostgreSQL Query
- SQL to other databases/SQL Export
- SQL to other databases/SQLite blob
