VB SQLite Library (COM-Wrapper)

[Krool]

Update released.

The sqlite3 c source was upgraded from version 3.39.3 (2022-09-05) to 3.41.2 (2023-03-22).

[Krool]

Update released.

SQLiteCursor class is not auto-initialized anymore with sqlite3_step.
Use .MoveFirst to ensure it is initialized, otherwise an error will occur. (behavior break; as normally .MoveFirst could be skipped)

This allows to set parameters without prior executing of the statement. Also the statement is not locking something when the Cursor is created but ran later on.
The previous behavior was kind of mis-behavior.

[Krool]

Small minor adjustment to recent change of SQLiteCursor class.

Instead of using a private flag of whether to throw an error or not (= not initialized) the status counter SQLITE_STMTSTATUS_RUN (sqlite3_stmt_status) is now used.
The counter will be reset to 0 when clearing or binding new parameters. The counter will never be 0 when sqlite3_step is called at least once.

[Krool]

Update released.

The sqlite3 c source was upgraded from version 3.41.2 (2023-03-22) to 3.43.1 (2023-09-11).

[yokesee]

Thanks Krool

[Krool]

I accidentally compiled yesterday's new release w/o binary compatibility...

Also in the new sqlite3 c source there is a function sqlite3AtoF which has problems from converting unsigned __int64 to double due to the fact that MSVC 6.0 does not support that conversion.
So, from now on the MSVC v140_xp compiler is used.EDIT: postponed until really necessary ;-)

Update re-released.

[yokesee]

In this version error on compiler linker.
in the others no error.
https://pastebin.com/JEmT2Xv8

[Krool]

In this version error on compiler linker.
in the others no error.
https://pastebin.com/JEmT2Xv8

Did you update or re-download the .vbp ?

[VBCompiler]
LinkSwitches=kernel32.lib libvcruntime.lib libucrt.lib /OPT:NOREF /OPT:NOWIN98 /FORCE:MULTIPLE
LinkBefore=replace_cobj.bat

[yokesee]

Sorry, the problem is that I don't have visual studio installed on this machine.
I thought that was to compile the c code.

[Krool]

I might use following helper function to workaround the issue on msvc 6.0. (Vice versa double to unsigned __int64 works though out of the box)

static double uint64_to_double(u64 u)
{
i64 s = (u & 0x7FFFFFFFFFFFFFFi64);
double dbl = (double)s;
if (u & 0x8000000000000000i64)
dbl += (double)0x8000000000000000i64;
return dbl;
}

static LONGDOUBLE_TYPE uint64_to_ldouble(u64 u)
{
i64 s = (u & 0x7FFFFFFFFFFFFFFi64);
LONGDOUBLE_TYPE ldbl = (LONGDOUBLE_TYPE)s;
if (u & 0x8000000000000000i64)
ldbl += (LONGDOUBLE_TYPE)0x8000000000000000i64;
return ldbl;
}

[Krool]

Update released.

1.2.19 back to MSVC 6.0 using helper functions uint64_to_double/uint64_to_ldouble.

sqlite3.c source slightly modified in function sqlite3AtoF to use

uint64_to_double(s)

instead of

(double)s

for assigning double var.

[yokesee]

thank you very much and sorry for bothering

[xiaoyao]

libmysql.dll, sqlite3.dll
What is the way to package COM DLL, and then operate like ADO, you can query, modify the data.

set rs=new sqlite3_com.openrecordset("select * from usertable")
debug.print rs("username") & "," & rs("password")
editdata by fieldname and value
rs("password")="newpass"
rs.update

rs(1)="newpass2"
rs.update

You can implement this function is perfect, I mentioned this in the previous reply. Basically, it's based on querying the primary key of a table. For example, currently editing article 5,ID field =995.
Originally, the UPDATE was to use the SQL sentence "UPDATE TABLE **".
The Sqlite operation in rc6.dll implements the function of editing field values (can be read and modified at the same time).

The principle of how to simulate ADO is:
rs.edit 'Takes the primary key id=995
'Adds multiple fields and values to the dictionary object
rs("password")="newpassword"
rs("info")="newinfo"
'Construct an UPDATE SQL sentence and execute it, returning a success status
'update *** where id=995
rs.update

[xiaoyao]

Update released.

The sqlite3 c source was upgraded from version 3.31.1 (2020-01-27) to 3.34.1 (2021-01-20).

Biggest benefit for new sqlite3 version (IMO) is the support for UPDATE-FROM sql statements. (Possible since version 3.33)

A "corner case" bugfix of version 3.35.0 got included in this build. (Incorrect optimization of IN operator)

Function sqlite3WhereCodeOneLoopStart:

    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.

can try ,

ub=100
redim arr1(100)
if datacount>ub then
   ub=ub*2
   redim preserve arr1(ub)
end if

[xiaoyao]

Ok, I'll make a test project.



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.

you can try twinbasic to update this project to x64,so it can support 10GB,100gb,but
But an efficient database is certainly not achieved by loading 2GB,10GB of all data into memory. Normal words are to create multiple files or memory mapping, indexing, multi-threaded search and other methods to improve speed.

[xiaoyao]

copy .. \.. \Bin\sqlite3win32helper.cobj Bin\sqlite3win32helper.obj
copy .. \.. \Bin\sqlite3win32stubs.cobj Bin\sqlite3win32stubs.obj

My editing experience:
1, add Add LIB to the environment variable
2,... \.. \ directory how to deal with, more difficult, I changed
Perhaps such a project directory structure is convenient:
VBSQLite12\ActiveX DLL
VBSQLite12\Standard EXE Demo
VBSQLite12\sqlite3win32

Adding a project group file would be even better.

=========================
testall.vbg

VBGROUP 5.0
StartupProject=Standard EXE Demo\VBSQLiteDemo2.vbp
Project=ActiveX DLL\VBSQLite12.vbp

VBSQLiteDemo2.vbp

Type=Exe
Reference=*\G{00020430-0000-0000-C000-000000000046}#2.0#0#C:\Windows\SysWOW64\stdole2.tlb#OLE Automation
Reference=*\A..\ActiveX DLL\VBSQLite12.vbp

mainform.frm

Private Declare Function SetCurrentDirectoryW Lib "kernel32" (ByVal lpPathName As Long) As Long

Private Sub Form_Load()
    Dim DllPath As String, NowPath As String
    DllPath = App.Path & "\"
    SetCurrentDirectoryW StrPtr(DllPath)
End Sub

=========================
At the same time, you need administrator mode permission to run vb6.exe to compile successfully.
sqlite3win32helper.cobj is 1.7MB.cobj links to very little content, right?
It took me more than an hour to successfully compile, it is still very difficult, maybe 80% of people have failed.
--------------------
Of course, it is better to have a way to automatically download 3 projects from 3 different sites.
VB6IDELinkerAddin.zip
VBSQLite12.zip
sqlite3win32.zip
Then automatically decompress to the corresponding directory, so that a key to run the compilation successfully, so that the most convenient.
= = = = = = = = = = =
This is how package managers like NUGET,NPM.EXE work.
Maybe in another 2 years, 5 years someone can make a VB6 package manager, online upgrade components, module management, source code management software.
At the same time support GITHUB upload, download and other source management functions.

[xiaoyao]

Replaces OBJ files generated by 2 modules of VB6
500 constant declarations
1300 API declarations, static linking with VC++ COBJ? What's the benefit?

sqlite3win32 is only needed for compiling the Ax-DLL. The compiled Ax-DLL doesn't have any dependency, because sqlite3win32 was then compiled into it.

Perhaps this method can be used to write VC++ websocket.dll, 7zip.dll source code engineering compiled into DLL,COBJ file,
Then add to the VB6 project, compiled into EXE or com dll does not need to bring another DLL?

sqlite3win32.dll 972KB,VBSQLite12.DLL only 160KB, is my understanding wrong?

sqlite3win32.dll is essential, may also need to install the VC2015 operation and library, using this method is equivalent to static compilation of VC2015 some functions.
This allows you to run without installing the VC2015 runtime

[Krool]

Rollback to 1.2.18 (sqlite3 c source 3.41.2)

Reason was due to facing issues with ROUND() and SUM(), but on only some lines in a query and only when attached to another DB.. very obscure.
Therefore this rollback, let's try again in another version. Also I noticed in the sqlite.org timeline that they already tackle the "old MSVC" compiler issues in regards to unsigned __int64 and double.

[xiaoyao]

Can I encapsulate another VC + + web socket. DLL? Powerful ah, the data will not be wrong, stable operation. The volume is relatively small. It is best to have no other dependencies such as the VC runtime. Support win7 and above operating systems.

[Krool]

Update released.

The sqlite3 c source was upgraded from version 3.41.2 (2023-03-22) to 3.43.2 (2023-10-10).

EDIT: Updated to 3.44.2 (2023-11-24)

[Nouyana]

Hi, Krool!

What does the negative value of the SQLiteDataSet.Position property mean? It is -3 in my case.

Edited:

Got it.

Public Property Get Position() As Long
If PropRecordCount(0) > 0 Then
    If PropPosition > PropRecordCount(0) Then
        Position = -3
    ElseIf PropPosition < 1 Then
        Position = -2
    Else
        Position = PropPosition
    End If
Else
    Position = -1
End If
End Property

[Nouyana]

Hi, all! I want to share three wrappers, which I've created to use with Krool's objects. I hope it will be convenient. Just replase the MainForm.frm in the Krool's Demo project.

SQLite_wrappers.zip

Krool, can you, please, explain what for do we need the SQLiteCursor object? What is it?

v.1.2 Updates:

Krool, thank you for your answer below this post.

Features of my wrappers:
- Added support for LightWeight type of Recordset (SQLiteCursor). Use LightWeight=True parameter of the OpenRecordset method.
- Added methods: AddNew, GetRows and Delete. All recordsets are being refreshed after updates.
- Added OrderBy property
- Added Find sub and IsFound property
- Added BeginTrans, CommitTrans and Rollback subs

v.1.3 Updates:
- Added Optional UpdateAll parameter to the Execute subs (SQL_Database and SQL_Command)
- Added AllowRefreshing property (SQL_Recordset, extremely speed up)
- Added Edit property (SQL_Recordset)

v.1.4 FINAL (No more updates):
- All recordsets stop refreshing during transaction;
- SQL_Recordset: Major bugfixes in the AddNew, Edit and Find subs. The first two now use SQL_Command with parameters. The NormalizeType private function converts Date and Currency types to Double. I store it as REAL.
- SQL_Recordset: Added IsLightWeight property. You may change the recordset type at run-time.
- SQL_Command: Added OpenRecordset method. Use it this way:

   Dim cmd As SQL_Command
   Set cmd = oDBMain.CreateCommand("SELECT * FROM my_table WHERE my_field < @val")
   cmd![@val] = 8356

   Dim rs As SQL_Recordset
   Set rs = cmd.OpenRecordset
   With rs
      .MoveFirst
      Do Until .EOF
         Debug.Print !my_field
         .MoveNext
      Loop
   End With

[Krool]

Krool, can you, please, explain what for do we need the SQLiteCursor object? What is it?

It's like a ForwardOnly cursor to save memory as the records are fetched only on the current line on each .MoveNext.
However, you can reset to start again with .MoveFirst.
.MoveFirst must be used to initialize the cursor.

[Nouyana]

This is my first experience of SQLite usage. I don't know, if it really so slow, or I can't use it right way.

I've created a database with one table:

CREATE TABLE `t_SPR_DATES` (
	`DATE_ID`	INTEGER PRIMARY KEY,
	`DATE_DATE`	REAL NOT NULL UNIQUE,
	`DATE_YEAR`	INTEGER NOT NULL,
	`DATE_MONTH`	INTEGER NOT NULL,
	`DATE_DAY`	INTEGER NOT NULL,
	`DATE_STRING`	TEXT NOT NULL,
);

CREATE UNIQUE INDEX DATE_DATE On t_SPR_DATES (DATE_DATE);

CREATE INDEX DATE_YEAR On t_SPR_DATES (DATE_YEAR);

And I'm trying to fill it with 15 000 records. It takes a couple of minutes. Can somebody do it faster? I used BEGIN EXCLUSIVE and COMMIT. With DAO databases it takes a second. I'm afraid to even try to use this in real applications. I usually process several million records. I wanted to try switching to SQLite because the documentation says it can handle almost unlimited number of records. But I'm not sure if it really possible.

[jpbro]

I just tried your schema and inserted 15000 records in under 200ms. Do you have antivirus running? If so, try disabling it and see if there's any difference. If that doesn't help, please post your code.

[Nouyana]

I just tried your schema and inserted 15000 records in under 200ms. Do you have antivirus running? If so, try disabling it and see if there's any difference. If that doesn't help, please post your code.

VBSQLite12Test.zip

Thank you for your answer. The main job is in the SQL_MAIN.GetDatabaseMain. I have no antivirus. WinXP SP3.

[Nouyana]

I've found the problem. I always refresh my recordsets after Executing something if AffectedRows returns > 0.
I believed that AffectedRows = 0 until COMMIT. I was wrong.

Krool, is it by design?

[jpbro]

The issue is the event being raised in SQL_Recordset.Execute here:

Public Sub Execute(SQL As String)
   Connection.Execute SQL
   If Connection.AffectedRows Then RaiseEvent NeedRefreshRecords  ' This is slow - it's being raised every Execute
End Sub

Comment out that line and the inserts run quickly.

[jpbro]

One way around this is to add an Enabled property to you SQL_Recordset class, and only raise NeedRefreshRecords if Enabled = True. When you know you are going to be inserting a lot of records, set .Enabled = False, then .Enabled = True when you are done. I've attached an example using your project.

VBSQLITE_disabled.zip

[Nouyana]

One way around this is to add an Enabled property to you SQL_Recordset class, and only raise NeedRefreshRecords if Enabled = True. When you know you are going to be inserting a lot of records, set .Enabled = False, then .Enabled = True when you are done. I've attached an example using your project.

Thank you, jpbro. Yes, this is a god idea. I've implemented it in the SQL_Recordset.AllowRefreshing property, because SQL_Database may have a lot of recordsets. SQL_Recordset now refreshes itselves after AddNew, Delete or Edit (new property). And SQL_Database now refreshes its children only when custom QSL statement or SQLiteCommand was executed.

SQLite wrappers were updated. Now you may use it like that:

With rs_SPR_DATES
   .AllowRefreshing = False
   For nDateId = (DATE_BEG_LNG - DATE_OFFSET) To (DATE_END_LNG - DATE_OFFSET)
      ' ...
      .AddNew "DATE_ID", nDateId, _
              "DATE_DATE", nDateOffset, _
              "DATE_YEAR", Year(nDateOffset), _
              "DATE_MONTH", Month(nDateOffset), _
              "DATE_DAY", Day(nDateOffset), _
              "DATE_STRING", sDateString
      ' ...
   Next
   .AllowRefreshing = True
End With

But it's not enough. This example fills the table with constants, and it works fine now. But other tables have a lot of relationships. Usually it's one-to-many type. And usually I work with these types of operations:

1. I find a record in the "one-side" table by Name and get its ID. If I haven't found record by Name, I add the new one. "One-side" recordsets are small, but they should be always updated. About 90% tables are on the "one-side" of the relation.

2. I fill the "many-side" tables with millions of records. Here we can turn off refreshing, and all should works fine (I'll test it in a week).

3. I find a set of records (usually 10 to 100) in the "many-side" table by some criterion, then analyse and edit them. Using DAO the fastest way was to sort it by complex index. But, may be, with SQLite I will create a new short recordset with records I need. Usually I mark processed records somehow, and then I query another set of records skiping the marked ones. Therefore my "many-side" recordsets should be updated at least (5 000 000 / 100 = ) 50 000 times or even more. I'll try to use SQLiteCursor (LightWeight type of my SQL_Recordset object) with these tables.

Conclusion. There are a lot of refreshing operations in my code. Using DAO I have the TableType recordsets which are always updated and work very fast. In fact this is the fastest file database I know. But using Krool's VB SQLite wrapper I need instrinct and very fast Refresh method for both types of datasets (SQLiteCursor and SQLiteDataSet) which will allow me to avoid fully recreating recordset objects. Or maybe Krool can develop a TableType recordset analogue.

[Nouyana]

Can anyone explain me how to save and restore a Double value in a SQLite database without loss?

[jpbro]

Can anyone explain me how to save and restore a Double value in a SQLite database without loss?

Use "REAL" for your field data type. e.g.:

CREATE TABLE IF NOT EXISTS my_table (id INTEGER PRIMARY KEY, someval_ieee REAL)

[Nouyana]

Use "REAL" for your field data type. e.g.:

CREATE TABLE IF NOT EXISTS my_table (id INTEGER PRIMARY KEY, someval_ieee REAL)

The main condition is to do this without loss.

Option Explicit
Private DBConnection As SQLiteConnection

Sub Main()
   Const SQL = "CREATE TABLE IF NOT EXISTS my_table " & _
               "(id INTEGER PRIMARY KEY, someval_ieee REAL)"
   With New SQLiteConnection
      On Error Resume Next
      Kill App.Path & "\main.db"
      .OpenDB App.Path & "\main.db", SQLiteReadWriteCreate
      .Execute SQL
      On Error GoTo 0
      If .hDB <> 0 Then Set DBConnection = .Object
   End With
   If Not DBConnection Is Nothing Then Call TestDoubleValues
End Sub

Private Sub TestDoubleValues()
   On Error GoTo CATCH_EXCEPTION
   Dim OriginalValue As Double
   Dim ReturnedValue As Double
   Dim DataSet       As SQLiteDataSet
   Dim SQL           As String

10 OriginalValue = 1 / 3
20 SQL = "INSERT INTO my_table (someval_ieee) VALUES ('" & OriginalValue & "')"
25 DBConnection.Execute SQL
30 Set DataSet = DBConnection.OpenDataSet("SELECT id, someval_ieee FROM my_table")
40 DataSet.MoveFirst
50 ReturnedValue = DataSet!someval_ieee

   Debug.Print (OriginalValue = ReturnedValue)  ' False
   Debug.Assert (OriginalValue = ReturnedValue)

   Exit Sub
CATCH_EXCEPTION:
   VBA.MsgBox Erl & ": " & Err.Description, vbCritical + vbOKOnly
End Sub

I think, we need a formatted string here. Something like:

 Format$(OriginalValue, "0.000000000000000E+00")

And, may be, field type should be TEXT, not REAL.

[Nouyana]

Hi, Krool!

You use TextCompareLike function only once (in the SQLiteBase.SQLiteFunctionLike). You may safely use this code instead:

    If UCase$(szString) Like UCase$(szPattern) Then
        stub_sqlite3_result_int pCtx, 1
    Else
        stub_sqlite3_result_int pCtx, 0
    End If

[Arnoutdv]

The main condition is to do this without loss.

Both SQLite real and VB6/A double are IEEE 64-bit (8-byte)

REAL: The value is a floating point value, stored as an 8-byte IEEE floating point number.

Double Data Type (Visual Basic)
Holds signed IEEE 64-bit (8-byte) double-precision floating-point numbers that range in value from -1.79769313486231570E+308 through -4.94065645841246544E-324 for negative values and from 4.94065645841246544E-324 through 1.79769313486231570E+308 for positive values. Double-precision numbers store an approximation of a real number.

Maybe something else is not correct?

I think, we need a formatted string here. Something like:

 Format$(OriginalValue, "0.000000000000000E+00")

And, may be, field type should be TEXT, not REAL.

That's a very very bad idea!

[Nouyana]

Both SQLite real and VB6/A double are IEEE 64-bit (8-byte)

Yes, I read it.

Maybe something else is not correct?

Yes, maybe. I'm trying to find it.

That's a very very bad idea!

I know, because it doesn't work.

So, how would you do it?

[Arnoutdv]

When you store numbers as text then you can't hardly do anything with it.
You first have to convert it back to a numeric variable.
And when stored as text you can get all kind of parsing problems, think of local settings of each computer

Just store the values as a real value.

Later today I will do some tests on a SQLite db myself.

[Nouyana]

When you store numbers as text then you can't hardly do anything with it.
You first have to convert it back to a numeric variable.
And when stored as text you can get all kind of parsing problems, think of local settings of each computer
Just store the values as a real value.

You can't pass anything but text with SQL statement.

[Arnoutdv]

If you use plain textual SQL statements you mean.
If you use parameterized queries then you should be able to store values directly, without text conversion

[Nouyana]

If you use plain textual SQL statements you mean.
If you use parameterized queries then you should be able to store values directly, without text conversion

Fabulous! It works! And what about the Currency type? What type of field should we use to store it? TEXT?