迎广肖邦电源更换风扇:sqlite教程 - 我的文章 - Jerry
来源:百度文库 编辑:中财网 时间:2024/05/06 09:08:25
1:sqlite常用接口
2个重要结构体和5个主要函数:
sqlite3 *pdb, 数据库句柄,跟文件句柄FILE很类似
sqlite3_stmt *stmt, 这个相当于ODBC的Command对象,用于保存编译好的SQL语句
sqlite3_open(), 打开数据库
sqlite3_exec(), 执行非查询的sql语句
sqlite3_prepare(), 准备sql语句,执行select语句或者要使用parameter bind时,用这个函数(封装了sqlite3_exec).
Sqlite3_step(), 在调用sqlite3_prepare后,使用这个函数在记录集中移动。
Sqlite3_close(), 关闭数据库文件
还有一系列的函数,用于从记录集字段中获取数据,如
sqlite3_column_text(), 取text类型的数据。
sqlite3_column_blob(),取blob类型的数据
sqlite3_column_int(), 取int类型的数据
…
2:sqlite数据类型介绍
在进行数据库Sql操作之前,首先有个问题需要说明,就是Sqlite的数据类型,和其他的数据库不同,Sqlite支持的数据类型有他自己的特色,这个特色有时会被认为是一个潜在的缺点,但是这个问题并不在我们的讨论范围之内。大多数的数据库在数据类型上都有严格的限制,在建立表的时候,每一列都必须制定一个数据类型,只有符合该数据类型的数据可以被保存在这一列当中。而在Sqlite 2.X中,数据类型这个属性只属于数据本生,而不和数据被存在哪一列有关,也就是说数据的类型并不受数据列限制(有一个例外:INTEGER PRIMARY KEY,该列只能存整型数据)。但是当Sqlite进入到3.0版本的时候,这个问题似乎又有了新的答案,Sqlite的开发者开始限制这种无类型的使用,在3.0版本当中,每一列开始拥有自己的类型,并且在数据存入该列的时候,数据库会试图把数据的类型向该类型转换,然后以转换之后的类型存储。当然,如果转换被认为是不可行的,Sqlite仍然会存储这个数据,就像他的前任版本一样。举个例子,如果你企图向一个INTEGER类型的列中插入一个字符串,Sqlite会检查这个字符串是否有整型数据的特征, 如果有而且可以被数据库所识别,那么该字符串会被转换成整型再保存,如果不行,则还是作为整型存储。
总的来说,所有存在Sqlite 3.0版本当中的数据都拥有以下之一的数据类型:空(NULL):该值为空整型(INTEGEER):有符号整数,按大小被存储成1,2,3,4,6或8字节。实数(REAL):浮点数,以8字节指数形式存储。文本(TEXT):字符串,以数据库编码方式存储(UTF-8, UTF-16BE 或者 UTF-16-LE)。BLOB:BLOB数据不做任何转换,以输入形式存储。
ps: 在关系数据库中,CLOB和BLOB类型被用来存放大对象。BOLB表示二进制大对象,这种数据类型通过用来保存图片,图象,视频等。CLOB表示字符大对象,能够存放大量基于字符的数据。
对应的,对于数据列,同样有以下的数据类型:TEXTNUMERICINTEGERREALNONE数据列的属性的作用是确定对插入的数据的转换方向:TEXT 将数据向文本进行转换,对应的数据类型为NULL,TEXT 或 BLOBNUMERIC 将数据向数字进行转换,对应的数据类型可能为所有的五类数据,当试图存入文本 时将执行向整型或浮点类型的转换(视具体的数值而定),转换若不可行,则保留文本类型存储,NULL或BLOB不做变化INTEGER 将数据向整型转换,类似于NUMERIC,不同的是没有浮点标志的浮点数将转换为整型保存REAL 将数据向浮点数类型转换,类似于NUMERIC,不同的是整数将转换为浮点数保存NULL 不做任何转换的数据列类型
实例代码如下,
附件工程可直接编译,例子使用了blob数据类型。
#include "sqlite3.h" //包含一个头文件就可以使用所以sqlite的接口了
#include "stdlib.h"
#include "stdio.h"
#include "string.h"
#pragma comment(lib, "sqlite.lib") //我把sqlite编译成了一个静态的lib文件。
void createdb();
void querydb();
int main()
{
createdb();
querydb();
return 0;
}
void createdb()
{
int ret;
sqlite3 *pdb = 0;
sqlite3_stmt *stmt = 0;
char *error = 0;
char *sql = "insert into table1 values('value11',:aaa)";
int index;
static void *value = "asdfadsfasdfjasdfjaksdfaskjdfakdsfaksfja";
ret = sqlite3_open("db1.sdb", &pdb); //打开数据库,跟打开文本文件一样
if( ret != SQLITE_OK )
return;
ret = sqlite3_exec(pdb, "create table table1(col1 char(20), col2 BLOB)", 0,0, &error );
if( ret != SQLITE_OK )
return;
ret = sqlite3_prepare(pdb, sql,strlen(sql), &stmt, &error);
if( ret != SQLITE_OK )
return;
index = sqlite3_bind_parameter_index(stmt, ":aaa");
ret = sqlite3_bind_blob(stmt, index, value, strlen(value), SQLITE_STATIC);
if( ret != SQLITE_OK )
return;
ret = sqlite3_step(stmt);
if( ret != SQLITE_DONE )
return;
sqlite3_close(pdb);
}
void querydb()
{
int ret;
sqlite3 *pdb = 0;
sqlite3_stmt *pstmt = 0;
char *error = 0;
char *sql = "select * from table1";
int len;
int i;
char *name;
void *value;
ret = sqlite3_open("db1.sdb", &pdb);
if( ret != SQLITE_OK )
return;
ret = sqlite3_prepare(pdb, sql, strlen(sql), &pstmt, &error);
if( ret != SQLITE_OK )
return;
while( 1 )
{
ret = sqlite3_step(pstmt);
if( ret != SQLITE_ROW )
break;
name = sqlite3_column_text(pstmt, 0);
value = sqlite3_column_blob(pstmt, 1);
len = sqlite3_column_bytes(pstmt,1 );
}
}
实例二:SQLite中如何用api操作blob类型的字段
在实际的编程开发当中我们经常要处理一些大容量二进制数据的存储,如图片或者音乐等等。对于这些二进制数据(blob字段)我们不能像处理普通的文本那样简单的插入或者查询,为此SQLite提供了一组函数来处理这种BLOB字段类型。下面的代码演示了如何使用这些API函数。 首先我们要建立一个数据库:sqlite3_exec(db, "CREATE TABLE list (fliename varchar(128) UNIQUE, fzip blob);", 0, 0, &zErrMsg); //由于mmmm.rar是一个二进制文件,所以要在使用insert语句时先用?号代替 sqlite3_prepare(db, "insert into list values ('mmmm.rar',?);", -1, &stat, 0); FILE *fp; long filesize = 0; char * ffile; fp = fopen("mmmm.rar", "rb"); if(fp != NULL) { //计算文件的大小 fseek(fp, 0, SEEK_END); filesize = ftell(fp); fseek(fp, 0, SEEK_SET); //读取文件 ffile = new char[filesize+1]; size_t sz = fread(ffile, sizeof(char), filesize+1, fp); fclose(fp); } //将文件数据绑定到insert语句中,替换“?”部分 sqlite3_bind_blob(stat, 1, ffile, filesize, NULL); //执行绑定之后的SQL语句 sqlite3_step(stat);这时数据库当中已经有了一条包含BLOB字段的数据。接下来我们要读取这条数据: //选取该条数据 sqlite3_prepare(db, "select * from list;", -1, &stat, 0); sqlite3_step(stat);//得到纪录中的BLOB字段const void * test = sqlite3_column_blob(stat, 1);//得到字段中数据的长度 int size = sqlite3_column_bytes(stat, 1); //拷贝该字段 sprintf(buffer2, "%s", test);此时可以将buffer2写入到文件当中,至此BLOB数据处理完毕。 实例三:sqlite 中用blob存储图片和取出图片 #include#include#includeusing namespace std;int main(){ sqlite3 *db; sqlite3_stmt *stat; char *zErrMsg = 0; char buffer2[1024]="0";
sqlite3_open("./MetaInfo.db", &db); int result; if(result) { cout<<"Open the database sqlite.db failed"<
SQLite3是SQLite一个全新的版本,它虽然是在SQLite 2.8.13的代码基础之上开发的,但是使用了和之前的版本不兼容的数据库格式和API. SQLite3是为了满足以下的需求而开发的:
支持UTF-16编码.用户自定义的文本排序方法.可以对BLOBs字段建立索引.因此为了支持这些特性我改变了数据库的格式,建立了一个与之前版本不兼容的3.0版. 至于其他的兼容性的改变,例如全新的API等等,都将在理论介绍之后向你说明,这样可以使你最快的一次性摆脱兼容性问题.
3.0版的和2.X版的API非常相似,但是有一些重要的改变需要注意. 所有API接口函数和数据结构的前缀都由"sqlite_"改为了"sqlite3_". 这是为了避免同时使用SQLite 2.X和SQLite 3.0这两个版本的时候发生链接冲突.
由于对于C语言应该用什么数据类型来存放UTF-16编码的字符串并没有一致的规范. 因此SQLite使用了普通的void* 类型来指向UTF-16编码的字符串. 客户端使用过程中可以把void*映射成适合他们的系统的任何数据类型.
2.0 C/C++ 接口SQLite 3.0一共有83个API函数,此外还有一些数据结构和预定义(#defines). (完整的API介绍请参看另一份文档.) 不过你们可以放心,这些接口使用起来不会像它的数量所暗示的那么复杂. 最简单的程序仍然使用三个函数就可以完成: sqlite3_open(), sqlite3_exec(), 和 sqlite3_close(). 要是想更好的控制数据库引擎的执行,可以使用提供的sqlite3_prepare()函数把SQL语句编译成字节码,然后在使用sqlite3_step()函数来执行编译后的字节码. 以sqlite3_column_开头的一组API函数用来获取查询结果集中的信息. 许多接口函数都是成对出现的,同时有UTF-8和UTF-16两个版本. 并且提供了一组函数用来执行用户自定义的SQL函数和文本排序函数.
2.1 如何打开关闭数据库 typedef struct sqlite3 sqlite3; int sqlite3_open(const char*, sqlite3**); int sqlite3_open16(const void*, sqlite3**); int sqlite3_close(sqlite3*); const char *sqlite3_errmsg(sqlite3*); const void *sqlite3_errmsg16(sqlite3*); int sqlite3_errcode(sqlite3*);sqlite3_open() 函数返回一个整数错误代码,而不是像第二版中一样返回一个指向sqlite3结构体的指针. sqlite3_open() 和 sqlite3_open16() 的不同之处在于sqlite3_open16() 使用UTF-16编码(使用本地主机字节顺序)传递数据库文件名. 如果要创建新数据库, sqlite3_open16() 将内部文本转换为UTF-16编码, 反之sqlite3_open() 将文本转换为UTF-8编码.
打开或者创建数据库的命令会被缓存,直到这个数据库真正被调用的时候才会被执行. 而且允许使用PRAGMA声明来设置如本地文本编码或默认内存页面大小等选项和参数.
sqlite3_errcode() 通常用来获取最近调用的API接口返回的错误代码. sqlite3_errmsg() 则用来得到这些错误代码所对应的文字说明. 这些错误信息将以 UTF-8 的编码返回,并且在下一次调用任何SQLite API函数的时候被清除. sqlite3_errmsg16() 和 sqlite3_errmsg() 大体上相同,除了返回的错误信息将以 UTF-16 本机字节顺序编码.
SQLite3的错误代码相比SQLite2没有任何的改变,它们分别是:
#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_CORRUPT 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 */2.2 执行 SQL 语句
typedef int (*sqlite_callback)(void*,int,char**, char**); int sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void*, char**);
sqlite3_exec 函数依然像它在SQLite2中一样承担着很多的工作. 该函数的第二个参数中可以编译和执行零个或多个SQL语句. 查询的结果返回给回调函数. 更多地信息可以查看API 参考.
在SQLite3里,sqlite3_exec一般是被准备SQL语句接口封装起来使用的.
typedef struct sqlite3_stmt sqlite3_stmt; int sqlite3_prepare(sqlite3*, const char*, int, sqlite3_stmt**, const char**); int sqlite3_prepare16(sqlite3*, const void*, int, sqlite3_stmt**, const void**); int sqlite3_finalize(sqlite3_stmt*); int sqlite3_reset(sqlite3_stmt*);
sqlite3_prepare 接口把一条SQL语句编译成字节码留给后面的执行函数. 使用该接口访问数据库是当前比较好的的一种方法.
sqlite3_prepare() 处理的SQL语句应该是UTF-8编码的. 而sqlite3_prepare16() 则要求是UTF-16编码的. 输入的参数中只有第一个SQL语句会被编译. 第四个参数则用来指向输入参数中下一个需要编译的SQL语句存放的SQLite statement对象的指针, 任何时候如果调用 sqlite3_finalize() 将销毁一个准备好的SQL声明. 在数据库关闭之前,所有准备好的声明都必须被释放销毁. sqlite3_reset() 函数用来重置一个SQL声明的状态,使得它可以被再次执行.
SQL声明可以包含一些型如"?" 或 "?nnn" 或 ":aaa"的标记, 其中"nnn" 是一个整数,"aaa" 是一个字符串. 这些标记代表一些不确定的字符值(或者说是通配符),可以在后面用sqlite3_bind 接口来填充这些值. 每一个通配符都被分配了一个编号(由它在SQL声明中的位置决定,从1开始),此外也可以用 "nnn" 来表示 "?nnn" 这种情况. 允许相同的通配符在同一个SQL声明中出现多次, 在这种情况下所有相同的通配符都会被替换成相同的值. 没有被绑定的通配符将自动取NULL值.
int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void(*)(void*)); int sqlite3_bind_double(sqlite3_stmt*, int, double); int sqlite3_bind_int(sqlite3_stmt*, int, int); int sqlite3_bind_int64(sqlite3_stmt*, int, long long int); int sqlite3_bind_null(sqlite3_stmt*, int); int sqlite3_bind_text(sqlite3_stmt*, int, const char*, int n, void(*)(void*)); int sqlite3_bind_text16(sqlite3_stmt*, int, const void*, int n, void(*)(void*)); int sqlite3_bind_value(sqlite3_stmt*, int, const sqlite3_value*);
以上是 sqlite3_bind 所包含的全部接口,它们是用来给SQL声明中的通配符赋值的. 没有绑定的通配符则被认为是空值. 绑定上的值不会被sqlite3_reset()函数重置. 但是在调用了sqlite3_reset()之后所有的通配符都可以被重新赋值.
在SQL声明准备好之后(其中绑定的步骤是可选的), 需要调用以下的方法来执行:
int sqlite3_step(sqlite3_stmt*);
如果SQL返回了一个单行结果集,sqlite3_step() 函数将返回 SQLITE_ROW , 如果SQL语句执行成功或者正常将返回 SQLITE_DONE , 否则将返回错误代码. 如果不能打开数据库文件则会返回 SQLITE_BUSY . 如果函数的返回值是 SQLITE_ROW, 那么下边的这些方法可以用来获得记录集行中的数据:
const void *sqlite3_column_blob(sqlite3_stmt*, int iCol); int sqlite3_column_bytes(sqlite3_stmt*, int iCol); int sqlite3_column_bytes16(sqlite3_stmt*, int iCol); int sqlite3_column_count(sqlite3_stmt*); const char *sqlite3_column_decltype(sqlite3_stmt *, int iCol); const void *sqlite3_column_decltype16(sqlite3_stmt *, int iCol); double sqlite3_column_double(sqlite3_stmt*, int iCol); int sqlite3_column_int(sqlite3_stmt*, int iCol); long long int sqlite3_column_int64(sqlite3_stmt*, int iCol); const char *sqlite3_column_name(sqlite3_stmt*, int iCol); const void *sqlite3_column_name16(sqlite3_stmt*, int iCol); const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol); const void *sqlite3_column_text16(sqlite3_stmt*, int iCol); int sqlite3_column_type(sqlite3_stmt*, int iCol);
sqlite3_column_count()函数返回结果集中包含的列数. sqlite3_column_count() 可以在执行了 sqlite3_prepare()之后的任何时刻调用. sqlite3_data_count()除了必需要在sqlite3_step()之后调用之外,其他跟sqlite3_column_count() 大同小异. 如果调用sqlite3_step() 返回值是 SQLITE_DONE 或者一个错误代码, 则此时调用sqlite3_data_count() 将返回 0 ,然而 sqlite3_column_count() 仍然会返回结果集中包含的列数.
返回的记录集通过使用其它的几个 sqlite3_column_***() 函数来提取, 所有的这些函数都把列的编号作为第二个参数. 列编号从左到右以零起始. 请注意它和之前那些从1起始的参数的不同.
sqlite3_column_type()函数返回第N列的值的数据类型. 具体的返回值如下:
#define SQLITE_INTEGER 1 #define SQLITE_FLOAT 2 #define SQLITE_TEXT 3 #define SQLITE_BLOB 4 #define SQLITE_NULL 5
sqlite3_column_decltype() 则用来返回该列在 CREATE TABLE 语句中声明的类型. 它可以用在当返回类型是空字符串的时候. sqlite3_column_name() 返回第N列的字段名. sqlite3_column_bytes() 用来返回 UTF-8 编码的BLOBs列的字节数或者TEXT字符串的字节数. sqlite3_column_bytes16() 对于BLOBs列返回同样的结果,但是对于TEXT字符串则按 UTF-16 的编码来计算字节数. sqlite3_column_blob() 返回 BLOB 数据. sqlite3_column_text() 返回 UTF-8 编码的 TEXT 数据. sqlite3_column_text16() 返回 UTF-16 编码的 TEXT 数据. sqlite3_column_int() 以本地主机的整数格式返回一个整数值. sqlite3_column_int64() 返回一个64位的整数. 最后, sqlite3_column_double() 返回浮点数.
不一定非要按照sqlite3_column_type()接口返回的数据类型来获取数据. 数据类型不同时软件将自动转换.
Datatypes In SQLite Version 3
1. Storage Classes
Version 2 of SQLite stores all column values as ASCII text. Version 3 enhances this by providing the ability to store integer and real numbers in a more compact format and the capability to store BLOB data.
Each value stored in an SQLite database (or manipulated by the database engine) has one of the following storage classes:
NULL. The value is a NULL value.
INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.
TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16-LE).
BLOB. The value is a blob of data, stored exactly as it was input.
As in SQLite version 2, any column in a version 3 database except an INTEGER PRIMARY KEY may be used to store any type of value. The exception to this rule is described below under 'Strict Affinity Mode'.
All values supplied to SQLite, whether as literals embedded in SQL statements or values bound to pre-compiled SQL statements are assigned a storage class before the SQL statement is executed. Under circumstances described below, the database engine may convert values between numeric storage classes (INTEGER and REAL) and TEXT during query execution.
Storage classes are initially assigned as follows:
Values specified as literals as part of SQL statements are assigned storage class TEXT if they are enclosed by single or double quotes, INTEGER if the literal is specified as an unquoted number with no decimal point or exponent, REAL if the literal is an unquoted number with a decimal point or exponent and NULL if the value is a NULL. Literals with storage class BLOB are specified using the X'ABCD' notation.
Values supplied using the sqlite3_bind_* APIs are assigned the storage class that most closely matches the native type bound (i.e. sqlite3_bind_blob() binds a value with storage class BLOB).
The storage class of a value that is the result of an SQL scalar operator depends on the outermost operator of the expression. User-defined functions may return values with any storage class. It is not generally possible to determine the storage class of the result of an expression at compile time.
2. Column Affinity
In SQLite version 3, the type of a value is associated with the value itself, not with the column or variable in which the value is stored. (This is sometimes called manifest typing.) All other SQL databases engines that we are aware of use the more restrictive system of static typing where the type is associated with the container, not the value.
In order to maximize compatibility between SQLite and other database engines, SQLite support the concept of "type affinity" on columns. The type affinity of a column is the recommended type for data stored in that column. The key here is that the type is recommended, not required. Any column can still store any type of data, in theory. It is just that some columns, given the choice, will prefer to use one storage class over another. The preferred storage class for a column is called its "affinity".
Each column in an SQLite 3 database is assigned one of the following type affinities:
TEXTNUMERICINTEGERREALNONEA column with TEXT affinity stores all data using storage classes NULL, TEXT or BLOB. If numerical data is inserted into a column with TEXT affinity it is converted to text form before being stored.
A column with NUMERIC affinity may contain values using all five storage classes. When text data is inserted into a NUMERIC column, an attempt is made to convert it to an integer or real number before it is stored. If the conversion is successful, then the value is stored using the INTEGER or REAL storage class. If the conversion cannot be performed the value is stored using the TEXT storage class. No attempt is made to convert NULL or blob values.
A column that uses INTEGER affinity behaves in the same way as a column with NUMERIC affinity, except that if a real value with no floating point component (or text value that converts to such) is inserted it is converted to an integer and stored using the INTEGER storage class.
A column with REAL affinity behaves like a column with NUMERIC affinity except that it forces integer values into floating point representation. (As an optimization, integer values are stored on disk as integers in order to take up less space and are only converted to floating point as the value is read out of the table.)
A column with affinity NONE does not prefer one storage class over another. It makes no attempt to coerce data before it is inserted.
2.1 Determination Of Column Affinity
The type affinity of a column is determined by the declared type of the column, according to the following rules:
If the datatype contains the string "INT" then it is assigned INTEGER affinity.
If the datatype of the column contains any of the strings "CHAR", "CLOB", or "TEXT" then that column has TEXT affinity. Notice that the type VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity.
If the datatype for a column contains the string "BLOB" or if no datatype is specified then the column has affinity NONE.
If the datatype for a column contains any of the strings "REAL", "FLOA", or "DOUB" then the column has REAL affinity
Otherwise, the affinity is NUMERIC.
If a table is created using a "CREATE TABLE AS SELECT..." statement, then all columns have no datatype specified and they are given no affinity.
2个重要结构体和5个主要函数:
sqlite3 *pdb, 数据库句柄,跟文件句柄FILE很类似
sqlite3_stmt *stmt, 这个相当于ODBC的Command对象,用于保存编译好的SQL语句
sqlite3_open(), 打开数据库
sqlite3_exec(), 执行非查询的sql语句
sqlite3_prepare(), 准备sql语句,执行select语句或者要使用parameter bind时,用这个函数(封装了sqlite3_exec).
Sqlite3_step(), 在调用sqlite3_prepare后,使用这个函数在记录集中移动。
Sqlite3_close(), 关闭数据库文件
还有一系列的函数,用于从记录集字段中获取数据,如
sqlite3_column_text(), 取text类型的数据。
sqlite3_column_blob(),取blob类型的数据
sqlite3_column_int(), 取int类型的数据
…
2:sqlite数据类型介绍
在进行数据库Sql操作之前,首先有个问题需要说明,就是Sqlite的数据类型,和其他的数据库不同,Sqlite支持的数据类型有他自己的特色,这个特色有时会被认为是一个潜在的缺点,但是这个问题并不在我们的讨论范围之内。大多数的数据库在数据类型上都有严格的限制,在建立表的时候,每一列都必须制定一个数据类型,只有符合该数据类型的数据可以被保存在这一列当中。而在Sqlite 2.X中,数据类型这个属性只属于数据本生,而不和数据被存在哪一列有关,也就是说数据的类型并不受数据列限制(有一个例外:INTEGER PRIMARY KEY,该列只能存整型数据)。但是当Sqlite进入到3.0版本的时候,这个问题似乎又有了新的答案,Sqlite的开发者开始限制这种无类型的使用,在3.0版本当中,每一列开始拥有自己的类型,并且在数据存入该列的时候,数据库会试图把数据的类型向该类型转换,然后以转换之后的类型存储。当然,如果转换被认为是不可行的,Sqlite仍然会存储这个数据,就像他的前任版本一样。举个例子,如果你企图向一个INTEGER类型的列中插入一个字符串,Sqlite会检查这个字符串是否有整型数据的特征, 如果有而且可以被数据库所识别,那么该字符串会被转换成整型再保存,如果不行,则还是作为整型存储。
总的来说,所有存在Sqlite 3.0版本当中的数据都拥有以下之一的数据类型:空(NULL):该值为空整型(INTEGEER):有符号整数,按大小被存储成1,2,3,4,6或8字节。实数(REAL):浮点数,以8字节指数形式存储。文本(TEXT):字符串,以数据库编码方式存储(UTF-8, UTF-16BE 或者 UTF-16-LE)。BLOB:BLOB数据不做任何转换,以输入形式存储。
ps: 在关系数据库中,CLOB和BLOB类型被用来存放大对象。BOLB表示二进制大对象,这种数据类型通过用来保存图片,图象,视频等。CLOB表示字符大对象,能够存放大量基于字符的数据。
对应的,对于数据列,同样有以下的数据类型:TEXTNUMERICINTEGERREALNONE数据列的属性的作用是确定对插入的数据的转换方向:TEXT 将数据向文本进行转换,对应的数据类型为NULL,TEXT 或 BLOBNUMERIC 将数据向数字进行转换,对应的数据类型可能为所有的五类数据,当试图存入文本 时将执行向整型或浮点类型的转换(视具体的数值而定),转换若不可行,则保留文本类型存储,NULL或BLOB不做变化INTEGER 将数据向整型转换,类似于NUMERIC,不同的是没有浮点标志的浮点数将转换为整型保存REAL 将数据向浮点数类型转换,类似于NUMERIC,不同的是整数将转换为浮点数保存NULL 不做任何转换的数据列类型
实例代码如下,
附件工程可直接编译,例子使用了blob数据类型。
#include "sqlite3.h" //包含一个头文件就可以使用所以sqlite的接口了
#include "stdlib.h"
#include "stdio.h"
#include "string.h"
#pragma comment(lib, "sqlite.lib") //我把sqlite编译成了一个静态的lib文件。
void createdb();
void querydb();
int main()
{
createdb();
querydb();
return 0;
}
void createdb()
{
int ret;
sqlite3 *pdb = 0;
sqlite3_stmt *stmt = 0;
char *error = 0;
char *sql = "insert into table1 values('value11',:aaa)";
int index;
static void *value = "asdfadsfasdfjasdfjaksdfaskjdfakdsfaksfja";
ret = sqlite3_open("db1.sdb", &pdb); //打开数据库,跟打开文本文件一样
if( ret != SQLITE_OK )
return;
ret = sqlite3_exec(pdb, "create table table1(col1 char(20), col2 BLOB)", 0,0, &error );
if( ret != SQLITE_OK )
return;
ret = sqlite3_prepare(pdb, sql,strlen(sql), &stmt, &error);
if( ret != SQLITE_OK )
return;
index = sqlite3_bind_parameter_index(stmt, ":aaa");
ret = sqlite3_bind_blob(stmt, index, value, strlen(value), SQLITE_STATIC);
if( ret != SQLITE_OK )
return;
ret = sqlite3_step(stmt);
if( ret != SQLITE_DONE )
return;
sqlite3_close(pdb);
}
void querydb()
{
int ret;
sqlite3 *pdb = 0;
sqlite3_stmt *pstmt = 0;
char *error = 0;
char *sql = "select * from table1";
int len;
int i;
char *name;
void *value;
ret = sqlite3_open("db1.sdb", &pdb);
if( ret != SQLITE_OK )
return;
ret = sqlite3_prepare(pdb, sql, strlen(sql), &pstmt, &error);
if( ret != SQLITE_OK )
return;
while( 1 )
{
ret = sqlite3_step(pstmt);
if( ret != SQLITE_ROW )
break;
name = sqlite3_column_text(pstmt, 0);
value = sqlite3_column_blob(pstmt, 1);
len = sqlite3_column_bytes(pstmt,1 );
}
}
实例二:SQLite中如何用api操作blob类型的字段
在实际的编程开发当中我们经常要处理一些大容量二进制数据的存储,如图片或者音乐等等。对于这些二进制数据(blob字段)我们不能像处理普通的文本那样简单的插入或者查询,为此SQLite提供了一组函数来处理这种BLOB字段类型。下面的代码演示了如何使用这些API函数。 首先我们要建立一个数据库:sqlite3_exec(db, "CREATE TABLE list (fliename varchar(128) UNIQUE, fzip blob);", 0, 0, &zErrMsg); //由于mmmm.rar是一个二进制文件,所以要在使用insert语句时先用?号代替 sqlite3_prepare(db, "insert into list values ('mmmm.rar',?);", -1, &stat, 0); FILE *fp; long filesize = 0; char * ffile; fp = fopen("mmmm.rar", "rb"); if(fp != NULL) { //计算文件的大小 fseek(fp, 0, SEEK_END); filesize = ftell(fp); fseek(fp, 0, SEEK_SET); //读取文件 ffile = new char[filesize+1]; size_t sz = fread(ffile, sizeof(char), filesize+1, fp); fclose(fp); } //将文件数据绑定到insert语句中,替换“?”部分 sqlite3_bind_blob(stat, 1, ffile, filesize, NULL); //执行绑定之后的SQL语句 sqlite3_step(stat);这时数据库当中已经有了一条包含BLOB字段的数据。接下来我们要读取这条数据: //选取该条数据 sqlite3_prepare(db, "select * from list;", -1, &stat, 0); sqlite3_step(stat);//得到纪录中的BLOB字段const void * test = sqlite3_column_blob(stat, 1);//得到字段中数据的长度 int size = sqlite3_column_bytes(stat, 1); //拷贝该字段 sprintf(buffer2, "%s", test);此时可以将buffer2写入到文件当中,至此BLOB数据处理完毕。 实例三:sqlite 中用blob存储图片和取出图片 #include
sqlite3_open("./MetaInfo.db", &db); int result; if(result) { cout<<"Open the database sqlite.db failed"<
SQLite3是SQLite一个全新的版本,它虽然是在SQLite 2.8.13的代码基础之上开发的,但是使用了和之前的版本不兼容的数据库格式和API. SQLite3是为了满足以下的需求而开发的:
支持UTF-16编码.用户自定义的文本排序方法.可以对BLOBs字段建立索引.因此为了支持这些特性我改变了数据库的格式,建立了一个与之前版本不兼容的3.0版. 至于其他的兼容性的改变,例如全新的API等等,都将在理论介绍之后向你说明,这样可以使你最快的一次性摆脱兼容性问题.
3.0版的和2.X版的API非常相似,但是有一些重要的改变需要注意. 所有API接口函数和数据结构的前缀都由"sqlite_"改为了"sqlite3_". 这是为了避免同时使用SQLite 2.X和SQLite 3.0这两个版本的时候发生链接冲突.
由于对于C语言应该用什么数据类型来存放UTF-16编码的字符串并没有一致的规范. 因此SQLite使用了普通的void* 类型来指向UTF-16编码的字符串. 客户端使用过程中可以把void*映射成适合他们的系统的任何数据类型.
2.0 C/C++ 接口SQLite 3.0一共有83个API函数,此外还有一些数据结构和预定义(#defines). (完整的API介绍请参看另一份文档.) 不过你们可以放心,这些接口使用起来不会像它的数量所暗示的那么复杂. 最简单的程序仍然使用三个函数就可以完成: sqlite3_open(), sqlite3_exec(), 和 sqlite3_close(). 要是想更好的控制数据库引擎的执行,可以使用提供的sqlite3_prepare()函数把SQL语句编译成字节码,然后在使用sqlite3_step()函数来执行编译后的字节码. 以sqlite3_column_开头的一组API函数用来获取查询结果集中的信息. 许多接口函数都是成对出现的,同时有UTF-8和UTF-16两个版本. 并且提供了一组函数用来执行用户自定义的SQL函数和文本排序函数.
2.1 如何打开关闭数据库 typedef struct sqlite3 sqlite3; int sqlite3_open(const char*, sqlite3**); int sqlite3_open16(const void*, sqlite3**); int sqlite3_close(sqlite3*); const char *sqlite3_errmsg(sqlite3*); const void *sqlite3_errmsg16(sqlite3*); int sqlite3_errcode(sqlite3*);sqlite3_open() 函数返回一个整数错误代码,而不是像第二版中一样返回一个指向sqlite3结构体的指针. sqlite3_open() 和 sqlite3_open16() 的不同之处在于sqlite3_open16() 使用UTF-16编码(使用本地主机字节顺序)传递数据库文件名. 如果要创建新数据库, sqlite3_open16() 将内部文本转换为UTF-16编码, 反之sqlite3_open() 将文本转换为UTF-8编码.
打开或者创建数据库的命令会被缓存,直到这个数据库真正被调用的时候才会被执行. 而且允许使用PRAGMA声明来设置如本地文本编码或默认内存页面大小等选项和参数.
sqlite3_errcode() 通常用来获取最近调用的API接口返回的错误代码. sqlite3_errmsg() 则用来得到这些错误代码所对应的文字说明. 这些错误信息将以 UTF-8 的编码返回,并且在下一次调用任何SQLite API函数的时候被清除. sqlite3_errmsg16() 和 sqlite3_errmsg() 大体上相同,除了返回的错误信息将以 UTF-16 本机字节顺序编码.
SQLite3的错误代码相比SQLite2没有任何的改变,它们分别是:
#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_CORRUPT 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 */2.2 执行 SQL 语句
typedef int (*sqlite_callback)(void*,int,char**, char**); int sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void*, char**);
sqlite3_exec 函数依然像它在SQLite2中一样承担着很多的工作. 该函数的第二个参数中可以编译和执行零个或多个SQL语句. 查询的结果返回给回调函数. 更多地信息可以查看API 参考.
在SQLite3里,sqlite3_exec一般是被准备SQL语句接口封装起来使用的.
typedef struct sqlite3_stmt sqlite3_stmt; int sqlite3_prepare(sqlite3*, const char*, int, sqlite3_stmt**, const char**); int sqlite3_prepare16(sqlite3*, const void*, int, sqlite3_stmt**, const void**); int sqlite3_finalize(sqlite3_stmt*); int sqlite3_reset(sqlite3_stmt*);
sqlite3_prepare 接口把一条SQL语句编译成字节码留给后面的执行函数. 使用该接口访问数据库是当前比较好的的一种方法.
sqlite3_prepare() 处理的SQL语句应该是UTF-8编码的. 而sqlite3_prepare16() 则要求是UTF-16编码的. 输入的参数中只有第一个SQL语句会被编译. 第四个参数则用来指向输入参数中下一个需要编译的SQL语句存放的SQLite statement对象的指针, 任何时候如果调用 sqlite3_finalize() 将销毁一个准备好的SQL声明. 在数据库关闭之前,所有准备好的声明都必须被释放销毁. sqlite3_reset() 函数用来重置一个SQL声明的状态,使得它可以被再次执行.
SQL声明可以包含一些型如"?" 或 "?nnn" 或 ":aaa"的标记, 其中"nnn" 是一个整数,"aaa" 是一个字符串. 这些标记代表一些不确定的字符值(或者说是通配符),可以在后面用sqlite3_bind 接口来填充这些值. 每一个通配符都被分配了一个编号(由它在SQL声明中的位置决定,从1开始),此外也可以用 "nnn" 来表示 "?nnn" 这种情况. 允许相同的通配符在同一个SQL声明中出现多次, 在这种情况下所有相同的通配符都会被替换成相同的值. 没有被绑定的通配符将自动取NULL值.
int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void(*)(void*)); int sqlite3_bind_double(sqlite3_stmt*, int, double); int sqlite3_bind_int(sqlite3_stmt*, int, int); int sqlite3_bind_int64(sqlite3_stmt*, int, long long int); int sqlite3_bind_null(sqlite3_stmt*, int); int sqlite3_bind_text(sqlite3_stmt*, int, const char*, int n, void(*)(void*)); int sqlite3_bind_text16(sqlite3_stmt*, int, const void*, int n, void(*)(void*)); int sqlite3_bind_value(sqlite3_stmt*, int, const sqlite3_value*);
以上是 sqlite3_bind 所包含的全部接口,它们是用来给SQL声明中的通配符赋值的. 没有绑定的通配符则被认为是空值. 绑定上的值不会被sqlite3_reset()函数重置. 但是在调用了sqlite3_reset()之后所有的通配符都可以被重新赋值.
在SQL声明准备好之后(其中绑定的步骤是可选的), 需要调用以下的方法来执行:
int sqlite3_step(sqlite3_stmt*);
如果SQL返回了一个单行结果集,sqlite3_step() 函数将返回 SQLITE_ROW , 如果SQL语句执行成功或者正常将返回 SQLITE_DONE , 否则将返回错误代码. 如果不能打开数据库文件则会返回 SQLITE_BUSY . 如果函数的返回值是 SQLITE_ROW, 那么下边的这些方法可以用来获得记录集行中的数据:
const void *sqlite3_column_blob(sqlite3_stmt*, int iCol); int sqlite3_column_bytes(sqlite3_stmt*, int iCol); int sqlite3_column_bytes16(sqlite3_stmt*, int iCol); int sqlite3_column_count(sqlite3_stmt*); const char *sqlite3_column_decltype(sqlite3_stmt *, int iCol); const void *sqlite3_column_decltype16(sqlite3_stmt *, int iCol); double sqlite3_column_double(sqlite3_stmt*, int iCol); int sqlite3_column_int(sqlite3_stmt*, int iCol); long long int sqlite3_column_int64(sqlite3_stmt*, int iCol); const char *sqlite3_column_name(sqlite3_stmt*, int iCol); const void *sqlite3_column_name16(sqlite3_stmt*, int iCol); const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol); const void *sqlite3_column_text16(sqlite3_stmt*, int iCol); int sqlite3_column_type(sqlite3_stmt*, int iCol);
sqlite3_column_count()函数返回结果集中包含的列数. sqlite3_column_count() 可以在执行了 sqlite3_prepare()之后的任何时刻调用. sqlite3_data_count()除了必需要在sqlite3_step()之后调用之外,其他跟sqlite3_column_count() 大同小异. 如果调用sqlite3_step() 返回值是 SQLITE_DONE 或者一个错误代码, 则此时调用sqlite3_data_count() 将返回 0 ,然而 sqlite3_column_count() 仍然会返回结果集中包含的列数.
返回的记录集通过使用其它的几个 sqlite3_column_***() 函数来提取, 所有的这些函数都把列的编号作为第二个参数. 列编号从左到右以零起始. 请注意它和之前那些从1起始的参数的不同.
sqlite3_column_type()函数返回第N列的值的数据类型. 具体的返回值如下:
#define SQLITE_INTEGER 1 #define SQLITE_FLOAT 2 #define SQLITE_TEXT 3 #define SQLITE_BLOB 4 #define SQLITE_NULL 5
sqlite3_column_decltype() 则用来返回该列在 CREATE TABLE 语句中声明的类型. 它可以用在当返回类型是空字符串的时候. sqlite3_column_name() 返回第N列的字段名. sqlite3_column_bytes() 用来返回 UTF-8 编码的BLOBs列的字节数或者TEXT字符串的字节数. sqlite3_column_bytes16() 对于BLOBs列返回同样的结果,但是对于TEXT字符串则按 UTF-16 的编码来计算字节数. sqlite3_column_blob() 返回 BLOB 数据. sqlite3_column_text() 返回 UTF-8 编码的 TEXT 数据. sqlite3_column_text16() 返回 UTF-16 编码的 TEXT 数据. sqlite3_column_int() 以本地主机的整数格式返回一个整数值. sqlite3_column_int64() 返回一个64位的整数. 最后, sqlite3_column_double() 返回浮点数.
不一定非要按照sqlite3_column_type()接口返回的数据类型来获取数据. 数据类型不同时软件将自动转换.
Datatypes In SQLite Version 3
1. Storage Classes
Version 2 of SQLite stores all column values as ASCII text. Version 3 enhances this by providing the ability to store integer and real numbers in a more compact format and the capability to store BLOB data.
Each value stored in an SQLite database (or manipulated by the database engine) has one of the following storage classes:
NULL. The value is a NULL value.
INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.
TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16-LE).
BLOB. The value is a blob of data, stored exactly as it was input.
As in SQLite version 2, any column in a version 3 database except an INTEGER PRIMARY KEY may be used to store any type of value. The exception to this rule is described below under 'Strict Affinity Mode'.
All values supplied to SQLite, whether as literals embedded in SQL statements or values bound to pre-compiled SQL statements are assigned a storage class before the SQL statement is executed. Under circumstances described below, the database engine may convert values between numeric storage classes (INTEGER and REAL) and TEXT during query execution.
Storage classes are initially assigned as follows:
Values specified as literals as part of SQL statements are assigned storage class TEXT if they are enclosed by single or double quotes, INTEGER if the literal is specified as an unquoted number with no decimal point or exponent, REAL if the literal is an unquoted number with a decimal point or exponent and NULL if the value is a NULL. Literals with storage class BLOB are specified using the X'ABCD' notation.
Values supplied using the sqlite3_bind_* APIs are assigned the storage class that most closely matches the native type bound (i.e. sqlite3_bind_blob() binds a value with storage class BLOB).
The storage class of a value that is the result of an SQL scalar operator depends on the outermost operator of the expression. User-defined functions may return values with any storage class. It is not generally possible to determine the storage class of the result of an expression at compile time.
2. Column Affinity
In SQLite version 3, the type of a value is associated with the value itself, not with the column or variable in which the value is stored. (This is sometimes called manifest typing.) All other SQL databases engines that we are aware of use the more restrictive system of static typing where the type is associated with the container, not the value.
In order to maximize compatibility between SQLite and other database engines, SQLite support the concept of "type affinity" on columns. The type affinity of a column is the recommended type for data stored in that column. The key here is that the type is recommended, not required. Any column can still store any type of data, in theory. It is just that some columns, given the choice, will prefer to use one storage class over another. The preferred storage class for a column is called its "affinity".
Each column in an SQLite 3 database is assigned one of the following type affinities:
TEXTNUMERICINTEGERREALNONEA column with TEXT affinity stores all data using storage classes NULL, TEXT or BLOB. If numerical data is inserted into a column with TEXT affinity it is converted to text form before being stored.
A column with NUMERIC affinity may contain values using all five storage classes. When text data is inserted into a NUMERIC column, an attempt is made to convert it to an integer or real number before it is stored. If the conversion is successful, then the value is stored using the INTEGER or REAL storage class. If the conversion cannot be performed the value is stored using the TEXT storage class. No attempt is made to convert NULL or blob values.
A column that uses INTEGER affinity behaves in the same way as a column with NUMERIC affinity, except that if a real value with no floating point component (or text value that converts to such) is inserted it is converted to an integer and stored using the INTEGER storage class.
A column with REAL affinity behaves like a column with NUMERIC affinity except that it forces integer values into floating point representation. (As an optimization, integer values are stored on disk as integers in order to take up less space and are only converted to floating point as the value is read out of the table.)
A column with affinity NONE does not prefer one storage class over another. It makes no attempt to coerce data before it is inserted.
2.1 Determination Of Column Affinity
The type affinity of a column is determined by the declared type of the column, according to the following rules:
If the datatype contains the string "INT" then it is assigned INTEGER affinity.
If the datatype of the column contains any of the strings "CHAR", "CLOB", or "TEXT" then that column has TEXT affinity. Notice that the type VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity.
If the datatype for a column contains the string "BLOB" or if no datatype is specified then the column has affinity NONE.
If the datatype for a column contains any of the strings "REAL", "FLOA", or "DOUB" then the column has REAL affinity
Otherwise, the affinity is NUMERIC.
If a table is created using a "CREATE TABLE