FMDatabase

Whether should trace execution

Whether checked out or not

Crash on errors

Logs errors

Dictionary of cached statements

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 zero-length 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 open a database in the app’s “Application Support” directory:

NSURL *folder  = [[NSFileManager defaultManager] URLForDirectory:NSApplicationSupportDirectory inDomain:NSUserDomainMask appropriateForURL:nil create:true error:&error];
NSURL *fileURL = [folder URLByAppendingPathComponent:@"test.db"];
FMDatabase *db = [FMDatabase databaseWithPath:fileURL.path];

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

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 URL. The file does not have to exist on disk. If it does not exist, it is created for you.

2. nil . An in-memory database is created. This database will be destroyed with the FMDatabase connection is closed.

For example, to open a database in the app’s “Application Support” directory:

NSURL *folder  = [[NSFileManager defaultManager] URLForDirectory:NSApplicationSupportDirectory inDomain:NSUserDomainMask appropriateForURL:nil create:true error:&error];
NSURL *fileURL = [folder URLByAppendingPathComponent:@"test.db"];
FMDatabase *db = [FMDatabase databaseWithURL:fileURL];

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

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. A zero-length 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 open a database in the app’s “Application Support” directory:

 NSURL *folder  = [[NSFileManager defaultManager] URLForDirectory:NSApplicationSupportDirectory inDomain:NSUserDomainMask appropriateForURL:nil create:true error:&error];
 NSURL *fileURL = [folder URLByAppendingPathComponent:@"test.db"];
 FMDatabase *db = [[FMDatabase alloc] initWithPath:fileURL.path];

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

Initialize a FMDatabase object.

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

1. A file system URL. The file does not have to exist on disk. If it does not exist, it is created for you.

2. nil . An in-memory database is created. This database will be destroyed with the FMDatabase connection is closed.

For example, to open a database in the app’s “Application Support” directory:

 NSURL *folder  = [[NSFileManager defaultManager] URLForDirectory:NSApplicationSupportDirectory inDomain:NSUserDomainMask appropriateForURL:nil create:true error:&error];
 NSURL *fileURL = [folder URLByAppendingPathComponent:@"test.db"];
 FMDatabase *db = [[FMDatabase alloc] initWithURL:fileURL];

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

Is the database open or not?

Opening a new database connection

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

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

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.

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

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.

Closing a database connection

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.

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.

Execute single update statement

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.

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.

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

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.

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, this throws errors, as if it were defined as follows:

func executeUpdate(sql: String, values: [Any]?) throws -> Bool { }

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.

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.

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 .

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.

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 INSERT statements have ever occurred on that database connection, zero is returned.

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.

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.

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.

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

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.

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, this throws errors, as if it were defined as follows:

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

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.

Undocumented

Prepare SQL statement.

Begin a transaction

Begin a deferred transaction

Begin an immediate transaction

Begin an exclusive transaction

Commit a transaction

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

Rollback a transaction

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

Identify whether currently in a transaction or not

Undocumented

Clear cached statements

Close all open result sets

Whether database has any open result sets

Whether should cache statements or not

Interupt pending database operation

This method causes any pending database operation to abort and return at its earliest opportunity

Set encryption key.

Reset encryption key

Set encryption key using keyData.

Reset encryption key using keyData.

The path of the database file.

The file URL of the database file.

The underlying SQLite handle .

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.

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.

Last extended error code

Returns the numeric 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.

Had error

Last error

Undocumented

Start save point

Release save point

Roll back to save point

Start save point

Performs a WAL checkpoint

Performs a WAL checkpoint

Performs a WAL checkpoint

Test to see if the library is threadsafe

Examine/set limits

Run-time library version numbers

The FMDB version number as a string in the form of "2.7.7" .

If you want to compare version number strings, you can use NSNumericSearch option:

NSComparisonResult result = [[FMDatabase FMDBUserVersion] compare:@"2.11.0" options:NSNumericSearch];

@returns The version number string.

The FMDB version

This returns the FMDB as hexadecimal value, e.g., 0x0243 for version 2.4.3.

 NSComparisonResult result = [[FMDatabase FMDBUserVersion] compare:@"2.11.0" options:NSNumericSearch];

@returns The version number in hexadecimal, e.g., 0x0243 for version 2.4.3. If any component exceeds what can be can be represented in four bits, we’ll max it out at 0xf .

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

For example:

[db makeFunctionNamed:@"RemoveDiacritics" arguments:1 block:^(void *context, int argc, void **argv) {
    SqliteValueType type = [self.db valueType:argv[0]];
    if (type == SqliteValueTypeNull) {
        [self.db resultNullInContext:context];
         return;
    }
    if (type != SqliteValueTypeText) {
        [self.db resultError:@"Expected text" context:context];
        return;
    }
    NSString *string = [self.db valueString:argv[0]];
    NSString *result = [string stringByFoldingWithOptions:NSDiacriticInsensitiveSearch locale:nil];
    [self.db resultString:result context:context];
}];

FMResultSet *rs = [db executeQuery:@"SELECT * FROM employees WHERE RemoveDiacritics(first_name) LIKE 'jose'"];
NSAssert(rs, @"Error %@", [db lastErrorMessage]);

Undocumented

Undocumented

Get integer value of parameter in custom function.

Get long value of parameter in custom function.

Get double value of parameter in custom function.

Get NSData value of parameter in custom function.

Get string value of parameter in custom function.

Return null value from custom function.

Return integer value from custom function.

Return long value from custom function.

Return double value from custom function.

Return NSData object from custom function.

Return string value from custom function.

Return error string from custom function.

Return error code from custom function.

Report memory error in custom function.

Report that string or BLOB is too long to represent in custom function.

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"];

Test whether the database has a date formatter assigned.

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

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

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

Return int value for query

Return long value for query

Return BOOL value for query

Return double value for query

Return NSString value for query

Return NSData value for query

Return NSDate value for query

Does table exist in database?

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

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

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

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

Validate SQL statement

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