database.insertRecord error

I’m trying to import any tab delimited text file of various columns and headings into an SQLite database
eg:

First Name Last Name Email User Name *Employee ID
Paul Blogs paul.Blogs@email.com paul.blogs@email.com 123
Rodney Smith rodney.smith @email.au rodney.smith @email.au 456

The database is created with the correct columns and the databaseRecord looks ok in the debug window but I get the error message -

(this one appears once)
Database error: near “*”: syntax error

(then this one repeats for each row)
Database error: near “)”: syntax error

I realise the files have spaces in the field names and there is star characters but it doesn’t seem to make any difference to creating the table. It only fails on entering the rows.

[code] dim txtin as TextInputStream
dim s as String
dim lines() as string

txtin = TextInputStream.Open(dataFile)  //

while not txtin.EOF
  lines.append( txtin.ReadLine )
wend
txtin.close

dim i,count As integer

'Create table
if db.Connect then
  db.SQLExecute ("CREATE TABLE main (ID INTEGER NOT NULL, PRIMARY KEY(ID));")
  
  'Create Headings
  dim columnCount as integer = lines(0).CountFields(chrb(9))
  for i = 0 to columnCount - 1
    dim strHeading as string = lines(0).NthField(chrb(9),i+1)
    dim pstrSQL as string = "ALTER TABLE main ADD COLUMN '" + strHeading + "' Varchar"
    db.SQLExecute (pstrSQL)
  next
  db.Commit
  
  'Enter data
  
  dim intRows as integer =lines.Ubound
  Dim rs As RecordSet = db.FieldSchema("main")
  for i=1 to intRows
    dim c as integer
    dim intFieldCount as integer = rs.FieldCount - 1
    
    // Make database record
    dim row as new DatabaseRecord
    for c = 1 to intFieldCount
      dim strFieldText as string = lines(i).NthField(chrb(9),c)
      
      rs.MoveNext // start with second column (not id)
      dim colName As String = rs.IdxField(1).StringValue
      row.Column(colName) = strFieldText
    next
    db.InsertRecord("main",row)
    if DB.Error Then
      MsgBox("Database Error: " + db.ErrorMessage)
    end if
  next
  db.commit
else
  msgbox db.ErrorMessage
end if[/code]

ok… fails WHERE? what error message? on 1st record? or some time into the read?

personally I’d skip all that insertrecord, movenext code and just use straight SQL,
create an “INSERT INTO MAIN values(”")

and don’t do a boat load of ALTER TABLES… build a string that is a proper CREATE TABLE and execute ONE time

and that “*” has gotta go

The error message occurs here:

db.InsertRecord("main",row) if DB.Error Then MsgBox("Database Error: " + db.ErrorMessage) end if
I don’t have much control over characters and spaces in the field names as the users create anything they like in Excel which is acceptable then export to tab delimited text.
The star error is on the first record only, then the bracket error for every record after that.
Removing the star from the text file still produces the bracket errors.

This is NOT tested, and for ILLUSTRATION ONLY… but it is a much better method… there may be some syntax errors, as I took your original code and altered it … up to you to decide if you want to go this way, and to make the required changes to make it fit your process.

  Dim txtin As TextInputStream
  Dim s As String
  Dim lines() As String
  dim ColumnCount as integer
  txtin = TextInputStream.Open(dataFile)  //
  s=txtin.readall
  s=ReplaceLineEndings(s,EndOfLine.UNIX)
  lines.Split(s,EndOfLine.UNIX)
  txtin.close
  
  Dim i,count As Integer
  Dim SQL As String
  
  'Create table
  If db.Connect Then
    SQL="CREATE TABLE main (ID INTEGER NOT NULL, PRIMARY KEY(ID)")
    
    'Create Headings
    columnCount  = lines(0).CountFields(ChrB(9))
    For i = 0 To columnCount - 1
      Dim strHeading As String = lines(0).NthField(ChrB(9),i+1)
      strHeading=ReplaceAll(strHeading," ","_")
      strHeading=ReplaceAll(strHeading,"*","")
      SQL=SQL+ "," + strHeading + " Varchar"
    Next
    SQL=SQL+")"
    db.SQLExecute (SQL)
    db.Commit
    //
    //Enter data
    //
    Dim intRows As Integer =lines.Ubound
    For i=1 To intRows
      Dim c As Integer
      
      
      // Make database record
      
      SQL="INSERT INTO main VALUES("
      For c = 1 To columnCount
        Dim strFieldText As String = lines(i).NthField(ChrB(9),c)
        sql=sql+"'"+strFieldText+"'"
        if c<columnCount then sql=sql+","
      Next
      sql=sql+")"
      db.SQLExecute (SQL)
    Next
    
    db.commit
  End If

Thanks I got it to work, with a few alterations.
One question about using

"INSERT INTO main VALUES(  ...."

If the first field is my primary key how do skip it or make it auto-fill without specifying the name of all the other fields and their assigned value?

[quote=298804:@Craig Grech]Thanks I got it to work, with a few alterations.
One question about using

"INSERT INTO main VALUES(  ...."

If the first field is my primary key how do skip it or make it auto-fill without specifying the name of all the other fields and their assigned value?[/quote]
you’re correct… I just typed that off the top of my head :slight_smile:

it would be more along the lines of “INSERT INTO main (”+") VALUES( …

it could be built at the same time you were building the CREATE TABLE statement

I ditched the primary key for now.
It works ok but extremely slow on large files (8000 lines)

[code] 'Enter data
dim intRows as integer =lines.Ubound

  for i=1 to intRows
    dim c as integer
    // Make database record
    SQL="INSERT INTO main VALUES("
    For c = 1 To columnCount
     
      Dim strFieldText As String = lines(i).NthField(ChrB(9),c)
      sql=sql+"'"+strFieldText+"'"
      if c<columnCount then sql=sql+","
    Next
    sql=sql+")"
    db.SQLExecute (SQL)
    if DB.Error Then
      MsgBox("Database Error: " + db.ErrorMessage)
    end if
  Next
  db.commit
  msgbox "Data imported"[/code]

here is an idea that might speed it up a bit…

Ditch the NthField method
you have a string that is ^^
where ^ is a tab (chrb(9))
and you want ‘’,’’,’’ right?

so s="'"+replaceAll(lines(i),chrb(9),"','")+"'" should do the same as that FOR C loop

// Make database record SQL="INSERT INTO main VALUES(" SQL = SQL + "'" + replaceAll(lines(i),chrb(9),"','")+"'" + ")" db.SQLExecute (SQL)

That’s definitely simpler but it’s still very slow on large files.
I even put it in a thread with no noticeable difference.

Funny I have code to copy the same data into a listbox and 50,000 rows loads in less than 5 seconds.

Insert this before for i=1 to intRows :

db.SQLExecute("BEGIN TRANSACTION;")

Using a 10,000 row file of random data records matching your example,
inserted 5 times for 50,000 total rows (with primary key) takes less than 1 second.

you can also group the “insert” commands into one sentence (with endofline between and “;”)
then you send a string with 1000 “insert” inside to the database.
this really speeds things up.

Or: INSERT INTO ‘tablename’
SELECT ‘data1’ AS ‘column1’, ‘data2’ AS ‘column2’
UNION ALL SELECT ‘data1’, ‘data2’
UNION ALL SELECT ‘data1’, ‘data2’
UNION ALL SELECT ‘data1’, ‘data2’

as recommeded here . This trick appears to be working with 500 records maximum though. UNION ALL is used here for maximum speed. You may want to use UNION if you need to skip duplicates.