SQLite-C language API Introduction

1.1Application Example

In this section, I will show some simple applications to illustrate the many features of SQLite. These applications will be shown in the following sub-sections

1.1.1 An SQLite application example

Let's start our exploration of the SQLite continent by learning a very simple application. The following example shows a typical SQLite application. It is a C program that uses the SQLite API to process an SQLite database file.

This is a typical SQLite application:

# Include <stdio. h> # Include "sqlite3.h"   Int main (void) { Sqlite3 * DB = 0; Sqlite3_stmt * stmt = 0; Int retcode; Retcode = sqlite3_open ("mydb", & dB ); If (retcode! = Sqlite_ OK ){ Sqlite3_close (db ); Fprintf (stderr, "cocould not open mydb/N "); Return retcode; } Retcode = sqlite3_prepare (dB, "select Sid from students order by SID ", -1, & stmt, 0 ); If (retcode! = Sqlite_ OK ){ Sqlite3_close (db ); Fprintf (stderr, "cocould not execute select/N "); Return retcode; } While (sqlite3_step (stmt) =Sqlite_row){ Int I = sqlite3_column_int (stmt, 0 ); Printf ("SID = % d/N", I ); } Sqlite3_finalize (stmt ); Sqlite3_close (db ); Return sqlite_ OK; }

You can compile the above example and execute it. The sample output shown in this document is obtained on a Linux machine, but these examples can work on other platforms running SQLite.

These examples assume that you have prepared the executable sqlite3, libsqlite3.so (sqlite3.dll on Windows, libsqlite3.dylib on Mac OS X) shared libraries, and sqlite3.h interface definition files. You can get these source code or binary files from the http://www.sqlite.org. You will find it easier to process all three (sqlite3, the shared library, and sqlite3.h) and examples in the same directory.

For example, assume that you are on a Linux system and save the app1.c sample program in the same directory as libsqlite3.so, sqlite3, and sqlite3.h. You can compile the file by executing this command:

GCC app1.c-o./app1-lsqlite3-L.

 

It will generate a binary file named app1 in the current working directory. You can execute this binary file to view the output.

Note:

SQLite source code and applications must be compiled in the same compiler. If you have installed SQLite as a package, or if your operating system has pre-installed SQLite, you may need to use a set of different compilation parameters. For example, on Ubuntu, you can install SQLite through The sudo aptitude install sqlite3 libsqlite3-dev, and you can use the command CC app1.c-o./app1-lsqlite3 to compile the application. Because SQLite is included in the current Mac OS X version, the same compilation command above can also work.

This application opens the mydb database file in the current working directory. This database requires at least one table named students. This table must have at least one integer column named Sid. In the next example, you will learn how to create a new table in the database, and how to insert rows (also known as tuples and records) into the table, you can use these commands to create and settle tables:

Sqlite3 mydb "create table students (SID integer )" Sqlite3 mydb "insert into students values (200 )" Sqlite3 mydb "insert into students values (100 )" Sqlite3 mydb "insert into students values (300 )"

If you are running app1 now (to introduce the SQLite library in Linux, you may need to add your working directory name to the LD_LIBRARY_PATH environment variable. You will see the following output:

SID = 1, 100 SID = 1, 200 SID = 1, 300

 

Note:

In Linux, UNIX, and Mac OS X, when you type the app1 name in a command prompt, you may need the prefix ./ ./App1

The application first makes preparations, and then runs the SQL statement: Select Sid from students order by SID. Then it goes to the row set as the result, obtains the SID value one by one, and prints each value. Close the database.

SQLite is a call-level interface library that can be embedded into applications. This library implements all SQLite API functions as C functions. All API functions are named with the sqlite3 _ prefix and declared in sqlite3.h. Some of them are used in the example application. They are sqlite3_open, sqlite3_prepare, sqlite3_step, sqlite3_column_int, sqlite3_finalize, and sqlite3_close. The application also uses the Enable constant, that is, the sqlite_ OK andSqlite_row. These Enis are defined in sqlite3.h.

1.1.1. 1 sqlite3_open

By executing the sqlite3_open function, the application opens a new connection to the database file through the SQLite library. (This application may have other open connections to the same or different databases. SQLite clearly processes these connections, and they are independent of each other before SQLite's attention ). If the file does not exist, SQLite automatically creates the database file.

Note:

When opening or creating a file, SQLite follows a lazy policy: knowing that the file is read and accessed can only be opened or created.

The sqlite3_open function returns a connection handle (a pointer to an object of the sqlite3 type) through a form parameter (dB, in the previous example ), the handle is used to support further operations on the database connection (because the connection is opened this time. The handle indicates the integrity of the connection.

1.1.1. 2 sqlite3_prepare

The sqlite3_prepare function compiles an SQL statement and generates an equivalent internal object. This object is widely mentioned in database syntax as a preparation statement. In SQLite, it is implemented as a byte encoding program. The bytecode program is an abstract representation of the SQL language running on a virtual machine or interpreter. For more information, see the subsequent part of the bytecode programming language. In this book, I will use conditional bytecode programs and preparation statements alternately.

The sqlite3_prepare function returns a status handle (pointer to sqlite3_stmt type object) through a form parameter (stmt in the preceding example). This handle is used to support further operations to manipulate the preparation status. In this example, I have prepared the select Sid from students order by SID statement as the stmt handle. This handle acts like an open cursor and is used to obtain the row set returned by the SELECT statement as the result, one row at a time.

1.1.1. 3 sqlite3_step

The sqlite3_step function will execute the bytecode program until it encounters a break (because it has calculated a new row), or until it stops (there are no remaining rows ). In the previous example, he returnedSqlite_rowIn the following example, sqlite_done is used. Because some SQL statements do not return rows (such as update, insert, delete, and create), sqlite_done is always returned when no rows are to be processed. The step function moves the pointer to obtain the result of the SELECT statement. Initially, the Pointer Points to the previous position of the first row of the output row set. Execute the step function once and move the pointer to the next row of the output row set. The pointer can only be moved forward.

1.1.1. 4 sqlite3_column_int

If the step function returnsSqlite_rowYou can perform the sqlite3_column _ * API function to retrieve the values of each column (also known as attributes or fields. The mismatch of the impedance (data type) in SQL/SQLite and C languages can be automatically processed: the API converts the data from the storage type to the request type in two languages. In the example application, each output row is an integer value. The SID value is read by executing the sqlite3_column_int function that returns the integer value.

1.1.1. 5 sqlite3_finalize

Sqlite3_finalize function destroys the prepared state. That is to say, the bytecode program is erased and all resources allocated to the status handle are released. The handle becomes unavailable.

1.1.1. 6 sqlite3_close

The sqlite3_close function closes the database connection and releases all resources allocated to the connection. The connection handle becomes unavailable.

1.1.1. 7 Other useful functions

Other widely used APIs are sqlite3_bind _ * And sqlite3_reset. In an SQL statement string (input to sqlite3_prepare), one or more text values can be identified by SQL parameters? (Or? Nnn,: AAA, @ AAA or $ AAA, where NNN is a number, and AAA is a identifier) instead. They are the input parameters of the prepared statement. The values of these parameters can be set by using the bind api function. If a parameter has no value constraints, it does not take the default value, that is, SQL null is used when no default value is declared. The reset API function resets the status handle (for example, preparation status) back to its initial State through an exception: All parameters with restricted values keep their original values. State preparation is re-executed by the application and these values are reused during re-execution. However, the application may run the bind api again before it starts re-execution to obtain a new value instead of the previous value.

1.1.1. 8 Return Value

All API functions return 0 or negative integer values. SQLite recommends that you use a mnemonic to check the returned value. The return value sqlite_ OK indicates that the operation is successful.Sqlite_rowThe sqlite3_step function finds a new row in the row set returned by the SELECT statement, and sqlite_done indicates that the statement execution is complete.