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 14.1
    • Fixed an issue in SQL functions where an error message would be cut on the first character improperly encoded.
    • Improved SQL functions to better return error messages if the ODBC driver provided them with ASCII instead of UTF16/32 encoding.
  • Version 14.0
    • Added checks for SQL functions in multiple places to avoid crashes if SQL command text is empty.
    • Fixed a problem with int32 read as string in SQL functions.
  • 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
  • Version 13.1
    • Changed SQL functions to log SQL statements when trace is active.
  • Version 13.0
  • Version 12.3
  • Version 12.2
    • Improved exception handling for SQL functions.

Blog Entries

FileMaker Magazin

Example Databases