FMDatabase Class Reference

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.

Properties

  traceExecution

Whether should trace execution

@property (atomic, assign) BOOL traceExecution

Discussion

Whether should trace execution

Declared In

FMDatabase.h

  checkedOut

Whether checked out or not

@property (atomic, assign) BOOL checkedOut

Discussion

Whether checked out or not

Declared In

FMDatabase.h

  crashOnErrors

Crash on errors

@property (atomic, assign) BOOL crashOnErrors

Discussion

Crash on errors

Declared In

FMDatabase.h

  logsErrors

Logs errors

@property (atomic, assign) BOOL logsErrors

Discussion

Logs errors

Declared In

FMDatabase.h

  cachedStatements

Dictionary of cached statements

@property (atomic, retain) NSMutableDictionary *cachedStatements

Discussion

Dictionary of cached statements

Declared In

FMDatabase.h

Initialization

+ databaseWithPath:

Create a FMDatabase object.

+ (instancetype)databaseWithPath:(NSString *)inPath

Parameters

inPath

Path of database file

Return Value

FMDatabase object if successful; nil if failure.

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)

Declared In

FMDatabase.h

– initWithPath:

Initialize a FMDatabase object.

- (instancetype)initWithPath:(NSString *)inPath

Parameters

inPath

Path of database file

Return Value

FMDatabase object if successful; nil if failure.

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)

Declared In

FMDatabase.h

Opening and closing database

– open

Opening a new database connection

- (BOOL)open

Return Value

YES if successful, NO on error.

Discussion

Opening a new database connection

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

Declared In

FMDatabase.h

– openWithFlags:

Opening a new database connection with flags and an optional virtual file system (VFS)

- (BOOL)openWithFlags:(int)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.

If vfs is given the value is passed to the vfs parameter of sqlite3_open_v2.

Return Value

YES if successful, NO on error.

Discussion

Opening a new database connection with flags and an optional virtual file system (VFS)

Declared In

FMDatabase.h

– close

Closing a database connection

- (BOOL)close

Return Value

YES if success, NO on error.

Discussion

Closing a database connection

Declared In

FMDatabase.h

– goodConnection

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

- (BOOL)goodConnection

Return Value

YES if everything succeeds, NO on failure.

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.

Declared In

FMDatabase.h

Perform updates

– executeUpdate:withErrorAndBindings:

Execute single update statement

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

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.

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.

Declared In

FMDatabase.h

– update:withErrorAndBindings:

Execute single update statement

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

Discussion

Execute single update statement

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

Declared In

FMDatabase.h

– executeUpdate:

Execute single update statement

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

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.

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

Declared In

FMDatabase.h

– executeUpdateWithFormat:

Execute single update statement

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

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.

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 (%@).

Declared In

FMDatabase.h

– executeUpdate:withArgumentsInArray:

Execute single update statement

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

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.

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.

Declared In

FMDatabase.h

– executeUpdate:values:error:

Execute single update statement

- (BOOL)executeUpdate:(NSString *)sql values:(NSArray *)values error:(NSError *__autoreleasing *)error

Parameters

sql

The SQL to be performed, with optional ? placeholders.

values

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

error

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

Return Value

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

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.

This is similar to executeUpdate:withArgumentsInArray:, except that this also accepts a pointer to a NSError pointer, so that errors can be returned.

In Swift 2, this throws errors, as if it were defined as follows:

func executeUpdate(sql: String!, values: [AnyObject]!) throws -> Bool

Declared In

FMDatabase.h

– executeUpdate:withParameterDictionary:

Execute single update statement

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

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.

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.

Declared In

FMDatabase.h

– executeUpdate:withVAList:

Execute single update statement

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

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.

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.

Declared In

FMDatabase.h

– executeStatements:

Execute multiple SQL statements

- (BOOL)executeStatements:(NSString *)sql

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.

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.

Declared In

FMDatabase.h

– executeStatements:withResultBlock:

Execute multiple SQL statements with callback handler

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

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.

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.

Declared In

FMDatabase.h

– lastInsertRowId

Last insert rowid

- (sqlite_int64)lastInsertRowId

Return Value

The rowid of the last inserted row.

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.

Declared In

FMDatabase.h

– changes

The number of rows changed by prior SQL statement.

- (int)changes

Return Value

The number of rows changed by prior SQL statement.

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.

Declared In

FMDatabase.h

Retrieving results

– executeQuery:

Execute select statement

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

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.

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.

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

Declared In

FMDatabase.h

– executeQueryWithFormat:

Execute select statement

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

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.

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=%@.

Declared In

FMDatabase.h

– executeQuery:withArgumentsInArray:

Execute select statement

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

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.

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.

Declared In

FMDatabase.h

– executeQuery:values:error:

Execute select statement

- (FMResultSet *)executeQuery:(NSString *)sql values:(NSArray *)values error:(NSError *__autoreleasing *)error

Parameters

sql

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

values

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

error

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

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.

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 is similar to executeQuery:withArgumentsInArray:, except that this also accepts a pointer to a NSError pointer, so that errors can be returned.

In Swift 2, this throws errors, as if it were defined as follows:

func executeQuery(sql: String!, values: [AnyObject]!) throws -> FMResultSet!

Note: When called from Swift, only use the first two parameters, sql and values. This but throws the error.

Declared In

FMDatabase.h

– executeQuery:withParameterDictionary:

Execute select statement

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

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.

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.

Declared In

FMDatabase.h

Transactions

– beginTransaction

Begin a transaction

- (BOOL)beginTransaction

Return Value

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

Discussion

Begin a transaction

Declared In

FMDatabase.h

– beginDeferredTransaction

Begin a deferred transaction

- (BOOL)beginDeferredTransaction

Return Value

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

Discussion

Begin a deferred transaction

Declared In

FMDatabase.h

– commit

Commit a transaction

- (BOOL)commit

Return Value

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

Discussion

Commit a transaction

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

Declared In

FMDatabase.h

– rollback

Rollback a transaction

- (BOOL)rollback

Return Value

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

Discussion

Rollback a transaction

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

Declared In

FMDatabase.h

– inTransaction

Identify whether currently in a transaction or not

- (BOOL)inTransaction

Return Value

YES if currently within transaction; NO if not.

Discussion

Identify whether currently in a transaction or not

Declared In

FMDatabase.h

Cached statements and result sets

– clearCachedStatements

Clear cached statements

- (void)clearCachedStatements

Discussion

Clear cached statements

Declared In

FMDatabase.h

– closeOpenResultSets

Close all open result sets

- (void)closeOpenResultSets

Discussion

Close all open result sets

Declared In

FMDatabase.h

– hasOpenResultSets

Whether database has any open result sets

- (BOOL)hasOpenResultSets

Return Value

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

Discussion

Whether database has any open result sets

Declared In

FMDatabase.h

– shouldCacheStatements

Return whether should cache statements or not

- (BOOL)shouldCacheStatements

Return Value

YES if should cache statements; NO if not.

Discussion

Return whether should cache statements or not

Declared In

FMDatabase.h

– setShouldCacheStatements:

Set whether should cache statements or not

- (void)setShouldCacheStatements:(BOOL)value

Parameters

value

YES if should cache statements; NO if not.

Discussion

Set whether should cache statements or not

Declared In

FMDatabase.h

Encryption methods

– setKey:

Set encryption key.

- (BOOL)setKey:(NSString *)key

Parameters

key

The key to be used.

Return Value

YES if success, NO on error.

Discussion

Set encryption key.

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

Declared In

FMDatabase.h

– rekey:

Reset encryption key

- (BOOL)rekey:(NSString *)key

Parameters

key

The key to be used.

Return Value

YES if success, NO on error.

Discussion

Reset encryption key

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

Declared In

FMDatabase.h

– setKeyWithData:

Set encryption key using keyData.

- (BOOL)setKeyWithData:(NSData *)keyData

Parameters

keyData

The NSData to be used.

Return Value

YES if success, NO on error.

Discussion

Set encryption key using keyData.

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

Declared In

FMDatabase.h

– rekeyWithData:

Reset encryption key using keyData.

- (BOOL)rekeyWithData:(NSData *)keyData

Parameters

keyData

The NSData to be used.

Return Value

YES if success, NO on error.

Discussion

Reset encryption key using keyData.

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

Declared In

FMDatabase.h

General inquiry methods

– databasePath

The path of the database file

- (NSString *)databasePath

Return Value

path of database.

Discussion

The path of the database file

Declared In

FMDatabase.h

– sqliteHandle

The underlying SQLite handle

- (sqlite3 *)sqliteHandle

Return Value

The sqlite3 pointer.

Discussion

The underlying SQLite handle

Declared In

FMDatabase.h

Retrieving error codes

– lastErrorMessage

Last error message

- (NSString *)lastErrorMessage

Return Value

NSString of the last error message.

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.

Declared In

FMDatabase.h

– lastErrorCode

Last error code

- (int)lastErrorCode

Return Value

Integer value of the last error code.

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.

Declared In

FMDatabase.h

– hadError

Had error

- (BOOL)hadError

Return Value

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

Discussion

Had error

Declared In

FMDatabase.h

– lastError

Last error

- (NSError *)lastError

Return Value

NSError representing the last error.

Discussion

Last error

Declared In

FMDatabase.h

Save points

– startSavePointWithName:error:

Start save point

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

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.

Discussion

Start save point

Declared In

FMDatabase.h

– releaseSavePointWithName:error:

Release save point

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

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.

Discussion

Release save point

Declared In

FMDatabase.h

– rollbackToSavePointWithName:error:

Roll back to save point

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

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.

Discussion

Roll back to save point

Declared In

FMDatabase.h

– inSavePoint:

Start save point

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

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.

Discussion

Start save point

Declared In

FMDatabase.h

SQLite library status

+ isSQLiteThreadSafe

Test to see if the library is threadsafe

+ (BOOL)isSQLiteThreadSafe

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.

Discussion

Test to see if the library is threadsafe

Declared In

FMDatabase.h

+ sqliteLibVersion

Run-time library version numbers

+ (NSString *)sqliteLibVersion

Return Value

The sqlite library version string.

Discussion

Run-time library version numbers

Declared In

FMDatabase.h

Make SQL function

– makeFunctionNamed:maximumArguments:withBlock:

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

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

Parameters

name

Name of function

count

Maximum number of parameters

block

The block of code for the function

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);
}];

Declared In

FMDatabase.h

Date formatter

+ storeableDateFormat:

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

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

Parameters

format

A valid NSDateFormatter format string.

Return Value

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

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.

Declared In

FMDatabase.h

– hasDateFormatter

Test whether the database has a date formatter assigned.

- (BOOL)hasDateFormatter

Return Value

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

Discussion

Test whether the database has a date formatter assigned.

Declared In

FMDatabase.h

– setDateFormat:

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

- (void)setDateFormat:(NSDateFormatter *)format

Parameters

format

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

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.

Declared In

FMDatabase.h

– dateFromString:

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

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

Parameters

s

NSString to convert to NSDate.

Return Value

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

Discussion

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

Declared In

FMDatabase.h

– stringFromDate:

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

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

Parameters

date

NSDate of date to convert to NSString.

Return Value

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

Discussion

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

Declared In

FMDatabase.h