使用“C"将 sqlite3 表导出到文件中执行API [英] Exporting the sqlite3 table into file using "C" execute API
问题描述
我在 fedora 14 中使用 sqlite3 版本 3.6.23.1.我能够使用这样的命令提示符将表导出到文件中,
sqlite3 data.dbsqlite>.output sample.txt;sqlite>从样本中选择*;
我想在应用层处理这个案例.
我正在使用 sqlite3 开源C"API 在应用程序级别执行此命令.
execute("delete from sample:);//工作正常执行(.输出样本.txt");//不工作
它抛出的错误叫做sqlite3_exec中的SQL错误:."附近:语法错误"
请建议我如何使用此 API 创建文件以导入数据.
API 的功能定义.
int execute(const char* fmt, ...){字符 *err_messg;int ret = 0, 结果 = 0;char sql_string[1024] = "";//老实说,这需要更优雅;现在会做va_list 参数;va_start(args, fmt);ret = vsprintf(sql_string, fmt, args);va_end(args);printf("sql_string: %s\n", sql_string);如果 (!ret)结果 = 0;别的结果 = 1;如果(结果!= -1){if (sqlite3_exec(db_conn, sql_string, NULL, 0, &err_messg) == SQLITE_OK)//与数据库一起工作的实际API.{返回成功;}别的{printf("\n sqlite3_exec 中的 SQL 错误:%s\n", err_messg);返回 DBEXCE_FAIL;}}返回成功;}
您使用的 SQLite API 调用仅接受 SQL:
在作为第一个参数传入的数据库连接的上下文中,sqlite3_exec() 接口运行零个或多个 UTF-8 编码、以分号分隔的 SQL 语句传递到其第二个参数中."[来自 http://www.sqlite.org/c3ref/exec.html]
如果你浏览 SQLite 的源代码,你可以看到 .output 命令打开一个文件,然后使用 sqlite3_snprintf API 将查询结果的内容写入打开的文件句柄.
显示正在发生的事情的最新来源在这里:
http://www.sqlite.org/src/artifact/076e1c90d59462a2a06c90d59462a39a
.output 的相关部分是这样的:
if( c=='o' && strncmp(azArg[0], "output", n)==0 && nArg==2 ){if( p->outfile[0]=='|' ){pclose(p->out);}其他{output_file_close(p->out);}p->outfile[0] = 0;if( azArg[1][0]=='|' ){p->out = popen(&azArg[1][1], "w");if( p->out==0 ){fprintf(stderr,"错误: 无法打开管道 \"%s\"\n", &azArg[1][1]);p->out = 标准输出;rc = 1;}其他{sqlite3_snprintf(sizeof(p->outfile), p->outfile, "%s", azArg[1]);}}其他{p->out = output_file_open(azArg[1]);if( p->out==0 ){if( strcmp(azArg[1],"off")!=0 ){fprintf(stderr,"错误: 无法写入\"%s\"\n", azArg[1]);}p->out = 标准输出;rc = 1;}其他{sqlite3_snprintf(sizeof(p->outfile), p->outfile, "%s", azArg[1]);}}
I am using sqlite3 version 3.6.23.1 in fedora 14.I am able to export table into the file using command prompt like this,
sqlite3 data.db
sqlite> .output sample.txt;
sqlite> select *from sample;
I want handle this case at the application level.
I am using sqlite3 open source "C" API to execute this command in application level.
execute("delete from sample:);// working fine
execute(".output sample.txt"); //Not working
its throwing error called "SQL error in sqlite3_exec: near ".": syntax error"
Please suggest me how do i create a file to import the data using this API.
Function definition of the API.
int execute(const char* fmt, ...)
{
char *err_messg;
int ret = 0, result = 0;
char sql_string[1024] = ""; //this honestly needs to be more elegant; will do for now
va_list args;
va_start(args, fmt);
ret = vsprintf(sql_string, fmt, args);
va_end(args);
printf("sql_string: %s\n", sql_string);
if (!ret)
result = 0;
else
result = 1;
if (result != -1)
{
if (sqlite3_exec(db_conn, sql_string, NULL, 0, &err_messg) == SQLITE_OK) //Actual API which will work with database.
{
return SUCCESS;
}
else
{
printf("\n SQL error in sqlite3_exec: %s\n", err_messg);
return DBEXCE_FAIL;
}
}
return SUCCESS;
}
The SQLite API call you are using only accepts SQL:
"The sqlite3_exec() interface runs zero or more UTF-8 encoded, semicolon-separate SQL statements passed into its 2nd argument, in the context of the database connection passed in as its 1st argument." [from http://www.sqlite.org/c3ref/exec.html]
If you browse through the source of SQLite you can see that the .output command opens a file and then uses the sqlite3_snprintf API to write the contents of the query result to the opened file handle.
The most recent source that shows what is happening is here:
http://www.sqlite.org/src/artifact/076e1c90d594644f36027c8ecff9a392cf2d3a06
The relevant part for .output is this:
if( c=='o' && strncmp(azArg[0], "output", n)==0 && nArg==2 ){
if( p->outfile[0]=='|' ){
pclose(p->out);
}else{
output_file_close(p->out);
}
p->outfile[0] = 0;
if( azArg[1][0]=='|' ){
p->out = popen(&azArg[1][1], "w");
if( p->out==0 ){
fprintf(stderr,"Error: cannot open pipe \"%s\"\n", &azArg[1][1]);
p->out = stdout;
rc = 1;
}else{
sqlite3_snprintf(sizeof(p->outfile), p->outfile, "%s", azArg[1]);
}
}else{
p->out = output_file_open(azArg[1]);
if( p->out==0 ){
if( strcmp(azArg[1],"off")!=0 ){
fprintf(stderr,"Error: cannot write to \"%s\"\n", azArg[1]);
}
p->out = stdout;
rc = 1;
} else {
sqlite3_snprintf(sizeof(p->outfile), p->outfile, "%s", azArg[1]);
}
}
这篇关于使用“C"将 sqlite3 表导出到文件中执行API的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!