Even when using the programming interface, the primary way of interacting with your data is to issue SQL commands to the database engine. This chapter focuses on the core of the API that is used to convey SQL command strings to the database engine. It is important to understand that there are no public functions to walk the internal structure of a table or, for example, access the tree structure of an index. You must use SQL to query data from the database. In order to be successful with the SQLite API, you not only need to understand the C API, but you also need to know enough SQL to form meaningful and efficient queries.
The C API for SQLite 3 includes a dozen-plus data structures, a fair number of
constants, and well over one hundred different function calls. While the API is somewhat large, using it
doesn’t have to be complex. A fair number of the functions are highly
specialized and infrequently used by most developers. Many of the
remaining functions are simple variations of the same basic operation.
For example, there are a dozen variations on the sqlite3_value_xxx() function, such as sqlite3_value_int(), sqlite3_value_double(), and sqlite3_value_text(). All of these
functions perform the same basic operation and can be considered
simple type variations of the same basic interface.
When referring to a whole category of
functions, either in text or in pseudo code, I’ll simply refer
to them as the sqlite3_value_xxx() functions. Much of the
SQLite documentation refers to them as sqlite3_value_*(), but I prefer to use the
xxx notation to avoid
any confusion with pointers. There are no actual SQLite3
functions with the letter sequence xxx in the name.
All public API function calls and datatypes have
the prefix sqlite3_, indicating
they are part of version 3.x of the SQLite product. Most of the
constants, such as error codes, use the prefix SQLITE_. The design and API
differences between SQLite 2.x and 3.x were significant enough to
warrant a complete change of all API names and structures. The depth
of these changes required anyone upgrading from SQLite 2 to SQLite 3
to modify their application, so changing the names of the API
functions only helped keep the names distinct and keep any version
questions clear. The distinct names also allowed applications that
were in transition to link to both libraries at the same time.
In addition to the sqlite3_ prefix, public function calls
can be identified by the use of lowercase letters and underscores in
their names. Private functions use run-together capitalized words (also known as CamelCase). For example,
sqlite3_create_function() is a public API function (used to register
a user-defined SQL function), while sqlite3CreateFunc() is an internal function that
should never be called directly. Internal
functions are not in the public header file,
are not documented, and are subject to change at any time.
The stability of the public interface is extremely important to the SQLite development team. An existing API function call will not be altered once it has been made public. The only possible exceptions are brand new interfaces that are marked experimental, and even experimental interfaces tend to become fairly solid after a few releases.
If a revised version of a function call is needed,
the newer function will generally be introduced with the suffix
_v2. For example, when a more
flexible version of the existing sqlite3_open()
function was introduced, the old version of the function
was retained as is and the new, improved
sqlite3_open_v2() was introduced.
Although no _v3 (or higher)
functions currently exist, it is possible they may be introduced in
the future.
By adding a new function, rather than modifying the parameters of an existing function, new code could take advantage of the newer features, while existing, unmodified code could continue to link against updated versions of the SQLite library. The use of a different function name also means that if a newer application is ever accidentally linked against an older version of the library, the result will be a link error rather than a program crash, making it much easier to track down and resolve the problem.
There are a number of API functions that have a
16 variant. For instance,
both an
sqlite3_column_text() function and
an sqlite3_column_text16() function are available. The
first requests a text value in UTF-8 format, while the second will request a text value in
UTF-16.
All of the strings in an SQLite database file are stored using the same encoding. SQLite database files support the UTF-8, UTF-16LE, and UTF-16BE encodings. A database’s encoding is determined when the database is created.
Regardless of the database, you can insert or
request text values in either UTF-8 or UTF-16. SQLite will
automatically convert text values between the database encoding and
the API encoding. The UTF-16 encoding passed by the 16 APIs will always be in the
machine’s native byte order. UTF-16 buffers use a void* C data type. The wchar_t data type is not used, as its
size is not fixed, and not all platforms define a 16-bit type.
Most of the string- and text-related functions have some type of length parameter. SQLite does not assume input text values are null-terminated, so explicit lengths are often required. These lengths are always given in bytes, not characters, regardless of the string encoding.
All string lengths are given in bytes, not characters, even if the string uses a multi-byte encoding such as UTF-16.
This difference is important to keep in mind when using international strings.
SQLite follows the convention of returning integer error codes in any situation when there is a chance of failure. If data needs to be passed back to the function caller, it is returned through a reference parameter.
In all cases, if a function succeeds, it will
return the constant SQLITE_OK,
which happens to have the value zero. If something went wrong, API
functions will return one of the standard error codes to indicate the
nature of the error.
More recently, a set of extended error codes were introduced. These provide a more specific indication of what went wrong. However, to keep things backwards compatible, these extended codes are only available when you activate them.
The situation is complex enough to warrant its own discussion later in the chapter. It will be much easier to explain the different error codes once you’ve had a chance to see how the API works. See Result Codes and Error Codes for more details.
I also have to give the standard “do as I say, not as I do” caveat about properly checking error codes and return results. The example code in this chapter and elsewhere in this book tends to have extremely terse (as in, almost none at all) error checking. This is done to keep the examples short and clear. Needless to say, this isn’t the best approach for production code. When working with your own code, do the right thing and check your error codes.
Although the native SQLite API is often referred to as a C/C++ API, technically the interface is only available in C. As mentioned in Building, the SQLite source code is strictly C based, and as such can only be compiled with a C compiler. Once compiled, the library can be easily linked to, and called from, both C and C++ code, as well as any other language that follows the C linking conventions for your platform.
Although the API is written in C, it has a distinct object-like flavor. Most of the program state is held in a series of opaque data structures that act like objects. The most common data structures are database connections and prepared statements. You should never directly access the fields of these data structures. Instead, functions are provided to create, destroy, and manipulate these structures in much the same way that object methods are used to manipulate object instances. This results in an API design that has similar feelings to an object-oriented design. In fact, if you download one of the third-party C++ wrappers, you’ll notice that the wrappers tend to be rather thin, owing most of their structure to the underlying C functions and the data structures.
It is important that you allow SQLite to allocate and manage its own data structures. The design of the API means that you should never manually allocate one of these structures, nor should you put these structures on the stack. The API provides calls to internally allocate the proper data structures, initialize them, and return them. Similarly, for every function that allocates a data structure, there is some function that is used to clean it up and release it. As with memory management, you need to be sure these calls are balanced, and that every data structure that is created is eventually released.
The core of the SQLite API focuses on opening database connections, preparing SQL statements, binding parameter values, executing statements, and finally stepping through the results. These procedures are the focus of this chapter.
There are also interfaces to create your own SQL functions, load dynamic modules, and create code-driven virtual tables. We’ll be covering some of these more advanced interfaces in other chapters.
Beyond that, there are a fair number of management and customization functions. Not all of these are covered in the main part of the book, but a reference for the full API can be found in Appendix G. If the description of a function leaves you with additional questions, be sure to check that appendix for more specific details.