No write to SQLDatabaseMBS

Add-Ons
1

I’m using an SQLite database as index for Valentina because Valentina doesn’t have an FTS. So far I have been opening and closing the database with each write operation:

call ConnectToDatabaseLocalMBS WriteLocalMBS(theRecID, MessageBody) CloseSQLite

where WriteLocalMBS is

dim thePreparedStatement as SQLPreparedStatementMBS = SQLiteIndexDBMBS.Prepare("INSERT INTO bodyindex(docid, messagebody) VALUES(" + str(theRecID) + ", ?)") thePreparedStatement.BindType(0, thePreparedStatement.kTypeString) if MessageBody = "" then thePreparedStatement.SQLExecuteMT(App.MailFields.MessageBody) else thePreparedStatement.SQLExecuteMT(MessageBody) end if thePreparedStatement = Nil

I wanted to simplify and speed up writing to SQLite and took out opening and closing for each write. Now nothing is written. I checked that the database is connected and open. I added an Autocommit and tried a Commit in WriteLocalMBS: nothing is added to the database. If I do a loop over every record for a reindex then everything works fine. Just the individual write doesn’t work.

What am I doing wrong?

Xojo 2019r3, SQL plugin 20.1

2

I don’t see any error checking… have you verified that no error is generated that might point at the issue?

3

I omitted the error checking for the forum.

4

There is usually no need to open/close database each time.

But the insert statement should of course insert something.
Can you see this in a small project?

5

I’ll try to make an example.

6

As usual the problem was a PEBKAC one. I closed the database before using it. I’ll do more investigating why the database wasn’t reopened as it should have been.

7

Great. As usual asking for a sample project or writing an email causes people to check their code again and often solves it.