Use sqlite3 and C interfaces to develop database programs-[programming]

Source: Internet
Author: User
Tags sql error

Recently I am writing a software program on the Linux platform and need to use a simple database.
MySQL is a good database, but its data is stored on the server. The basic function I want is to use C program to create a local database file, and then perform basic operations on this database file.
SQL operations. Just like programming the DAO Database Based on vc6.0 on Windows platform (creating a local file. mdb ).

From the Internet to find a free open source database development tools-SQLite, the introduction of SQLite on the Internet there are a lot of, For details, see the official website: http://www.sqlite.com.cn /. I found that SQLite is exactly what I need. summarize the following features:
1. Open source code
2. Special program. In Windows, sqlite.exe is only less than kb.
3. Supports most SQL commands at extremely fast speeds
4. directly create a XXX. DB, which is a database without server support.
5. Simple C language API

Based on the above points, we can see that SQLite is powerful and outstanding. The open source code is very small and the program is very small. You can even directly compile "gcc-O sqlite3 *" across platforms. How wonderful it is to integrate this into a sneak system!

Install sqlite3 and Its Development Kit on ubuntu6.10:
# Sudo apt-Get install sqlite3 libsqlite3-dev

This article introduces how to use SQLite:
Http://www.sqlite.com.cn/MySqlite/4/378.Html

The following is my summary of the APIs for sqlite3 and C interfaces.
I mainly use the following functions (header file sqlite3.h ):
Int sqlite3_open (const char *, sqlite3 **); // open a database
Int sqlite3_close (sqlite3 *); // close
Int sqlite3_exec (sqlite3 *, const char * SQL, sqlite_callback, void *, char **); // Execute
Int sqlite3_get_table (sqlite3 *, const char * SQL, char *** result, int * nrow, int * ncolumn, char ** errmsg );
// Result stores the queried data in the form of an array, first the table name and then the data;
// Nrow/ncolumn are the number of rows/columns in the result set returned by the query statement. If no result is found, 0 is returned.
Sqlite3_errcode () is usually used to obtain the error code returned by the recently called API.
Sqlite3_errmsg () is used to obtain the text description corresponding to these error codes.

Exec error code
# Define sqlite_ OK 0/* successful result */
# Define sqlite_error 1/* SQL error or missing database */
# Define sqlite_internal 2/* an internal logic error in SQLite */
# Define sqlite_perm 3/* access permission denied */
# Define sqlite_abort 4/* callback routine requested an abort */
# Define sqlite_busy 5/* The database file is locked */
# Define sqlite_locked 6/* a table in the database is locked */
# Define sqlite_nomem 7/* A malloc () failed */
# Define sqlite_readonly 8/* attempt to write a readonly database */
# Define sqlite_interrupt 9/* operation terminated by sqlite_interrupt ()*/
# Define sqlite_ioerr 10/* some kind of disk I/O error occurred */
# Define sqlite_0000upt 11/* The database disk image is malformed */
# Define sqlite_notfound 12/* (internal only) table or record not found */
# Define sqlite_full 13/* insertion failed because database is full */
# Define sqlite_cantopen 14/* Unable to open the database file */
# Define sqlite_protocol 15/* database lock protocol error */
# Define sqlite_empty 16/* (internal only) database table is empty */
# Define sqlite_schema 17/* The database schema changed */
# Define sqlite_toobig 18/* too much data for one row of a table */
# Define sqlite_constraint 19/* abort due to contraint violation */
# Define sqlite_mismatch 20/* Data Type Mismatch */
# Define sqlite_misuse 21/* library used incorrectly */
# Define sqlite_nolfs 22/* uses OS features not supported on Host */
# Define sqlite_auth 23/* Authorization denied */
# Define sqlite_row 100/* sqlite_step () has another row ready */
# Define sqlite_done 101/* sqlite_step () has finished executing */
Application instance:

Definition
Sqlite3 * DB = NULL;
Char * errmsg = NULL;
Char SQL _cmd [2, 200];

Open the file:
Int rc = sqlite3_open ("XXX. DB", & dB );
If (RC) // open failed
Printf ("Open Database failed! /N ");
Else
Printf ("create the database successful! /N ");

Create a table:
Sprintf (SQL _cmd, "CREATE TABLE datapro (package integer, offset/
Integer, lklen integer, base inteher, link integer, err integer );");
Rc = sqlite3_exec (dB, SQL _cmd, & emsg); // create a table datapro
If (rc = sqlite_ OK) // The table is successfully created.
Printf ("create the chn_to_eng Table successful! /N ");
Else
Printf ("% s/n", emsg );
Add data:
Sprintf (SQL _cmd, "insert into datapro values (% d, % d);", 3 );
Rc = sqlite3_exec (pro_db, pro_sqlcmd, 0, & emsg );

Query:
Int nrow = 0, ncolumn = 0;
Char ** azresult; // Save the result
SQL = "select * From datapro ";
Sqlite3_get_table (dB, SQL, & azresult, & nrow, & ncolumn, & emsg );
// Where nrow is the number of rows and ncolum is the number of Columns
Printf ("/nthe result of querying is:/N ");
For (INT I = 1; I <nrow + 1; I ++)
{
For (Int J = 0; j <ncolumn; j ++)
Printf ("% s", azresult [I * ncolumn + J]);
Printf ("/N ");
}

Delete:
Sprintf (SQL _cmd, "delete from datapro where package = 1 ;");
Rc = sqlite3_exec (dB, SQL, 0, 0, & emsg );

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.