Bugfix in SQLiteCommand for the SetParameterValue method.
The VarType was not correctly when passing VT_DISPATCH that has a DISPID_VALUE.
Simple scenario where this can be a problem is when passing a DataSet column as parameter for another command.
Code:
Command.SetParameterValue 100, DS![My Column]
Problem was that I previously extracted the VarType with CopyMemory (2 bytes).
That was needed to check for VT_BYREF necessary for BLOB, byte streams.
However, VBA.VarType() checks deeper when passing a vbObject (VT_DISPATCH) for a DISPID_VALUE.
Thus I switched over to VBA.VarType() instead of CopyMemory.
So, in worst case (BLOB, byte stream) the VarType is double checked, once with VBA.VarType() and then again with CopyMemory (2 bytes)..
Thank you for your reply. What I want to express is that after opening the .sqlite file, you can list all the tables in it and then select one of them to open.
Last edited by ChenLin; Apr 29th, 2020 at 07:20 PM.
Reason: I have found a way, I will upload the demo later.
Since the attachment cannot be uploaded all the time, just send the relevant code.
Code:
Private Sub CommandConnect_Click()
If DBConnection Is Nothing Then
With New SQLiteConnection
On Error Resume Next
Dim s As String
s = AppPath() & "\data.sqlite" 'Data to be tested
.OpenDB s, SQLiteReadWrite
If Err.Number <> 0 Then
Err.Clear
If MsgBox("Test.db does not exist. Create new?", vbExclamation + vbOKCancel) <> vbCancel Then
.OpenDB s, SQLiteReadWriteCreate
.Execute "CREATE TABLE test_table (ID INTEGER PRIMARY KEY, szText TEXT)"
End If
End If
On Error GoTo 0
If .hDB <> 0 Then
Set DBConnection = .Object
CommandInsert.Enabled = True
List1.Enabled = True
ListAllTable
End If
End With
Else
MsgBox "Already connected.", vbExclamation
End If
End Sub
Private Sub Requery(tlbName As String)
On Error GoTo CATCH_EXCEPTION
List1.Clear
Dim DataSet As SQLiteDataSet
Set DataSet = DBConnection.OpenDataSet("SELECT * FROM " & tlbName & " limit 0,100")
DataSet.MoveFirst
Dim I As Integer, j As Long, k As Long
LynxGrid1.ClearAll
Dim ss As String
For I = 1 To DataSet.Columns.Count
LynxGrid1.AddColumn DataSet.Columns(I).Name, 1000
Next I
DataSet.MoveFirst
For j = 1 To DataSet.RecordCount - 1
For k = 1 To DataSet.Columns.Count
ss = ss & vbTab & DataSet.Columns(k).Value
Next k
LynxGrid1.AddItem Replace(ss, vbTab, "", 1, 1)
DataSet.MoveNext
Next j
LynxGrid1.Redraw = True
LynxGrid1.Refresh
Exit Sub
CATCH_EXCEPTION:
MsgBox Err.Description, vbCritical + vbOKOnly
End Sub
Private Sub ListAllTable()
On Error GoTo CATCH_EXCEPTION
List2.Clear
Dim DataSet As SQLiteDataSet
Set DataSet = DBConnection.OpenDataSet("select name from sqlite_master where type='table' order by name")
DataSet.MoveFirst
Dim I As Integer
For I = 0 To DataSet.RecordCount - 1
List2.AddItem DataSet.Columns(1)
DataSet.MoveNext
Next I
Exit Sub
CATCH_EXCEPTION:
MsgBox Err.Description, vbCritical + vbOKOnly
End Sub
Last edited by ChenLin; Apr 29th, 2020 at 08:15 PM.
Reason: Attachment cannot be uploaded
if( iLevel>0 && (pLoop->wsFlags & WHERE_IN_SEEKSCAN)!=0 ){
/* In case OP_SeekScan is used, ensure that the index cursor does not
** point to a valid row for the first iteration of this loop. */
sqlite3VdbeAddOp1(v, OP_NullRow, iIdxCur);
}
Also optimized SQLiteDataSet to improve performance for SELECT statements.
The costly 'ReDim Preserve' is called less times now. (array bump of 10 is defined)
Previously there was no "array bump". Means by each row a 'ReDim Preserve' was applied.
The CONCAT() and CONCAT_WS() function are now included by statically linking to the concat c extension.
The CONCAT() function does certainly the same as the concatenation operator || to concatenate strings into one.
The two examples below will produce the same output 'SQLite concat'.
The main "advantage" of the CONCAT() function is when referring to field names and any NULL value is contained.
Unlike the concatenation operator ||, the CONCAT() function ignores NULL arguments.
The CONCAT_WS() function concatenates strings into one separated by a particular separator. (ws stands for with separator)
So..
Code:
SELECT CONCAT_WS(', ', 'SQLite', 'concat');
will output to 'SQLite, concat'.
Also here NULL values will be ignored. Even if the seperator value is NULL.
When CONCAT() has no args the return value is NULL.
When CONCAT_WS() has only 1 or no arg the return value is NULL.
PS: SQLite has already in-built GROUP_CONCAT() aggregate function so no change there.
Included the SetProgressHandler method which registers/unregisters a progress handler callback.
For this the new ISQLiteProgressHandler class needs to be implemented on the receiver.
This is useful to keep a GUI "alive".
Code:
SetProgressHandler(ByVal Handler As ISQLiteProgressHandler, [ByVal VMInstructions As Long = 100])
Code:
Private Sub ISQLiteProgressHandler_Callback(Cancel As Boolean)
' The SetProgressHandler method (which registers this callback) has a default value of 100 for the
' number of virtual machine instructions that are evaluated between successive invocations of this callback.
' This means that this callback is never invoked for very short running SQL statements.
' An example use case for this handler is to keep the GUI updated and responsive.
' The operation will be interrupted if the cancel parameter is set to true.
' This can be used to implement a "cancel" button on a GUI progress dialog box.
DoEvents
End Sub
To unregister the progress handler callback set the Handler to Nothing.
Code:
DBConnection.SetProgressHandler Nothing
Included the BackupDB method which backups (copies) a SQLite database between two SQLite database connections.
Code:
BackupDB(ByVal Destination As SQLiteConnection, [ByVal DestinationDBName As String = "main"], [ByVal SourceDBName As String = "main"])
Included the SharedCache parameter in the OpenDB method. (optional)
Included the LastInsertRowID property in the SQLiteConnection class.
Included the AutoCommit (read-only) property in the SQLiteConnection class.
LIKE search somewhat broken in latest 3.34.1 sqlite3win32.dll / SQLite11.dll version?
Code:
Dim DataSet As SQLiteDataSet
'Description field (typename) nvarchar(250)
'Set DataSet = PartsDBConnection.OpenDataSet("SELECT Description FROM Info WHERE Description ='192465' LIMIT 1;") 'Works ok in 3.34.1 -> returns numrows = 1
Set DataSet = PartsDBConnection.OpenDataSet("SELECT Description, FROM Info WHERE Description LIKE '%192465%' LIMIT 1;") 'No worky in 3.34.1 -> returns numrows = 0
DataSet.MoveFirst
numrows = DataSet.RecordCount
What am i missing?
Last edited by Tech99; Apr 20th, 2021 at 10:20 PM.
LIKE search somewhat broken in latest 3.34.1 sqlite3win32.dll / SQLite11.dll version?
Code:
Dim DataSet As SQLiteDataSet
'Description field (typename) nvarchar(250)
'Set DataSet = PartsDBConnection.OpenDataSet("SELECT Description FROM Info WHERE Description ='192465' LIMIT 1;") 'Works ok in 3.34.1 -> returns numrows = 1
Set DataSet = PartsDBConnection.OpenDataSet("SELECT Description, FROM Info WHERE Description LIKE '%192465%' LIMIT 1;") 'No worky in 3.34.1 -> returns numrows = 0
DataSet.MoveFirst
numrows = DataSet.RecordCount
What am i missing?
The LIKE operator is now 1:1 VB-Ish.
Means '%' will not be replaced to an '*' anymore.
This enables to use '%' as a literal and '[*]' as literal for '*'. (No wildcard, escape clause)
Don't know others, but i would prefer database native engine dialect, be it; ansi, postgre, oracle, sql server, access etc.
The GLOB sql function also uses * instead of %.
The problem with the native sqlite LIKE operator is the problem of unicode no-case handling.
So it is necessary to resort to VB's Like operator.
And mimic '%' in VB's Like operator has edge case side effects when wanting to use % as literal.
Also the sqlite LIKE sql function has a 3 arg variant with an escape clause. This 3 arg variant is disabled now also in VBSQLite, because the VB's Like operator is 2 arg only.
The VB's Like operator is even better as it can handle multiple escape clauses put in [].
Sorry for the quirk caused but this is the only straight forward solution.
Word of warning about the VB LIKE operator - you should be careful using it anywhere the user supplies the text for the pattern, especially true for any code that is Internet facing. Specially crafted Like patterns will crash your application, and it is theoretically possible that they could be crafted to get privilege escalation. Microsoft security has confirmed the bug, but have decided not to fix it because they have decided that there is too great a risk to accidentally breaking existing apps. They might change their tune if an actual exploit appears in the wild, but thankfully I don't think this is the case (yet).
Olaf has worked around the bug in RC5/RC6 - it might be prudent for Krool to do the same.
hoe yo load memory sqlite database from vb6 resfile?
like show png file from res.
It is best to add a function to load from the memory address to the memory database.
Originally a function is: load files to the memory database. The file itself will not be modified, which is equivalent to read-only, only the tables in the memory database are modified. It may also be necessary to save the settings in a hard disk file(This is difficult, don't care about it).
Code:
sqlitehandle = sqlite3_open(":memory:", pdb)
sqlite3_open(sPath & "data.sqlite", pdb)
dim buffer() as byte
buffer= read byte from :sPath & "data.sqlite"
sqlite3_openMemory(varptr(buffer(0)), pdb)
sqlite3_open(sPath & "data.sqlite", pdb)
What I mean is how to open the database file in the resource or memory address (read-only) without a hard disk database file
If you need to modify, I can also write a memory class. When the file increases, expand the capacity of this memory address. When modifying the data to be written, locate the corresponding address and write.
Last edited by xiaoyao; May 11th, 2021 at 07:16 AM.
xiaoyao, your texts are so awful that I am not able to understand these. So I can't respond to you, sorry!
I want to load the database without a hard disk file, such as downloading a byte array or resource file from the Internet.
Maybe this is too difficult. The most appropriate approach is estimated to be to HOOK the file read and write function of this process, so that you can simulate a virtual file pointer and jump to the memory address.
Use this method to open an abc.doc file with WORD. In fact, this file is 0 bytes, or use code to generate an empty file with the same size as a real DOC file.
The actual content is read from memory.
According to me this is not possible with the current SQLite engine.
You can load a disk file to memory, you can safe a memory db to a file, but there are no from and to memory
The compilation fails, and there is a lot of code. It is difficult and takes a lot of time to successfully apply.
Could you give it a try to build a lightweight ADO recordset to encapsulate SQLite API? It only needs 12 interface processes.
like this:
RC5 Sqlite Like Adodb.Connection/Adodb.RecordSet(WithOut Reg Com Dll)-VBForums https://www.vbforums.com/showthread....t-Reg-Com-Dll)
Sometimes, some lightweight versions are needed to implement the simplest query, reading and modification.
Such as reading data
A=rs ("field 1") value
B=rs ("field 2") value
Modify field value
RS ("field 1") = "A1"
RS ("field 2") = "TTT"
-------------
Self writing class connection and recordset, instead of adodb For objects such as recordset, only 12 interfaces (or methods) need to be implemented for normal mode calls.
Four interfaces of connection: createnewdb, opendb, execute, openrecordset
8 interfaces of recordset: openrecordset, get item (let item), addnew, updatebatch, RecordCount, EOF, MoveNext
Code:
Sub Test_Like_Adodb_RecordSet()
ShowErr = True
dBFile = App.Path & "\Sqlite_Test3.db"
Dim Cnn As New Connection
MsgBox Cnn.cConnectionA.Version
If Dir(dBFile) = "" Then 'Create New Sqlite DB
Cnn.CreateNewDB dBFile
Else
Cnn.OpenDB (dBFile)
End If
Dim RS As Recordset
On Error Resume Next
Set RS = Cnn.OpenRecordset("select * FROM UserTable")
If ErrNumber <> 0 Then
Err.Clear
Set RS = Nothing
'New Table
Cnn.Execute "CREATE TABLE UserTable (UserName TEXT PRIMARY KEY, ID INTEGER, Info TEXT)"
'Set RS = New Recordset
'RS.OpenRecordset "select * FROM UserTable", Cnn
Set RS = Cnn.OpenRecordset("select * FROM UserTable")
RS.AddNew
RS("UserName") = "name1"
RS("ID") = 100
RS("Info") = "A"
RS.AddNew
RS("UserName") = "name2"
RS("ID") = 200
RS("Info") = "B"
RS.UpdateBatch
RS.AddNew
RS("UserName") = "name2"
RS("ID") = 201
RS("Info") = "Bb"
If Not RS.UpdateBatch Then
MsgBox "key value err:" & ErrDescription
End If
'Set RS = Nothing
Set RS = New Recordset
RS.OpenRecordset "select * FROM UserTable", Cnn
End If
'Read Rows ,Data
MsgBox "RecordCount=" & RS.RecordCount & ", Field:UserName=" & RS("UserName") & ", Field:ID=" & RS("ID")
'Edit RecordSet
Set RS = New Recordset
RS.OpenRecordset "select * FROM UserTable", Cnn
RS("ID") = RS("ID") + 1
RS.UpdateBatch
'Read Rows ,Data
Set RS = New Recordset
RS.OpenRecordset "select * FROM UserTable", Cnn
MsgBox "After Edit,RecordCount=" & RS.RecordCount & ", Field:UserName=" & RS("UserName") & ", Field:ID=" & RS("ID")
RS.Delete
RS.Delete
If RS.Delete = False Then
'MsgBox "Delete err:" & ErrDescription
End If
Set RS = Nothing
Set Cnn = Nothing
End Sub
Last edited by xiaoyao; Jun 30th, 2022 at 12:18 AM.
Re:The SQLiteDataSet object is read-only and meaningful for SELECT statements only.
You can fix and add code support for RS.UPDATE
Code:
DIM Rs as Recordset
Set RS = New Recordset
RS.OpenRecordset "select * FROM UserTable", Cnn
RS("ID") = RS("ID") + 1
RS.UpdateBatch
Code:
Public Property Get Item(Field As Variant) As Variant
'Read Field Value/读取字段值
Item = cRecordset1(Field)
End Property
Public Property Let Item(Field As Variant, ByVal vNewValue As Variant)
'Edit Field Value/修改字段值
cRecordset1(Field) = vNewValue
End Property
RS.EditField("ID") = 11
RS.EditField("Info") = "info edited"
If RS.UpdateEx("UserTable", "UserName='" & RS("UserName") & "'") Then
MsgBox "Update One Recordset ok"
End If
Recordset.cls
Code:
Public FieldList As New Dictionary
Public Cnn As Connection
Public Property Let EditField(Field As String, ByVal vNewValue As Variant)
'Edit Field Value/修改字段值
On Error GoTo Err
Dim Field1 As New FieldClass
Field1.FieldName = Field
Field1.FieldValue = vNewValue
Field1.TypeName = TypeName(vNewValue)
'InputBox "", "Field1.TypeName ", Field1.TypeName
Set FieldList(Field) = Field1
Exit Property
Err:
DoErr "Edit Field Value"
End Property
Function UpdateEx(table_name As String, WhereStr As String) As Boolean
'Update table_name
'SET column1 = value1, column2 = value2...., columnN = valueN
'WHERE [condition];
Dim i As Long, SQL As String
Dim SqlArr() As String, FieldCount As Long
Dim Field1 As FieldClass
Dim Fg As String
FieldCount = FieldList.Count
ReDim SqlArr(FieldCount - 1)
For i = 0 To FieldCount - 1
Set Field1 = FieldList.Items(i)
Select Case Field1.TypeName
Case "String"
Fg = "'"
Case "Date"
Case Else
Fg = ""
End Select
SqlArr(i) = Field1.FieldName & "=" & Fg & Field1.FieldValue & Fg
Next
SQL = "Update " & table_name & " SET " & Join(SqlArr, ",") & IIf(WhereStr <> "", " WHERE " & WhereStr, "")
MsgBox SQL
Set FieldList = New Dictionary
On Error GoTo Err
UpdateEx = Cnn.Execute(SQL)
Exit Function
Err:
Set FieldList = New Dictionary
DoErr "UpdateEx"
End Function
fieldClass.cls
Code:
Public TypeName As String
Public FieldName As String
Public FieldValue As Variant
Hello, I trying to convert code from DAO to VBSQLite and I'm wondering how to convert DAO's Seek method that works on indexed fields and is actually very fast (hundreds of times faster than DAO's FindFirst) but I don't know how to handle that.
If I have to make a new SQL query for every row that I need to fetch I guess it will be quite slower (it is a guess, I didn't test that still).
Any advice?
What would be the way to insert a new record into a table that has a BLOB type field (binary data in a byte array)?
An easy way is the Command class where you can pass a byte stream via SetParameterValue.
If that's not wanted you can "textize" your byte stream.
Example:
X'0102030405060708090a0b0c0d0e0f'
About your other question:
When I do heavy tasks with lot of ping pong queries I usually create a memory db and attach it with the current connection. Then backup the table into memory and process query/update and backup back to file.
But that's some kind of effort and pays of in huge number only and when the db is on a network drive etc.
In your case you only query or update as well?
Maybe you can first start to optimize "begin transaction" and "end transaction" so the file db journal is not updated by every single row but rather in a batch.
An easy way is the Command class where you can pass a byte stream via SetParameterValue.
If that's not wanted you can "textize" your byte stream.
Hello Krool, thank you.
No, I don't want to textize. That would use more space in the db and also would take some time to convert (and convert back in the retrievals).
How would be the way to store using SetParameterValue?
Lets assume I have these fields: The ID that is the primary key, and autoincrement integer, a second field that is a string, and a third field that is the BLOB.
I have the BLOB data in a bite array.
In my attempts I have something like this:
Code:
Dim SQLiteCon As SQLiteConnection
Dim MyString As String
Dim MyData() As Byte
Set SQLiteCon = New SQLiteConnection
SQLiteCon.OpenDB "D:\TestDB.db", SQLiteReadWrite
' ... fill the variables
SQLiteCon.Execute "INSERT INTO MyTable (String_Field, Blob_Field) VALUES ('" & MyString & "', " & MyData & ")"
That of course doesn't work.
Originally Posted by Krool
About your other question:
When I do heavy tasks with lot of ping pong queries I usually create a memory db and attach it with the current connection. Then backup the table into memory and process query/update and backup back to file.
But that's some kind of effort and pays of in huge number only and when the db is on a network drive etc.
In this case the database will be local.
Originally Posted by Krool
In your case you only query or update as well?
Maybe you can first start to optimize "begin transaction" and "end transaction" so the file db journal is not updated by every single row but rather in a batch.
I have been looking for a way to do that but for the other question, to be able to put one field at a time like DAO does, but could not find how to use transactions.
Back to the second question, I see that your component store all the queried data in memory (o that's what I understood).
In my case I'm trying to move to SQLIte because I have hit the 2 GB limit of Access. I have all the information in just one table. If I load all the data in memory (at least in the local memory) I'lll run out of memory.
IDK how DAO works but I think it does not use local memory, or maybe it caches in memory just some records but I guess not all.
I would like to make this program ready to handle large data, for example 10 GB or more. If the programs attempts to load that data into memory, not only local process memory, but all the machine memory could be exhausted (in case I was using it).
I was trying to make a test program to see how it performs making a new SQL query for each record retrieval (I confess that I don't have high hopes that it will perform as DAO's Seek, but I also hope I'm wrong), but got stuck with adding the BLOB field.
PD: worth to mention that I created an index for the field String_Field, and the data need to be retrieved but that field.
Thanks.
Last edited by Eduardo-; Jul 24th, 2022 at 11:05 AM.
"INSERT INTO MyTable (String_Field, Blob_Field) VALUES (?100, ?101)"
Then you use .SetParameterValue with 100 to pass a String and again with 101 to pass a byte stream
Then execute the command.
You can recycle the Command object and change only the parameters. That's more performant.
I hope you get it. I'm only on phone now so bear with me.
I was able to add data using the Command, but I must be doing something wrong because it was extremely slow.
I added 1,000,000 records and it took like 2 hours 40 minutes.
I also see that Vb6.exe was not using all the processing power, but just some minor percent. I was expecting it to be running at full processor power.
I'm using the VBSQLite ActiveX DLL compiled, and testing in IDE.
The code was like this:
Code:
Dim SQLiteCon As SQLiteConnection
Dim MyString As String
Dim MyData() As Byte
Dim MyCommand As SQLiteCommand
Set SQLiteCon = New SQLiteConnection
SQLiteCon.OpenDB "D:\TestDB.db", SQLiteReadWrite
Set MyCommand = SQLiteCon.CreateCommand("INSERT INTO MyTable (String_Field, Blob_Field) VALUES (?100 , ?101)")
for ...
MyCommand.SetParameterValue 100, MyString
MyCommand.SetParameterValue 101, MyData
MyCommand.Execute
Next
The data actually came from an Access database where I already have it.
Without the underlying test-code behind these results,
it's hard to give recommendations how to speed up SQLite properly.
(in all of my tests, it was faster than the JET-engine).
@Krool
Perhaps a little addition to your wrapper would be worthwhile:
- an SQLiteCursor-Class
99% of the implementation for that could be copied from your SQLiteCommand-Class...
One such step will then only perform "the Seek-Condition" (as formulated in the SQL-Select the statement is based on).
After such a "seek-step", the User should be free,
- to retrieve ColumnValues at the current Record-Position...
- via Extra-MethodCalls on that Cursor-Class,
- and not implicitely "all of them in each step" as your DataTable-Class currently does for the sake of convenience
Such a Cursor-Class would simulate the DAO-Seek-behaviour quite nicely (even if it is "forward-only")...
(for backward-searches on a given index, the underlying select would have to specify an ...Order By IndexedField Desc in the underlying SQL).
Edit: ... back to Eduardo...
Perhaps all you need (but were not offered with JET) - is an UpSert-Command?
(Insert when a unique Field is not existent, update instead when existent)...
It is described for SQLite here: https://www.sqlite.org/lang_upsert.html (see the examples in 2.1...)
As said, code is needed, which describes in more detail, what you currently "seek".
Olaf
Last edited by Schmidt; Jul 24th, 2022 at 07:49 PM.
It says "This Ax-DLL project is intended to get a VB-friendly COM-Wrapper for the SQLite library". Do I need to download both the StdCall SQLite library and the COM wrapper? Does the COM wrapper have StdCall SQLite library as a dependency?
Or in the COM Wrapper does it have all the SQLite stuff built-in?
Without the underlying test-code behind these results,
it's hard to give recommendations how to speed up SQLite properly.
(in all of my tests, it was faster than the JET-engine).
Ok, I'll make a test project.
Originally Posted by Schmidt
Edit: ... back to Eduardo...
Perhaps all you need (but were not offered with JET) - is an UpSert-Command?
(Insert when a unique Field is not existent, update instead when existent)...
It is described for SQLite here: https://www.sqlite.org/lang_upsert.html (see the examples in 2.1...)
As said, code is needed, which describes in more detail, what you currently "seek".
Olaf
If the record is existent I need to read the record, if it does not exist I need to calculate everything and save it in a new record. It is a cache.
But never have update records in this program. When something changed, I delete the old record and schedule a delayed action to insert a new one (after I have the new data).
But one warning, IDK how that Cursor would work, but if it needs to have all the data loaded in memory, it won't be possible.
I need this to work with millions records (they won't fit in the RAM).
That's why I need to change the database system, to be able to handle more records.
In the case of DAO/mdb the problem was the 2 GB file limit, but I don't think it has all the records in memory.
Last edited by Eduardo-; Jul 24th, 2022 at 09:42 PM.