Reading sqlite database

Targets Web
1

I don’t have a lot experience with web-apps but I have one operational for a non-profit organisation. I run into this problem.

Sometimes this query is unsuccessful. My problem is that I can’t detect this.
I don’t get an error!

stmt = db.Prepare(“SELECT * FROM Rides WHERE date = ? ORDER BY ID”)
stmt.BindType(0, SQLitePreparedStatement.SQLITE_INTEGER)
stmt.Bind(0, agdDate)

try
rows = stmt.SelectSQL
Catch e as DatabaseException
app.AppendToDBErrorLog("DBLog: " + e.message)
end try

If rows = Nil or rows.RowCount = 0 then return

2

Maybe a type mismatch, is your date field an Integer?

3

Yes the date field is integer. If there is a type mismatch I should get an error!
The query goes in most of the cases fine, but sometimes not. Maybe when the connection is not 100%, but I want to detect this.

4

Have you tried using the newer format?

try
    rows = db.SelectSQL("SELECT * FROM Rides WHERE date = ? ORDER BY ID", agdDate)
Catch e as DatabaseException
    app.AppendToDBErrorLog("DBLog: " + e.message)
end try

Also, make sure you have a separate database instance in each session and that you set WriteAheadLogging = True when initializing it so multiple sessions can make changes at the same time.

Edit: fixed code bug

5

have your sqlite an issue with multi user access?
have your web service permissions to the sql database file in release?
be sure the database is open/connected before use.

you can write this in a single row

Var rs As RowSet
rs = db.SelectSQL("SELECT * FROM Rides WHERE date = ? ORDER BY ID", agdDate)

https://www.sqlite.org/lang_datefunc.html

6

Might that be:

rows = db.SelectSQL ("SELECT ...
1 Like
7

Thx. Fixed.

8

Thank you for your help!
Sorry to respond in delay…

I use SQLitePreparedStatement because I learned from this Forum that it was more save for web-apps. For iOS I use the db.selectSQL as suggested.
I am going to change it and see what happens.

I open the db in the open-event of a session. I assume that is ok.
WriteAheadLogging is True.

The app is running on Xojo.cloud.

9

fwiw, SQLitePreparedStatement is the same as SelectSQL in terms of safety if you pass in the parameters. SQLSelect is the unsafe version.

The thing that’s not safe to do is inserting the query parameters by hand by concatenating strings. Having the database engine build the query means that the quoting is always correct and you don’t have to worry about someone typing something into a text field that would corrupt your database.

1 Like
10

Thanks Greg, what about db.executeSQL?

11

Same. It was the old methods that start with SQL and return a recordset that were the problem. That was the whole reason that the prepared statement classes were created.

The new methods, ExecuteSQL and SelectSQL which return RowSets and when used with the additional positional parameters offer the same protections as the prepared statement classes.

12

Thanks…