/*
* filename : main.c
* path: ./
* version : 1.0
* note : main function
*/
#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#include <unistd.h>
#include "sqlite3.h"
#define FILEPATH_MAX (80)
int main()
{
/*db name start*/
char *pPath = NULL;
struct sqlite3 *pdb = NULL;
char *pErrMsg = NULL;
char dbName[] = "/mysqlite";
pPath = (char *) malloc (FILEPATH_MAX + 10);
if (NULL == pPath)
{
return -1;
}
memset(pPath, 0, FILEPATH_MAX + 10);
if (NULL == getcwd(pPath, FILEPATH_MAX))
{
return -1;
}
printf("%s %d\n",pPath,strlen(pPath));
memcpy(pPath + strlen(pPath), dbName, strlen(dbName));
printf("%s %d\n",pPath,strlen(pPath));
/*db name end*/
//open or create db
int iResult = sqlite3_open((const char *)pPath,&pdb);
if(SQLITE_OK != iResult || NULL == pdb)
{
printf("invoke sqlite3_open function error!\n");
free(pPath);
pPath = NULL;
return -1;
}
//create table
char sqlCreateTable[] = "create table test (id integer primary key AUTOINCREMENT,"
" name text not null, "
"inTime TimeStamp NOT NULL DEFAULT (datetime('now','localtime')),"
"inTimeSecond integer NOT NULL DEFAULT (strftime('%s', 'now', 'localtime')))";
iResult = sqlite3_exec(pdb, sqlCreateTable, 0, 0, &pErrMsg);
if(SQLITE_OK != iResult)
{
printf("%s\n", pErrMsg);
//sqlite3_free(pErrMsg);
}
sqlite3_free(pErrMsg);
//insert table
char sqlInsert[] = "insert into test (name) values ('aaa')";
iResult = sqlite3_exec(pdb, sqlInsert, 0, 0, &pErrMsg);
if(SQLITE_OK != iResult)
{
printf("%s\n", pErrMsg);
//sqlite3_free(pErrMsg);
}
sqlite3_free(pErrMsg);
#if 1
sleep(1);
char sqlInsert2[] = "insert into test (name) values ('bbb')";
iResult = sqlite3_exec(pdb, sqlInsert2, 0, 0, &pErrMsg);
if(SQLITE_OK != iResult)
{
printf("%s\n", pErrMsg);
//sqlite3_free(pErrMsg);
}
sqlite3_free(pErrMsg);
//select table
char sqlSelect1[] = "select inTimeSecond from test where name = 'aaa'";
char** ppTable1 = NULL;
int iRow1 = 0;
int iColumn1 = 0;
iResult = sqlite3_get_table(pdb, sqlSelect1, &ppTable1, &iRow1, &iColumn1,&pErrMsg);
if (SQLITE_OK != iResult)
{
printf("%s\n", pErrMsg);
}
sqlite3_free(pErrMsg);
printf("select sql row %d column %d\n", iRow1, iColumn1);
if (NULL != ppTable1)
{
int i = 0;
for (; i <= iRow1; i++)
{
int j = 0;
for (; j < iColumn1; j++)
{
if (NULL != ppTable1[i * iColumn1 + j])
{
printf("%s ",ppTable1[i * iColumn1 + j]);
}
}
printf("\n");
}
}
long num1 = atol(ppTable1[1]);
sqlite3_free_table(ppTable1);
char** ppTable2 = NULL;
char sqlSelect2[] = "select strftime('%s', 'now', 'localtime')";
iResult = sqlite3_get_table(pdb, sqlSelect2, &ppTable2, &iRow1, &iColumn1,&pErrMsg);
if (SQLITE_OK != iResult)
{
printf("%s\n", pErrMsg);
}
sqlite3_free(pErrMsg);
printf("-------------------------\n");
if (NULL != ppTable2)
{
int i = 0;
for (; i <= iRow1; i++)
{
int j = 0;
for (; j < iColumn1; j++)
{
if (NULL != ppTable2[i * iColumn1 + j])
{
printf("%s ",ppTable2[i * iColumn1 + j]);
}
}
printf("\n");
}
}
long num2 = atol(ppTable2[1]);
printf("-----------%ld - %ld = %ld --------\r\n", num2, num1, num2 - num1);
sqlite3_free_table(ppTable2);
#endif
//select table
char sqlSelect[] = "select * from test";
char** ppTable = NULL;
int iRow = 0;
int iColumn = 0;
iResult = sqlite3_get_table(pdb, sqlSelect, &ppTable, &iRow, &iColumn,&pErrMsg);
if (SQLITE_OK != iResult)
{
printf("%s\n", pErrMsg);
}
sqlite3_free(pErrMsg);
printf("select sql row %d column %d\n", iRow, iColumn);
if (NULL != ppTable)
{
int i = 1;
for (; i <= iRow; i++)
{
int j = 0;
for (; j < iColumn; j++)
{
if (NULL != ppTable[i * iColumn + j])
{
printf("%s ",ppTable[i * iColumn + j]);
}
}
printf("\n");
}
}
sqlite3_free_table(ppTable);
//close db
iResult = sqlite3_close(pdb);
if(SQLITE_OK != iResult)
{
return -1;
}
//free memory
free(pPath);
pPath = NULL;
pdb = NULL;
return 0;
}
/*
------------------------------------------------------------------------
yyyy-mm-dd name description
------------------------------------------------------------------------
2013-04-06 liuhuizhe init
------------------------------------------------------------------------
*/
Linux下sqlite3 的基础代码
最新推荐文章于 2023-03-19 18:10:43 发布