I must be doing something very stupid .

Please explain why searching works fine directly after adding data to the SQLite database. But searching does NOT work after quitting and restarting. The SQLite database is then only a couple of k. So there there can't be any data for searching. That's the main reason why I think there is something wrong with the WAL file.

I've restarted vServer and do the "SELECT docid FROM bodyindex WHERE messagebody MATCH 'some'" query from vStudio.
Still getting correct result - "1"

t_sql = "SELECT docid FROM bodyindex WHERE messagebody MATCH ?"

dim ps as SqlitePreparedStatement = app.db.Prepare( t_sql )



ps.BindType( 0, SqlitePreparedStatement.SQLITE_TEXT )



rs = ps.SqlSelect( "some" )



while Not rs.EOF

  db_listbox.AddRow 

  

  For i = 1 to rs.fieldcount

    dim ss as String = rs.IdxField(i).StringValue

    ss = DefineEncoding( ss, Encodings.UTF8 )

    

    db_listbox.cell(db_listbox.lastIndex,i-1) = ss

  next

  

  rs.MoveNext

wend



rs.Close

There is correct result - "1"

SELECT docid FROM bodyindex WHERE messagebody MATCH 'some'



-- docid

-- 1

It seems it worls

dim ps as SqlitePreparedStatement = app.db.PrepareEx( "INSERT INTO bodyindex(docid, messagebody) VALUES( 1, ?)" )

if db.error then

  MsgBox db.ErrorMessage

  exit

end if

ps.BindType( 0, SqlitePreparedStatement.SQLITE_TEXT )

ps.SqlExecute( "some text" )

if db.error then

  MsgBox db.ErrorMessage

  exit

end if

Indeed, no errors.

Checking result with vStudio I see 1 record in bodyindex_docsize, bodyindex_segdir, bodyindex_stat tables

Here is the query for searching:

dim PreparedForSearch as SqlitePreparedStatement = remote_db.Prepare("SELECT docid FROM bodyindex WHERE messagebody MATCH ?") 

PreparedForSearch.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)

SearchString = ReplaceAll(SearchString, """", """""")

dim data as RecordSet = PreparedForSearch.SQLSelect("'*'" + searchstring + "'*'")



If data <> Nil Then

  While Not data.EOF

    ResultIDs.AddRow data.Field("docid").StringValue

    data.MoveNext

  Wend

  data.Close

End If

Please explain why searching works fine directly after adding data to the SQLite database. But searching does NOT work after quitting and restarting. The SQLite database is then only a couple of k. So there there can't be any data for searching. That's the main reason why I think there is something wrong with the WAL file.

Trixi, let it be some empty db initially.
Then do:

CREATE VIRTUAL TABLE if not exists bodyindex USING fts4(tokenize=unicode61,content='', messagebody);

INSERT INTO bodyindex(docid, messagebody) VALUES( 1, 'some text');

Is it correct?

What the query you check the result with?

Have a look at my latest screenshots. The WAL file is filled in but nothing is written to the database file. WAL = off shouldn't create the WAL file at all - as far as I read.

Why do you think it is related to WAL?

I checked both errors and exceptions. Nothing.

I also changed WAL mode to OFF. The WAL file still is being written. My problem must have something to do with the WAL file.

About errors.

It seems before Xojo 2019.2 RBDB should not throw an errors. Everything should be checked via db.error property.
But now there is no such restriction, so we will fix this and your "catch" will work.

Yes, my table is only for an index because Valentina still doesn't have a full text search. Of course, I've omitted the error handling for brevity.

Trixi, I see the changes on the disk after all for the example db.

But, I've noticed just now, that your case is about some "external" table - not simply regular table as I described above.
So, first of all - your code should check for possible errors - something like this one:

...

dim ps as SqlitePreparedStatement = app.db.PrepareEx( "INSERT INTO test VALUES (?, ?)" )



if db.error then

  MsgBox db.ErrorMessage

  exit

end if

...

I'm not expert in Sqlite, but I think that you should init/load that external feature before use it somehow.

Thanks for replying on Sunday. But something is off here. The data isn't written to the hard disk.

Screenshot 1 shows the files on the hard disk directly after adding data. Using the SQLite database for searching worked fine. Screenshot 2 shows that the main file is now much smaller. Is this normal? After quitting and restarting the same query now doesn't work.

After adding begin transaction/commit to the write operation I get the same result: searching works before quit/restart but not after.

If I check the database I can see that synchronous is set to full, which should be enough. Should I try to change the WAL mode?

With explicit transactions:

db.SQLExecute( "BEGIN TRANSACTION" )

// all that stuff above with binded prepared statement

db.SQLExecute( "COMMIT" )

It's still working for PrepareEx:

dim ps as VSqlitePreparedStatement = app.db.PrepareEx( "INSERT INTO test VALUES (?, ?)" )

vServer log:

2020-06-14 16:47:53.837070 (TID 123145493139456): (UID 24) I_SqliteDatabase 'vsqlitetest.sqlite' CreateStatement 'INSERT INTO test VALUES (?, ?)'.

2020-06-14 16:47:53.837118 (TID 123145493139456): (UID 24) SqliteStatement 'INSERT INTO test VALUES (?, ?)' remoteID -921182432 registered.

2020-06-14 16:47:53.837278 (TID 123145492602880): (UID 24) I_SqliteStatement 'INSERT INTO test VALUES (?, ?)' Prepare.

2020-06-14 16:47:53.838786 (TID 123145493676032): (UID 24) I_SqliteStatement 'INSERT INTO test VALUES (?, ?)' SqlBind.

2020-06-14 16:47:53.838954 (TID 123145493139456): (UID 24) I_SqliteStatement 'INSERT INTO test VALUES (?, ?)' SqlExecute.

2020-06-14 16:47:53.839521 (TID 123145492602880): (UID 24) I_SqliteStatement 'INSERT INTO test VALUES (?, ?)' SqlBind.

2020-06-14 16:47:53.839674 (TID 123145493676032): (UID 24) I_SqliteStatement 'INSERT INTO test VALUES (?, ?)' SqlExecute.

2020-06-14 16:47:53.840131 (TID 123145493139456): (UID 24) I_SqliteStatement 'INSERT INTO test VALUES (?, ?)' get_SqliteDatabase.

2020-06-14 16:47:53.840281 (TID 123145492602880): (UID 24) I_SqliteDatabase 'vsqlitetest.sqlite' CloseStatement 'INSERT INTO test VALUES (?, ?)'.

2020-06-14 16:47:53.840315 (TID 123145492602880): (UID 24) SqliteStatement 'INSERT INTO test VALUES (?, ?)' remoteID -921182432 unregistered.

Ok, let's play with V4RB/Examples/RBDB_way/VSqlite/VSqlite.rbp example.

Let' comment inserts without binding there:

...

'db.SQLExecute( "INSERT INTO test VALUES (1, 'Ivanov')" )

'db.SQLExecute( "INSERT INTO test VALUES (2, 'Petrov')" )

...

Now, let's do it with binding:

...

dim ps as SqlitePreparedStatement = app.db.Prepare( "INSERT INTO test VALUES (?, ?)" )

ps.BindType( 0, SqlitePreparedStatement.SQLITE_INTEGER )

ps.BindType( 1, SqlitePreparedStatement.SQLITE_TEXT )

...

There are two ways to execute prepared statements:

first:

ps.Bind( 0, 1 )

ps.Bind( 1, "Ivanov" )

ps.SqlExecute()

ps.Bind( 0, 2 )

ps.Bind( 1, "Petrov" )

ps.SqlExecute()

second:

ps.SqlExecute( 1, "Ivanov" )

ps.SqlExecute( 2, "Petrov" )

Both ways are ok - the records were inserted.

I changed the log so that everything is logged:

2020-06-14 06:40:51.712122 (TID 123145479368704): (UID 38) I_SqliteDatabase 'mail archive.sqlite' CreateStatement 'INSERT INTO bodyindex(docid, messagebody) VALUES(1, ?)'.
2020-06-14 06:40:51.712164 (TID 123145479368704): (UID 38) SqliteStatement 'INSERT INTO bodyindex(docid, messagebody) VALUES(1, ?)' remoteID -1041261968 registered.
2020-06-14 06:40:51.712319 (TID 123145475612672): (UID 38) I_SqliteStatement 'INSERT INTO bodyindex(docid, messagebody) VALUES(1, ?)' Prepare.
2020-06-14 06:40:55.250582 (TID 123145477758976): (UID 38) I_SqliteStatement 'INSERT INTO bodyindex(docid, messagebody) VALUES(1, ?)' SqlBind.
2020-06-14 06:40:55.250748 (TID 123145477222400): (UID 38) I_SqliteStatement 'INSERT INTO bodyindex(docid, messagebody) VALUES(1, ?)' SqlExecute.
2020-06-14 06:40:56.362225 (TID 123145476685824): (UID 38) I_SqliteStatement 'INSERT INTO bodyindex(docid, messagebody) VALUES(1, ?)' get_SqliteDatabase.
2020-06-14 06:40:56.362363 (TID 123145478832128): (UID 38) I_SqliteDatabase 'mail archive.sqlite' CloseStatement 'INSERT INTO bodyindex(docid, messagebody) VALUES(1, ?)'.
2020-06-14 06:40:56.362383 (TID 123145478832128): (UID 38) SqliteStatement 'INSERT INTO bodyindex(docid, messagebody) VALUES(1, ?)' remoteID -1041261968 unregistered.

My app talks to Valentina Server and VSqlite at the same time. Is this allowed? I still want to use Sqlite as full text search because Valentina is so slow when doing a text search.

Yes, sqlite which is inside Valentina. Local I have working fine - so server. Cough... FTS cough...