In the previous sections, we dealt
primarily with simple data types (strings, integers, and floating
points). In addition to scalar and text data types, the SQLite database
engine also supports the BLOB data type. A BLOB storage class allows
you to store binary data (e.g., image files) as-is. We will demonstrate
the mechanism for storing BLOBs in this section, and retrieving them in
the next section.
To explain the main concepts behind inserting BLOB
values in a database, we consider a new table in the database that
stores information about the companies we are investing in. In addition
to the company's symbol and name, we add an image column of type BLOB
that stores the logo of the company in PNG format.
Listing 1 shows the main() function. It creates a new companies table using the following SQL statement:
CREATE TABLE companies
(symbol VARCHAR(5) PRIMARY KEY, name VARCHAR(128), image BLOB)
Example 1. The main() function demonstrating storing BLOBs in a table.
#import "/usr/include/sqlite3.h"
int main(int argc, char* argv[]) {
char *sqlStatement;
sqlite3 *pDb;
char *errorMsg;
int returnCode;
char *databaseName;
NSAutoreleasePool * pool = [[NSAutoreleasePool alloc] init];
databaseName = "financial.db";
returnCode = sqlite3_open(databaseName, &pDb);
if(returnCode!=SQLITE_OK) {
fprintf(stderr, "Error in opening the database. Error: %s",
sqlite3_errmsg(pDb));
sqlite3_close(pDb);
return −1;
}
sqlStatement = "DROP TABLE IF EXISTS companies";
returnCode = sqlite3_exec(pDb, sqlStatement, NULL, NULL, &errorMsg);
if(returnCode!=SQLITE_OK) {
fprintf(stderr, "Error in dropping table companies. Error: %s",
errorMsg);
sqlite3_free(errorMsg);
}
sqlStatement =
"CREATE TABLE companies "
"(symbol VARCHAR(5) PRIMARY KEY, "
" name VARCHAR(128), image BLOB)";
returnCode = sqlite3_exec(pDb, sqlStatement, NULL, NULL, &errorMsg);
if(returnCode!=SQLITE_OK) {
fprintf(stderr, "Error in creating the companies table. Error: %s",
errorMsg);
sqlite3_free(errorMsg);
return −1;
}
insertCompany(pDb, "ALU", "Alcatel-Lucent");
insertCompany(pDb, "GOOG", "Google");
insertCompany(pDb, "MSFT", "Microsoft");
insertCompany(pDb, "NT", "Nortel");
sqlite3_close(pDb);
[pool release];
return 0;
}
|
After creating the companies table, we add four records by invoking the insertCompany() function shown in Listing 2.
Example 2. The insertCompany() function for inserting a company record that includes a BLOB image.
#import "/usr/include/sqlite3.h"
void insertCompany(sqlite3 *pDb, const char* symbol,const char* name){
int returnCode;
sqlite3_stmt *pStmt;
unsigned char *buffer;
char *st = "INSERT INTO companies VALUES (?, ?, ?)";
returnCode = sqlite3_prepare_v2(pDb, st, −1, &pStmt, 0);
if(returnCode != SQLITE_OK) {
fprintf(stderr, "Error in inserting into companies table.");
return;
}
NSMutableString *imageFileName =
[NSMutableString stringWithCString:symbol];
[imageFileName appendString:@".png"];
NSData * pData = [NSData dataWithContentsOfFile:imageFileName];
buffer = malloc([pData length]);
[pData getBytes:buffer];
sqlite3_bind_text(pStmt, 1, symbol, −1, SQLITE_STATIC);
sqlite3_bind_text(pStmt, 2, name, −1, SQLITE_STATIC);
sqlite3_bind_blob(pStmt, 3, buffer,[pData length], SQLITE_STATIC);
returnCode = sqlite3_step(pStmt);
if(returnCode != SQLITE_DONE) {
fprintf(stderr, "Error in inserting into companies table.");
}
returnCode = sqlite3_finalize(pStmt);
if(returnCode != SQLITE_OK) {
fprintf(stderr, "Error in inserting into companies table. ");
}
free(buffer);
}
|
The insertCompany() function starts by compiling the following INSERT statement:
INSERT INTO companies VALUES (?, ?, ?)
This statement is a little bit different from what we have used before. This type of statement is called a parametrized statement.
It uses "?" indicating that a value that will be bound later. To
actually bind a parameter to a specific value, you use one of several
functions that have the form sqlite3_bind_xxxx(). For example, to bind an integer, you use sqlite3_bind_int(). The following are the important bind functions:
Binding BLOBs. The bind function for BLOBs is declared as: int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n,
void(*)(void*))
The first parameter of this, and all bind functions,
is a pointer to a statement handle received from the statement
preparation function sqlite3_prepare_v2(). The second
parameter is the index of the SQL statement's parameter that you want
to bind. Note that the index starts at 1. The third parameter is the
number of bytes in the BLOB. The fourth parameter is a pointer to a
function that will be invoked when the SQLite engine finishes with the
execution of the statement to release the BLOB's memory. There are two
special values for this parameter: SQLITE_STATIC. This special value informs the SQLite engine that the BLOB is static and does not need to be freed. SQLITE_TRANSIENT.
This special value informs the SQLite engine that the BLOB is transient
and needs to be copied. The SQLite engine makes a copy of the BLOB
before the bind function returns.
Binding text. The bind function for text is very similar to the one for BLOBs. int sqlite3_bind_text(sqlite3_stmt*, int, const char*,
int n, void(*)(void*))
The first two parameters, as well as the last one,
are the same as the BLOB's bind function. The third parameter is the
zero-terminated text that you would like to bind. The fourth parameter
is the length (in bytes) of the text, excluding the zero-terminator. If
the value is negative, then the number of bytes up to the first zero
terminator is used. Binding integers. The bind function for integers is very simple: int sqlite3_bind_int(sqlite3_stmt*, int, int)
The first two parameters are the same as above. The last parameter is the integer value. Binding reals. The bind function for real numbers is also very simple, and is similar to binding integers: int sqlite3_bind_double(sqlite3_stmt*, int, double)
The first two parameters are the same as above. The last parameter is the real number value. Binding a NULL. This is the simplest of them all: int sqlite3_bind_null(sqlite3_stmt*, int)
The first two parameters are the same as above and the value is, of course, implicit.
The insertCompany() (see Listing 2)
function assumes that a PNG file for each company is available. The
file names are assumed to have the same name as the symbol. For
example, for AlcatelLucent, the logo is stored in the ALU.png file. To retrieve the bytes of an image file, we create an NSData object using NSData's class method dataWithContentsOfFile:. This method retrieves the contents of a file and builds an NSData
around it. Once we have the bytes in the Objective-C object, we
retrieve them into a C-string using the following two statements:
buffer = malloc([pData length]);
[pData getBytes:buffer];
The first statement allocates a buffer of length equal to the NSData object length. To retrieve the bytes, we use the instance method getBytes: in the second statement.
Now that we have the three values for the
three SQL parameters, we use the appropriate bind function in order to
complete the SQL statement. Executing the INSERT statement is the same as any prepared statement: just use sqlite3_step(). Lastly, we finalize the statement and free the allocated buffer since we have specified SQLITE_STATIC in the BLOB bind function.
|
