Monkeybread Software - MBS FileMaker Plugin: Component: SQL

Topics All Mac OS X (Only) Windows (Only) Linux (Only, Not) iOS (Only, Not)
Components Crossplatform Mac & Win Server Client Old Guides Examples
New in version: 8.2 8.3 8.4 8.5 9.0 9.1 9.2 9.3 9.4 9.5 Statistic

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

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.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.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 datetime value; converts if needed.	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.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.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.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 Returns the number of rows affected by the last insert/update/delete command execution.	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.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

125 functions.

Release notes

Version 9.5
- Fixed a bug in SQL.FreeConnection freeing wrong command objects.
Version 9.4
- Changed SQL functions for ODBC on Mac to not complain about missing SQLBrowseConnect.
Version 9.3
- Fixed potential crash with SQL.FreeCommand and SQL.FreeConnection in server scripts.
Version 9.2
- Changed SQL functions to not complain about missing functions with freetds.
- Changed SQL.SetConnectionOption to pass more options to commands for MS SQL: SQL_ATTR_CURSOR_TYPE, SQL_ATTR_QUERY_TIMEOUT, SQL_ATTR_CONCURRENCY, SQL_ATTR_CURSOR_SCROLLABLE and SQL_ATTR_CURSOR_SENSITIVITY.
Version 9.1
- Fixed bug with SQL.SetParamAsTime and SQLAPI handling TIME field type for SQLite.
Version 9.0
- Fixed a bug in Oracle initialization for Linux for SQL functions.
- Fixed crash in SQL functions when using unnamed fields in SQLite recordsets.
- Fixed problem with text encoding with Oracle databases for SQL functions.
Version 8.5
- Fixed bug in SQL.GetFieldValueList with empty texts being ignored in first field.
- Fixed bug with SQL functions on finding the MySQL libraries.
Version 8.4
- Added SQL.GetCommandOptions, SQL.GetConnectionOptions, SQL.GetFieldOptions and SQL.GetParamOptions functions to query options list.
- Added SQL.GetParamOption and SQL.SetParamOption for options related to a command.
Version 8.3
- Fixed bug where SQL functions would return date with year 1899 instead of empty value for empty SQLDateTime value.
- Improved SQL.GetFieldAsTime to handle intervals and return them as time.
Version 8.2
- Changed SQL.GetFieldAsTime to return a time and not timestamp.
Version 8.1
- Added option for second and third primary keys to SQL.InsertOrUpdateRecords.
- Changed SQL.InsertOrUpdateRecords to accept more keys, up to 10.
Version 8.0
- Changed SQL.SetParamAsDateTime and SQL.SetParamAsDate to set param to NULL if you pass empty value.
- Fixed SQL.GetFieldAsDate to assign a date and not a timestamp.
Version 7.4
- Added SQL.SQLite3.MemoryHighwater and SQL.SQLite3.MemoryUsed.
Version 7.3
- Added IgnoreDuplicates parameter to SQL.InsertRecords functions to ignore duplicate error on insert.
- Added SQL.InsertOrUpdateRecords function.
- Changed SQL.GetFieldAsNumber and SQL.GetParamAsNumber to return numbers outside 32-bit integer as text.
Version 7.2
- Fixed SQL.InsertRecords to handle floating point numbers better.

Feedback: Report problem or ask question.