Re: [sqlite] Problem with insert

[Black, Michael (IS)]

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: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Bageesh.M.Bose [bageesh...@gmail.com]
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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users