SQLite question

Getting Started
1

I am new to SQLite. I bought a book written by Eugene Dakin about SQLite with Xojo. Keeping myself almost to what was written in the book, I wrote my a piece to insert a row of data into my database:

Dim row as new DatabaseRecord
Dim db as SQLiteDatabase

[i]f = GetFolderItem(gPathDB+“DTS.sqlite”)
db = new SQLiteDatabase
db.DatabaseFile=f

if db.Connect = true then
  
  cell()=split(Records,",")
  
  row.Column("dDate")=cell(0)
  row.Column("dTime")=cell(1)
  row.Column("dTitle") =cell(2)
  row.Column("dLat") =cell(3)
  row.Column("dLon")=cell(4)
  row.Column("dDepth")=cell(5)
  row.Column("dForce")=cell(6)
  row.Column("dType")=cell(7)
  row.Column("dDesc")=cell(8)
  row.Column("dName")=cell(9)
  row.Column("dMove")=cell(10)
  row.Column("dPresure")=cell(11)
  row.Column("dSpeed")= cell(12)
  
  db.InsertRecord("Dist",row)
  
  db.Commit
else 
  msgbox "Could not connect to database"+EndOfLine+db.ErrorMessage

end if[/i]

Dist a a file in the database containing the records as written in the row lines.

Xojo can connect to the database, because it does not pop/up the msgbox with the error. As soon as I change anything in the path or filename, the msgbox pops up.

I do not know if db.commit is valid here, the examples in the book does insert the record, but does not commit. There my database was not updated without the commit statement, I added that to the code.

Still, there is no update into my database.

Any SQLite knowledged person who can help me?

I tried even to use another SQLite command to update the database:

db.SQLExecute(“INSERT INTO Disaster (dDate,dTime,dTitle,dLatT,dLonT,dDepth,dForce,dType,dDesc,dName,dMove,dPresure,dSpeed) VALUES (cell(0).cell(1),cell(2),cell(3),cell(4),cell(5),cell(6).cell(7),cell(8),cell(9),cell(10),cell(11),cell(12)”)

…but same result…no update

2

Missing from your code is checking the db for an error and its error message after the insert. What do those tell you?

3

In the INSERT INTO version, I would say you are MISSING quotes around any STRING/TEXT type values (dDESC, dName etc)
as a test… replace “db.SQLExecute” with “MsgBox” and LOOK at the constructed SQL Statement

FYI… INSERT INTO is MY preferred way of doing this type operation

4

Hi Dave, I did what you said I had to do. That I did not put qoutes around the input values, was because I thought that had to be done when you send strings in the execution and that you do not have to put those quotes when there were variables in the insertion carrying strings.

But…:
db.SQLExecute(“INSERT INTO Disaster (dDate,dTime,dTitle,dLatT,dLonT,dDepth,dForce,dType,dDesc,dName,dMove,dPresure,dSpeed) VALUES (‘cell(0)’,‘cell(1)’,‘cell(2)’,‘cell(3)’,‘cell(4)’,‘cell(5)’,‘cell(6)’,‘cell(7)’,‘cell(8)’,‘cell(9)’,‘cell(10)’,‘cell(11)’,‘cell(12)’”)

did not drop an error, not did it update my databases.

Hi Kem,
the insertion did not drop an error at all, my program keeps on running like nothing stops it, no errors, but the database is stil not updated.

5

a) you inserted the quotes incorrectly
b) are you CHECKING for an error? or just assuming it will “bomb” if there is one? if so, that is an incorrect assumption

DIM SQL as STRING
sql="INSERT INTO Disaster 
(dDate,dTime,dTitle,dLatT,dLonT,dDepth,dForce,dType,dDesc,dName,dMove,dPresure,dSpeed) VALUES ('"+cell(0)+"','"+cell(1)+"','"+cell(2)+"','"+cell(3)+"','"+cell(4)+"','"+cell(5)+"','"+cell(6)+"','+"cell(7)+"','"+cell(8)+"','"+cell(9)+"','"+
cell(10)+"','+"cell(11)+"','"+cell(12)+"'")

db.SQLExecute(SQL)

if db.error then 
msgbox "ERROR : "+str(db.Errorcode)+" Msg : "+db.errormessage
end if
6

Hi Dave, are you sure you put the qoutes right? When I paste your solution into my project it makes everything after the first ’ a remark, just like it used to be…

7

Rob, as a side issue, Dave’s example puts the SQL into a string variable and using that in the SQLExecute call. By doing this, you can set a breakpoint on the SQLExecute statement and examine the string being sent to ensure it is correct before it gets sent to the database. It makes debugging easier.

8

Using an INSERT SQL statement this way is not recommended. If anything, you should use a Prepared Statement, but the DatabaseRecord already does that for you.

Rob, what does db.Error tell you after you attempt to insert?

9

ok, I speeled the command closely and found some single/double quotes on the wrong place. After I corrected that, the database was updated correctly. Knowing this way of working now, I will find out tomorrow if the other way with row’s can work too.

Thanks Dave for your answer.

10

I would do it more like this, not needing quotes :slight_smile:

  Dim SQL as String
  Dim ps as SQLitePreparedStatement
  
  sql="INSERT INTO Disaster " _
  + "(dDate,dTime,dTitle,dLatT,dLonT,dDepth,dForce,dType,dDesc,dName,dMove,dPresure,dSpeed) " _
  + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  
  ps = SQLitePreparedStatement(db.Prepare(sql))
  ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
  ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
  ps.BindType(2, SQLitePreparedStatement.SQLITE_TEXT)
  ps.BindType(3, SQLitePreparedStatement.SQLITE_TEXT)
  ps.BindType(4, SQLitePreparedStatement.SQLITE_TEXT)
  ps.BindType(5, SQLitePreparedStatement.SQLITE_TEXT)
  ps.BindType(6, SQLitePreparedStatement.SQLITE_TEXT)
  ps.BindType(7, SQLitePreparedStatement.SQLITE_TEXT)
  ps.BindType(8, SQLitePreparedStatement.SQLITE_TEXT)
  ps.BindType(9, SQLitePreparedStatement.SQLITE_TEXT)
  ps.BindType(10, SQLitePreparedStatement.SQLITE_TEXT)
  ps.BindType(11, SQLitePreparedStatement.SQLITE_TEXT)
  ps.BindType(12, SQLitePreparedStatement.SQLITE_TEXT)
  ps.Bind(0, cell(0))
  ps.Bind(1, cell(1))
  ps.Bind(2, cell(2))
  ps.Bind(3, cell(3))
  ps.Bind(4, cell(4))
  ps.Bind(5, cell(5))
  ps.Bind(6, cell(6))
  ps.Bind(7, cell(7))
  ps.Bind(8, cell(8))
  ps.Bind(9, cell(9))
  ps.Bind(10, cell(10))
  ps.Bind(11, cell(11))
  ps.Bind(12, cell(12))
  
  ps.SQLExecute()
  
  if db.error then
    msgbox "ERROR : "+str(db.Errorcode)+" Msg : "+db.errormessage
  end if

If it was PostgreSQL, the BindType would not be needed…I love pg :wink:

11

So, now I changes my code into:

f = GetFolderItem(gPathDB+"DTS.sqlite")
db = new SQLiteDatabase
db.DatabaseFile=f

if db.Connect = true then
  
  cell()=split(Records,",")
  
  row.Column("dDate")="'"+cell(0)+"'"
  row.Column("dTime")="'"+cell(1)+"'"
  row.Column("dTitle") ="'"+cell(2)+"'"
  row.Column("dLat") ="'"+cell(3)+"'"
  row.Column("dLon")="'"+cell(4)+"'"
  row.Column("dDepth")="'"+cell(5)+"'"
  row.Column("dForce")="'"+cell(6)+"'"
  row.Column("dType")="'"+cell(7)+"'"
  row.Column("dDesc")="'"+cell(8)+"'"
  row.Column("dName")="'"+cell(9)+"'"
  row.Column("dMove")="'"+cell(10)+"'"
  row.Column("dPresure")="'"+cell(11)+"'"
  row.Column("dSpeed")= "'"+cell(12)+"'"
  row.Column("dRetPeriod")="'"+cell(13)+"'"
  
  db.InsertRecord("Disaster",row)
  
  if db.error then 
    msgbox "ERROR : "+str(db.Errorcode)+" Msg : "+db.errormessage
  end if

  db.Commit
  db.close
  
else 
  msgbox "Could not connect to database"+EndOfLine+db.ErrorMessage
  
end if

Which is less error prune, because the double and single qoutes are per row and not in one statement, of which you easily can make a mistake.

Albin,
I will try your piece too, because writing code without all the double/single quotes looks much better. Because I am rather new in this I will, while writing the code, have to search and learn about the Bind and Bindtype you use. Thank you for your solution :slight_smile:

12

May be a little function like this? It takes care also of possible single quotes in your strings.

Function beautify(s as string) As string
Return s.ReplaceAll("’", “’’”)
End Function

row.Column(“dDate”)=beautify(myString)

13

Sorry, the idea was to take care of possible quotes in your strings.

Function beautify(s as string) As string
Return s.ReplaceAll("’", “’’”)
End Function

row.Column(“dDate”) = “’” + beautify(myString) + “’”

14

DatabaseRecord does not need you to surround the data in each column in quotes. In fact, you will end up with quotes embedded in your data that way.

row.Column("dDate")=cell(0)
row.Column("dTime")=cell(1)
…
15

You ONLY need to include extra quotes in you code if you are BUILDING a single SQL statement string… the resulting string MUST resolve to a VALID SQL statement. If you are using any of the other methods shown above, leave the quotes out of the code.

16

Yep, I messed it up. Sorry. Time to go to sleep.

17

If you’ve got an error you’ll want to Rollback a transaction rather than dropping through to the Commit statement as you’re doing here.

18

I left the qoutes out, they were indeed embedded in the string when I started. It looks good now and it works like a champ.

Thank you all for your advise and examples. With your advise and examples I went working with the SQLite books I have and it was a great learning experience. The code I wrote is improved.