SQLite 3 DLL Ver 1.2

Discussion in 'Questions (Windows Mobile)' started by alfcen, May 17, 2007.

  1. alfcen

    alfcen Well-Known Member Licensed User

    Hi gang,
    I am generating a table with TEXT, REAL, NUMERIC and BLOB class columns.
    Trying to write a string consisting of both numbers and alphabets,
    such as "16h30m34s" into a TEXT class column.
    A that point an error message prompts:

    Invalid token "16h30m34s"

    Is this perhaps a limitation of SQLite? :sign0163:

    The SQL Database Browser has no such problems.

    Thanks a lot!
    Robert
     
  2. Erel

    Erel Administrator Staff Member Licensed User

    This is a simple string and there shouldn't be any problem adding it to the db.
    Did you use single quotes ' before and after the string?
     
  3. alfcen

    alfcen Well-Known Member Licensed User

    Hi Erel,
    The source may say word than words (I tried with 's, no better):

    Sub insertpic_Click
    cmd.New1("CREATE TABLE IF NOT EXISTS exoplanets (rarad REAL,derad REAL,Name TEXT,Constellation TEXT,RA TEXT,Dec TEXT,Dist NUMERIC,Mag NUMERIC,Msun NUMERIC,Mjup NUMERIC,Period NUMERIC,a NUMERIC,ex NUMERIC,Spectral TEXT,image BLOB)",con.Value)
    cmd.ExecuteNonQuery
    fileOpen(c1,"exoplanets.csv",cread)
    zt=fileRead(c1)
    do until zt=EOF
    zt=fileRead(c1)
    v()=gps.StrSplit(zt,",")
    cmd.CommandText = "INSERT INTO exoplanets values("&v(0)&","&v(1)&","&v(2)&","&v(3)&","&v(4)&","&v(5)&","&v(6)&","&v(7)&","&v(8)&","&v(9)&","&v(10)&","&v(11)&","&v(12)&","&v(13)& "," & cmd.FileToBLOB(AppPath & "\images\" & v(14)) & ")"
    cmd.ExecuteNonQuery
    loop
    fileClose(c1)

    'cmd.CommandText = "SELECT image FROM exoplanets"
    'reader.Value = cmd.ExecuteReader
    'reader.ReadNextRow
    'Image1.Image = reader.GetImage(0)
    End Sub
     
  4. Erel

    Erel Administrator Staff Member Licensed User

    Each string that is inserted into the db should be wrapped with single quotes.
    Try something like:
    v()=gps.StrSplit(zt,",")
    For i = 0 To 13
    v(i) = "'" & v(i) & "'"
    Next
    ...

    BTW, you should use connection.BeginTransaction at the beginning of this sub and connection.EndTransaction at the end. It will improve the speed of the data insert.
     
  5. alfcen

    alfcen Well-Known Member Licensed User

    Thanks a lot for help, Erel, I will try it.
     
Loading...