VB SQLite Library (COM-Wrapper)

[Arnoutdv]

The VB currency data type is quite a strange beast.
Maybe you can store it as an Integer, but you have to multiple and divide by 1000 when storing and retrieving them.

INTEGER. The value is a signed integer, stored in 0, 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.

[Nouyana]

The VB currency data type is quite a strange beast.
Maybe you can store it as an Integer, but you have to multiple and divide by 1000 when storing and retrieving them.

Sounds good, but too complex for me now. It needs to create one more wrapper for the SQLiteColumns class and to catch currency fields... And I need something to do with all queries...

I'll try to use REAL type in database and Currency in the program. Let's see if I will lost something.

[xiaoyao]

*.cobj, what is this file format?
*.cpp like *.bas,*.cls?
During compilation, it first generates an obj format file, which is then linked into an exe?
The final compiled PE files are: exe, DLL, *.lib static libraries?

vb6 can't support *.lib format link to the final exe?
SQLite win 32, is this only available with the vc6 compiler?
If you use vc2015, VC 2019, VB6, can you still link *.cobj files? What do you need to do?

[Nouyana]

During compilation, it first generates an obj format file, which is then linked into an exe?

Yes. All you need is VB6 IDE Linker AddIn.

SQLite win 32, is this only available with the vc6 compiler?
If you use vc2015, VC 2019, VB6, can you still link *.cobj files? What do you need to do?

You may use the VBSQLite12.dll any way you use any other ActiveX dll. You may also use VBSQLite12SideBySide.res (standalone manifest).

Or you can add to your project all the VBSQLite's *.cls and *.bas files and put into the project folder the sqlite3win32.dll std library.

Also, take a look at this post, may be you will find it usefull.

[xiaoyao]

It took me a lot of time to compile it successfully, and in the past year or two, I have tried this project, but it has been a failure.
The original exe is only 108kb, but now it is about 1124kb

I tried to compile it into exe or activex dll and it can be statically linked successfully.

Now there is one more problem. The database cannot be opened normally in the IDE.

If this technology can be realized 20 years ahead of schedule.
If you sell a lot of VB6 DLLs, you can also turn them into static libraries through this technology.

But now we have registration-free loading of com DLLs.
In addition, our hard disks are getting cheaper and cheaper, and the memory is also very cheap.
One of our programs uses 10 megabytes or 100 megabytes.
What does it matter if he still needs to attach 80 DLLs?

[xiaoyao]

Maybe V8 JS engine, chakaracore. DLL, many open source projects, we can also turn it into a static compilation way. This is also the principle of.net aot8 compiling it into a standalone exe. Perhaps its greatest use is to deploy containers for virtual environments.

[Nouyana]

Now there is one more problem. The database cannot be opened normally in the IDE.

We need more details or a code example.

[xiaoyao]

If you can make a tool to automatically open your three projects and compile them automatically. That's much more convenient.

For such a huge project, it is really troublesome to download different DLLs and link libraries from this website and that website.
If we have a powerful package manager, like nuget, it can even automatically download the source code of multiple different projects and compile it locally.
This is very convenient.

Maybe we can use the command line to do this, and we can.
But it also involves downloading some files from one of your websites and then adding them to a directory, which is really complicated to implement.

The source code of many other programming languages needs to be downloaded, different compilers need to be configured, and some still have many errors and need to be modified.

If all open source projects have a one-click configuration tool, just run a command line to automatically download these resources through the website, and the compiler, it will be convenient, but unfortunately there is no such thing at present.


When we were in China 20 years ago, we had printers and graphics cards installed on our computers, and many of the driver discs had been lost.We have a website that can automatically collect all the drivers, and a tool that will automatically detect the hardware of the computer and automatically install the best match. In this way, almost 100% of them are correct. A small number of them may not match in place.


At present, our Linux system or Android system, if you want to install other systems on your mobile phone, the main problem is that we can't give these cameras or mobile phone motherboard CPU. Ah, and the graphics card, give them the right driver, so it may fail to refresh the machine, but if we use the VINOS automatic driver search mode, then as long as our mobile phone can access the Internet, or in the U disk, ah, it is not difficult to find the driver, it is very simple.

[xiaoyao]

Maybe the reason for the error was that there was no database file at the beginning, and then there was an error when calling the API to open the file.
You can directly check where the file is opened.

[Krool]

Update released.

The sqlite3 c source was upgraded to 3.46.1 (2024-08-13).

[xiaoyao]

Quote from sqlite3_get_table:


sqlite3-get_table comes from older sqlite2 where all was stored as text.
You can use sqlite3-get_table in the sqlite3win32.dll but NOT in the COM-Wrapper.
You can test which option (1, 2 or 3) is the best for you and decide.

It is also an efficient method to save multi-column data into multiple arrays.
If all the field data of the query are strings, it is not bad to use GETTABLE.
If the data types are different, it is also an efficient method to pass in 10 arrays (with different data types) and get all the data directly.
I don't know which reading efficiency is higher than the variant way.
Property Value As Variant
VBSQLite12.SQLiteColumn

[xiaoyao]

SQLiteDataSet.cls
Friend Function FInit(ByVal hStmt As Long) As Boolean

While Result = SQLITE_ROW

For iCol = 0 To (Count - 1)
Select Case stub_sqlite3_column_type(hStmt, iCol)
Case SQLITE_INTEGER
Result = stub_sqlite3_step(hStmt)
Wend


If there are 10,000 rows and 30 columns of data, you need to detect the data type 300,000 times. If there is a BUG here, you can change it to only detect the data type 30 times and save it in the array.
ReDim Preserve PropValues.Rows(0 To ArrUBound) As TCOLS
Can you get the number of data rows at one time and allocate all the data to the array?

Is the design method here to read all rows and columns of data into a two-dimensional array at a time?
Is there another mode to read only one row of data at a time, move to the next row, and then read one row of data (multiple columns of data into an array)?

[Krool]

Is there another mode to read only one row of data at a time, move to the next row, and then read one row of data (multiple columns of data into an array)?

Yes, use SQLiteCursor.cls

[yokesee]

Thanks Krool

[Krool]

Update released.

Passing vbNullString to .SetParameterValue no longer binds a SQL NULL in a SQLiteCommand or SQLiteCursor class.

The change in code is as following:

(from)

Result = stub_sqlite3_bind_text16(PropHandle, Index, StrPtr(Value), -1, SQLITE_TRANSIENT)

(to)

If StrPtr(Value) <> NULL_PTR Then
    Result = stub_sqlite3_bind_text16(PropHandle, Index, StrPtr(Value), -1, SQLITE_TRANSIENT)
Else
    Result = stub_sqlite3_bind_text16(PropHandle, Index, StrPtr(""), 0, SQLITE_TRANSIENT)
End If

Because if StrPtr(Value) is a NULL pointer then sqlite3 does not bind an empty string but instead an SQL NULL, which is wrong.

A vbNullString from a SELECT '' should bind an empty string (vartype vbString) instead SQL NULL when passing to .SetParameterValue.

[Krool]

Update released.

Included SQL function JulianDayFromOADate and JulianDayToOADate. (OLE Automation Date)

All the built-in sqlite date-time functions can process JulianDay dates.
But often we want to store our VB6 dates not as text but as double value. That's where the JulianDayFromOADate and JulianDayToOADate are helpful for.

Test to verify:

SELECT JulianDayToOADate(JulianDay('1900-01-01')) ' returns #1/1/1900#

Use case example: (like Year([MyColumn]) in SQL server)

SELECT STRFTIME('%Y', JulianDayFromOADate([VB6DateCol]))

[Krool]

Also included SQL function UnixEpochFromOADate and UnixEpochToOADate (UnixEpoch; seconds and fractional subseconds, if necessary)

[Krool]

Below are some helper functions (just put in a .bas) to convert from/to JulianDay, UnixEpoch which can be convenient when an app wants to store the VB6's OADates as native datetime formats in sqlite. (JulianDay/UnixEpoch)
Thus you can use the SQL datetime functions without the help of the recently added custom SQL functions to convert from/to OADates.

All is well tested to resolve all the edge cases.
To note is that ToUnixEpoch and FromUnixEpoch returns and provide always double figures. These shall be saved as doubles in sqlite. Or truncated via Int() and converted via CDec() to eliminate possible fractional subseconds and to store as integer.

Public Function FromJulianDay(ByVal JulianDay As Double) As Date
Const JULIANDAY_OFFSET As Double = 2415018.5
If JulianDay >= 1757584.5 And JulianDay < 5373484.5 Then
    If JulianDay >= JULIANDAY_OFFSET Then
        FromJulianDay = CDate(JulianDay - JULIANDAY_OFFSET)
    Else
        Dim DateValue As Double, Temp As Double
        DateValue = JulianDay - JULIANDAY_OFFSET
        Temp = Int(DateValue)
        FromJulianDay = CDate(Temp + (Temp - DateValue))
    End If
Else
    Err.Raise 5
End If
End Function

Public Function ToJulianDay(ByVal OADate As Date) As Double
Const JULIANDAY_OFFSET As Double = 2415018.5
If CDbl(OADate) >= 0# Then
    ToJulianDay = CDbl(OADate) + JULIANDAY_OFFSET
Else
    Dim Temp As Double
    Temp = Fix(CDbl(OADate))
    ToJulianDay = Temp - (CDbl(OADate) - Temp) + JULIANDAY_OFFSET
End If
End Function

Public Function FromUnixEpoch(ByVal UnixEpoch As Double) As Date
Const UNIXEPOCH_OFFSET As Double = 25569#
If UnixEpoch >= -59010681600# And UnixEpoch < 253402300800# Then
    Dim DateValue As Double
    DateValue = (UnixEpoch / 86400#) + UNIXEPOCH_OFFSET
    If DateValue >= 0# Then
        FromUnixEpoch = CDate(DateValue)
    Else
        Dim Temp As Double
        Temp = Int(DateValue)
        FromUnixEpoch = CDate(Temp + (Temp - DateValue))
    End If
Else
    Err.Raise 5
End If
End Function

Public Function ToUnixEpoch(ByVal OADate As Date) As Double
Const UNIXEPOCH_OFFSET As Double = 25569#
If CDbl(OADate) >= 0# Then
    ToUnixEpoch = (CDbl(OADate) - UNIXEPOCH_OFFSET) * 86400#
Else
    Dim Temp As Double
    Temp = Fix(CDbl(OADate))
    ToUnixEpoch = (Temp - (CDbl(OADate) - Temp) - UNIXEPOCH_OFFSET) * 86400#
End If
End Function

Example for use with VBSQLITE:

.SetParameterValue 100, CDbl(DTPicker1.Value) ' Store as OADate
.SetParameterValue 101, ToJulianDay(DTPicker2.Value) ' Store as native JulianDay
.SetParameterValue 102, ToUnixEpoch(DTPicker3.Value) ' Store as native float UnixEpoch (can have fractional subseconds when the date variable has milliseconds; which is normally not the case)
.SetParameterValue 103, CDec(Int(ToUnixEpoch(DTPicker4.Value))) ' Store as native integer UnixEpoch (ensure seconds only)

And the reverse:

Dim DS As SQLiteDataSet
DTPicker1.Value = CDate(DS![DateColumn100]) ' OADate 1:1
DTPicker2.Value = FromJulianDay(DS![DateColumn101])
DTPicker3.Value = FromUnixEpoch(DS![DateColumn102])
DTPicker4.Value = FromUnixEpoch(DS![DateColumn103])

When retrieving date and time and you want to ensure that there are no subseconds ever then you can ensure that as following:

DTPicker2.Value = FromJulianDay(Fix(DS![DateColumn101] * 86400#) / 86400#)
DTPicker3.Value = FromUnixEpoch(Int(DS![DateColumn102]))

To note is that this may only be meaningful if a 'subsec' modifier was used or when retrieving the field directly and you know that they can be milliseconds.

[Krool]

Update released.

OADate() SQL function included which behaves like the native JulianDay() and UnixEpoch() SQL function.
Not supported modifiers are "ceiling", "floor" and time-shift operations with string literals
No-op modifiers are "subsec" and "subsecond".

So

OADate([VB6DateCol], 'julianday')

is basically the same as

JulianDayFromOADate([VB6DateCol])

And currency data type is now allowed on .SetParameterValue method and .LastInsertRowID property. (SQLITE_INTEGER)

[xiaoyao]

https://github.com/sammycage/plutosvg
HOW TO make by vc2017,vs2022?

How to generate c obj file format and statically compile VB6 source code like sqlite vb6 com wrapper?
The attachment files VBSQLite12. zip (Src) and sqlite3win32 (Bin) need to be extracted to the same folder.
Sqlite3win32.dll and 2 COBJ files, only used for compilation. The compiled VB ActiveX DLL has no dependencies as SQLite3Win32 was subsequently compiled into it.

[Krool]

Update released.

The sqlite3 c source was upgraded to 3.50.1 (2025-06-06).

[yokesee]

good job Krool

[tmighty2]

May I ask if you plan integration of sqlite-vec? https://github.com/asg017/sqlite-vec
I would need that for faiss.

[smileyoufu]

Delete

[yereverluvinuncleber]

Just have to re-iterate exactly how good this is. If this wasn't already available as a part/package for TwinBasic I would be strongly recommending it!

This package is the "canine testicles" of database code for VB6.

[Krool]

Update released.

The sqlite3 c source was upgraded to 3.51.3 (2026-03-13).

[Tech99]

Hi Krool, thanks for the SQLite library.

I needed to add some math helper functions and hence tried to compile project.
Linker add-in is installed and replace_cobj.bat runs ok ie. those object files are copied to BIN folder.
Problem is that for some reason, sqlite3win32helper.obj and sqlite3win32stubs.obj object files do not statically link in to VBSQLite12.dll
Compilation produces SQLite12.dll without sqlite3win32 obj files.

Any idea?

Problem solved...
I added pause command to replace_cobj.bat and peeked what happens. Found out that VB compiler produced object files with ucase .OBJ extension.
Edited replace_cobj.bat to copy object files with ucase extension.
Bin\sqlite3win32helper.OBJ
Bin\sqlite3win32stubs.OBJ

[Tech99]

Deleted...

[cliv]

Please add database encryption/password (maybe using wxSQLite3 - FREE SQLite3 database wrapper or SQLCipher Community Edition)