specifying datatypes when converting table to SQLite DB

Discussion in 'Questions (Windows Mobile)' started by Steve, Jun 14, 2007.

  1. Steve

    Steve Member Licensed User

    Hi folks,

    I am looking at a means to optimize the database I will eventually use in my GPS application. The following code produces a database, but from the inflated size I think all the numeric data is being turned into UTF8 text. Is there a way to import a table into an SQLite database and specify the data types to be used? Here is what I have so far, borrowed from the very handy database conversion utility posted to this forum earlier.

    table1.LoadCSV("points.csv", ";",true, true)

    doevents

    Con.Open("Data Source = " & AppPath & "\points.sl3")

    cmd.CommandText = "DROP TABLE points"
    cmd.ExecuteNonQuery

    Con.CreateSQLTable ("Table1","points")

    cmd.CommandText = "CREATE INDEX points_IDX1 ON points(id ASC)"
    cmd.ExecuteNonQuery

    cmd.CommandText = "CREATE INDEX points_IDX2 ON points(NAME ASC)"
    cmd.ExecuteNonQuery

    con.Close

    In the short run, I know I could loop through the table and issue numerous "INSERT INTO" statements, but I suspect that with thousands of points to compile, such a process would not be very efficient. I appreciate any tips that can be offered.

    - All my best,
    - Steve
     
  2. Erel

    Erel Administrator Staff Member Licensed User

    You should first create the table columns using cString or cNumber, afterwards load the data using LoadCSV without recreating the columns.
    That way it will save the number columns as REAL columns in the SQL db.
     
Loading...