1、基本的数据库操作
(1)加libsqlite3.0.dylib库, #import "/usr/include/sqlite3.h"
(2)打开数据库
int sqlite3_open(
const char *filename, /* Database filename (UTF-8) */
sqlite3 **ppDb /* OUT: SQLite db handle */
);
(3 )对表的操作
int sqlite3_exec(
sqlite3*, /* An open database */
const char *sql, /* SQL to be evaluated */
int (*callback)(void*,int,char**,char**), /*Callbk func*/
void *, /* 1st argument to callback*/
char **errmsg /* Error msg written here */
);
SQLite有五种数据存储类型:
(a)INTEGER.整型值
(b)REAL.浮点类型(An 8-byte IEEE floating-point storage representing a floating-point number.)
(c)TEXT.字符串 (A storage area for text. The text can be in any of the following encodings: UTF-8, UTF-16BE, or UTF-16-LE.)
(d)BLOB.大数据 (Used to store data exactly as entered — for example, an image. )
(e)NULL.空值 (Used to store the value NULL.)
(4)示例
#import "/usr/include/sqlite3.h"
int main(int argc, char *argv[])
{
char *sqlStatement;
sqlite3 *pDb;
char *errorMsg;
int returnCode;
char *databaseName;
databaseName = "financial.db";
returnCode = sqlite3_open(databaseName, &pDb);
if (returnCode!=SQLITE_OK)
{
fprintf(stderr, "Error in opening the database. Error: %s", sqlite3_errmsg(pDb));
sqlite3_close(pDb);
return -1;
}
sqlStatement = "DROP TABLE IF EXISTS stocks";
returnCode = sqlite3_exec(pDb, sqlStatement, NULL, NULL, &errorMsg);
if (returnCode != SQLITE_OK)
{
fprintf(stderr, "Error in dropping table stocks. Error: %s", errorMsg);
sqlite3_free(errorMsg);
}
sqlStatement = "CREATE TABLE stocks (symbol VARCHAR(5), "
"purchasePrice FLOAT(10,4), "
"unitsPurchased INTEGER, "
"purchase_date VARCHAR(10))";
returnCode = sqlite3_exec(pDb, sqlStatement, NULL, NULL, &errorMsg);
if (returnCode != SQLITE_OK)
{
fprintf(stderr, "Error in creating the stocks table. Error: %s", errorMsg);
sqlite3_free(errorMsg);
}
insertStockPurchase(pDb, "ALU", 14.23, 100, "03-17-2012");
insertStockPurchase(pDb, "GOOG", 600.77, 20, "01-09-2012");
insertStockPurchase(pDb, "NT", 20.23,140, "02-05-2012");
insertStockPurchase(pDb, "MSFT", 30.23, 5, "01-03-2012");
sqlite3_close(pDb);
return 0;
}
void insertStockPurchase(sqlite3 *pDb, const char*symbol, float price, int units, const char* theDate)
{
char *errorMsg;
int returnCode;
char *st;
st = sqlite3_mprintf("INSERT INTO stocks VALUES"
" (’%q’, %f, %d, ’%q’)", symbol, price, units, theDate);
returnCode = sqlite3_exec(pDb, st, NULL, NULL, &errorMsg);
if (returnCode != SQLITE_OK)
{
fprintf(stderr, "Error in inserting into the stocks table. Error: %s", errorMsg);
sqlite3_free(errorMsg);
}
sqlite3_free(st);
}
2、Processing Row Results
利用回调函数来处理每行的数据
3、预申明(Prepared Statements)
对于不返回数据的sql操作(insert,drop,create),用sqlite3_exec();对于返回数据的操作(select),
(1)三个阶段:
准备(Preparation),执行(Execution),结束(Finalization)
准备 - sqlite3_prepare_v2(sqlite3 *db,const char *zSql ,int nBytes,sqlite3_stmt **ppStmt,const char **pzTail)
执行 - sqlite3_step(sqlite3_stmt*);
结束 - sqlite3_finalize(sqlite3_stmt *pStmt);
示例:
#import "/usr/include/sqlite3.h"
int main(int argc, char *argv[])
{
char *sqlStatement;
sqlite3 *database;
int returnCode;
char *databaseName;
sqlite3_stmt *statement;
databaseName = "financial.db";
returnCode = sqlite3_open(databaseName, &database);
if (returnCode!=SQLITE_OK)
{
fprintf(stderr, "Error in opening the database. Error: %s", sqlite3_errmsg(database));
sqlite3_close(database);
return -1;
}
sqlStatement = sqlite3_mprintf("SELECT S.symbol, S.unitsPurchased, " "S.purchasePrice FROM stocks AS S WHERE " "S.purchasePrice >= %f", 30.0);
returnCode = sqlite3_prepare_v2(database, sqlStatement, strlen(sqlStatement), &statement, NULL);
if (returnCode != SQLITE_OK)
{
fprintf(stderr, "Error in preparation of query. Error: %s", sqlite3_errmsg(database));
sqlite3_close(database);
return -1;
}
returnCode = sqlite3_step(statement);
while (returnCode == SQLITE_ROW)
{
char *symbol;
int units;
double price;
symbol = sqlite3_column_text(statement, 0);
units = sqlite3_column_int(statement, 1);
price = sqlite3_column_double(statement, 2);
printf("We bought %d from %s at a price equal to %.4f\n", units, symbol, price);
returnCode = sqlite3_step(statement);
}
sqlite3_finalize(statement);
sqlite3_free(sqlStatement);
return 0;
}
4 、用户自定义函数 (User-Defined Functions)
sqlite3_create_function()
5、存储二进制大对象(Storing BLOBs)
6、检索二进制大对象(Retrieving BLOBs)