[sqlite] sqlite shell .import drops last cell if too less columns

[udo . liess]

Hi,
here comes a bug report with fix proposal.

If a CSV file contains a row with missing column, the previous cell will not be 
imported.
Tested version: sqlite-shell-win32-x86-3080802.zip, 
sqlite-autoconf-3080802.tar.gz


Steps to reproduce:

- A file named data.csv with following content:
a,b,c
d,THIS_ENTRY_WILL_BE_MISSING
f,g,h

- Open shell and execute following commands:
.open db.sqlite
CREATE TABLE data (col1, col2, col3);
.mode csv
.import data.csv data
SELECT * FROM data;


Bug source:

After a missing column was detected (in shell.c line 2890) the bound text 
paramter (line 2889) will be overwritten with NULL in line 2895.


Bug fix:

In file shell.c line 2894 change
          i++;
to
          i+=2;

                  
Corrected source will lock like:
...
    do{
      int startLine = sCtx.nLine;
      for(i=0; i<nCol; i++){
        char *z = xRead(&sCtx);
        /*
        ** Did we reach end-of-file before finding any columns?
        ** If so, stop instead of NULL filling the remaining columns.
        */
        if( z==0 && i==0 ) break;
        /*
        ** Did we reach end-of-file OR end-of-line before finding any
        ** columns in ASCII mode?  If so, stop instead of NULL filling
        ** the remaining columns.
        */
        if( p->mode==MODE_Ascii && (z==0 || z[0]==0) && i==0 ) break;
        sqlite3_bind_text(pStmt, i+1, z, -1, SQLITE_TRANSIENT);
        if( i<nCol-1 && sCtx.cTerm!=sCtx.cColSep ){
          fprintf(stderr, "%s:%d: expected %d columns but found %d - "
                          "filling the rest with NULL\n",
                          sCtx.zFile, startLine, nCol, i+1);
          i+=2; // BUG FIX HERE <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
          while( i<=nCol ){ sqlite3_bind_null(pStmt, i); i++; }
        }
      }
      if( sCtx.cTerm==sCtx.cColSep ){
        do{
          xRead(&sCtx);
          i++;
        }while( sCtx.cTerm==sCtx.cColSep );
        fprintf(stderr, "%s:%d: expected %d columns but found %d - "
                        "extras ignored\n",
                        sCtx.zFile, startLine, nCol, i);
      }
      if( i>=nCol ){
        sqlite3_step(pStmt);
        rc = sqlite3_reset(pStmt);
        if( rc!=SQLITE_OK ){
          fprintf(stderr, "%s:%d: INSERT failed: %s\n", sCtx.zFile, startLine,
                  sqlite3_errmsg(db));
        }
      }
    }while( sCtx.cTerm!=EOF );
...


Udo
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users