SQLite 3 DLL Ver 1.2

[alfcen]

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

 

[Erel]

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?

 

[alfcen]

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

 

[Erel]

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.

 

[alfcen]

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