Inherits from NSObject
Declared in FMDatabase.h

Overview

A SQLite (http://sqlite.org/) Objective-C wrapper.

Usage

The three main classes in FMDB are:

  • FMDatabase - Represents a single SQLite database. Used for executing SQL statements.
  • FMResultSet - Represents the results of executing a query on an FMDatabase.
  • FMDatabaseQueue - If you want to perform queries and updates on multiple threads, you’ll want to use this class.

See also

External links

Warning: Do not instantiate a single FMDatabase object and use it across multiple threads. Instead, use FMDatabaseQueue.

Category of additions for <FMDatabase> class.

See also

  • <FMDatabase>

    This category provides methods to access the FTS3 extensions in SQLite.

Tasks

Properties

Initialization

Opening and closing database

Perform updates

Retrieving results

Transactions

Cached statements and result sets

Encryption methods

General inquiry methods

Retrieving error codes

Save points

SQLite library status

Make SQL function

Date formatter

FMDatabaseAdditions Methods

FTS3 Methods

Properties

cachedStatements

@property (atomic, retain) NSMutableDictionary *cachedStatements
Discussion

Dictionary of cached statements

Declared In

FMDatabase.h

checkedOut

@property (atomic, assign) BOOL checkedOut
Discussion

Whether checked out or not

Declared In

FMDatabase.h

crashOnErrors

@property (atomic, assign) BOOL crashOnErrors
Discussion

Crash on errors

Declared In

FMDatabase.h

logsErrors

@property (atomic, assign) BOOL logsErrors
Discussion

Logs errors

Declared In

FMDatabase.h

traceExecution

@property (atomic, assign) BOOL traceExecution
Discussion

Whether should trace execution

Declared In

FMDatabase.h

Class Methods

FMDBUserVersion

+ (NSString *)FMDBUserVersion

FMDBVersion

+ (SInt32)FMDBVersion

databaseWithPath:

+ (instancetype)databaseWithPath:(NSString *)inPath
Discussion

Create a FMDatabase object.

An FMDatabase is created with a path to a SQLite database file. This path can be one of these three:

  1. A file system path. The file does not have to exist on disk. If it does not exist, it is created for you.
  2. An empty string (@""). An empty database is created at a temporary location. This database is deleted with the FMDatabase connection is closed.
  3. nil. An in-memory database is created. This database will be destroyed with the FMDatabase connection is closed.

For example, to create/open a database in your Mac OS X tmp folder:

FMDatabase *db = [FMDatabase databaseWithPath:@"/tmp/tmp.db"];

Or, in iOS, you might open a database in the app’s Documents directory:

NSString *docsPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];
NSString *dbPath   = [docsPath stringByAppendingPathComponent:@"test.db"];
FMDatabase *db     = [FMDatabase databaseWithPath:dbPath];

(For more information on temporary and in-memory databases, read the sqlite documentation on the subject: http://www.sqlite.org/inmemorydb.html)

Parameters

inPath

Path of database file

Return Value

FMDatabase object if successful; nil if failure.

Declared In

FMDatabase.h

isSQLiteThreadSafe

+ (BOOL)isSQLiteThreadSafe
Discussion

Test to see if the library is threadsafe

Return Value

NO if and only if SQLite was compiled with mutexing code omitted due to the SQLITE_THREADSAFE compile-time option being set to 0.

Declared In

FMDatabase.h

registerTokenizer:

+ (void)registerTokenizer:(id<FMTokenizerDelegate>)tokenizer
Discussion

Register a delegate implementation in the global table. This should be used when using a single tokenizer.

Declared In

FMDatabase+FTS3.h

registerTokenizer:withKey:

+ (void)registerTokenizer:(id<FMTokenizerDelegate>)tokenizer withKey:(NSString *)key
Discussion

Register a delegate implementation in the global table. The key should be used as a parameter when creating the table.

Declared In

FMDatabase+FTS3.h

sqliteLibVersion

+ (NSString *)sqliteLibVersion
Discussion

Run-time library version numbers

Return Value

The sqlite library version string.

Declared In

FMDatabase.h

storeableDateFormat:

+ (NSDateFormatter *)storeableDateFormat:(NSString *)format
Discussion

Generate an NSDateFormatter that won’t be broken by permutations of timezones or locales.

Use this method to generate values to set the dateFormat property.

Example:

myDB.dateFormat = [FMDatabase storeableDateFormat:@"yyyy-MM-dd HH:mm:ss"];

Warning: Note that NSDateFormatter is not thread-safe, so the formatter generated by this method should be assigned to only one FMDB instance and should not be used for other purposes.

Parameters

format

A valid NSDateFormatter format string.

Return Value

A NSDateFormatter that can be used for converting dates to strings and vice versa.

Declared In

FMDatabase.h

Instance Methods

applicationID

- (uint32_t)applicationID
Discussion

Retrieve application ID

Return Value

The uint32_t numeric value of the application ID.

Declared In

FMDatabaseAdditions.h

applicationIDString

- (NSString *)applicationIDString
Discussion

Retrieve application ID string

Return Value

The NSString value of the application ID.

Declared In

FMDatabaseAdditions.h

beginDeferredTransaction

- (BOOL)beginDeferredTransaction
Discussion

Begin a deferred transaction

Return Value

YES on success; NO on failure. If failed, you can call lastError, lastErrorCode, or lastErrorMessage for diagnostic information regarding the failure.

Declared In

FMDatabase.h

beginTransaction

- (BOOL)beginTransaction
Discussion

Begin a transaction

Return Value

YES on success; NO on failure. If failed, you can call lastError, lastErrorCode, or lastErrorMessage for diagnostic information regarding the failure.

Declared In

FMDatabase.h

boolForQuery:

- (BOOL)boolForQuery:(NSString *)query, ...
Discussion

Return BOOL value for query

Note: To use this method from Swift, you must include FMDatabaseAdditionsVariadic.swift in your project.

Parameters

query

The SQL query to be performed.

...

A list of parameters that will be bound to the ? placeholders in the SQL query.

Return Value

BOOL value.

Declared In

FMDatabaseAdditions.h

changes

- (int)changes
Discussion

The number of rows changed by prior SQL statement.

This function returns the number of database rows that were changed or inserted or deleted by the most recently completed SQL statement on the database connection specified by the first parameter. Only changes that are directly specified by the INSERT, UPDATE, or DELETE statement are counted.

Return Value

The number of rows changed by prior SQL statement.

Declared In

FMDatabase.h

clearCachedStatements

- (void)clearCachedStatements
Discussion

Clear cached statements

Declared In

FMDatabase.h

close

- (BOOL)close
Discussion

Closing a database connection

Return Value

YES if success, NO on error.

Declared In

FMDatabase.h

closeOpenResultSets

- (void)closeOpenResultSets
Discussion

Close all open result sets

Declared In

FMDatabase.h

columnExists:columnName:

- (BOOL)columnExists:(NSString *)tableName columnName:(NSString *)columnName
Discussion

Test to see if particular column exists for particular table in database

Warning: Deprecated - use columnExists:inTableWithName: instead.

Parameters

tableName

The name of the table.

columnName

The name of the column.

Return Value

YES if column exists in table in question; NO otherwise.

Declared In

FMDatabaseAdditions.h

columnExists:inTableWithName:

- (BOOL)columnExists:(NSString *)columnName inTableWithName:(NSString *)tableName
Discussion

Test to see if particular column exists for particular table in database

Parameters

columnName

The name of the column.

tableName

The name of the table.

Return Value

YES if column exists in table in question; NO otherwise.

Declared In

FMDatabaseAdditions.h

commit

- (BOOL)commit
Discussion

Commit a transaction

Commit a transaction that was initiated with either beginTransaction or with beginDeferredTransaction.

Return Value

YES on success; NO on failure. If failed, you can call lastError, lastErrorCode, or lastErrorMessage for diagnostic information regarding the failure.

Declared In

FMDatabase.h

dataForQuery:

- (NSData *)dataForQuery:(NSString *)query, ...
Discussion

Return NSData value for query

Note: To use this method from Swift, you must include FMDatabaseAdditionsVariadic.swift in your project.

Parameters

query

The SQL query to be performed.

...

A list of parameters that will be bound to the ? placeholders in the SQL query.

Return Value

NSData value.

Declared In

FMDatabaseAdditions.h

databasePath

- (NSString *)databasePath
Discussion

The path of the database file

Return Value

path of database.

Declared In

FMDatabase.h

dateForQuery:

- (NSDate *)dateForQuery:(NSString *)query, ...
Discussion

Return NSDate value for query

Note: To use this method from Swift, you must include FMDatabaseAdditionsVariadic.swift in your project.

Parameters

query

The SQL query to be performed.

...

A list of parameters that will be bound to the ? placeholders in the SQL query.

Return Value

NSDate value.

Declared In

FMDatabaseAdditions.h

dateFromString:

- (NSDate *)dateFromString:(NSString *)s
Discussion

Convert the supplied NSString to NSDate, using the current database formatter.

Parameters

s

NSString to convert to NSDate.

Return Value

The NSDate object; or nil if no formatter is set.

Declared In

FMDatabase.h

doubleForQuery:

- (double)doubleForQuery:(NSString *)query, ...
Discussion

Return double value for query

Note: To use this method from Swift, you must include FMDatabaseAdditionsVariadic.swift in your project.

Parameters

query

The SQL query to be performed.

...

A list of parameters that will be bound to the ? placeholders in the SQL query.

Return Value

double value.

Declared In

FMDatabaseAdditions.h

executeQuery:

- (FMResultSet *)executeQuery:(NSString *)sql, ...
Discussion

Execute select statement

Executing queries returns an FMResultSet object if successful, and nil upon failure. Like executing updates, there is a variant that accepts an NSError ** parameter. Otherwise you should use the lastErrorMessage and lastErrorMessage methods to determine why a query failed.

In order to iterate through the results of your query, you use a while() loop. You also need to “step” (via [FMResultSet next]) from one record to the other.

This method employs sqlite3_bind for any optional value parameters. This properly escapes any characters that need escape sequences (e.g. quotation marks), which eliminates simple SQL errors as well as protects against SQL injection attacks. This method natively handles NSString, NSNumber, NSNull, NSDate, and NSData objects. All other object types will be interpreted as text values using the object’s description method.

Parameters

sql

The SELECT statement to be performed, with optional ? placeholders.

...

Optional parameters to bind to ? placeholders in the SQL statement. These should be Objective-C objects (e.g. NSString, NSNumber, etc.), not fundamental C data types (e.g. int, char *, etc.).

Return Value

A FMResultSet for the result set upon success; nil upon failure. If failed, you can call lastError, lastErrorCode, or lastErrorMessage for diagnostic information regarding the failure.

Declared In

FMDatabase.h

executeQuery:withArgumentsInArray:

- (FMResultSet *)executeQuery:(NSString *)sql withArgumentsInArray:(NSArray *)arguments
Discussion

Execute select statement

Executing queries returns an FMResultSet object if successful, and nil upon failure. Like executing updates, there is a variant that accepts an NSError ** parameter. Otherwise you should use the lastErrorMessage and lastErrorMessage methods to determine why a query failed.

In order to iterate through the results of your query, you use a while() loop. You also need to “step” (via [FMResultSet next]) from one record to the other.

Parameters

sql

The SELECT statement to be performed, with optional ? placeholders.

arguments

A NSArray of objects to be used when binding values to the ? placeholders in the SQL statement.

Return Value

A FMResultSet for the result set upon success; nil upon failure. If failed, you can call lastError, lastErrorCode, or lastErrorMessage for diagnostic information regarding the failure.

Declared In

FMDatabase.h

executeQuery:withParameterDictionary:

- (FMResultSet *)executeQuery:(NSString *)sql withParameterDictionary:(NSDictionary *)arguments
Discussion

Execute select statement

Executing queries returns an FMResultSet object if successful, and nil upon failure. Like executing updates, there is a variant that accepts an NSError ** parameter. Otherwise you should use the lastErrorMessage and lastErrorMessage methods to determine why a query failed.

In order to iterate through the results of your query, you use a while() loop. You also need to “step” (via [FMResultSet next]) from one record to the other.

Parameters

sql

The SELECT statement to be performed, with optional ? placeholders.

arguments

A NSDictionary of objects keyed by column names that will be used when binding values to the ? placeholders in the SQL statement.

Return Value

A FMResultSet for the result set upon success; nil upon failure. If failed, you can call lastError, lastErrorCode, or lastErrorMessage for diagnostic information regarding the failure.

Declared In

FMDatabase.h

executeQuery:withVAList:

- (FMResultSet *)executeQuery:(NSString *)sql withVAList:(va_list)args

executeQueryWithFormat:

- (FMResultSet *)executeQueryWithFormat:(NSString *)format, ...
Discussion

Execute select statement

Executing queries returns an FMResultSet object if successful, and nil upon failure. Like executing updates, there is a variant that accepts an NSError ** parameter. Otherwise you should use the lastErrorMessage and lastErrorMessage methods to determine why a query failed.

In order to iterate through the results of your query, you use a while() loop. You also need to “step” (via [FMResultSet next]) from one record to the other.

Note: This method does not technically perform a traditional printf-style replacement. What this method actually does is replace the printf-style percent sequences with a SQLite ? placeholder, and then bind values to that placeholder. Thus the following command

[db executeQueryWithFormat:@"SELECT * FROM test WHERE name=%@", @"Gus"];

is actually replacing the %@ with ? placeholder, and then performing something equivalent to executeQuery:

[db executeQuery:@"SELECT * FROM test WHERE name=?", @"Gus"];

There are two reasons why this distinction is important. First, the printf-style escape sequences can only be used where it is permissible to use a SQLite ? placeholder. You can use it only for values in SQL statements, but not for table names or column names or any other non-value context. This method also cannot be used in conjunction with pragma statements and the like. Second, note the lack of quotation marks in the SQL. The WHERE clause was not WHERE name='%@' (like you might have to do if you built a SQL statement using NSString method stringWithFormat), but rather simply WHERE name=%@.

Note: If you want to use this from Swift, please note that you must include FMDatabaseVariadic.swift in your project. Without that, you cannot use this method directly, and instead have to use methods such as executeQuery:withArgumentsInArray:.

Parameters

format

The SQL to be performed, with printf-style escape sequences.

...

Optional parameters to bind to use in conjunction with the printf-style escape sequences in the SQL statement.

Return Value

A FMResultSet for the result set upon success; nil upon failure. If failed, you can call lastError, lastErrorCode, or lastErrorMessage for diagnostic information regarding the failure.

Declared In

FMDatabase.h

executeStatements:

- (BOOL)executeStatements:(NSString *)sql
Discussion

Execute multiple SQL statements

This executes a series of SQL statements that are combined in a single string (e.g. the SQL generated by the sqlite3 command line .dump command). This accepts no value parameters, but rather simply expects a single string with multiple SQL statements, each terminated with a semicolon. This uses sqlite3_exec.

Parameters

sql

The SQL to be performed

Return Value

YES upon success; NO upon failure. If failed, you can call lastError, lastErrorCode, or lastErrorMessage for diagnostic information regarding the failure.

Declared In

FMDatabase.h

executeStatements:withResultBlock:

- (BOOL)executeStatements:(NSString *)sql withResultBlock:(FMDBExecuteStatementsCallbackBlock)block
Discussion

Execute multiple SQL statements with callback handler

This executes a series of SQL statements that are combined in a single string (e.g. the SQL generated by the sqlite3 command line .dump command). This accepts no value parameters, but rather simply expects a single string with multiple SQL statements, each terminated with a semicolon. This uses sqlite3_exec.

Parameters

sql

The SQL to be performed.

block

A block that will be called for any result sets returned by any SQL statements. Note, if you supply this block, it must return integer value, zero upon success (this would be a good opportunity to use SQLITE_OK), non-zero value upon failure (which will stop the bulk execution of the SQL). If a statement returns values, the block will be called with the results from the query in NSDictionary *resultsDictionary. This may be nil if you don’t care to receive any results.

Return Value

YES upon success; NO upon failure. If failed, you can call lastError, lastErrorCode, or lastErrorMessage for diagnostic information regarding the failure.

Declared In

FMDatabase.h

executeUpdate:

- (BOOL)executeUpdate:(NSString *)sql, ...
Discussion

Execute single update statement

This method executes a single SQL update statement (i.e. any SQL that does not return results, such as UPDATE, INSERT, or DELETE. This method employs sqlite3_prepare_v2, sqlite3_bind to bind values to ? placeholders in the SQL with the optional list of parameters, and sqlite_step to perform the update.

The optional values provided to this method should be objects (e.g. NSString, NSNumber, NSNull, NSDate, and NSData objects), not fundamental data types (e.g. int, long, NSInteger, etc.). This method automatically handles the aforementioned object types, and all other object types will be interpreted as text values using the object’s description method.

Note: This technique supports the use of ? placeholders in the SQL, automatically binding any supplied value parameters to those placeholders. This approach is more robust than techniques that entail using stringWithFormat to manually build SQL statements, which can be problematic if the values happened to include any characters that needed to be quoted.

Note: If you want to use this from Swift, please note that you must include FMDatabaseVariadic.swift in your project. Without that, you cannot use this method directly, and instead have to use methods such as executeUpdate:withArgumentsInArray:.

Parameters

sql

The SQL to be performed, with optional ? placeholders.

...

Optional parameters to bind to ? placeholders in the SQL statement. These should be Objective-C objects (e.g. NSString, NSNumber, etc.), not fundamental C data types (e.g. int, char *, etc.).

Return Value

YES upon success; NO upon failure. If failed, you can call lastError, lastErrorCode, or lastErrorMessage for diagnostic information regarding the failure.

Declared In

FMDatabase.h

executeUpdate:withArgumentsInArray:

- (BOOL)executeUpdate:(NSString *)sql withArgumentsInArray:(NSArray *)arguments
Discussion

Execute single update statement

This method executes a single SQL update statement (i.e. any SQL that does not return results, such as UPDATE, INSERT, or DELETE. This method employs sqlite3_prepare_v2 and sqlite3_bind binding any ? placeholders in the SQL with the optional list of parameters.

The optional values provided to this method should be objects (e.g. NSString, NSNumber, NSNull, NSDate, and NSData objects), not fundamental data types (e.g. int, long, NSInteger, etc.). This method automatically handles the aforementioned object types, and all other object types will be interpreted as text values using the object’s description method.

Parameters

sql

The SQL to be performed, with optional ? placeholders.

arguments

A NSArray of objects to be used when binding values to the ? placeholders in the SQL statement.

Return Value

YES upon success; NO upon failure. If failed, you can call lastError, lastErrorCode, or lastErrorMessage for diagnostic information regarding the failure.

Declared In

FMDatabase.h

executeUpdate:withErrorAndBindings:

- (BOOL)executeUpdate:(NSString *)sql withErrorAndBindings:(NSError **)outErr, ...
Discussion

Execute single update statement

This method executes a single SQL update statement (i.e. any SQL that does not return results, such as UPDATE, INSERT, or DELETE. This method employs sqlite3_prepare_v2, sqlite3_bind to bind values to ? placeholders in the SQL with the optional list of parameters, and sqlite_step to perform the update.

The optional values provided to this method should be objects (e.g. NSString, NSNumber, NSNull, NSDate, and NSData objects), not fundamental data types (e.g. int, long, NSInteger, etc.). This method automatically handles the aforementioned object types, and all other object types will be interpreted as text values using the object’s description method.

Parameters

sql

The SQL to be performed, with optional ? placeholders.

outErr

A reference to the NSError pointer to be updated with an auto released NSError object if an error if an error occurs. If nil, no NSError object will be returned.

...

Optional parameters to bind to ? placeholders in the SQL statement. These should be Objective-C objects (e.g. NSString, NSNumber, etc.), not fundamental C data types (e.g. int, char *, etc.).

Return Value

YES upon success; NO upon failure. If failed, you can call lastError, lastErrorCode, or lastErrorMessage for diagnostic information regarding the failure.

Declared In

FMDatabase.h

executeUpdate:withParameterDictionary:

- (BOOL)executeUpdate:(NSString *)sql withParameterDictionary:(NSDictionary *)arguments
Discussion

Execute single update statement

This method executes a single SQL update statement (i.e. any SQL that does not return results, such as UPDATE, INSERT, or DELETE. This method employs sqlite3_prepare_v2 and sqlite_step to perform the update. Unlike the other executeUpdate methods, this uses printf-style formatters (e.g. %s, %d, etc.) to build the SQL.

The optional values provided to this method should be objects (e.g. NSString, NSNumber, NSNull, NSDate, and NSData objects), not fundamental data types (e.g. int, long, NSInteger, etc.). This method automatically handles the aforementioned object types, and all other object types will be interpreted as text values using the object’s description method.

Parameters

sql

The SQL to be performed, with optional ? placeholders.

arguments

A NSDictionary of objects keyed by column names that will be used when binding values to the ? placeholders in the SQL statement.

Return Value

YES upon success; NO upon failure. If failed, you can call lastError, lastErrorCode, or lastErrorMessage for diagnostic information regarding the failure.

Declared In

FMDatabase.h

executeUpdate:withVAList:

- (BOOL)executeUpdate:(NSString *)sql withVAList:(va_list)args
Discussion

Execute single update statement

This method executes a single SQL update statement (i.e. any SQL that does not return results, such as UPDATE, INSERT, or DELETE. This method employs sqlite3_prepare_v2 and sqlite_step to perform the update. Unlike the other executeUpdate methods, this uses printf-style formatters (e.g. %s, %d, etc.) to build the SQL.

The optional values provided to this method should be objects (e.g. NSString, NSNumber, NSNull, NSDate, and NSData objects), not fundamental data types (e.g. int, long, NSInteger, etc.). This method automatically handles the aforementioned object types, and all other object types will be interpreted as text values using the object’s description method.

Parameters

sql

The SQL to be performed, with optional ? placeholders.

args

A va_list of arguments.

Return Value

YES upon success; NO upon failure. If failed, you can call lastError, lastErrorCode, or lastErrorMessage for diagnostic information regarding the failure.

Declared In

FMDatabase.h

executeUpdateWithFormat:

- (BOOL)executeUpdateWithFormat:(NSString *)format, ...
Discussion

Execute single update statement

This method executes a single SQL update statement (i.e. any SQL that does not return results, such as UPDATE, INSERT, or DELETE. This method employs sqlite3_prepare_v2 and sqlite_step to perform the update. Unlike the other executeUpdate methods, this uses printf-style formatters (e.g. %s, %d, etc.) to build the SQL. Do not use ? placeholders in the SQL if you use this method.

Note: This method does not technically perform a traditional printf-style replacement. What this method actually does is replace the printf-style percent sequences with a SQLite ? placeholder, and then bind values to that placeholder. Thus the following command

[db executeUpdateWithFormat:@"INSERT INTO test (name) VALUES (%@)", @"Gus"];

is actually replacing the %@ with ? placeholder, and then performing something equivalent to executeUpdate:

[db executeUpdate:@"INSERT INTO test (name) VALUES (?)", @"Gus"];

There are two reasons why this distinction is important. First, the printf-style escape sequences can only be used where it is permissible to use a SQLite ? placeholder. You can use it only for values in SQL statements, but not for table names or column names or any other non-value context. This method also cannot be used in conjunction with pragma statements and the like. Second, note the lack of quotation marks in the SQL. The VALUES clause was not VALUES ('%@') (like you might have to do if you built a SQL statement using NSString method stringWithFormat), but rather simply VALUES (%@).

Parameters

format

The SQL to be performed, with printf-style escape sequences.

...

Optional parameters to bind to use in conjunction with the printf-style escape sequences in the SQL statement.

Return Value

YES upon success; NO upon failure. If failed, you can call lastError, lastErrorCode, or lastErrorMessage for diagnostic information regarding the failure.

Declared In

FMDatabase.h

getSchema

- (FMResultSet *)getSchema
Discussion

The schema of the database.

This will be the schema for the entire database. For each entity, each row of the result set will include the following fields:

  • type - The type of entity (e.g. table, index, view, or trigger)
  • name - The name of the object
  • tbl_name - The name of the table to which the object references
  • rootpage - The page number of the root b-tree page for tables and indices
  • sql - The SQL that created the entity

Return Value

FMResultSet of schema; nil on error.

Declared In

FMDatabaseAdditions.h

getTableSchema:

- (FMResultSet *)getTableSchema:(NSString *)tableName
Discussion

The schema of the database.

This will be the schema for a particular table as report by SQLite PRAGMA, for example:

PRAGMA table_info('employees')

This will report:

  • cid - The column ID number
  • name - The name of the column
  • type - The data type specified for the column
  • notnull - whether the field is defined as NOT NULL (i.e. values required)
  • dflt_value - The default value for the column
  • pk - Whether the field is part of the primary key of the table

Parameters

tableName

The name of the table for whom the schema will be returned.

Return Value

FMResultSet of schema; nil on error.

See Also

Declared In

FMDatabaseAdditions.h

goodConnection

- (BOOL)goodConnection
Discussion

Test to see if we have a good connection to the database.

This will confirm whether:

  • is database open
  • if open, it will try a simple SELECT statement and confirm that it succeeds.

Return Value

YES if everything succeeds, NO on failure.

Declared In

FMDatabase.h

hadError

- (BOOL)hadError
Discussion

Had error

Return Value

YES if there was an error, NO if no error.

Declared In

FMDatabase.h

hasDateFormatter

- (BOOL)hasDateFormatter
Discussion

Test whether the database has a date formatter assigned.

Return Value

YES if there is a date formatter; NO if not.

Declared In

FMDatabase.h

hasOpenResultSets

- (BOOL)hasOpenResultSets
Discussion

Whether database has any open result sets

Return Value

YES if there are open result sets; NO if not.

Declared In

FMDatabase.h

inSavePoint:

- (NSError *)inSavePoint:(void ( ^ ) ( BOOL *rollback ))block
Discussion

Start save point

Parameters

block

Block of code to perform from within save point.

Return Value

The NSError corresponding to the error, if any. If no error, returns nil.

Declared In

FMDatabase.h

inTransaction

- (BOOL)inTransaction
Discussion

Identify whether currently in a transaction or not

Return Value

YES if currently within transaction; NO if not.

Declared In

FMDatabase.h

initWithPath:

- (instancetype)initWithPath:(NSString *)inPath
Discussion

Initialize a FMDatabase object.

An FMDatabase is created with a path to a SQLite database file. This path can be one of these three:

  1. A file system path. The file does not have to exist on disk. If it does not exist, it is created for you.
  2. An empty string (@""). An empty database is created at a temporary location. This database is deleted with the FMDatabase connection is closed.
  3. nil. An in-memory database is created. This database will be destroyed with the FMDatabase connection is closed.

For example, to create/open a database in your Mac OS X tmp folder:

FMDatabase *db = [FMDatabase databaseWithPath:@"/tmp/tmp.db"];

Or, in iOS, you might open a database in the app’s Documents directory:

NSString *docsPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];
NSString *dbPath   = [docsPath stringByAppendingPathComponent:@"test.db"];
FMDatabase *db     = [FMDatabase databaseWithPath:dbPath];

(For more information on temporary and in-memory databases, read the sqlite documentation on the subject: http://www.sqlite.org/inmemorydb.html)

Parameters

inPath

Path of database file

Return Value

FMDatabase object if successful; nil if failure.

Declared In

FMDatabase.h

installTokenizerModule

- (BOOL)installTokenizerModule
Discussion

Calls the fts3_tokenizer() function on this database, installing tokenizer module with the ‘fmdb’ name.

Declared In

FMDatabase+FTS3.h

installTokenizerModuleWithName:

- (BOOL)installTokenizerModuleWithName:(NSString *)name
Discussion

Calls the fts3_tokenizer() function on this database, installing the tokenizer module with specified name.

Declared In

FMDatabase+FTS3.h

intForQuery:

- (int)intForQuery:(NSString *)query, ...
Discussion

Return int value for query

Note: To use this method from Swift, you must include FMDatabaseAdditionsVariadic.swift in your project.

Parameters

query

The SQL query to be performed.

...

A list of parameters that will be bound to the ? placeholders in the SQL query.

Return Value

int value.

Declared In

FMDatabaseAdditions.h

issueCommand:forTable:

- (BOOL)issueCommand:(NSString *)command forTable:(NSString *)tableName
Discussion

Runs a “special command” for FTS3/FTS4 tables.

Declared In

FMDatabase+FTS3.h

lastError

- (NSError *)lastError
Discussion

Last error

Return Value

NSError representing the last error.

Declared In

FMDatabase.h

lastErrorCode

- (int)lastErrorCode
Discussion

Last error code

Returns the numeric result code or extended result code for the most recent failed SQLite API call associated with a database connection. If a prior API call failed but the most recent API call succeeded, this return value is undefined.

Return Value

Integer value of the last error code.

Declared In

FMDatabase.h

lastErrorMessage

- (NSString *)lastErrorMessage
Discussion

Last error message

Returns the English-language text that describes the most recent failed SQLite API call associated with a database connection. If a prior API call failed but the most recent API call succeeded, this return value is undefined.

Return Value

NSString of the last error message.

Declared In

FMDatabase.h

lastInsertRowId

- (sqlite_int64)lastInsertRowId
Discussion

Last insert rowid

Each entry in an SQLite table has a unique 64-bit signed integer key called the “rowid”. The rowid is always available as an undeclared column named ROWID, OID, or _ROWID_ as long as those names are not also used by explicitly declared columns. If the table has a column of type INTEGER PRIMARY KEY then that column is another alias for the rowid.

This routine returns the rowid of the most recent successful INSERT into the database from the database connection in the first argument. As of SQLite version 3.7.7, this routines records the last insert rowid of both ordinary tables and virtual tables. If no successful INSERTs have ever occurred on that database connection, zero is returned.

Return Value

The rowid of the last inserted row.

Declared In

FMDatabase.h

longForQuery:

- (long)longForQuery:(NSString *)query, ...
Discussion

Return long value for query

Note: To use this method from Swift, you must include FMDatabaseAdditionsVariadic.swift in your project.

Parameters

query

The SQL query to be performed.

...

A list of parameters that will be bound to the ? placeholders in the SQL query.

Return Value

long value.

Declared In

FMDatabaseAdditions.h

makeFunctionNamed:maximumArguments:withBlock:

- (void)makeFunctionNamed:(NSString *)name maximumArguments:(int)count withBlock:(void ( ^ ) ( sqlite3_context *context , int argc , sqlite3_value **argv ))block
Discussion

Adds SQL functions or aggregates or to redefine the behavior of existing SQL functions or aggregates.

For example:

[queue inDatabase:^(FMDatabase *adb) {

    [adb executeUpdate:@"create table ftest (foo text)"];
    [adb executeUpdate:@"insert into ftest values ('hello')"];
    [adb executeUpdate:@"insert into ftest values ('hi')"];
    [adb executeUpdate:@"insert into ftest values ('not h!')"];
    [adb executeUpdate:@"insert into ftest values ('definitely not h!')"];

    [adb makeFunctionNamed:@"StringStartsWithH" maximumArguments:1 withBlock:^(sqlite3_context *context, int aargc, sqlite3_value **aargv) {
        if (sqlite3_value_type(aargv[0]) == SQLITE_TEXT) {
            @autoreleasepool {
                const char *c = (const char *)sqlite3_value_text(aargv[0]);
                NSString *s = [NSString stringWithUTF8String:c];
                sqlite3_result_int(context, [s hasPrefix:@"h"]);
            }
        }
        else {
            NSLog(@"Unknown formart for StringStartsWithH (%d) %s:%d", sqlite3_value_type(aargv[0]), __FUNCTION__, __LINE__);
            sqlite3_result_null(context);
        }
    }];

    int rowCount = 0;
    FMResultSet *ars = [adb executeQuery:@"select * from ftest where StringStartsWithH(foo)"];
    while ([ars next]) {
        rowCount++;
        NSLog(@"Does %@ start with 'h'?", [rs stringForColumnIndex:0]);
    }
    FMDBQuickCheck(rowCount == 2);
}];

Parameters

name

Name of function

count

Maximum number of parameters

block

The block of code for the function

Declared In

FMDatabase.h

maxBusyRetryTimeInterval

- (NSTimeInterval)maxBusyRetryTimeInterval

open

- (BOOL)open
Discussion

Opening a new database connection

The database is opened for reading and writing, and is created if it does not already exist.

Return Value

YES if successful, NO on error.

Declared In

FMDatabase.h

openWithFlags:

- (BOOL)openWithFlags:(int)flags
Discussion

Opening a new database connection with flags

Parameters

flags

one of the following three values, optionally combined with the SQLITE_OPEN_NOMUTEX, SQLITE_OPEN_FULLMUTEX, SQLITE_OPEN_SHAREDCACHE, SQLITE_OPEN_PRIVATECACHE, and/or SQLITE_OPEN_URI flags:

SQLITE_OPEN_READONLY

The database is opened in read-only mode. If the database does not already exist, an error is returned.

SQLITE_OPEN_READWRITE

The database is opened for reading and writing if possible, or reading only if the file is write protected by the operating system. In either case the database must already exist, otherwise an error is returned.

SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE

The database is opened for reading and writing, and is created if it does not already exist. This is the behavior that is always used for open method.

Return Value

YES if successful, NO on error.

Declared In

FMDatabase.h

rekey:

- (BOOL)rekey:(NSString *)key
Discussion

Reset encryption key

Warning: You need to have purchased the sqlite encryption extensions for this method to work.

Parameters

key

The key to be used.

Return Value

YES if success, NO on error.

Declared In

FMDatabase.h

rekeyWithData:

- (BOOL)rekeyWithData:(NSData *)keyData
Discussion

Reset encryption key using keyData.

Warning: You need to have purchased the sqlite encryption extensions for this method to work.

Parameters

keyData

The NSData to be used.

Return Value

YES if success, NO on error.

Declared In

FMDatabase.h

releaseSavePointWithName:error:

- (BOOL)releaseSavePointWithName:(NSString *)name error:(NSError **)outErr
Discussion

Release save point

Parameters

name

Name of save point.

outErr

A NSError object to receive any error object (if any).

Return Value

YES on success; NO on failure. If failed, you can call lastError, lastErrorCode, or lastErrorMessage for diagnostic information regarding the failure.

Declared In

FMDatabase.h

rollback

- (BOOL)rollback
Discussion

Rollback a transaction

Rollback a transaction that was initiated with either beginTransaction or with beginDeferredTransaction.

Return Value

YES on success; NO on failure. If failed, you can call lastError, lastErrorCode, or lastErrorMessage for diagnostic information regarding the failure.

Declared In

FMDatabase.h

rollbackToSavePointWithName:error:

- (BOOL)rollbackToSavePointWithName:(NSString *)name error:(NSError **)outErr
Discussion

Roll back to save point

Parameters

name

Name of save point.

outErr

A NSError object to receive any error object (if any).

Return Value

YES on success; NO on failure. If failed, you can call lastError, lastErrorCode, or lastErrorMessage for diagnostic information regarding the failure.

Declared In

FMDatabase.h

setApplicationID:

- (void)setApplicationID:(uint32_t)appID
Discussion

Set the application ID

Parameters

appID

The uint32_t numeric value of the application ID.

See Also

Declared In

FMDatabaseAdditions.h

setApplicationIDString:

- (void)setApplicationIDString:(NSString *)string
Discussion

Set the application ID string

Parameters

string

The NSString value of the application ID.

Declared In

FMDatabaseAdditions.h

setDateFormat:

- (void)setDateFormat:(NSDateFormatter *)format
Discussion

Set to a date formatter to use string dates with sqlite instead of the default UNIX timestamps.

Warning: Note there is no direct getter for the NSDateFormatter, and you should not use the formatter you pass to FMDB for other purposes, as NSDateFormatter is not thread-safe.

Parameters

format

Set to nil to use UNIX timestamps. Defaults to nil. Should be set using a formatter generated using FMDatabase::storeableDateFormat.

Declared In

FMDatabase.h

setKey:

- (BOOL)setKey:(NSString *)key
Discussion

Set encryption key.

Warning: You need to have purchased the sqlite encryption extensions for this method to work.

Parameters

key

The key to be used.

Return Value

YES if success, NO on error.

Declared In

FMDatabase.h

setKeyWithData:

- (BOOL)setKeyWithData:(NSData *)keyData
Discussion

Set encryption key using keyData.

Warning: You need to have purchased the sqlite encryption extensions for this method to work.

Parameters

keyData

The NSData to be used.

Return Value

YES if success, NO on error.

Declared In

FMDatabase.h

setMaxBusyRetryTimeInterval:

- (void)setMaxBusyRetryTimeInterval:(NSTimeInterval)timeoutInSeconds

setShouldCacheStatements:

- (void)setShouldCacheStatements:(BOOL)value
Discussion

Set whether should cache statements or not

Parameters

value

YES if should cache statements; NO if not.

Declared In

FMDatabase.h

setUserVersion:

- (void)setUserVersion:(uint32_t)version
Discussion

Set the user-version

Parameters

version

The uint32_t numeric value of the user version.

See Also

Declared In

FMDatabaseAdditions.h

shouldCacheStatements

- (BOOL)shouldCacheStatements
Discussion

Return whether should cache statements or not

Return Value

YES if should cache statements; NO if not.

Declared In

FMDatabase.h

sqliteHandle

- (sqlite3 *)sqliteHandle
Discussion

The underlying SQLite handle

Return Value

The sqlite3 pointer.

Declared In

FMDatabase.h

startSavePointWithName:error:

- (BOOL)startSavePointWithName:(NSString *)name error:(NSError **)outErr
Discussion

Start save point

Parameters

name

Name of save point.

outErr

A NSError object to receive any error object (if any).

Return Value

YES on success; NO on failure. If failed, you can call lastError, lastErrorCode, or lastErrorMessage for diagnostic information regarding the failure.

Declared In

FMDatabase.h

stringForQuery:

- (NSString *)stringForQuery:(NSString *)query, ...
Discussion

Return NSString value for query

Note: To use this method from Swift, you must include FMDatabaseAdditionsVariadic.swift in your project.

Parameters

query

The SQL query to be performed.

...

A list of parameters that will be bound to the ? placeholders in the SQL query.

Return Value

NSString value.

Declared In

FMDatabaseAdditions.h

stringFromDate:

- (NSString *)stringFromDate:(NSDate *)date
Discussion

Convert the supplied NSDate to NSString, using the current database formatter.

Parameters

date

NSDate of date to convert to NSString.

Return Value

The NSString representation of the date; nil if no formatter is set.

Declared In

FMDatabase.h

tableExists:

- (BOOL)tableExists:(NSString *)tableName
Discussion

Does table exist in database?

Parameters

tableName

The name of the table being looked for.

Return Value

YES if table found; NO if not found.

Declared In

FMDatabaseAdditions.h

update:withErrorAndBindings:

- (BOOL)update:(NSString *)sql withErrorAndBindings:(NSError **)outErr, ...
Discussion

Execute single update statement

Warning: Deprecated: Please use <executeUpdate:withErrorAndBindings> instead.

Declared In

FMDatabase.h

userVersion

- (uint32_t)userVersion
Discussion

Retrieve user version

Return Value

The uint32_t numeric value of the user version.

Declared In

FMDatabaseAdditions.h

validateSQL:error:

- (BOOL)validateSQL:(NSString *)sql error:(NSError **)error
Discussion

Validate SQL statement

This validates SQL statement by performing sqlite3_prepare_v2, but not returning the results, but instead immediately calling sqlite3_finalize.

Parameters

sql

The SQL statement being validated.

error

This is a pointer to a NSError object that will receive the autoreleased NSError object if there was any error. If this is nil, no NSError result will be returned.

Return Value

YES if validation succeeded without incident; NO otherwise.

Declared In

FMDatabaseAdditions.h