specifying datatypes when converting table to SQLite DB

[Steve]

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

[Erel]

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.