This chapter covers the part of the SQLite API used to work with databases. You already saw an overview of how the API works in Chapter 5. Now let's concentrate on the specifics.
Starting with a few easily understood examples, we will take an in-depth tour through the C API and expand upon the examples, filling in various details with a variety of useful functions. As we go along, you should see the C equivalents of the model all fall into place, with some additional features you may not have seen before—features that primarily exist only in the C API. By the time we reach the end of this chapter, you should have a good feel for all of the API functions related to running commands, managing transactions, fetching records, handling errors, and performing many other tasks related to general database work.
The SQLite version 3 API consists of dozens and dozens of functions. Only about eight functions, however, are needed to actually connect, process queries, and disconnect from a database. The remaining functions can be arranged into small groups that specialize in accomplishing specific tasks.
Although it is best to read the chapter straight through, if at any point you want more detailed information on a particular function, you can consult the C API reference at www.sqlite.org/c3ref/intro.html
.
You can find all the examples in this chapter in self-contained example programs in the examples zip file, available on the Apress web site at www.apress.com
. For every example presented, we specifically identify the name of the corresponding source file from which the example was taken.
You are already familiar with the way that SQLite executes queries, as well as its various wrapper functions for executing SQL commands in a single function call. We will start with the C API versions of these wrappers because they are simple, self-contained, and easy to use. They are a good starting point, which will let you have some fun and not get too bogged down with details. Along the way, I'll introduce some other handy functions that go hand in hand with query processing. By the end of this section, you will be able connect, disconnect, and query a database using the wrapped queries.
Before you can execute SQL commands, you first have to connect to a database. Connecting to a database is perhaps best described as opening a database, because SQLite databases are contained in single operating system files (one file to one database). Similarly, the preferred term for disconnecting is closing the database.
You open a database with the sqlite3_open_v2()
, sqlite3_open()
, or sqlite3_open16()
functions, which have the following declaration(s):
int sqlite3_open_v2(
const char *filename, /* Database filename (UTF-8) */
sqlite3 **ppDb /* OUT: SQLite db handle */
int flags, /* Flags */
const char *zVfs /* Name of VFS module to use */
);
int sqlite3_open (
const void *filename, /* Database filename (UTF-8) */
sqlite3 **ppDb /* OUT: SQLite db handle */
);
int sqlite3_open16(
const void *filename, /* Database filename (UTF-16) */
sqlite3 **ppDb /* OUT: SQLite db handle */
);
Typically, you'll use sqlite3_open_v2()
, because this is the latest and greatest function in SQLite for opening your database, and it accommodates more options and capabilities over the old sqlite3_open()
. Regardless of the function chosen, the filename
argument can be the name of an operating system file, the text string ':memory:'
, or an empty string or a NULL
pointer. If you use ':memory:'
, sqlite3_open_v2()
will create an in-memory database in RAM that lasts only for the duration of the session. If filename
is an empty string or a NULL
, sqlite3_open_v2()
opens a temporary disk file that is automatically deleted when the connection closes. Otherwise, sqlite3_open_v2()
attempts to open the database file by using its value. If no file by that name exists, sqlite3_open_v2()
will open a new database file by that name if the SQLITE_OPEN_CREATE
flag is included in the third parameter, or it will return an error if the SQLITE_OPEN_CREATE
flag is omitted.
Note Here we have included both the UTF-8 and UTF-16 declarations for sqlite3_open()
, as well as the more modern sqlite3_open_v2()
. From here on out, we will refer to the UTF-8 declarations only for the sake of brevity, and where they exist, we'll deal with the latest function signatures that incorporate SQLite's latest functionality, rather than cover all the legacy functions as well. Therefore, please keep in mind that there are many functions in the API that have UTF-16 forms, as well as older UTF-8 forms for backward compatibility. The complete C API reference is available on the SQLite web site at www.sqlite.org/c3ref/intro.html
.
The flags
parameter is a bit vector that can include the following values: SQLITE_OPEN_READONLY
, SQLITE_OPEN_READWRITE
, and SQLITE_OPEN_CREATE
. The names are reasonable self-explanatory, but a few subtleties are worth noting. SQLITE_OPEN_READONLY
and SQLITE_OPEN_READWRITE
open a SQLite database in read-only or read/write mode as their names suggest. Both options require that the database file already exist; otherwise, an error will be returned. SQLITE_OPEN_CREATE
combined with SQLITE_OPEN_READWRITE
exhibits the behavior we've seen in the first five chapters, which is the legacy sqlite3_open()
behavior. Where a database already exists, it is opened for reading and writing. If the database specified doesn't exist, it is created (though as we've pointed out quite a few times, the act of persisting the database to disk will be pending the creation of the first object).
The flags
parameter can also be combined with the SQLITE_OPEN_NOMUTEX
, SQLITE_OPEN_FULLMUTEX
, SQLITE_OPEN_SHAREDCACHE
, or SQLITE_OPEN_PRIVATECACHE
flags to further control transactional behavior for the database handle. The final parameter, zVfs
, allows the caller to override the default sqlite3_vfs
operating system interface.
Upon completion, sqlite3_open_v2()
will initialize the sqlite3
structure passed into it by the ppDb
argument. This structure should be considered as an opaque handle representing a single connection to a database. This is more of a connection handle than a database handle since it is possible to attach multiple databases to a single connection. However, this connection still represents exactly one transaction context regardless of how many databases are attached.
You close a connection by using the sqlite3_close()
function, which is declared as follows:
int sqlite3_close(sqlite3*);
For sqlite3_close()
to complete successfully, all prepared queries associated with the connection must be finalized. If any queries remain that have not been finalized, sqlite3_close()
will return SQLITE_BUSY
with the error message “Unable to close due to unfinalized statements.”
Note If there is a transaction open on a connection when it is closed by sqlite3_close()
, the transaction will automatically be rolled back.
The sqlite3_exec()
function provides a quick, easy way to execute SQL commands and is especially handy for commands that modify the database (that is, don't return any data). This is often also referred to as a convenience function, which nicely wraps up a lot of other tasks in one easy API call. This function has the following declaration:
int sqlite3_exec(
sqlite3*, /* An open database */
const char *sql, /* SQL to be executed */
sqlite_callback, /* Callback function */
void *data /* 1st argument to callback function */
char **errmsg /* Error msg written here */
);
The SQL provided in the sql
argument can consist of more than one SQL command. sqlite3_exec()
will parse and execute every command in the sql
string until it reaches the end of the string or encounters an error. Listing 6-1 (taken from create.c
) illustrates how to use sqlite3_exec()
.The example opens a database called test.db and creates within it a single table called episodes
. After that, it inserts one record. The create table
command will physically create the database file if it does not already exist.
Listing 6-1. Using sqlite3_exec()
for Simple Commands
int main(int argc, char **argv)
{
sqlite3 *db;
char *zErr;
int rc;
char *sql;
rc = sqlite3_open_v2("test.db", &db);
if(rc) {
fprintf(stderr, "Can't open database: %s
", sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
}
sql = "create table episodes(id int, name text)";
rc = sqlite3_exec(db, sql, NULL, NULL, &zErr);
if(rc != SQLITE_OK) {
if (zErr != NULL) {
fprintf(stderr, "SQL error: %s
", zErr);
sqlite3_free(zErr);
}
}
sql = "insert into episodes values (10, 'The Dinner Party')";
rc = sqlite3_exec(db, sql, NULL, NULL, &zErr);
sqlite3_close(db);
return 0;
}
As mentioned in Chapter 5, it is actually possible to get records from sqlite3_exec()
, although you don't see it implemented much outside of the C API. sqlite3_exec()
contains a callback mechanism that provides a way to obtain results from select
statements. This mechanism is implemented by the third and fourth arguments of the function. The third argument is a pointer to a callback function. If it's provided, SQLite will call the function for each record processed in each select
statement executed within the sql
argument. The callback function has the following declaration:
typedef int (*sqlite3_callback)(
void*, /* Data provided in the 4th argument of sqlite3_exec() */
int, /* The number of columns in row */
char**, /* An array of strings representing fields in the row */
char** /* An array of strings representing column names */
);
The fourth argument to sqlite3_exec()
is a void pointer to any application-specific data you want to supply to the callback function. SQLite will pass this data as the first argument of the callback function.
The final argument (errmsg
) is a pointer to a string to which an error message can be written should an error occur during processing. Thus, sqlite3_exec()
has two sources of error information. The first is the return value. The other is the human-readable string, assigned to errmsg
. If you pass in a NULL
for errmsg
, then SQLite will not provide any error message. Note that if you do provide a pointer for errmsg
, the memory used to create the message is allocated on the heap. You should therefore check for a non-NULL
value after the call and use sqlite3_free()
to free the memory used to hold the errmsg
string if an error occurs.
Note Note that you can pass a NULL into sqlite3_free()
. The result is a harmless no-op. So, you can, if you want, call sqlite3_free(errmsg)
without having to check to see whether errmsg
is not NULL
. It mainly depends on where and how you want to interrogate any actual errors and respond accordingly.
Putting it all together, sqlite3_exec()
allows you to issue a batch of commands, and you can collect all the returned data by using the callback interface. For example, let's insert a record into the episodes
table and then select all of its records, all in a single call to sqlite3_exec()
. The complete code, shown in Listing 6-2, is taken from exec.c
.
Listing 6-2. Using sqlite3_exec()
for Record Processing
int callback(void* data, int ncols, char** values, char** headers);
int main(int argc, char **argv)
{
sqlite3 *db;
int rc;
char *sql;
char *zErr;
rc = sqlite3_open_v2("test.db", &db);
if(rc) {
fprintf(stderr, "Can't open database: %s
", sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
}
const char* data = "Callback function called";
sql = "insert into episodes (id, name) values (11,'Mackinaw Peaches'),"
"select * from episodes;";
rc = sqlite3_exec(db, sql, callback, data, &zErr);
if(rc != SQLITE_OK) {
if (zErr != NULL) {
fprintf(stderr, "SQL error: %s
", zErr);
sqlite3_free(zErr);
}
}
sqlite3_close(db);
return 0;
}
int callback(void* data, int ncols, char** values, char** headers)
{
int i;
fprintf(stderr, "%s: ", (const char*)data);
for(i=0; i < ncols; i++) {
fprintf(stderr, "%s=%s ", headers[i], values[i]);
}
fprintf(stderr, "
");
return 0;
}
SQLite parses the sql
string; runs the first command, which inserts a record; and then runs the second command, consisting of the select
statement. For the second command, SQLite calls the callback function for each record returned. Running the program produces the following output:
Callback function called: id=10 name=The Dinner Party
Callback function called: id=11 name=Mackinaw Peaches
Notice that the callback function returns 0. This return value actually exerts some control over sqlite3_exec()
. If the callback function returns a nonzero value, then sqlite3_exec()
will abort (in other words, it will terminate all processing of this and subsequent commands in the sql
string).
So, sqlite3_exec()
provides an easy way to modify the database and also provides an interface with which to process records. Why then should you bother with prepared queries? Well, as you will see in the next section, there are quite a few advantages:
sqlite3_exec()
's callback interface provides just the column names.int
and double
, whereas sqlite3_exec()
's callback interface only provides fields as string values.The sqlite3_get_table()
function returns an entire result set of a command in a single function call. Just as sqlite3_exec()
wraps the prepared query API functions, allowing you to run them all at once, sqlite3_get_table()
wraps sqlite3_exec()
for commands that return data with just as much convenience. Using sqlite3_get_table()
, you don't have to bother with the sqlite3_exec()
callback function, thus making it easier to fetch records. sqlite3_get_table()
has the following declaration:
int sqlite3_get_table(
sqlite3*, /* An open database */
const char *sql, /* SQL to be executed */
char ***resultp, /* Result written to a char *[] that this points to */
int *nrow, /* Number of result rows written here */
int *ncolumn, /* Number of result columns written here */
char **errmsg /* Error msg written here */
);
This function takes all the records returned from the SQL statement in sql
and stores them in the resultp
argument using memory declared on the heap (using sqlite3_malloc()
). The memory must be freed using sqlite3_free_table()
, which takes the resultp
pointer as its sole argument. The first record in resultp
is actually not a record but contains the names of the columns in the result set. Examine the code fragment in Listing 6-3 (taken from get_table.c
).
Listing 6-3. Using sqlite3_get_table
int main(int argc, char **argv)
{
/* Connect to database, etc. */
char *result[];
sql = "select * from episodes;";
rc = sqlite3_get_table(db, sql, &result, &nrows, &ncols, &zErr);
/* Do something with data */
/* Free memory */
sqlite3_free_table(result)
}
If, for example, the result set returned is of the following form:
name | id
-----------------------
The Junior Mint | 43
The Smelly Car | 28
The Fusilli Jerry | 21
then the format of the result array will be structured as follows:
result [0] = "name";
result [1] = "id";
result [2] = "The Junior Mint";
result [3] = "43";
result [4] = "The Smelly Car";
result [5] = "28";
result [6] = "The Fusilli Jerry";
result [7] = "21";
The first two elements contain the column headings of the result set. Therefore, you can think of the result set indexing as 1-based with respect to rows but 0-based with respect to columns. An example may help clarify this. Listing 6-4 shows the code to print out each column of each row in the result set.
Listing 6-4. Iterating Through sqlite3_get_table()
Results
rc = sqlite3_get_table(db, sql, &result, &nrows, &ncols, &zErr);
for(i=0; i < nrows; i++) {
for(j=0; j < ncols; j++) {
/* the i+1 term skips over the first record,
which is the column headers */
fprintf(stdout, "%s", result[(i+1)*ncols + j]);
}
}
Our Chapter 5 overview of SQLite's API introduced the concepts of the prepare, step, and finalize functions. This section covers all aspects of this process, including stepping through result sets, fetching records, and using parameterized queries. We told you prepared statements were infinitely preferable to the convenience wrapper functions, and this is where we prove the point.
The wrapper functions simply wrap all these steps into a single function call, making it more convenient in some situations to run specific commands. Each query function provides its own way of getting at rows and columns. As a general rule, the more packaged the method is, the less control you have over execution and results. Therefore, prepared queries offer the most features, the most control, and the most information, with sqlite3_exec()
offering slightly fewer features and sqlite3_get_table()
offering fewer still.
Prepared queries use a special group of functions to access field and column information from a row. You get column values using sqlite3_column_xxx()
, where xxx
represents the data type of the value to be returned (for example, int
, double
, blob
). You can retrieve data in whatever format you like. You can also obtain the declared types of columns (as they are defined in the create table
statement) and other miscellaneous metadata such as storage format and both associated table and database names. sqlite3_exec()
, by comparison, provides only a fraction of this information through its callback function. The same is true with sqlite3_get_table()
, which only includes the result set's column headers with the data.
In practice, you will find that each query method has its uses. sqlite3_exec()
is especially good for running commands that modify the database (create
, drop
, insert
, update
, and delete
). One function call, and it's done. Prepared queries are typically better for select
statements because they offer so much more information, more linear coding (no callback functions), and more control by using cursors to iterate over results.
As you'll recall from Chapter 5, prepared queries are performed in three basic steps: compilation, execution, and finalization. You compile the query with sqlite3_prepare_v2()
, execute it step-by-step using sqlite3_step()
, and close it using sqlite3_finalize()
, or you can reuse it using sqlite3_reset()
. This process and the individual steps are all explained in detail in the following sections.
Compilation, or preparation, takes a SQL statement and compiles it into byte code readable by the virtual database engine (VDBE). It is performed by sqlite3_prepare_v2()
, which is declared as follows:
int sqlite3_prepare_v2(
sqlite3 *db, /* Database handle */
const char *zSql, /* SQL text, UTF-8 encoded */
int nBytes, /* Length of zSql in bytes. */
sqlite3_stmt **ppStmt, /* OUT: Statement handle */
const char **pzTail /* OUT: Pointer to unused portion of zSql */
);
sqlite3_prepare_v2()
compiles the first SQL statement in the zSQL
string (which can contain multiple SQL statements). It allocates all the resources necessary to execute the statement and associates it along with the byte code into a single statement handle (also referred to as simply a statement), designated by the out parameter ppStmt
, which is a sqlite3_stmt
structure. From the programmer's perspective, this structure is little more than an opaque handle used to execute a SQL statement and obtain its associated records. However, this data structure contains the command's byte code, bound parameters, B-tree cursors, execution context, and any other data sqlite3_step()
needs to manage the state of the query during execution.
sqlite3_prepare_v2()
does not affect the connection or database in any way. It does not start a transaction or get a lock. It works directly with the compiler, which simply prepares the query for execution. Statement handles are highly dependent on the database schema with which they were compiled. If another connection alters the database schema, between the time you prepare a statement and the time you actually run it, your prepared statement will expire. However, sqlite3_prepare_v2()
is built to automatically attempt to recompile (re-prepare) your statement if possible and will do this silently if a schema change has invalidated your existing statement. If the schema has changed in such a way as to make recompilation impossible, your call to sqlite3_step()
with the statement will lead to a SQLITE_SCHEMA
error, which is discussed later in the section “Errors and the Unexpected.” At this point, you would need to examine the error associated with SQLITE_SCHEMA
by using the sqlite3_errmsg()
function.
Once you prepare the query, the next step is to execute it using sqlite3_step()
, declared as follows:
int sqlite3_step(sqlite3_stmt *pStmt);
sqlite3_step()
takes the statement handle and talks directly to the VDBE, which steps through its byte-code instructions one by one to carry out the SQL statement. On the first call to sqlite3_step()
, the VDBE obtains the requisite database lock needed to perform the command. If it can't get the lock, sqlite3_step()
will return SQLITE_BUSY
, if there is no busy handler installed. If one is installed, it will call that handler instead.
For SQL statements that don't return data, the first call to sqlite3_step()
executes the command in its entirety, returning a result code indicating the outcome. For SQL statements that do return data, such as select
, the first call to sqlite3_step()
positions the statement's B-tree cursor on the first record. Subsequent calls to sqlite3_step()
position the cursor on subsequent records in the result set. sqlite3_step()
returns SQLITE_ROW
for each record in the set until it reaches the end, whereupon it returns SQLITE_DONE
, indicating that the cursor has reached the end of the set.
Note For those of you familiar with older versions of SQLite, these result codes are part of the more advanced—and some would say more correct—set of result codes that replace the legacy simple return values like SQLITE_ERROR
. A full list of result codes and extended result codes is available at www.sqlite.org/c3ref/c_abort.html
All other API functions related to data access use the statement's cursor to obtain information about the current record. For example, the sqlite3_column_xxx()
functions all use the statement handle, specifically its cursor, to fetch the current record's fields.
Once the statement has reached the end of execution, it must be finalized. You can either finalize or reset the statement using one of the following functions:
int sqlite3_finalize(sqlite3_stmt *pStmt);
int sqlite3_reset(sqlite3_stmt *pStmt);
sqlite3_finalize()
will close out the statement. It frees resources and commits or rolls back any implicit transactions (if the connection is in autocommit mode), clearing the journal file and freeing the associated lock.
If you want to reuse the statement, you can do so using sqlite3_reset()
. It will keep the compiled SQL statement (and any bound parameters) but commits any changes related to the current statement to the database. It also releases its lock and clears the journal file if autocommit is enabled. The primary difference between sqlite3_finalize()
and sqlite3_reset()
is that the latter preserves the resources associated with the statement so that it can be executed again, avoiding the need to call sqlite3_
prepare()
to compile the SQL command.
Let's go through an example. Listing 6-5 shows a simple, complete program using a prepared query. It is taken from select.c
in the examples.
Listing 6-5. Using Prepared Queries
int main(int argc, char **argv)
{
int rc, i, ncols;
sqlite3 *db;
sqlite3_stmt *stmt;
char *sql;
const char *tail;
rc = sqlite3_open_v2("foods.db", &db);
if(rc) {
fprintf(stderr, "Can't open database: %s
", sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
}
sql = "select * from episodes;";
rc = sqlite3_prepare_v2(db, sql, -1, &stmt, &tail);
if(rc != SQLITE_OK) {
fprintf(stderr, "SQL error: %s
", sqlite3_errmsg(db));
}
rc = sqlite3_step(stmt);
ncols = sqlite3_column_count(stmt);
while(rc == SQLITE_ROW) {
for(i=0; i < ncols; i++) {
fprintf(stderr, "'%s' ", sqlite3_column_text(stmt, i));
}
fprintf(stderr, "
");
rc = sqlite3_step(stmt);
}
sqlite3_finalize(stmt);
sqlite3_close(db);
return 0;
}
This example connects to the foods.db
database, queries the episodes
table, and prints out all columns of all records within it. Keep in mind this is a simplified example—there are a few other things we need to check for when calling sqlite3_step()
, such as errors and busy conditions, but we will address them later.
Like sqlite3_exec()
, sqlite3_prepare_v2()
can accept a string containing multiple SQL statements. However, unlike sqlite3_exec()
, it will process only the first statement in the string. But it does make it easy for you to process subsequent SQL statements in the string by providing the pzTail
out parameter. After you call sqlite3_prepare()
, it will point this parameter (if provided) to the starting position of the next statement in the zSQL
string. Using pzTail
, processing a batch of SQL commands in a given string can be executed in a loop as follows:
while(sqlite3_complete(sql) ){
rc = sqlite3_prepare(db, sql, -1, &stmt, &tail);
/* Process query results */
/* Skip to next command in string. */
sql = tail;
}
This example uses another API function not yet covered—sqlite3_complete()
, which does as its name suggests. It takes a string and returns true if there is at least one complete (but not necessarily valid) SQL statement in it, and it returns false otherwise. In reality, sqlite_complete()
looks for a semicolon terminator for the string (and accounting for literals in the SQL). So, although its name suggests some kind of infallible observer checking your statements, it's really just a handy tool for things such as showing you the continuation prompt in the sqlite
command line when writing multiline statements and doing other similar useful tasks.
So far, you have seen how to obtain records and columns from sqlite3_exec()
and sqlite3_get_table()
. Prepared queries, by comparison, offer many more options when it comes to getting information from records in the database.
For a statement that returns records, the number of columns in the result set can be obtained using sqlite3_column_count()
and sqlite3_data_count()
, which are declared as follows:
int sqlite3_column_count(sqlite3_stmt *pStmt);
int sqlite3_data_count(sqlite3_stmt *pStmt);
sqlite3_column_count()
returns the number of columns associated with a statement handle. You can call it on a statement handle before it is actually executed. If the query in question is not a select
statement, sqlite3_column_count()
will return 0. Similarly, sqlite3_data_count()
returns the number of columns for the current record, after sqlite3_step()
returns SQLITE_ROW
. This function will work only if the statement handle has an active cursor.
You can obtain the name of each column in the current record using sqlite3_column_name()
, which is declared as follows:
const char *sqlite3_column_name( sqlite3_stmt*, /* statement handle */
int iCol /* column ordinal */);
Similarly, you can get the associated storage class for each column using sqlite3_column_type()
, which is declared as follows:
int sqlite3_column_type( sqlite3_stmt*, /* statement handle */
int iCol /* column ordinal */);
This function returns an integer value that corresponds to one of five storage class codes, defined as follows:
#define SQLITE_INTEGER 1
#define SQLITE_FLOAT 2
#define SQLITE_TEXT 3
#define SQLITE_BLOB 4
#define SQLITE_NULL 5<A NAME="50520099_sqlite3_column_name16">
These are SQLite's native data types, or storage classes, as described in Chapter 4. All data stored within a SQLite database is stored in one of these five forms, depending on its initial representation and the affinity of the column. For our purposes, the terms storage class and data type are synonymous. For more information on storage classes, see the sections “Storage Classes” and “Type Affinity” in Chapter 4.
You can obtain the declared data type of a column as it is defined in the table's schema using the sqlite3_column_decltype()
function, which is declared as follows:
const char *sqlite3_column_decltype( sqlite3_stmt*, /* statement handle */
int /* column ordinal */);
If a column in a result set does not correspond to an actual table column (say, for example, the column is the result of a literal value, expression, function, or aggregate), this function will return NULL
as the declared type of that column. For example, suppose you have a table in your database defined as follows:
Then you execute the following query:
SELECT c1 + 1, 0 FROM t1;
In this case, sqlite3_column_decltype()
will return INTEGER
for the first column and NULL
for the second.
In addition to the declared type, you can obtain other information on a column using the following functions:
const char *sqlite3_column_database_name(sqlite3_stmt *pStmt, int iCol);
const char *sqlite3_column_table_name(sqlite3_stmt *pStmt, int iCol);
const char *sqlite3_column_origin_name(sqlite3_stmt *pStmt, int iCol);
The first function will return the database associated with a column, the second will return its table, and the last function will return the column's actual name as defined in the schema. That is, if you assigned the column an alias in the SQL statement, sqlite3_column_origin_name()
will return its actual name as defined in the schema. Note that these functions are available only if you compile SQLite with the SQLITE_ENABLE_COLUMN_METADATA
preprocessor directive.
You can obtain the values for each column in the current record using the sqlite3_column_xxx()
functions, which are of the following general form:
xxx sqlite3_column_xxx( sqlite3_stmt*, /* statement handle */
int iCol /* column ordinal */);
Here xxx
is the data type you want the data represented in (for example, int
, blob
, double
, and so on). These are the most commonly used of the sqlite3_column_xxx()
functions:
int sqlite3_column_int(sqlite3_stmt*, int iCol);
double sqlite3_column_double(sqlite3_stmt*, int iCol);
long long int sqlite3_column_int64(sqlite3_stmt*, int iCol);
const void *sqlite3_column_blob(sqlite3_stmt*, int iCol);
const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol);
const void *sqlite3_column_text16(sqlite3_stmt*, int iCol);
For each function, SQLite converts the internal representation (storage class in the column) to the type specified in the function name. There are a number of rules SQLite uses to convert the internal data type representation to that of the requested type. Table 6-1 lists these rules.
Table 6-1. Column Type Conversion Rules
Internal Type | Requested Type | Conversion |
NULL INTEGER |
Result is 0. | |
NULL FLOAT |
Result is 0.0. | |
NULL TEXT |
Result is a NULL pointer. |
|
NULL BLOB |
Result is a NULL pointer. |
|
INTEGER FLOAT |
Convert from integer to float. | |
INTEGER TEXT |
Result is the ASCII rendering of the integer. | |
INTEGER BLOB |
Result is the ASCII rendering of the integer. | |
FLOAT INTEGER |
Convert from float to integer. | |
FLOAT TEXT |
Result is the ASCII rendering of the float. | |
FLOAT BLOB |
Result is the ASCII rendering of the float. | |
TEXT INTEGER |
Use atoi() . |
|
TEXT FLOAT |
Use atof() . |
|
TEXT BLOB |
No change. | |
BLOB INTEGER |
Convert to TEXT and then use atoi() . |
|
BLOB FLOAT |
Convert to TEXT and then use atof() . |
|
BLOB TEXT |
Add a
|