1. Beatrix Willius
  2. as SQLite DB Server
  3. Friday, June 12 2020, 08:44 AM
  4.  Subscribe via email
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.
Comment
There are no comments made yet.
Ruslan Zasukhin Accepted Answer
Hi Trixi,

to clarify:

* sqlite which is inside of Valentina
* local or under vserver
Comment
There are no comments made yet.
  1. more than a month ago
  2. as SQLite DB Server
  3. # 1
Beatrix Willius Accepted Answer
Yes, sqlite which is inside Valentina. Local I have working fine - so server. Cough... FTS cough...
Comment
There are no comments made yet.
  1. more than a month ago
  2. as SQLite DB Server
  3. # 2
Beatrix Willius Accepted Answer
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.
Comment
There are no comments made yet.
  1. more than a month ago
  2. as SQLite DB Server
  3. # 3
Ivan Smahin Accepted Answer
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.
Comment
There are no comments made yet.
  1. more than a month ago
  2. as SQLite DB Server
  3. # 4
Ivan Smahin Accepted Answer
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.

Comment
There are no comments made yet.
  1. more than a month ago
  2. as SQLite DB Server
  3. # 5
Ivan Smahin Accepted Answer
It's still working for PrepareEx:


dim ps as VSqlitePreparedStatement = app.db.PrepareEx( "INSERT INTO test VALUES (?, ?)" )
Comment
There are no comments made yet.
  1. more than a month ago
  2. as SQLite DB Server
  3. # 6
Ivan Smahin Accepted Answer
With explicit transactions:


db.SQLExecute( "BEGIN TRANSACTION" )
// all that stuff above with binded prepared statement
db.SQLExecute( "COMMIT" )
Comment
There are no comments made yet.
  1. more than a month ago
  2. as SQLite DB Server
  3. # 7
Beatrix Willius Accepted Answer
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?
Attachments (2)
Comment
There are no comments made yet.
  1. more than a month ago
  2. as SQLite DB Server
  3. # 8
Ivan Smahin Accepted Answer
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.
Comment
There are no comments made yet.
  1. more than a month ago
  2. as SQLite DB Server
  3. # 9
Beatrix Willius Accepted Answer
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.
Comment
There are no comments made yet.
  1. more than a month ago
  2. as SQLite DB Server
  3. # 10
Ivan Smahin Accepted Answer
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.
Comment
There are no comments made yet.
  1. more than a month ago
  2. as SQLite DB Server
  3. # 11
Beatrix Willius Accepted Answer
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.
Comment
There are no comments made yet.
  1. more than a month ago
  2. as SQLite DB Server
  3. # 12
Ivan Smahin Accepted Answer
Why do you think it is related to WAL?
Comment
There are no comments made yet.
  1. more than a month ago
  2. as SQLite DB Server
  3. # 13
Beatrix Willius Accepted Answer
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.
Comment
There are no comments made yet.
  1. more than a month ago
  2. as SQLite DB Server
  3. # 14
Ivan Smahin Accepted Answer
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?
Comment
There are no comments made yet.
  1. more than a month ago
  2. as SQLite DB Server
  3. # 15
Beatrix Willius Accepted Answer
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.
Comment
There are no comments made yet.
  1. more than a month ago
  2. as SQLite DB Server
  3. # 16
Ivan Smahin Accepted Answer

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
Comment
There are no comments made yet.
  1. more than a month ago
  2. as SQLite DB Server
  3. # 17
Ivan Smahin Accepted Answer

SELECT docid FROM bodyindex WHERE messagebody MATCH 'some'

-- docid
-- 1


It seems it worls
Comment
There are no comments made yet.
  1. more than a month ago
  2. as SQLite DB Server
  3. # 18
Ivan Smahin Accepted Answer

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"
Comment
There are no comments made yet.
  1. more than a month ago
  2. as SQLite DB Server
  3. # 19
Ivan Smahin Accepted Answer

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"
Comment
There are no comments made yet.
  1. more than a month ago
  2. as SQLite DB Server
  3. # 20
  • Page :
  • 1
  • 2


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

Categories

Announcements & News
  1. 0 subcategories
Valentina Studio
  1. 2 subcategories
Valentina Server
  1. 4 subcategories
Valentina Database ADK
  1. 0 subcategories
Valentina Reports ADK
  1. 0 subcategories
Other Discussions
  1. 2 subcategories
BETA Testing
  1. 0 subcategories
Education & Research
  1. 0 subcategories