Before we can prepare or execute SQL statements, we must first establish a database connection. Most often this is done by opening or creating an SQLite3 database file. When you are done with the database connection, it must be closed. This verifies that there are no outstanding statements or allocated resources before closing the database file.
Database connections are allocated and established with one
of the sqlite3_
open_
xxx()
commands. These pass back a database connection
in the form of an sqlite3
data
structure. There are three variants:
int sqlite3_open( const char *filename, sqlite3 **db_ptr )
int sqlite3_open16( const void *filename, sqlite3 **db_ptr )
Opens a database file and allocates an sqlite3
data structure.
The first parameter is the filename of the database
file you wish to open, given as a null-terminated
string. The second parameter is a reference to an
sqlite3
pointer, and is used to pass back the new
connection. If possible, the database will be opened
read/write. If not, it will be opened read-only. If
the given database file does not exist, it will be
created.
The first variant assumes that the database filename is encoded in UTF-8, while the second assumes that the database filename is encoded in UTF-16.
int sqlite3_open_v2( const char *filename, sqlite3 **db_ptr,
int flags, const char *vfs_name )
The _v2
variant offers more control over
how the database file is created and opened. The
first two parameters are the same. The filename is
assumed to be in UTF-8. There is no UTF-16 variant
of this function.
A third parameter is a set of bit-field flags.
These flags allow you to specify if SQLite should
attempt to open the database read/write (SQLITE_OPEN_READWRITE
),
or read-only (SQLITE_OPEN_READONLY
). If you ask for
read/write access but only read-only access is
available, the database will be opened in read-only
mode.
This variant of open will not
create a new file for an unknown filename unless you
explicitly allow it using the SQLITE_OPEN_CREATE
flag.
This only works if the database is being opened in
read/write mode.
There are also a number of other
flags dealing with thread and cache management. See
sqlite3_open() in Appendix G for more details. The
standard version of open is equivalent to the flag
values of (SQLITE_READWRITE
| SQLITE_CREATE
).
The final parameter allows you to name a VFS (Virtual File System) module to use with this database connection. The VFS system acts as an abstraction layer between the SQLite library and any underlying storage system (such as a filesystem). In nearly all cases, you will want to use the default VFS module and can simply pass in a NULL pointer.
For new code, it is recommended that you use the
call sqlite3_open_v2()
. The newer
call allows more control over how the database is opened and
processed.
The use of the double pointer may be a bit confusing at first, but the idea behind it is simple enough. The pointer-to-a-pointer is really nothing more than a pointer that is passed by reference. This allows the function call to modify the pointer that is passed in. For example:
sqlite3 *db = NULL; rc = sqlite3_open_v2( "database.sqlite3", &db, SQLITE_OPEN_READWRITE, NULL ); /* hopefully, db now points to a valid sqlite3 structure */
Note
that db
is an sqlite3
pointer (sqlite3*
), not an actual sqlite3
structure. When we call
sqlite3_open_xxx()
and pass
in the pointer reference, the open function will allocate a new
sqlite3
data structure,
initialize it, and set our pointer to point to it.
This approach, including the use of a pointer reference, is a common theme in the SQLite APIs that are used to create or initialize something. They all basically work the same way and, once you get the hang of them, they are pretty straightforward and easy to use.
There is no standard file extension for an SQLite3 database file, although .sqlite3, .db, and .db3 are popular choices. The extension .sdb should be avoided, as this extension has special meaning on some Microsoft Windows platforms, and may suffer from significantly slower I/O performance.
The string encoding used by a database file is
determined by the function that is used to create the file. Using
sqlite3_open()
or sqlite3_open_v2()
will result in a
database with the default UTF-8 encoding. If sqlite3_open16()
is used to create a database, the
default string encoding will be UTF-16 in the native byte order of the
machine. You can override the default string encoding with the SQL
command PRAGMA encoding
. See encoding in Appendix F
for more details.
In addition to recognizing standard filenames, SQLite recognizes a few specialized
filename strings. If the
given filename is a NULL pointer or an empty string (""
), then an anonymous, temporary,
on-disk database is created. An anonymous database can only be
accessed through the database connection that created it. Each call
will create a new, unique database instance. Like all temporary items,
this database will be destroyed
when the connection is closed.
If the filename :memory:
is used, then a temporary, in-memory
database is created. In-memory databases live in the database cache
and have no backing store. This style of database is extremely fast,
but requires sufficient memory to hold the entire database image in
memory. As with anonymous databases, each open call will create a new,
unique, in-memory database, making it impossible for more than one
database connection to access a given in-memory database.
In-memory databases make great structured caches. It is not possible to directly image an in-memory database to disk, but you can copy the contents of an in-memory database to disk (or disk to memory) using the database backup API. See the section sqlite3_backup_init() in Appendix G for more details.
To close and release a database connection, call sqlite3_close()
.
int sqlite3_close( sqlite3 *db )
Closes a database connection and releases any associated data structures. All temporary items associated with this connection will be deleted. In order to succeed, all prepared statements associated with this database connection must be finalized. See Reset and Finalize for more details.
Any pointer returned by a call to sqlite3_open_xxx()
, including a NULL
pointer, can be passed to sqlite3_close()
. This function verifies there are no
outstanding changes to the database, then closes the file and frees
the sqlite3
data structure. If the
database still has nonfinalized statements, the SQLITE_BUSY
error will be returned. In
that case, you need to correct the problem and call sqlite3_close()
again.
In most cases, sqlite3_open_xxx()
will return a pointer to an
sqlite3
structure, even when
the return code indicates a problem. This allows the caller to
retrieve an error message with sqlite3_errmsg()
. (See Result Codes and Error Codes.) In these situations, you must still call sqlite3_close()
to free the sqlite3
structure.
Here is the outline of a program that opens a database, performs some operations, and then closes it. Most of the other examples in this chapter will build from this example by inserting code into the middle:
#include "sqlite3.h" #include <stdlib.h> int main( int argc, char **argv ) { char *file = ""; /* default to temp db */ sqlite3 *db = NULL; int rc = 0; if ( argc > 1 ) file = argv[1]; sqlite3_initialize( ); rc = sqlite3_open_v2( file, &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL ); if ( rc != SQLITE_OK) { sqlite3_close( db ); exit( -1 ); } /* perform database operations */ sqlite3_close( db ); sqlite3_shutdown( ); }
The default filename is an empty string. If passed to sqlite3_open_xxx()
, this will result
in a temporary, on-disk database that will be deleted as soon as the
database connection is closed. If at least one argument is given, the
first argument will be used as a filename. If the database does not
exist, it will be created and then opened for read/write access. It is
then immediately closed.
If this example is run using a new filename, it will not create a valid database file. The SQLite library delays writing the database header until some actual data operation is performed. This “lazy” initialization gives an application the chance to adjust any relevant pragmas, such as the text encoding, page size, and database file format, before the database file is fully created.
18.189.2.122