In this section, we talk about some of the basic SQL
statements and how we can realize them in SQLite. We present a simple
program that creates a database with one table. This table stores
records of stock purchases. Each record stores the stock identifier
(represented by the stock symbol), the purchase price, the number of
shares bought, and the date of purchase.
To use SQLite in your application, you need to add the libsqlite3.0.dylib library to your target . In addition, you need to add the following #import statement:
#import "/usr/include/sqlite3.h"
Listing 1 shows the main()
function. The function creates a database (if one does not exist), adds
a new table, and populates the table with some records.
Example 1. The main() function demonstrating basic SQL statements using SQLite library function calls.
#import "/usr/include/sqlite3.h"
int main(int argc, char * argv[]) {
char *sqlStatement;
sqlite3 *pDb;
char *errorMsg;
int returnCode;
char *databaseName;
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 stocks";
returnCode = sqlite3_exec(pDb, sqlStatement, NULL, NULL, &errorMsg);
if (returnCode!=SQLITE_OK) {
fprintf(stderr,
"Error in dropping table stocks. Error: %s", errorMsg);
sqlite3_free(errorMsg);
}
sqlStatement = "CREATE TABLE stocks (symbol VARCHAR(5), "
"purchasePrice FLOAT(10,4), "
"unitsPurchased INTEGER, "
"purchase_date VARCHAR(10))";
returnCode = sqlite3_exec(pDb, sqlStatement, NULL, NULL, &errorMsg);
if (returnCode!=SQLITE_OK) {
fprintf(stderr, "Error in creating the stocks table. Error: %s",
errorMsg);
sqlite3_free(errorMsg);
}
insertStockPurchase(pDb, "ALU", 14.23, 100, "03-17-2007");
insertStockPurchase(pDb, "GOOG", 600.77, 20, "01-09-2007");
insertStockPurchase(pDb, "NT", 20.23,140, "02-05-2007");
insertStockPurchase(pDb, "MSFT", 30.23, 5, "01-03-2007");
sqlite3_close(pDb);
return 0;
}
|
1. Opening, creating, and closing databases
The first thing that you do before working with a database is open it. The SQLite function for opening a database is sqlite3_open(). The function is declared as:
int sqlite3_open(
const char* filename, /* Database filename (UTF-8) */
sqlite3 **ppDb /* OUT: SQLite db handle */
);
A database in SQLite is stored in a file. To open a
database, you need to specify the filename of that database in the
first parameter filename. Upon successfully opening the database, the function will return a value of SQLITE_OK.
For other SQLite functions to work with this database, a handle is
needed. You specify a reference to a handle pointer in the second
parameter. If the database was successfully opened, a handle is written
in that address. The database connection handle is of type sqlite3. You pass the address of a variable of type sqlite3*
in the second parameter. It is worth noting that if the database does
not exist, it is created; thus this function is used for both opening
an existing database and creating a new one.
If the database was not opened successfully, you need to display an error message and close the database. The SQLite function sqlite3_errmsg() takes a pointer to a database handle and returns a meaningful string describing the error. The program shown in Listing 1
uses this function in displaying the error message for failed database
opening. Once you are finished with a database, you should close it.
The SQLite function sqlite3_close() is used for that purpose. It takes, as the sole parameter, a pointer to the opened database handle (sqlite3*) received when you opened the database.
2. Table operations
Once we have successfully opened a database, we
would like to perform some table operations. SQLite provides a helper
function that does a one-time evaluation of SQL statements. This
function sqlite3_exec() is easy to use and works very well
with many SQL statements. Later, we will talk about how this function
is implemented using other SQLite functions. The sqlite3_exec() is declared as:
int sqlite3_exec(
sqlite3*, /* An open database */
const char* sql, /* SQL to be evaluated */
int (*callback)(void *,int ,char **,char **),/*Callbk func*/
void* , /* 1st argument to callback */
char* *errmsg /* Error msg written here */
);
The first parameter is the pointer to the database handle we received from the sqlite3_open
() function. The second parameter is the C string SQL statement. If an
error occurs, an error message is written into memory obtained from sqlite3_malloc(), and *errmsg is made to point to that message. You are responsible for freeing that space using the SQLite function sqlite3_free().
The third and fourth parameters are used for callback functions
operating on the result of the SQL statement. The callback function, if
specified, will be called for every row in the result. We will cover
callback functions later, but note that the first parameter passed to
this callback function can be specified in the fourth parameter of the sqlite3_exec() function. A return value of SQLITE_OK indicates successful execution of the SQL statement.
The first thing that we do in the main() function is to delete the table stocks if it exists. The SQL statement for that is:
DROP TABLE IF EXISTS stocks
This SQL statement does not return records. Therefore, in the invocation of the sqlite3_exec() function, we pass NULL for both the callback function and its first argument. The execution of this SQL statement is achieved by the following:
returnCode = sqlite3_exec(pDb, sqlStatement, NULL, NULL, &errorMsg);
Once we have deleted the stocks table, we can go ahead and create a new one. The SQL statement for creating the stocks table is as follows:
CREATE TABLE stocks (
symbol VARCHAR(5),
purchasePrice FLOAT(10,4),
unitsPurchased INTEGER,
purchase_date VARCHAR(10)
)
This SQL statement should be familiar to you. It states that the stocks
table should have four columns. The first column is of variable
(maximum five) character. The second is of type float with ten digits
in total and four of these digits are used after the decimal point. The
third column is of type integer, and the fourth and final column is of
variable character with maximum size of ten characters.
Internally, SQLite has the following five classes for data storage:
INTEGER. Used to store a signed
integer value. The number of bytes actually used for storage depends on
the magnitude of the value and ranges from one to eight bytes.
REAL. An eight-byte IEEE floating-point storage representing a floating point number.
TEXT. A storage area for text. The text can be in any of the following encodings: UTF-8, UTF-16BE, or UTF-16-LE.
BLOB. Used to store data exactly as entered, for example, an image.
NULL. Used to store the value NULL.
After creating the table stocks, we insert several records into it. The function insertStock-Purchase() shown in Listing 2 is used for that purpose.
Example 2. The function insertStockPurchase() for adding records into the stocks table.
#import "/usr/include/sqlite3.h"
void insertStockPurchase(sqlite3 *pDb, const char*symbol,
float price, int units, const char* theDate){
char *errorMsg;
int returnCode;
char *st;
st = sqlite3_mprintf("INSERT INTO stocks VALUES"
" ('%q', %f, %d, '%q')", symbol, price, units, theDate);
returnCode = sqlite3_exec(pDb, st, NULL, NULL, &errorMsg);
if(returnCode!=SQLITE_OK) {
fprintf(stderr,
"Error in inserting into the stocks table. Error: %s",
errorMsg);
sqlite3_free(errorMsg);
}
sqlite3_free(st);
}
|
As an example, the following SQL statement adds a
record for purchasing 100 shares of AlcatelLucent's stock at $14.23 on
03-17-2007.
INSERT INTO stocks VALUES ('ALU', 14.23, 100,'03-17-2007')
We use the SQlite function sqlite3_mprintf() for formatted string printing. This function is similar to the standard C library function printf() except that it writes the result into memory obtained from the sqlite3_malloc() function, so you should release the string when you are finished with it using the sqlite3_free()
function. In addition to the well-known formatting options, you have
access to the options %q and %Q. You should use these options instead
of the %s options when dealing with text. The option %q works like %s
except that it doubles every' character. For example, the string "She said:'Hey Ya'all whats up?'" will be printed to the string as "She said:"Hey Ya"all whats up?"". The %Q option works like the %q option except that it produces the string NULL when the value of the pointer being printed is equal to NULL. It also surrounds the whole string with a pair of'. The previous string will be printed as "'She said: "Hey Ya"all whats up?"'" when %Q is used.
The complete application can be found in the Database 1 project in the source downloads.
