sqlite3编程实例
来源：互联网 发布：淘宝美工工资待遇知乎 编辑：程序博客网 时间：2024/06/07 02:25
#include "sqlite3.h"#include <stdio.h>#include <string.h>#include <errno.h>#define DEMO_DB "/tmp/demo.db"#define DEMO_TABLE "emp"int sqlite3_db_select(){ sqlite3 *db = NULL; sqlite3_stmt *stmt = NULL; char sql[1024]; int ret, ncols, i, rows = 0; ret = sqlite3_open(DEMO_DB, &db); if (ret != SQLITE_OK) { fprintf(stderr, "db open fail, errcode[%d], errmsg[%s]\n", ret, sqlite3_errmsg(db)); sqlite3_close(db); return -1; } memset(sql, 0, sizeof(sql)); sprintf(sql, "select empno, ename, job, mgr, hiredate, sal, comm, deptno from %s where 1 = 1", DEMO_TABLE); printf("sql select: \"%s\"\n", sql); ret = sqlite3_prepare(db, sql, strlen(sql), &stmt, NULL); if (ret != SQLITE_OK) { fprintf(stderr, "db prepare fail, errcode[%d], errmsg[%s]\n", ret, sqlite3_errmsg(db)); sqlite3_close(db); return -1; } ret = sqlite3_step(stmt); if (ret != SQLITE_ROW && ret != SQLITE_DONE) { fprintf(stderr, "db step fail, errcode[%d], errmsg[%s]\n", ret, sqlite3_errmsg(db)); sqlite3_close(db); return -1; } ncols = sqlite3_column_count(stmt); for(i=0; i < ncols; i++) { printf("column name=%s, storage class=%i, declared=%s\n", sqlite3_column_name(stmt, i), sqlite3_column_type(stmt, i), sqlite3_column_decltype(stmt, i)); } printf("\n"); for ( ; ; ) { if (ret == SQLITE_ROW) { int empno, mgr, sal, comm, deptno; char *ename, *job, *hiredate; /* 索引从0开始 */ empno = sqlite3_column_int(stmt, 0); ename = (char *)sqlite3_column_text(stmt, 1); job = (char *)sqlite3_column_text(stmt, 2); mgr = sqlite3_column_int(stmt, 3); hiredate = (char *)sqlite3_column_text(stmt, 4); sal = sqlite3_column_int(stmt, 5); comm = sqlite3_column_int(stmt, 6); deptno = sqlite3_column_int(stmt, 7); printf("empno=%d, ename=%s, job=%s, mgr=%d, hiredate=%s, sal=%d, comm=%d, deptno=%d\n", empno, ename, job, mgr, hiredate, sal, comm, deptno); rows++; } else if (ret == SQLITE_DONE) { printf("select done!\n"); break; } else { fprintf(stderr, "db step fail, errcode[%d], errmsg[%s]\n", ret, sqlite3_errmsg(db)); break; } ret = sqlite3_step(stmt); } printf("rows=%d\n", rows); sqlite3_finalize(stmt); sqlite3_close(db); return 0;}int sqlite3_db_get_rows(){ sqlite3 *db = NULL; char sql[1024]; sqlite3_stmt *stmt = NULL; int ret, rows = 0; ret = sqlite3_open(DEMO_DB, &db); if (ret != SQLITE_OK) { fprintf(stderr, "db open fail, errcode[%d]\n", ret); return -1; } memset(sql, 0, sizeof(sql)); sprintf(sql, "select count(1) from %s", DEMO_TABLE); ret = sqlite3_prepare(db, sql, strlen(sql), &stmt, NULL); if (ret != SQLITE_OK) { fprintf(stderr, "db prepare fail, errcode[%d], errmsg[%s]\n", ret, sqlite3_errmsg(db)); sqlite3_close(db); return -1; } ret = sqlite3_step(stmt); if (ret == SQLITE_ROW) { rows = sqlite3_column_int(stmt, 0); } else if (ret == SQLITE_DONE) { printf("no data!\n"); rows = 0; } else { fprintf(stderr, "db step fail, errcode[%d], errmsg[%s]\n", ret, sqlite3_errmsg(db)); rows = -1; } sqlite3_finalize(stmt); sqlite3_close(db); printf("rows=%d\n", rows); return rows;}int sqlite3_db_begin_transaction(sqlite3 *db){ char sql[1024]; char *errmsg = NULL; int ret = 0; memset(sql, 0, sizeof(sql)); sprintf(sql, "BEGIN TRANSACTION"); ret = sqlite3_exec(db, sql, NULL, NULL, &errmsg); if (ret != SQLITE_OK) { fprintf(stderr, "begin transaction fail, errcode[%d], errmsg[%s]\n", ret, errmsg); sqlite3_free(errmsg); errmsg = NULL; return -1; } return 0;}int sqlite3_db_commit_transaction(sqlite3 *db){ char sql[1024]; char *errmsg = NULL; int ret = 0; memset(sql, 0, sizeof(sql)); sprintf(sql, "COMMIT TRANSACTION"); ret = sqlite3_exec(db, sql, NULL, NULL, &errmsg); if (ret != SQLITE_OK) { fprintf(stderr, "commit transaction fail, errcode[%d], errmsg[%s]\n", ret, errmsg); sqlite3_free(errmsg); errmsg = NULL; return -1; } return 0;}int sqlite3_db_rollback_transaction(sqlite3 *db){ char sql[1024]; char *errmsg = NULL; int ret = 0; memset(sql, 0, sizeof(sql)); sprintf(sql, "ROLLBACK TRANSACTION"); ret = sqlite3_exec(db, sql, NULL, NULL, &errmsg); if (ret != SQLITE_OK) { fprintf(stderr, "rollback transaction fail, errcode[%d], errmsg[%s]\n", ret, errmsg); sqlite3_free(errmsg); errmsg = NULL; return -1; } sqlite3_close(db); return 0;}int sqlite3_db_delete(){ sqlite3 *db = NULL; char sql[1024]; char *errmsg = NULL; int ret = 0; ret = sqlite3_open(DEMO_DB, &db); if (ret != SQLITE_OK) { fprintf(stderr, "db open fail, errcode[%d], errmsg[%s]\n", ret, sqlite3_errmsg(db)); sqlite3_close(db); return -1; } memset(sql, 0, sizeof(sql)); sprintf(sql, "delete from %s where empno = 7566", DEMO_TABLE); printf("sql delete: \"%s\"\n", sql); ret = sqlite3_exec(db, sql, NULL, NULL, &errmsg); if (ret != SQLITE_OK) { fprintf(stderr, "delete fail, errcode[%d], errmsg[%s]\n", ret, errmsg); sqlite3_free(errmsg); errmsg = NULL; sqlite3_close(db); return -1; } sqlite3_close(db); return 0;}int sqlite3_db_bind_insert(){ sqlite3 *db = NULL; char sql[1024]; sqlite3_stmt *stmt = NULL; int ret, i, rv = 0; ret = sqlite3_open(DEMO_DB, &db); if (ret != SQLITE_OK) { fprintf(stderr, "db open fail, errcode[%d], errmsg[%s]\n", ret, sqlite3_errmsg(db)); sqlite3_close(db); return -1; } /* 开启事务 */ ret = sqlite3_db_begin_transaction(db); if (ret < 0) { fprintf(stderr, "begin transaction fail, errcode[%d], errmsg[%s]\n", ret, sqlite3_errmsg(db)); sqlite3_close(db); return -1; } memset(sql, 0, sizeof(sql)); sprintf(sql, "insert into %s (empno, ename, job, mgr, hiredate, sal, comm, deptno) values(?, ?, ?, ?, ?, ?, ?, ?)", DEMO_TABLE); printf("sql bind insert: \"%s\"\n", sql); ret = sqlite3_prepare(db, sql, strlen(sql), &stmt, NULL); if (ret != SQLITE_OK) { fprintf(stderr, "db prepare fail, errcode[%d], errmsg[%s]\n", ret, sqlite3_errmsg(db)); sqlite3_close(db); return -1; } for (i=0; i<100; i++) { /* 索引从1开始 */ sqlite3_bind_int(stmt, 1, 7521); sqlite3_bind_text(stmt, 2, "WARD", strlen("WARD"), SQLITE_TRANSIENT); sqlite3_bind_text(stmt, 3, "SALESMAN", strlen("SALESMAN"), SQLITE_TRANSIENT); sqlite3_bind_int(stmt, 4, 7698); sqlite3_bind_text(stmt, 5, "22-02-1981", strlen("22-02-1981"), SQLITE_TRANSIENT); sqlite3_bind_int(stmt, 6, 1250); sqlite3_bind_null(stmt, 7); sqlite3_bind_int(stmt, 8, 30); ret = sqlite3_step(stmt); if (ret != SQLITE_DONE) { fprintf(stderr, "db step fail, errcode[%d], errmsg[%s]\n", ret, sqlite3_errmsg(db)); break; } ret = sqlite3_reset(stmt); if (ret != SQLITE_OK) { fprintf(stderr, "db reset fail, errcode[%d], errmsg[%s]\n", ret, sqlite3_errmsg(db)); break; } } /* 提交事务 */ ret = sqlite3_db_commit_transaction(db); if (ret < 0) { fprintf(stderr, "commit transaction fail, errcode[%d], errmsg[%s]\n", ret, sqlite3_errmsg(db)); sqlite3_finalize(stmt); sqlite3_close(db); return -1; } ret = sqlite3_finalize(stmt); if (ret != SQLITE_OK) { fprintf(stderr, "db finalize fail, errcode[%d], errmsg[%s]\n", ret, sqlite3_errmsg(db)); sqlite3_close(db); return -1; } sqlite3_close(db); return 0;}int sqlite3_db_insert(){ sqlite3 *db = NULL; char sql[1024]; char *errmsg = NULL; int ret = 0; ret = sqlite3_open(DEMO_DB, &db); if (ret != SQLITE_OK) { fprintf(stderr, "db open fail, errcode[%d], errmsg[%s]\n", ret, sqlite3_errmsg(db)); sqlite3_close(db); return -1; } memset(sql, 0, sizeof(sql)); sprintf(sql, "insert into %s (empno, ename, job, mgr, hiredate, sal, comm, deptno) values(%d, '%s', '%s', %d, '%s', %d, %d, %d)", DEMO_TABLE, 7566, "JONES", "MANAGER", 7839, "02-04-1981", 2975, 500 , 20); printf("sql insert: \"%s\"\n", sql); ret = sqlite3_exec(db, sql, NULL, NULL, &errmsg); if (ret != SQLITE_OK) { fprintf(stderr, "db insert fail, errcode[%d], errmsg[%s]\n", ret, sqlite3_errmsg(db)); sqlite3_free(errmsg); errmsg = NULL; sqlite3_close(db); return -1; } sqlite3_close(db); return 0;}int sqlite3_db_table_exist(char *dbfile, const char *tablename){ sqlite3 *db = NULL; sqlite3_stmt *stmt = NULL; char sql[1024]; int ret = 0, rows = 0; ret = sqlite3_open(dbfile, &db); if (ret != SQLITE_OK) { fprintf(stderr, "db open fail, errcode[%d], errmsg[%s]\n", ret, sqlite3_errmsg(db)); sqlite3_close(db); return 0; } memset(sql, 0, sizeof(sql)); sprintf(sql, "select count(1) from sqlite_master where type='table' and name='%s'", tablename); ret = sqlite3_prepare(db, sql, strlen(sql), &stmt, NULL); if (ret != SQLITE_OK) { fprintf(stderr, "db prepare fail, errcode[%d], errmsg[%s]\n", ret, sqlite3_errmsg(db)); sqlite3_close(db); return -1; } ret = sqlite3_step(stmt); if (ret == SQLITE_ROW) { rows = sqlite3_column_int(stmt, 0); } else if (ret == SQLITE_DONE) { printf("no data!\n"); rows = 0; } else { fprintf(stderr, "db step fail, errcode[%d], errmsg[%s]\n", ret, sqlite3_errmsg(db)); rows = 0; } sqlite3_finalize(stmt); sqlite3_close(db); if (rows > 0) { return 1; } else { return 0; }}int sqlite3_db_create(){ sqlite3 *db = NULL; char sql[1024]; char *errmsg = NULL; int ret = 0; if (sqlite3_db_table_exist(DEMO_DB, DEMO_TABLE)) { printf("table exist\n"); return 0; } ret = sqlite3_open(DEMO_DB, &db); if (ret != SQLITE_OK) { fprintf(stderr, "db open fail, errcode[%d], errmsg[%s]\n", ret, sqlite3_errmsg(db)); sqlite3_close(db); return -1; } memset(sql, 0, sizeof(sql)); sprintf(sql, "create table %s ("\ "empno int(4) not null, "\ "ename varchar(10), "\ "job varchar(9), "\ "mgr int(4), "\ "hiredate date, "\ "sal int(7), "\ "comm int(7), "\ "deptno int(2))", DEMO_TABLE); printf("sql create: \"%s\"\n", sql); ret = sqlite3_exec(db, sql, NULL, NULL, &errmsg); if (ret != SQLITE_OK) { fprintf(stderr, "create table fail, errcode[%d], errmsg[%s]\n", ret, sqlite3_errmsg(db)); sqlite3_free(errmsg); errmsg = NULL; sqlite3_close(db); return -1; } sqlite3_close(db); return 0;}int main(){ sqlite3_db_create(); sqlite3_db_insert(); sqlite3_db_bind_insert(); sqlite3_db_get_rows(); sqlite3_db_select(); sqlite3_db_delete(); return 0;} 0 0
- sqlite3编程实例
- sqlite3 C简单编程实例
- sqlite3实例
- sqlite3编程
- iPhone编程实例—数据操作之sqlite3
- sqlite3插入时间实例
- android sqlite3 简单实例
- sqlite3简单实例
- Qt5 操作 Sqlite3实例
- sqlite3事务实例
- sqlite3编程接口
- SQLite3的C编程
- SQLite3基本编程手册
- sqlite3编程 & 示例
- Android Sqlite3 编程详解
- sqlite3编程笔记
- SQLite3基本编程手册
- SQLite3 -- C 编程
- C语言实例*2
- Error:Execution failed for task ':app:transformNativeLibsWithStripDebugSymbolForDebug'. > java.lang.
- 【Java基础】线程和并发机制
- 总结java中的反射机制
- vue事件简写方式
- sqlite3编程实例
- 4.26
- ADO.NET入门教程（一） 初识ADO.NET
- 编程题一道
- PAT-A-1044. Shopping in Mars (25)
- centos6.5 部署redmine-3.3.3+nginx
- UEFI 下 Windows 10 安装 Ubuntu 双系统（亲测有效）
- Java集合之TreeSet
- windows7系统中VMware下实现only-host上网
