SQLite 常用语句示例

示例一
sqlite3 *db;
char *zErrMsg = 0;
int rc, colNumber = 0, j,i, row_count = 0;
printf("sqlite3 info:n libversion:%sn souceid:%sn vesion_num:%dn",sqlite3_libversion(), sqlite3_sourceid(),
sqlite3_libversion_number());
sqlite3_stmt *pStmt = NULL;
// 打开数据库文件
rc = sqlite3_open("argv[1]", &db);
if( rc ){
fprintf(stderr, "Can't open database: %sn", sqlite3_errmsg(db));
sqlite3_close(db);
return(1);
}
// 把一条SQL语句编译成字节码留给后面的执行函数
rc = sqlite3_prepare(db, "SQLString", strlen("SQLString"), &pStmt, NULL);
printf("rc =%dn", rc);
if(rc != SQLITE_OK){
fprintf(stderr, "SQL error1: %sn", zErrMsg);
sqlite3_free(zErrMsg);
return 1;
}
printf("pStmt=%pn",pStmt);
// 获取数据库有多少列
ret = sqlite3_column_count(pStmt);
printf("BEFORE STEP col count= %d, rc = %dn", ret, rc);
printf("pStmt22=%pn",pStmt);
// 遍历数据库每列的名字、类型
/*
sqlite3_column_type()函数返回第N列的值的数据类型. 具体的返回值如下:
#define SQLITE_INTEGER
1
#define SQLITE_FLOAT
2
#define SQLITE_TEXT
3
#define SQLITE_BLOB
4
#define SQLITE_NULL
5
*/
i = 0;
while(i < ret){
printf("%20s t %d t %20s t %dn",
sqlite3_column_name(pStmt, i), //第N列的字段名
sqlite3_column_type(pStmt, i),
sqlite3_column_decltype(pStmt, i),
//该列在 CREATE TABLE 语句中声明的类型. 它可以用在当返回类型是空字符串的时候
sqlite3_column_bytes(pStmt, i));
//返回 UTF-8 编码的BLOBs列的字节数或者TEXT字符串的字节数
i++;
}
/*
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);
*/
rc = sqlite3_step(pStmt);
printf("pStmt3=%pn",pStmt);
if(rc != SQLITE_ROW){
fprintf(stderr, "SQL error2: %s,rc =%dn", zErrMsg, rc);
sqlite3_free(zErrMsg);
return 1;
}
while(rc == SQLITE_ROW){
// 遍历该行的每一列
i = 0;row_count++;
for(j=0;j< ret;j++){
//
printf("%s ", sqlite3_column_text(pStmt,j));
// }
//printf("%d -> %d w:%dn", sqlite3_column_int(pStmt,1), sqlite3_column_int(pStmt,2), sqlite3_column_int(pStmt,4));
insertArc(GPt,sqlite3_column_int(pStmt,1), sqlite3_column_int(pStmt,2), sqlite3_column_int(pStmt,4));
#if 0
printf("%d, %s, %s, %sn",
sqlite3_column_int(pStmt, 0),
sqlite3_column_text(pStmt, 1),
sqlite3_column_text(pStmt, 2),
sqlite3_column_text(pStmt, 3));
#endif
// 遍历每一行
rc = sqlite3_step(pStmt);
}
printf("row_count=%dn ", row_count);
//
将销毁一个准备好的SQL声明. 在数据库关闭之前，所有准备好的声明都必须被释放销毁.
sqlite3_finalize(pStmt);
sqlite3_close(db);
示例二
sqlite3* db = NULL;
if (sqlite3_initialize() != SQLITE_OK) {
fprintf(stderr, "error: failed to initialize sqliten");
exit(EXIT_FAILURE);
}
int flags = SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE;
if (sqlite3_open_v2(file, &db, flags, NULL) != SQLITE_OK) {
fprintf(stderr, "error: failed to open %sn", file);
exit(EXIT_FAILURE);
}
sqlite3_stmt *stmt = NULL;
result = sqlite3_prepare_v2(db, input->buffer, length, &stmt, NULL);
if (result != SQLITE_OK) {
send_error(sqlite3_errcode(db), sqlite3_errmsg(db));
continue;
}
/* Print out rows. */
int ncolumns = sqlite3_column_count(stmt);
while (sqlite3_step(stmt) == SQLITE_ROW)
{
int i;
for (i = 0; i < ncolumns; i++) {
// 获取每一列的类型并赋值
int type = sqlite3_column_type(stmt, i);
switch (type) {
case SQLITE_INTEGER:
printf("%lld", sqlite3_column_int64(stmt, i));
break;
case SQLITE_FLOAT:
printf("%f", sqlite3_column_double(stmt, i));
break;
case SQLITE_NULL:
printf("nil");
break;
case SQLITE_TEXT:
fwrite(sqlite3_column_text(stmt, i), 1,
sqlite3_column_bytes(stmt, i), stdout);
break;
case SQLITE_BLOB:
printf("nil");
break;
}
}
}
if (sqlite3_finalize(stmt) != SQLITE_OK) {
send_error(sqlite3_errcode(db), sqlite3_errmsg(db));
} else {
printf("successn");
}
sqlite3_close(db);
sqlite3_shutdown();

最后

以上就是清脆小甜瓜为你收集整理的SQLite 常用语句示例的全部内容，希望文章能够帮你解决SQLite 常用语句示例所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。