I needed some practice this morning to get my juju going...code could be
modularized a bit...I'll leave that exercise for the student...in particular,
the rc checking could be a function:
checkerr(rc,SQLITE_OK,"Insert error: ");
Or fancier yet with varargs....
According to your data you will create the database first using the sqlite3
shell.
So we will have:
test.db -- database
data -- table name
test.txt -- input data
sqlite3 test.db
sqlite> CREATE TABLE data(c1 integer,c2 varchar,c3 float);
Then compile the program below (e.g. on Unix). There's a fair bit of error
checking...lines without the correct number of columns are skipped for example.
The program assumes you have pipe delimited data and don't want the spaces
anywhere either (if your col2 has spaces it will result in the wrong column
count and skip them).
cc -o import import.c -lsqlite3 -ldl -lpthread
Run it:
./import test.txt test.db data
You should see:
3 columns detected
insert into data values (?,?,?)
After done confirm your data is as you expect:
sqlite3 test.db
sqlite> select count(*) from data where typeof(c1) != "integer";
0
sqlite> select count(*) from data where typeof(c2) != "text";
0
sqlite> select count(*) from data where typeof(c3) != "real";
0
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include "sqlite3.h"
char *sep="| \r\n";
// Add pipe delimited file to exisiting database/table
// Strings inside pipes can NOT have spaces in them
int main(int argc, char *argv[]) {
sqlite3 *db;
sqlite3_stmt *stmt;
int rc;
int ncol=0;
int nline=0;
char buf[8192];
char sql[8192];
FILE *fp;
if (argc != 4) {
fprintf(stderr,"Usage: %s filename databasename tablename\n",argv[0]);
exit(1);
}
rc = sqlite3_open_v2(argv[2],&db,SQLITE_OPEN_READWRITE,NULL);
if (rc) {
fprintf(stderr,"Error opening database '%s':
%s\n",argv[1],sqlite3_errmsg(db));
exit(1);
}
sprintf(sql,"insert into %s values (",argv[3]);
fp=fopen(argv[1],"r");
fgets(buf,sizeof(buf),fp);
// count the columns
char *p=strtok(buf,sep);
ncol=0;
while(p) {
++ncol;
strcat(sql,ncol==1?"":",");
strcat(sql,"?");
p=strtok(NULL,sep);
}
printf("%d columns detected\n",ncol);
strcat(sql,")");
puts(sql);
rewind(fp);
// prepare our statement
rc = sqlite3_prepare(db,sql,strlen(sql),&stmt,NULL);
if (rc) {
fprintf(stderr,"Prepare failed on '%s': %s\n",sql,sqlite3_errmsg(db));
exit(1);
}
// Loop over file file
while(fgets(buf,sizeof(buf),fp)) {
char *p=strtok(buf,sep);
int i=1;
++nline;
while(p) { // bind the columns as text, table will take care of conversion
to column types
rc=sqlite3_bind_text(stmt,i,p,-1,SQLITE_TRANSIENT);
if (rc) {
fprintf(stderr,"bind_text failed on '%s': %s\n",sql,sqlite3_errmsg(db));
exit(1);
}
++i;
p=strtok(NULL,sep);
}
if (--i != ncol) {
fprintf(stderr,"expected %d cols, got %d cols on line#%d\n",ncol,i,nline);
} else {
rc = sqlite3_step(stmt);
if (rc != SQLITE_DONE) {
fprintf(stderr,"Insert failed on '%s': %s\n",sql,sqlite3_errmsg(db));
exit(1);
}
rc = sqlite3_reset(stmt);
if (rc) {
fprintf(stderr,"Reset failed on '%s': %s\n",sql,sqlite3_errmsg(db));
exit(1);
}
}
}
rc=sqlite3_finalize(stmt);
if (rc) {
fprintf(stderr,"Finalize failed: %s\n",sqlite3_errmsg(db));
exit(1);
}
rc=sqlite3_close(db);
if (rc) {
fprintf(stderr,"Close failed on '%s': %s\n",argv[2],sqlite3_errmsg(db));
exit(1);
}
fclose(fp);
return 0;
}
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: [email protected] [[email protected]] on
behalf of Bageesh.M.Bose [[email protected]]
Sent: Friday, April 27, 2012 6:25 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Problem with insert
Thanks for your attention sir..
I tried ".import stock.txt myStockTable"
But in c it shows some errors...
Could you please tell me how to take individual fieds from a file having
the following format..
1 |ABC |2.365
123|ABCDE|123.22
22 |ass | 2588
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
