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 & 0x7FFFFFFFFFFFFFF);
double dbl = (double)s;
if (u & 0x8000000000000000)
dbl += (double)0x8000000000000000;
return dbl;
}

static LONGDOUBLE_TYPE uint64_to_ldouble(u64 u)
{
i64 s = (u & 0x7FFFFFFFFFFFFFF);
LONGDOUBLE_TYPE ldbl = (LONGDOUBLE_TYPE)s;
if (u & 0x8000000000000000)
ldbl += (LONGDOUBLE_TYPE)0x8000000000000000;
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)