Programming SQLite3 in C
This is a C programming tutorial for the SQLite database. It covers the basics of SQLite programming with the C language. You might also want to check the,SQLite tutorial,MySQL C tutorial,or PostgreSQL C tutorial on ZetCode.
SQLite databaseSQLite is an embedded relational database engine. Its developers call it a self-contained,serverless,zero-configuration,and transactional SQL database engine. It is currently very popular and there are hundreds of millions copies worldwide in use today. SQLite is used in the Solaris 10,Mac OS,Android,or in the iPhone. The Qt4 library has built-in support for SQLite as well as the Python and PHP. Many popular applications use SQLite internally such as Firefox,Google Chrome,or Amarok. The sqlite3 toolThe sqlite3 tool is a terminal based frontend to the SQLite library.It evaluates queries interactively and displays the results in multiple formats. It can also be used within scripts. It has its own set of meta commandsincluding Now we are going to use the $ sqlite3 test.db SQLite version 3.8.2 2013-12-06 14:53:30 Enter ".help" for instructions Enter SQL statements terminated with a ";" We provide a parameter to the sqlite> .tables sqlite> .exit $ ls test.db The C99This tutorial uses C99. For GNU C compiler,we need to use the int rc = sqlite3_open("test.db",&db); In C99,we can mix declarations with code. In older Cprograms,we would need to separate this line into two lines. SQLite versionIn the first code example,we will get the version of the SQLite database. #include <sqlite3.h> #include <stdio.h> int main(void) { printf("%sn",sqlite3_libversion()); return 0; } The #include <sqlite3.h> This header file defines the interface that the SQLite library presentsto the client programs. It contains definitions,function prototypes,andcomments. It is an authoritative source for SQLite API. $ gcc -o version version.c -lsqlite3 -std=c99 We compile the program with the GNU C compiler. $ ./version 3.8.2 This is the output of the example. In the second example,we again get the version of the SQLitedatabase. This time we will use an SQL query. #include <sqlite3.h> #include <stdio.h> int main(void) { sqlite3 *db; sqlite3_stmt *res; int rc = sqlite3_open(":memory:",&db); if (rc != SQLITE_OK) { fprintf(stderr,"Cannot open database: %sn",sqlite3_errmsg(db)); sqlite3_close(db); return 1; } rc = sqlite3_prepare_v2(db,"SELECT SQLITE_VERSION()",-1,&res,0); if (rc != SQLITE_OK) { fprintf(stderr,"Failed to fetch data: %sn",sqlite3_errmsg(db)); sqlite3_close(db); return 1; } rc = sqlite3_step(res); if (rc == SQLITE_ROW) { printf("%sn",sqlite3_column_text(res,0)); } sqlite3_finalize(res); sqlite3_close(db); return 0; } The sqlite3 *db; The sqlite3_stmt *res; The int rc = sqlite3_open(":memory:",&db); The if (rc != SQLITE_OK) { fprintf(stderr,sqlite3_errmsg(db)); sqlite3_close(db); return 1; } If the return code indicates an error,we print the message to the console,close the database handle,and terminate the program. The rc = sqlite3_prepare_v2(db,0); Before an SQL statement is executed,it must be first compiled into a byte-codewith one of the sqlite3_prepare* functions. (The The On success, if (rc != SQLITE_OK) { fprintf(stderr,sqlite3_errmsg(db)); sqlite3_close(db); return 1; } This is error handling code for the rc = sqlite3_step(res); The sqlite3_finalize(res); The sqlite3_close(db); The Inserting dataWe create a #include <sqlite3.h> #include <stdio.h> int main(void) { sqlite3 *db; char *err_msg = 0; int rc = sqlite3_open("test.db",sqlite3_errmsg(db)); sqlite3_close(db); return 1; } char *sql = "DROP TABLE IF EXISTS Cars;" "CREATE TABLE Cars(Id INT,Name TEXT,Price INT);" "INSERT INTO Cars VALUES(1,'Audi',52642);" "INSERT INTO Cars VALUES(2,'Mercedes',57127);" "INSERT INTO Cars VALUES(3,'Skoda',9000);" "INSERT INTO Cars VALUES(4,'Volvo',29000);" "INSERT INTO Cars VALUES(5,'Bentley',350000);" "INSERT INTO Cars VALUES(6,'Citroen',21000);" "INSERT INTO Cars VALUES(7,'Hummer',41400);" "INSERT INTO Cars VALUES(8,'Volkswagen',21600);"; rc = sqlite3_exec(db,sql,&err_msg); if (rc != SQLITE_OK ) { fprintf(stderr,"SQL error: %sn",err_msg); sqlite3_free(err_msg); sqlite3_close(db); return 1; } sqlite3_close(db); return 0; } We connect to the char *err_msg = 0; If an error occurs,this pointer will point a the created error message. int rc = sqlite3_open("test.db",&db); The connection to the char *sql = "DROP TABLE IF EXISTS Cars;" "CREATE TABLE Cars(Id INT,Price INT);" "INSERT INTO Cars VALUES(1,52642);" "INSERT INTO Cars VALUES(2,57127);" "INSERT INTO Cars VALUES(3,9000);" "INSERT INTO Cars VALUES(4,29000);" "INSERT INTO Cars VALUES(5,350000);" "INSERT INTO Cars VALUES(6,21000);" "INSERT INTO Cars VALUES(7,41400);" "INSERT INTO Cars VALUES(8,21600);"; These SQL statements create a rc = sqlite3_exec(db,&err_msg); The The function's third parameter is a callback function invoked for each result row coming out of the evaluated SQL statement. The fourthparameter is the first parameter to the callback function. If we do not need them,we can pass 0 to these parameters. If an error occurs then the last parameter points to the allocated errormessage. sqlite3_free(err_msg); The allocated message string must be freed with the sqlite> .mode column sqlite> .headers on We verify the written data with the sqlite> SELECT * FROM Cars; Id Name Price ---------- ---------- ---------- 1 Audi 52642 2 Mercedes 57127 3 Skoda 9000 4 Volvo 29000 5 Bentley 350000 6 Citroen 21000 7 Hummer 41400 8 Volkswagen 21600 This is the data that we have written to the The last inserted row idSometimes,we need to determine the id of the last insertedrow. For this,we have the #include <sqlite3.h> #include <stdio.h> int main(void) { sqlite3 *db; char *err_msg = 0; int rc = sqlite3_open(":memory:",sqlite3_errmsg(db)); sqlite3_close(db); return 1; } char *sql = "CREATE TABLE Friends(Id INTEGER PRIMARY KEY,Name TEXT);" "INSERT INTO Friends(Name) VALUES ('Tom');" "INSERT INTO Friends(Name) VALUES ('Rebecca');" "INSERT INTO Friends(Name) VALUES ('Jim');" "INSERT INTO Friends(Name) VALUES ('Roger');" "INSERT INTO Friends(Name) VALUES ('Robert');"; rc = sqlite3_exec(db,"Failed to create tablen"); fprintf(stderr,err_msg); sqlite3_free(err_msg); } else { fprintf(stdout,"Table Friends created successfullyn"); } int last_id = sqlite3_last_insert_rowid(db); printf("The last Id of the inserted row is %dn",last_id); sqlite3_close(db); return 0; } A char *sql = "CREATE TABLE Friends(Id INTEGER PRIMARY KEY,Name TEXT);" "INSERT INTO Friends(Name) VALUES ('Tom');" "INSERT INTO Friends(Name) VALUES ('Rebecca');" "INSERT INTO Friends(Name) VALUES ('Jim');" "INSERT INTO Friends(Name) VALUES ('Roger');" "INSERT INTO Friends(Name) VALUES ('Robert');"; In SQLite, When using auto-incremented columns,we need to explicitly statethe column names except for the auto-incremented column,whichis omitted. int last_id = sqlite3_last_insert_rowid(db); printf("The last Id of the inserted row is %dn",last_id); The $ ./last_row_id Table Friends created successfully The last Id of the inserted row is 5 We see the output of the program. Retrieving dataWe have inserted some data into the #include <sqlite3.h> #include <stdio.h> int callback(void *,int,char **,char **); int main(void) { sqlite3 *db; char *err_msg = 0; int rc = sqlite3_open("test.db",sqlite3_errmsg(db)); sqlite3_close(db); return 1; } char *sql = "SELECT * FROM Cars"; rc = sqlite3_exec(db,callback,"Failed to select datan"); fprintf(stderr,err_msg); sqlite3_free(err_msg); sqlite3_close(db); return 1; } sqlite3_close(db); return 0; } int callback(void *NotUsed,int argc,char **argv,char **azColName) { NotUsed = 0; for (int i = 0; i < argc; i++) { printf("%s = %sn",azColName[i],argv[i] ? argv[i] : "NULL"); } printf("n"); return 0; } We use the int callback(void *,char **); This is a function prototype for the callback function that is usedin conjunction with the int rc = sqlite3_open("test.db",&db); We connect to the char *sql = "SELECT * FROM Cars"; Here we define the SQL statement to select all data from the rc = sqlite3_exec(db,&err_msg); The int callback(void *NotUsed,argv[i] ? argv[i] : "NULL"); } printf("n"); return 0; } The first parameter of the callback function is data provided in the 4th argument of In the function body,we go through all columns and print their names and content. $ ./select_all Id = 1 Name = Audi Price = 52642 Id = 2 Name = Mercedes Price = 57127 Id = 3 Name = Skoda Price = 9000 ... This is a partial output of the example. Parameterized queriesNow we will mention parameterized queries. Parameterized queries,also called prepared statements,increase security and performance. When we use parameterized queries,we use placeholders instead of directly writing the values into the statements. #include <sqlite3.h> #include <stdio.h> int main(void) { sqlite3 *db; char *err_msg = 0; sqlite3_stmt *res; int rc = sqlite3_open("test.db",sqlite3_errmsg(db)); sqlite3_close(db); return 1; } char *sql = "SELECT Id,Name FROM Cars WHERE Id = ?"; rc = sqlite3_prepare_v2(db,0); if (rc == SQLITE_OK) { sqlite3_bind_int(res,1,3); } else { fprintf(stderr,"Failed to execute statement: %sn",sqlite3_errmsg(db)); } int step = sqlite3_step(res); if (step == SQLITE_ROW) { printf("%s: ",0)); printf("%sn",1)); } sqlite3_finalize(res); sqlite3_close(db); return 0; } In the example,a question mark (?) is used as a placeholder which islater replaced with an actual value. char *sql = "SELECT Id,Name FROM Cars WHERE Id = ?"; The question mark is used to provide an Id to the SQL query. rc = sqlite3_prepare_v2(db,0); The sqlite3_bind_int(res,3); The int step = sqlite3_step(res); The if (step == SQLITE_ROW) { printf("%s: ",0)); printf("%sn",1)); } If there is some row of data available,we get the values of two columnswith the $ ./parameterized 3: Skoda The example returns the Id and the car's name. The second example uses parameterized statements with named placeholders. #include <sqlite3.h> #include <stdio.h> int main(void) { sqlite3 *db; char *err_msg = 0; sqlite3_stmt *res; int rc = sqlite3_open("test.db",Name FROM Cars WHERE Id = @id"; rc = sqlite3_prepare_v2(db,0); if (rc == SQLITE_OK) { int idx = sqlite3_bind_parameter_index(res,"@id"); int value = 4; sqlite3_bind_int(res,idx,value); } else { fprintf(stderr,1)); } sqlite3_finalize(res); sqlite3_close(db); return 0; } We select the name and the price of a car using named placeholders. char *sql = "SELECT Id,Name FROM Cars WHERE Id = @id"; Named placeholders are prefixed with the colon (:) character or theat-sign (@) character. int idx = sqlite3_bind_parameter_index(res,"@id"); The Inserting imagesIn this section,we are going to insert an image to the SQLite database. Note that some people argue against putting images into databases. Here we only show how to do it. We do notdwell on the technical issues of whether to save images indatabases or not. sqlite> CREATE TABLE Images(Id INTEGER PRIMARY KEY,Data BLOB); For this example,we create a new table called Images. For the images,we usethe #include <sqlite3.h> #include <stdio.h> int main(int argc,char **argv) { FILE *fp = fopen("woman.jpg","rb"); if (fp == NULL) { fprintf(stderr,"Cannot open image filen"); return 1; } fseek(fp,SEEK_END); if (ferror(fp)) { fprintf(stderr,"fseek() failedn"); int r = fclose(fp); if (r == EOF) { fprintf(stderr,"Cannot close file handlern"); } return 1; } int flen = ftell(fp); if (flen == -1) { perror("error occurred"); int r = fclose(fp); if (r == EOF) { fprintf(stderr,"Cannot close file handlern"); } return 1; } fseek(fp,SEEK_SET); if (ferror(fp)) { fprintf(stderr,"Cannot close file handlern"); } return 1; } char data[flen+1]; int size = fread(data,flen,fp); if (ferror(fp)) { fprintf(stderr,"fread() failedn"); int r = fclose(fp); if (r == EOF) { fprintf(stderr,"Cannot close file handlern"); } return 1; } int r = fclose(fp); if (r == EOF) { fprintf(stderr,"Cannot close file handlern"); } sqlite3 *db; char *err_msg = 0; int rc = sqlite3_open("test.db",sqlite3_errmsg(db)); sqlite3_close(db); return 1; } sqlite3_stmt *pStmt; char *sql = "INSERT INTO Images(Data) VALUES(?)"; rc = sqlite3_prepare(db,&pStmt,0); if (rc != SQLITE_OK) { fprintf(stderr,"Cannot prepare statement: %sn",sqlite3_errmsg(db)); return 1; } sqlite3_bind_blob(pStmt,data,size,SQLITE_STATIC); rc = sqlite3_step(pStmt); if (rc != SQLITE_DONE) { printf("execution failed: %s",sqlite3_errmsg(db)); } sqlite3_finalize(pStmt); sqlite3_close(db); return 0; } In this program,we read an image from the current working directory and write it into the FILE *fp = fopen("woman.jpg","rb"); if (fp == NULL) { fprintf(stderr,"Cannot open image filen"); return 1; } We read binary data from the filesystem. We have a JPG imagecalled fseek(fp,SEEK_END); if (ferror(fp)) { fprintf(stderr,"fseek() failedn"); int r = fclose(fp); if (r == EOF) { fprintf(stderr,"Cannot close file handlern"); } return 1; } We move the file pointer to the end of the file using the int flen = ftell(fp); if (flen == -1) { perror("error occurred"); int r = fclose(fp); if (r == EOF) { fprintf(stderr,"Cannot close file handlern"); } return 1; } For binary streams,the char data[flen+1]; This array will store the image data. int size = fread(data,fp); The int r = fclose(fp); if (r == EOF) { fprintf(stderr,"Cannot close file handlern"); } After the data is read,we can close the file handler. char *sql = "INSERT INTO Images(Data) VALUES(?)"; This SQL statement is used to insert the image into the database. rc = sqlite3_prepare(db,0); The SQL statement is compiled. sqlite3_bind_blob(pStmt,SQLITE_STATIC); The rc = sqlite3_step(pStmt); The statement is executed and the image is written to the table. Reading imagesIn this section,we are going to perform the reverse operation.We will read an image from the database table. #include <sqlite3.h> #include <stdio.h> int main(void) { FILE *fp = fopen("woman2.jpg","wb"); if (fp == NULL) { fprintf(stderr,"Cannot open image filen"); return 1; } sqlite3 *db; char *err_msg = 0; int rc = sqlite3_open("test.db",sqlite3_errmsg(db)); sqlite3_close(db); return 1; } char *sql = "SELECT Data FROM Images WHERE Id = 1"; sqlite3_stmt *pStmt; rc = sqlite3_prepare_v2(db,0); if (rc != SQLITE_OK ) { fprintf(stderr,"Failed to prepare statementn"); fprintf(stderr,sqlite3_errmsg(db)); sqlite3_close(db); return 1; } rc = sqlite3_step(pStmt); int bytes = 0; if (rc == SQLITE_ROW) { bytes = sqlite3_column_bytes(pStmt,0); } fwrite(sqlite3_column_blob(pStmt,0),bytes,fp); if (ferror(fp)) { fprintf(stderr,"fwrite() failedn"); return 1; } int r = fclose(fp); if (r == EOF) { fprintf(stderr,"Cannot close file handlern"); } rc = sqlite3_finalize(pStmt); sqlite3_close(db); return 0; } We read image data from the FILE *fp = fopen("woman2.jpg","wb"); if (fp == NULL) { fprintf(stderr,"Cannot open image filen"); return 1; } We open a binary file in a writing mode. The datafrom the database is written to the file. char *sql = "SELECT Data FROM Images WHERE Id = 1"; This SQL statement selects data from the Imagestable. We obtain the binary data from the first row. if (rc == SQLITE_ROW) { bytes = sqlite3_column_bytes(pStmt,0); } The fwrite(sqlite3_column_blob(pStmt,fp); The binary data is written to the file with the if (ferror(fp)) { fprintf(stderr,"fwrite() failedn"); return 1; } The MetadataMetadata is information about the data in the database. Metadata in a SQLite contains information about the tables and columns,in which we store data. Number of rows affected by an SQL statement is a metadata. Number of rows and columns returned in a result set belong to metadata as well. Metadata in SQLite can be obtained using the #include <sqlite3.h> #include <stdio.h> int callback(void *,char **); int main(void) { sqlite3 *db; char *err_msg = 0; int rc = sqlite3_open("test.db",sqlite3_errmsg(db)); sqlite3_close(db); return 1; } char *sql = "PRAGMA table_info(Cars)"; rc = sqlite3_exec(db,argv[i] ? argv[i] : "NULL"); } printf("n"); return 0; } In this example,we issue the char *sql = "PRAGMA table_info(Cars)"; The $ ./column_names cid = 0 name = Id type = INT notnull = 0 dflt_value = NULL pk = 0 ... This is output of the example. In the next example related to the metadata,we will list all tables in the #include <sqlite3.h> #include <stdio.h> int callback(void *,sqlite3_errmsg(db)); sqlite3_close(db); return 1; } char *sql = "SELECT name FROM sqlite_master WHERE type='table'"; rc = sqlite3_exec(db,char **azColName) { NotUsed = 0; for (int i = 0; i < argc; i++) { printf("%sn",argv[i] ? argv[i] : "NULL"); } return 0; } The code example prints all available tables in the current databaseto the terminal. char *sql = "SELECT name FROM sqlite_master WHERE type='table'"; The table names are stored inside the system $ ./list_tables Cars Images This is a sample output. TransactionsA transaction is an atomic unit of database operations against the data in one or more databases. The effects of all the SQL statements in a transaction can be either all committed to the database or all rolled back. In SQLite,any command other than the Manual transactions are started with the SQLite supports three non-standard transaction levels: AutocommitBy default,SQLite version 3 operates in autocommit mode. In autocommit mode,all changes to the database are committed as soon as all operations associated with the current database connection complete. Autocommit mode is disabled by a #include <sqlite3.h> #include <stdio.h> int main() { sqlite3 *db; int rc = sqlite3_open("test.db",sqlite3_errmsg(db)); sqlite3_close(db); return 1; } printf("Autocommit: %dn",sqlite3_get_autocommit(db)); sqlite3_close(db); return 0; } This example check whether the database is in the autocommit mode. printf("Autocommit: %dn",sqlite3_get_autocommit(db)); The $ ./get_ac_mode Autocommit: 1 The example confirms that SQLite is in the autocommit mode by default. The next example further clarifies the autocommit mode. In the autocommitmode,each non-SELECT statement is a small transaction that is immediatelycommitted. #include <sqlite3.h> #include <stdio.h> int main(void) { sqlite3 *db; char *err_msg = 0; int rc = sqlite3_open("test.db",sqlite3_errmsg(db)); sqlite3_close(db); return 1; } char *sql = "DROP TABLE IF EXISTS Friends;" "CREATE TABLE Friends(Id INTEGER PRIMARY KEY,Name TEXT);" "INSERT INTO Friends(Name) VALUES ('Tom');" "INSERT INTO Friends(Name) VALUES ('Rebecca');" "INSERT INTO Friends(Name) VALUES ('Jim');" "INSERT INTO Friend(Name) VALUES ('Robert');"; rc = sqlite3_exec(db,err_msg); sqlite3_free(err_msg); sqlite3_close(db); return 1; } sqlite3_close(db); return 0; } We create the char *sql = "DROP TABLE IF EXISTS Friends;" "CREATE TABLE Friends(Id INTEGER PRIMARY KEY,Name TEXT);" "INSERT INTO Friends(Name) VALUES ('Tom');" "INSERT INTO Friends(Name) VALUES ('Rebecca');" "INSERT INTO Friends(Name) VALUES ('Jim');" "INSERT INTO Friend(Name) VALUES ('Robert');"; The last SQL statement has an error; there is no Friend table. $ ./autocommit SQL error: no such table: Friend $ sqlite3 test.db sqlite> .tables Cars Friends Images sqlite> SELECT * FROM Friends; 1|Tom 2|Rebecca 3|Jim The table is created and three rows are inserted. TransactionIn the next example,we put some SQL statements within a transaction. #include <sqlite3.h> #include <stdio.h> int main(void) { sqlite3 *db; char *err_msg = 0; int rc = sqlite3_open("test.db",sqlite3_errmsg(db)); sqlite3_close(db); return 1; } char *sql = "DROP TABLE IF EXISTS Friends;" "BEGIN TRANSACTION;" "CREATE TABLE Friends(Id INTEGER PRIMARY KEY,Name TEXT);" "INSERT INTO Friends(Name) VALUES ('Tom');" "INSERT INTO Friends(Name) VALUES ('Rebecca');" "INSERT INTO Friends(Name) VALUES ('Jim');" "INSERT INTO Friend(Name) VALUES ('Robert');" "COMMIT;"; rc = sqlite3_exec(db,err_msg); sqlite3_free(err_msg); sqlite3_close(db); return 1; } sqlite3_close(db); return 0; } We continue working with the char *sql = "DROP TABLE IF EXISTS Friends;" "BEGIN TRANSACTION;" "CREATE TABLE Friends(Id INTEGER PRIMARY KEY,Name TEXT);" "INSERT INTO Friends(Name) VALUES ('Tom');" "INSERT INTO Friends(Name) VALUES ('Rebecca');" "INSERT INTO Friends(Name) VALUES ('Jim');" "INSERT INTO Friend(Name) VALUES ('Robert');" "COMMIT;"; The first statement drops the sqlite> .tables Cars Images Since the last statement had an error,the transaction was rolled backand the SourcesThe SQLite documentation was used to create this tutorial. TweetThis was SQLite C tutorial. ZetCode has a complete e-book for SQLite Python:
转自: http://zetcode.com/db/sqlitec/ (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |