Expect SQLitePreparedStatement, straight out of examples

Databases
1

I’m having trouble with SQLite prepared statements. Actually, this is my first attempt at using SQLite but I took an example from the Language reference and put it to direct use.

[code] Dim ps As SQLitePreparedStatement = db.Prepare(“SELECT * FROM userInfo WHERE name=’?’ AND word=’?’”)

ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)

ps.Bind(0, name)
ps.Bind(1, word)[/code]

I am getting the error “Type mismatch error. Expect SQLitePreparedStatement, got SQLPreparedSQLStatement” for the Dim line.

Yes name and word are actual strings, and the db is available. I get no errors elsewhere when I skip this part.

Thanks in advance for any help.

2

What is db declared as?

3

I added it to the project via Menu->Insert->Database->New SQLite database.

The autofill picks up all database commands properly as I type. Does it need to be redeclared to be used?

4

When using the Insert > Database > … stuff, it creates the variable as a generic Database, which will work for all databases. That is the source of your error.

Try

Dim ps As PreparedSQLStatement = db.Prepare("SELECT * FROM userInfo WHERE name=? AND word=?")

Also note from my example I removed the quotes around your ?'s. They are not needed as the prepared SQL statement will add quotes and escape things as necessary itself.

5

Thanks for that! But I continue to the BindType problem. It doesn’t look like a good example is in the language reference for that. No officially printed error, but it looks like a NilObjectException.

ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT) ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)

6

Ok, I was able to get it. I had to change the Dim to:

Dim ps As SQLitePreparedStatement = SQLitePreparedStatement(db.Prepare("SELECT * FROM userInfo WHERE name=? AND word=?"))
7

Save yourself a lot of headaches and change the name of your db to db1 (or anything other than db). Then add a property to a module, db as SQLiteDatabase. Then in app.Open put

db = SQLiteDatabase(db1)

Now your global db variable is the proper type and your code from the example will work without modification.

8

Save yourself even more headaches and remove the database from the project altogether and connect to it in code. That will at least allow you to share your compiled project with your friends. As it stands, you’ll have mucho trouble getting it to work.

9

Tim,

So on a Web app, what would be the best way to open it and have each session able to connect to it?

I was thinking as App methods that accept incoming arguments that will be applied to the database. Should each method then open the database, or is there a more global way so I don’t have to repeat the open database process every time?

I just want to make sure I get the recommended way the first time.

10

Especially on a web app, you should open it in code. Each session should have a separate database object and connection. Adding the database to your app creates a single, shared object and connection. You shouldn’t share database connections between sessions.

11

So a Session.Method to work per query or set of queries then, opening and closing the database within that method is the best practices?

12

Depends on your application really and how data intensive it is. It may be that you keep a db connection open while the session is alive. Another method is to do connection pooling, i.e. keep X database connections open, when someone needs one call App.GetDb() (a method you write) and when you are done call App.PutBackDb(theDb). If App.GetDb() is asked for a database connection and it has no more in its queue, a new one is created, up to X connections of course.

I am sure there are other ways also.