1. Beatrix Willius
  2. as SQLite DB Server
  3. 金, 6月 12 2020, 08:44 AM
  4.  メールで購読
How do I write to VSQLite? I must be missing something simple - again.

For the database with the table

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

the following code to add data works:


dim theRecid as Integer = 11
dim thePreparedStatement as SqlitePreparedStatement = db.Prepare("INSERT INTO bodyindex(docid, messagebody) VALUES(" + str(theRecID) + ", ?)")
thePreparedStatement.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
dim messagebody as String = exampleTextFile
thePreparedStatement.SQLExecute(MessageBody)
thePreparedStatement = Nil


After writing I can see that the SQLite database now is much larger.

If I do something similar for my app


try
dim thePreparedStatement as VSqlitePreparedStatement = remote_db.PrepareEx("INSERT INTO bodyindex(docid, messagebody) VALUES(" + str(theRecID) + ", ?)")
thePreparedStatement.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
if MessageBody = "" then
thePreparedStatement.SQLExecute(App.MailFields.MessageBody)
else
thePreparedStatement.SQLExecute(MessageBody)
end if
thePreparedStatement = Nil

catch err as DatabaseException
Globals.theErrorLog.DialogErrorProceed kErrorWrite + " " + err.Message
globals.StopArchiving = True
end try


I can see the wal and shm files. A tiny little bit seems to be written because the database is now 100kb. Due to the nature of the database I can't easily check the content. But searching doesn't work. I tripple checked that the data that should be written is correct.

I also tried to do "begin transaction/commit transaction". But this just gives me the error:

Sqlite db "mail archive.sqlite": Error : "cannot commit - no transaction is active".

What am I doing wrong?

Valentina 10.4.something on High Sierra.
コメント
There are no comments made yet.
Ruslan Zasukhin 承諾済みの回答
Hi Trixi,

to clarify:

* sqlite which is inside of Valentina
* local or under vserver
コメント
There are no comments made yet.
Beatrix Willius 承諾済みの回答
Yes, sqlite which is inside Valentina. Local I have working fine - so server. Cough... FTS cough...
コメント
There are no comments made yet.
Beatrix Willius 承諾済みの回答
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.
コメント
There are no comments made yet.
Ivan Smahin 承諾済みの回答
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.
コメント
There are no comments made yet.
Ivan Smahin 承諾済みの回答
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.

コメント
There are no comments made yet.
Ivan Smahin 承諾済みの回答
It's still working for PrepareEx:


dim ps as VSqlitePreparedStatement = app.db.PrepareEx( "INSERT INTO test VALUES (?, ?)" )
コメント
There are no comments made yet.
Ivan Smahin 承諾済みの回答
With explicit transactions:


db.SQLExecute( "BEGIN TRANSACTION" )
// all that stuff above with binded prepared statement
db.SQLExecute( "COMMIT" )
コメント
There are no comments made yet.
Beatrix Willius 承諾済みの回答
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?
添付ファイル
コメント
There are no comments made yet.
Ivan Smahin 承諾済みの回答
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.
コメント
There are no comments made yet.
Beatrix Willius 承諾済みの回答
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.
コメント
There are no comments made yet.
Ivan Smahin 承諾済みの回答
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.
コメント
There are no comments made yet.
Beatrix Willius 承諾済みの回答
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.
コメント
There are no comments made yet.
Ivan Smahin 承諾済みの回答
Why do you think it is related to WAL?
コメント
There are no comments made yet.
Beatrix Willius 承諾済みの回答
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.
コメント
There are no comments made yet.
Ivan Smahin 承諾済みの回答
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?
コメント
There are no comments made yet.
Beatrix Willius 承諾済みの回答
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.
コメント
There are no comments made yet.
Ivan Smahin 承諾済みの回答

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
コメント
There are no comments made yet.
Ivan Smahin 承諾済みの回答

SELECT docid FROM bodyindex WHERE messagebody MATCH 'some'

-- docid
-- 1


It seems it worls
コメント
There are no comments made yet.
Ivan Smahin 承諾済みの回答

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"
コメント
There are no comments made yet.
Ivan Smahin 承諾済みの回答

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"
コメント
There are no comments made yet.
  • ページ :
  • 1
  • 2


There are no replies made for this post yet.
However, you are not allowed to reply to this post.