sqlite3——数据库——day2

今天学习了sqlite3数据库

sqlite3_open

sqlite3_open
	int sqlite3_open(
	    const char *filename,   /* Database filename (UTF-8) */
	    sqlite3 **ppDb          /* OUT: SQLite db handle */
	);
功能:
    打开数据库文件(创建一个数据库连接)
参数:
    filename:数据库文件路径 
    ppDb:操作数据库指针存放空间的首地址
返回值:
    成功返回SQLITE_OK
    失败返回错误码
eg：
	ret = sqlite3_open("worddict.db",&pdb);                                                                                                        
     if(SQLITE_OK != ret)
     {
         fprintf(stderr,"fail to sqlite3_open:%s\n",sqlite3_errmsg(pdb));
         return -1;
     }

sqlite3_close

sqlite3_close 
	int sqlite3_close(sqlite3*);
功能:
    关闭数据库连接

sqlite3_exec

int sqlite3_exec(
        sqlite3*,                                  /* An open database */
        const char *sql,                           /* SQL to be evaluated */
        int (*callback)(void*,int,char**,char**),  /* Callback function */
        void *,                                    /* 1st argument to callback */
        char **errmsg                              /* Error msg written here */
    );
功能:
    执行一条SQL语句
参数:
    sqlite3*: 数据库句柄
    sql: 要执行的SQL语句字符串首地址
    callback: 只有在select时才会使用,其余调用时传递NULL
    void*: 给回调函数的传参
    errmsg:出错信息存放空间首地址(使用完毕后使用sqlite3_free释放空间)
返回值:
    成功返回SQLITE_OK
    失败返回错误码 

eg：实现将dict.txt文件中的所有单词和含义插入到数据库中，插入之后，输入单词，在数据库中将其意思输出到终端上。

#include"head.h"
#include <sqlite3.h>

typedef struct callbackarg
{
	char mean[4096];
	int flag;
}arg_t;

typedef struct worddict
{
	char word[256];
	char wordmean[1024];
}word_t;

int callback(void *arg,int col,char **pcontent,char **ptitle)
{
	arg_t *pmeanmsg = arg;
	pmeanmsg->flag = 1;	
	strcpy(pmeanmsg->mean,pcontent[0]);

	return 0;
}

int FindWordMean(char *pword,arg_t *pmeanmsg)
{
	sqlite3 *pdb = NULL;
	int ret = 0;
	char *perrmsg = NULL;
	char cmdbuf[4096] = {0};


	ret = sqlite3_open("worddict.db",&pdb);
	if(ret != SQLITE_OK)
	{
		fprintf(stderr, "sqlite3_open failed:%s\n", sqlite3_errmsg(pdb));
		return -1;
	}

	sprintf(cmdbuf, "select wordmean from worddict where wordname = \"%s\";",pword);
	ret = sqlite3_exec(pdb, cmdbuf, callback, pmeanmsg, &perrmsg);
	if (ret != SQLITE_OK)
	{
		fprintf(stderr, "sqlite3_exec failed:%s\n", perrmsg);
		sqlite3_free(perrmsg);
		sqlite3_close(pdb);
		return -1;
	}

	sqlite3_close(pdb);

	return 0;
}

int LoadDictToDB(void)
{
	sqlite3 *pdb = NULL;
	FILE *fp = NULL;
	char cmdbuf[4096] = {0};
	char tmpbuff[4096] = {0};
	int ret = 0;
	char *pret = NULL;
	char *perrmsg = NULL;
	char *ptmpword = NULL;
	char *ptmpmean = NULL;

	fp = fopen("dict.txt","r");
	if(NULL == fp)
	{
		perror("fail to fopen");
		return -1;
	}
	
	ret = sqlite3_open("worddict.db",&pdb);
	if(ret != SQLITE_OK)
	{
		fprintf(stderr,"sqlite3_open failed:%s\n",sqlite3_errmsg(pdb));
		return -1;
	}

	sprintf(cmdbuf,"create table if not exists worddict(id integer primary key asc,wordname varchar(255),wordmean varchar(255));");
	ret = sqlite3_exec(pdb,cmdbuf,NULL,NULL,&perrmsg);
	if(ret != SQLITE_OK)
	{
		fprintf(stderr,"sqlite3_exec failed:%s\n",perrmsg);
		sqlite3_free(perrmsg);
		sqlite3_close(pdb);
		return -1;
	}

	while(1)
	{
		pret = fgets(tmpbuff,sizeof(tmpbuff),fp);
		if(NULL == pret)
		{
			break;
		}

		ptmpword = strtok(tmpbuff," ");
		ptmpmean = strtok(tmpbuff,"\r");

		sprintf(cmdbuf,"insert into worddict values(NULL,\"%s\",\"%s\");",ptmpword,ptmpmean);

		ret = sqlite3_exec(pdb,cmdbuf,NULL,NULL,&perrmsg);
		if(ret != SQLITE_OK)
		{
			fprintf(stderr,"sqlite3_exec failed:%s\n",perrmsg);
			sqlite3_free(perrmsg);
			sqlite3_close(pdb);
			return -1;
		}
	}


	sqlite3_close(pdb);
	fclose(fp);

	return 0;
}

int main(void)
{
	char word[256] = {0};

	arg_t meanmsg;

	if(access("worddict.db",F_OK))
	{
		LoadDictToDB();
	}
	
	printf("Please enter the word:");
	gets(word);

	FindWordMean(word,&meanmsg);

	if(meanmsg.flag)
	{
		printf("mean = %s\n",meanmsg.mean);
	}
	else
	{
		printf("not found!\n");
	}
	return 0;
}

结果：