VB SQLite Library (COM-Wrapper)

This Ax-DLL project is intended to get a VB-friendly COM-Wrapper for the SQLite library, which is based on the sqlite3win32 __stdcall compilation.

Ax-DLL version: 1.2.41
SQLite version: 3.51.3

A Std-EXE demo project is also included to show how to use the Ax-DLL.

The interaction of the various components are very simplistic:
Attachment 185387

There is only one public creatable component, the SQLiteConnection object.

The SQLiteDataSet object is read-only and meaningful for SELECT statements only.
INSERT, UPDATE and DELETE statements shall be done via the 'Execute' methods of the SQLiteConnection or SQLiteCommand object.

The SQLiteCommand object is just a helper. All can be done without it.
However, for recurring tasks it might perform better and can also be convient for parametrized SQL statements.

The source code of the project can also be viewed on GitHub.

Helper functions to convert from/to JulianDay and UnixEpoch outside of SQL which can be useful for .SetParameterValue to convert from OADate into JulianDay or UnixEpoch and for a query result to convert from JulianDay or UnixEpoch back to an OADate.

List of overloaded built-in SQL functions:

Code:

---

- NOCASE collating sequence to support unicode.
- LIKE(), UPPER(), and LOWER() to support unicode.

---

List of new SQL functions:

Code:

---

- OADate() that can alter date and/or time and returns an OLE automation date.
  It returns the current UTC when there are no args.
  Arg 1: 'now' (current UTC), a number or a VB6 date string format which translates to a number using IsDate() and CDate()
  Arg 2 to N are modifiers that alter date and/or time to its left.
  Following modifiers are supported from the standard:
  - 'NNN days', 'NNN hours', 'NNN minutes', 'NNN seconds', 'NNN months' and 'NNN years'. (s at the end is optional)
  - 'start of month', 'start of year' using DateSerial().
  - 'start of day' to remove the time using Fix().
  - 'weekday N' using DateAdd() in a loop.
  - 'oadate' which is a no-op but for completeness.
  - 'unixepoch', 'julianday' and 'auto'.
  - 'localtime' and 'utc' to convert from/to UTC.
  - 'subsec' and 'subsecond' are no-ops.
- JulianDayFromOADate() and JulianDayToOADate() to convert between OADate and JulianDay.
- UnixEpochFromOADate() and UnixEpochToOADate() to convert between OADate and UnixEpoch.

---

List of revisions:

Code:

---

17-Apr-2026
- Upgrade sqlite3 c source to version 3.51.3 (2026-03-13).
15-Dec-2025
- Upgrade sqlite3 c source to version 3.50.4 (2025-07-30).
09-Jun-2025
- Upgrade sqlite3 c source to version 3.50.1 (2025-06-06).
21-Jan-2025
- Upgrade sqlite3 c source to version 3.47.2 (2024-12-07).
07-Nov-2024
- Included SQL function OADate() that can alter date and/or time and returns an OLE automation date.
05-Nov-2024
- Currency data type allowed on .SetParameterValue method and .LastInsertRowID property. (SQLITE_INTEGER)
04-Nov-2024
- Included SQL function JulianDayFromOADate() and JulianDayToOADate().
- Included SQL function UnixEpochFromOADate() and UnixEpochToOADate().
22-Oct-2024
- Passing vbNullString to .SetParameterValue no longer binds a SQL NULL in a SQLiteCommand or SQLiteCursor class.
08-Oct-2024
- Upgrade sqlite3 c source to version 3.46.1 (2024-08-13).
22-Apr-2024
- Upgrade sqlite3 c source to version 3.45.3 (2024-04-15).
24-Jan-2024
- Upgrade sqlite3 c source to version 3.44.2 (2023-11-24).
08-Nov-2023
- Removed the stmt_readonly check for .OpenDataSet and .CreateCursor.
  This allows for example to use a PRAGMA statement and read the result row.
11-Oct-2023
- Upgrade sqlite3 c source to version 3.43.2 (2023-10-10).
08-Jul-2023
- SQLiteCursor class is not auto-initialized anymore with sqlite3_step.
  Use .MoveFirst to ensure it is initialized, otherwise an error will occur.
02-Jul-2023
- Upgrade sqlite3 c source to version 3.41.2 (2023-03-22).
26-Sep-2022
- Upgrade sqlite3 c source to version 3.39.3 (2022-09-05).
10-Aug-2022
- Upgrade sqlite3 c source to version 3.39.2 (2022-07-21).
- Upgrade of the regexp c extension.
- Added compile-time option SQLITE_OMIT_AUTOINIT 1.
03-Aug-2022
- Usage of sqlite3_prepare16_v2/v3 instead of sqlite3_prepare_v2/v3, if applicable.
01-Aug-2022
- Better performance due to sqlite3 c source compiled with SQLITE_DEFAULT_CACHE_SIZE -8000. (instead of default -2000)
- Usage of sqlite3_bind_text16 instead of sqlite3_bind_text.
28-Jul-2022
- Included the binding methods/properties in the SQLiteCursor class.
  This is meaningful only when created from a SQLiteConnection class, because the query will be expanded when created from an SQLiteCommand class.
  Using SetParameterValue or ClearParameters will move to the first row automatically.
27-Jul-2022
- Included the SQLiteCursor class which can be created from a SQLiteConnection or SQLiteCommand class.
  This is useful for very large tables in which data can be retrieved without memory problems. The cursor is treated here as an iterator.
02-Mar-2022
- Upgrade sqlite3 c source to version 3.37.2 (2022-01-06).
19-Apr-2021
- Included the SetProgressHandler method which registers/unregisters a progress handler callback.
  For this the new ISQLiteProgressHandler class needs to be implemented on the receiver.
- Included the BackupDB method which backups (copies) a SQLite database between two SQLite database connections.
- Included the SharedCache parameter in the OpenDB method.
- Included the LastInsertRowID property in the SQLiteConnection class.
- Included the AutoCommit (read-only) property in the SQLiteConnection class.
27-Mar-2021
- OpenDB method now uses the flags SQLITE_OPEN_FULLMUTEX to enable serialized threading mode.
  Also changed the compile-time option SQLITE_THREADSAFE from 2 to 1. (recommended default setting)
  This means multiple threads can safely use the same database connection.
  When each thread uses an own connection then the threading mode is as multi-thread. (equivalent as if SQLITE_OPEN_NOMUTEX or SQLITE_THREADSAFE=2)
26-Mar-2021
- REGEXP operator included by statically linking to the regexp c extension.
19-Mar-2021
- Upgrade sqlite3 c source to version 3.34.1 (2021-01-20).
- Optimized SQLiteDataSet as ReDim Preserve is called less times (array bump of 10 is defined) to improve performance.
29-Apr-2020
- Bugfix in SQLiteCommand for the SetParameterValue method.
  The VarType was not correctly when passing VT_DISPATCH that has a DISPID_VALUE.
04-Apr-2020
- Upgrade sqlite3 c source to version 3.31.1 (2020-01-27).
05-Feb-2020
- Unicode support for NOCASE collating sequence.
- Unicode support for LIKE(), UPPER(), and LOWER() functions.
24-Jan-2020
- First release.

---

The source code of "VBSQLite12SideBySide.res" for Registration-Free (Side-by-side) is:

Code:

---

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<assembly xmlns="urn:schemas-microsoft-com:asm.v1" manifestVersion="1.0">
  <file name="VBSQLite12.DLL">
      <typelib tlbid="{7CC1A5F1-A0FF-4546-A0F1-FBFE744A4522}" version="1.1" flags="hasdiskimage" helpdir="" />
      <comClass clsid="{1DE49E39-B9D4-4CDF-95AA-098B89F11318}" tlbid="{7CC1A5F1-A0FF-4546-A0F1-FBFE744A4522}" threadingModel="Apartment" progid="VBSQLite12.SQLiteConnection" />
  </file>
</assembly>

---

The attached file VBSQLite12.zip (Src) and sqlite3win32 (Bin) needs to be extracted in a same root folder.
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.

can it support sqlite_get_table?

on compiler get
LINK : fatal error LNK1181: cannot open input file "KERNEL32.LIB"

good work

Quote:

---

Originally Posted by yokesee

on compiler get
LINK : fatal error LNK1181: cannot open input file "KERNEL32.LIB"

good work

---

same problem

Quote:

---

Originally Posted by yokesee

on compiler get
LINK : fatal error LNK1181: cannot open input file "KERNEL32.LIB"

---

Good point.

It's because you only have Visual Studio 6.0 installed without C++.
The VB6 compiler resorts by "KERNEL32.LIB" to some files in /vc98/lib.
You can get it to work to copy paste that folder from another installation. So it's not really needed to have C++ 6.0 installed if you can get the folder somewhere else.

EDIT:
when not having C++ 6.0 installed it is needed to make also manually an environment variable under "Advanced system settings".
Variable = "lib"
Value = "C:\Program Files (x86)\Microsoft Visual Studio\VC98\mfc\lib" and "C:\Program Files (x86)\Microsoft Visual Studio\VC98\lib"

Quote:

---

Originally Posted by xiaoyao

can it support sqlite_get_table?

---

Why? It's a COM Wrapper so not need to fiddle with sqlite API's for the developer. Also sqlite_get_table is not used because it's deprecated and there are more efficient API functions to get the same result internally.

Quote:

---

Originally Posted by Krool

Good point.

It's because you only have Visual Studio 6.0 installed without C++.
The VB6 compiler resorts by "KERNEL32.LIB" to some files in /vc98/lib.
You can get it to work to copy paste that folder from another installation. So it's not really needed to have C++ 6.0 installed if you can get the folder somewhere else.


Why? It's a COM Wrapper so not need to fiddle with sqlite API's for the developer. Also sqlite_get_table is not used because it's deprecated and there are more efficient API functions to get the same result internally.

---

i think gettable is the quick method,you can test:
put all select result to :dim data(rows,cols),how much time need?
1,sqlite3-get_table
2,com object,
3,ado drivers

We need a quick way to read all the data。
also need a way to export csv format,or import csv file.

Quote:

---

Originally Posted by xiaoyao

i think gettable is the quick method,you can test:
put all select result to :dim data(rows,cols),how much time need?
1,sqlite3-get_table
2,com object,
3,ado drivers

We need a quick way to read all the data。
also need a way to export csv format,or import csv file.

---

Quote from sqlite3_get_table:

Quote:

---

This is a legacy interface that is preserved for backwards compatibility. Use of this interface is not recommended.

---

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.

Hi Krool

Could you strip out the binaries from the attachments, please? They're only allowed in the utilities section, not code bank.

Thanks
FD

more times ,we need quickly read alldatas(all row ,all cols),how can i read one row or readall?

Hi Krool, I took a preliminary look at your Sqlite COM-Wrapper. If you could wrap the Sqlite Library by simulating/referring to the SQLite objects (interfaces) in vbRichClient5, then the usability of your Sqlite COM-Wrapper will be greatly improved.

Quote:

---

Originally Posted by xiaoyao

more times ,we need quickly read alldatas(all row ,all cols),how can i read one row or readall?

---

Hi xiaoyao, have you tried SQLite wrapped in vbRichClient5?

Quote:

---

Originally Posted by FunkyDexter

Could you strip out the binaries from the attachments, please? They're only allowed in the utilities section, not code bank.

---

Done. sqlite3win32 (Bin) is now in the UtilityBank - Components.

Quote:

---

Originally Posted by dreammanor

I took a preliminary look at your Sqlite COM-Wrapper. If you could wrap the Sqlite Library by simulating/referring to the SQLite objects (interfaces) in vbRichClient5, then the usability of your Sqlite COM-Wrapper will be greatly improved.

---

It is NOT intended to replace ADO/vbrichclient5.

The intention of this is to have an open-source simplistic sqlite3 COM-Wrapper with it's own interface structure, which is aligned to the internal structure of sqlite3.

With this wrapper you can do all what sqlite3 offers. (memory-db, multiple attached db's etc.)
What vbrichclient5 offers in addition what I do not support is in-built encryption.

Of course this implies that this COM-Wrapper may be meaningful only for projects from scratch and is not something to quickly replace.

Quote:

---

Originally Posted by dreammanor

Hi xiaoyao, have you tried SQLite wrapped in vbRichClient5?

---

before some days,i use sqlite3.dll,by cdecl method.
if use com method sqlite,the same question:we need quickly read all rows,all cols,data.
quickly for millions.

Quote:

---

Originally Posted by xiaoyao

before some days,i use sqlite3.dll,by cdecl method.
if use com method sqlite,the same question:we need quickly read all rows,all cols,data.
quickly for millions.

---

If you use vbRichClient5(which provides an excellent wrapper for Sqlite), then you can solve any of the problems you mentioned. vbRichClient5 is the best VB6 auxiliary development tool (platform or framework) on our planet. It's free, but not open source. I've used it for more than 10 years, and there are people in this forum (such as jpbro) who have used it for more than 15 years. Moreover, its author (Olaf Schmidt) has been active on this forum.

vbRichClient5 is big,and more time maybe use old version sqlite。
if you know vc++,can easy chang source code,add(save out for csv or read all data like: dim data(rows,cols))
sqlite3.dll is very good ,need add api :exec16(for unicode),gettable16.one month ago,i use sqlite3.exe,by shell command,insert dada,create new table。
if table name or fields name is chinese,or insert new data with chinese words,so it's error。
because sqlite3.exe or sqlite3_exec(*) not support chinese(unicode)

if use com dll(activex dll),it's simple for use,but it will be run slowly

in vb code,i test:
for i=1 to 1000000000
a=1
next
it's used time:1.5ms
if use activex dll,(activex.exe)
dim obj as object
set obj=createobject("*")
for i=1 to 1000000000
obj.a=1
next
or

with obj
for i=1 to 1000000000
.a=1
next
end with

it's used time:13000000 ms

it's very slowly in com object method or put value,or read dada.

why ie is slowly,chrome is quickly,maybe com object is not good,it's bad chose for run quickly,only it's simple use for developers

Hi xiaoyao, maybe we have a different understanding about speed. Could you give a specific example of how you operate the sqliteDB? Maybe someone would give you an interesting answer.

xiaoyao,
It's very difficult to follow you. Also your understanding about sqlite3 seems to be incomplete.
1. all in sqlite3 is UTF8 (= Unicode, all natural languages covered)
2. Do you make a "BEGIN TRANSACTION" before your loop and a "END TRANSACTION" after your loop?

Quote:

---

Originally Posted by Krool

xiaoyao,
It's very difficult to follow you. Also your understanding about sqlite3 seems to be incomplete.
1. all in sqlite3 is UTF8 (= Unicode, all natural languages covered)
2. Do you make a "BEGIN TRANSACTION" before your loop and a "END TRANSACTION" after your loop?

---

here is my cpu speed test , for call method by:activex.dll or stand dll:
Running speed test for a=? or activex.dll(class1.a=?)-VBForums
http://www.vbforums.com/showthread.p...65#post5448365

Quote:

---

Originally Posted by xiaoyao

vbRichClient5 is big,and more time maybe use old version sqlite。

---

FYI...
The recent RC5-package comes with SQLite-version 3.28 ... whereas Krools wrapper is still at SQLite-version 3.24.

And no, a call to the deprecated, old sqlite3_get_table would be (much) slower, compared with gathering the table-values via explicit looping.

Here is some test-code with a performance-comparison on "Select * From Invoices" (from SQLite NWind.db):

Code:

---

Option Explicit

Private Declare Function sqlite3_get_table Lib "vb_cairo_sqlite" (ByVal hDB As Long, ByVal SQL As String, lpTable As Long, iRow As Long, iCol As Long, lpErrMsg As Long) As Long
Private Declare Sub sqlite3_free_table Lib "vb_cairo_sqlite" (ByVal lpTable As Long)
Private Declare Function MultiByteToWideChar Lib "kernel32" (ByVal CodePage As Long, ByVal dwFlags As Long, lpMultiByteStr As Any, ByVal cchMultiByte As Long, lpWideCharStr As Any, ByVal cchWideChar As Long) As Long
   
Private Cnn1 As New VBSQLite10.SQLiteConnection, Cnn2 As vbRichClient5.cConnection

Private Sub Form_Load()
  AutoRedraw = True
  Dim Ds As SQLiteDataSet, Rs As cRecordset, T1$
 
  Cnn1.OpenDB "c:\temp\nwind.db"
  Set Cnn2 = New_c.Connection("c:\temp\nwind.db")
 
  New_c.Timing True
    Dim SArr() As String
        SArr = GetTable(Cnn2.DBHdl, "Select * From Invoices")
  Debug.Print "sqlite3_get_table", New_c.Timing
 
  New_c.Timing True
    Set Ds = Cnn1.OpenDataSet("Select * From Invoices")
  Debug.Print "VBSQLite10-Select", New_c.Timing
 
  New_c.Timing True
    Set Rs = Cnn2.OpenRecordset("Select * From Invoices")
  Debug.Print "vbRichClient-Select", New_c.Timing
End Sub
 
Public Function GetTable(hDB As Long, SQL As String) As String()
  Dim pTbl&, Rows&, Cols&, pErr&, i&, j&, PArr&(), SArr$()
 
  sqlite3_get_table hDB, SQL, pTbl, Rows, Cols, pErr
  If pErr = 0 Then
    ReDim PArr(0 To Cols - 1, 0 To Rows)
    ReDim SArr(0 To Cols - 1, 0 To Rows)
    New_c.MemCopy VarPtr(PArr(0, 0)), pTbl, (Rows + 1) * Cols * 4
    For j = 0 To UBound(PArr, 2): For i = 0 To UBound(PArr, 1)
      SArr(i, j) = StringFromPtr(PArr(i, j))
    Next i, j
  End If
  If pTbl Then sqlite3_free_table pTbl
  GetTable = SArr
End Function

Function StringFromPtr(ByVal pUTF8 As Long) As String
  Dim Chars As Long
  If pUTF8 = 0 Then Exit Function
  Chars = MultiByteToWideChar(65001, 0&, ByVal pUTF8, -1, ByVal 0&, 0)
  StringFromPtr = Space$(Chars - 1) 'a VB-BString already contains a trailing Zero, so we allocate it one char less
  MultiByteToWideChar 65001, 0&, ByVal pUTF8, -1, ByVal StrPtr(StringFromPtr), Chars - 1
End Function

---

The results which are printed to the VB6-Debug-Window are (on my machine):

Code:

---

sqlite3_get_table            74.48msec
VBSQLite10-Select            28.87msec
vbRichClient-Select          9.65msec

---

@Krool
You have still work to do at the Unicode-front... as e.g. the following SQL-Select-String (no need to create a table first)
"Select 'ü'='Ü' Collate NoCase"
...the above should return a boolean 1 (and not 0, as it does currently in your wrapper)

Also (with regards to dreammanors suggestion)...
It definitely has value (for the User, to write better portable code), to make the interfaces between the COM-wrappers "as compatible as possible" ...
- not only with regards to "avoiding confusion with posted examples here in the forum"
- but also with "less code to rewrite when wrappers are switched" (or a new vbRuntime comes out in a few years).

This "kind of scattering" (different VB6-Usercode, due to different library-interfaces) is exactly what I'm trying to prevent with "keeping the RC5 closed" at the moment (until the new runtime- and compiler are finished).

Your sources are still "fresh" (not much Usercode out there in the wild, yet) - so there's still time to change the Interfaces to a more compatible way.

Remember, that your CommonControls-wrapper-project was succesful for exactly this reason
(being nearly 100% calling-compatible to the pre-existing MS-wrapper of the CommonControls).

Olaf

Quote:

---

Originally Posted by Schmidt

FYI...
The recent RC5-package comes with SQLite-version 3.28 ... whereas Krools wrapper is still at SQLite-version 3.24.

And no, a call to the deprecated, old sqlite3_get_table would be (much) slower, compared with gathering the table-values via explicit looping.

Here is some test-code with a performance-comparison on "Select * From Invoices" (from SQLite NWind.db):

Code:

---

Option Explicit

Private Declare Function sqlite3_get_table Lib "vb_cairo_sqlite" (ByVal hDB As Long, ByVal SQL As String, lpTable As Long, iRow As Long, iCol As Long, lpErrMsg As Long) As Long
Private Declare Sub sqlite3_free_table Lib "vb_cairo_sqlite" (ByVal lpTable As Long)
Private Declare Function MultiByteToWideChar Lib "kernel32" (ByVal CodePage As Long, ByVal dwFlags As Long, lpMultiByteStr As Any, ByVal cchMultiByte As Long, lpWideCharStr As Any, ByVal cchWideChar As Long) As Long
   
Private Cnn1 As New VBSQLite10.SQLiteConnection, Cnn2 As vbRichClient5.cConnection

Private Sub Form_Load()
  AutoRedraw = True
  Dim Ds As SQLiteDataSet, Rs As cRecordset, T1$
 
  Cnn1.OpenDB "c:\temp\nwind.db"
  Set Cnn2 = New_c.Connection("c:\temp\nwind.db")
 
  New_c.Timing True
    Dim SArr() As String
        SArr = GetTable(Cnn2.DBHdl, "Select * From Invoices")
  Debug.Print "sqlite3_get_table", New_c.Timing
 
  New_c.Timing True
    Set Ds = Cnn1.OpenDataSet("Select * From Invoices")
  Debug.Print "VBSQLite10-Select", New_c.Timing
 
  New_c.Timing True
    Set Rs = Cnn2.OpenRecordset("Select * From Invoices")
  Debug.Print "vbRichClient-Select", New_c.Timing
End Sub
 
Public Function GetTable(hDB As Long, SQL As String) As String()
  Dim pTbl&, Rows&, Cols&, pErr&, i&, j&, PArr&(), SArr$()
 
  sqlite3_get_table hDB, SQL, pTbl, Rows, Cols, pErr
  If pErr = 0 Then
    ReDim PArr(0 To Cols - 1, 0 To Rows)
    ReDim SArr(0 To Cols - 1, 0 To Rows)
    New_c.MemCopy VarPtr(PArr(0, 0)), pTbl, (Rows + 1) * Cols * 4
    For j = 0 To UBound(PArr, 2): For i = 0 To UBound(PArr, 1)
      SArr(i, j) = StringFromPtr(PArr(i, j))
    Next i, j
  End If
  If pTbl Then sqlite3_free_table pTbl
  GetTable = SArr
End Function

Function StringFromPtr(ByVal pUTF8 As Long) As String
  Dim Chars As Long
  If pUTF8 = 0 Then Exit Function
  Chars = MultiByteToWideChar(65001, 0&, ByVal pUTF8, -1, ByVal 0&, 0)
  StringFromPtr = Space$(Chars - 1) 'a VB-BString already contains a trailing Zero, so we allocate it one char less
  MultiByteToWideChar 65001, 0&, ByVal pUTF8, -1, ByVal StrPtr(StringFromPtr), Chars - 1
End Function

---

The results which are printed to the VB6-Debug-Window are (on my machine):

Code:

---

sqlite3_get_table            74.48msec
VBSQLite10-Select            28.87msec
vbRichClient-Select          9.65msec

---

@Krool
You have still work to do at the Unicode-front... as e.g. the following SQL-Select-String (no need to create a table first)
"Select 'ü'='Ü' Collate NoCase"
...the above should return a boolean 1 (and not 0, as it does currently in your wrapper)

Also (with regards to dreammanors suggestion)...
It definitely has value (for the User, to write better portable code), to make the interfaces between the COM-wrappers "as compatible as possible" ...
- not only with regards to "avoiding confusion with posted examples here in the forum"
- but also with "less code to rewrite when wrappers are switched" (or a new vbRuntime comes out in a few years).

This "kind of scattering" (different VB6-Usercode, due to different library-interfaces) is exactly what I'm trying to prevent with "keeping the RC5 closed" at the moment (until the new runtime- and compiler are finished).

Your sources are still "fresh" (not much Usercode out there in the wild, yet) - so there's still time to change the Interfaces to a more compatible way.

Remember, that your CommonControls-wrapper-project was succesful for exactly this reason
(being nearly 100% calling-compatible to the pre-existing MS-wrapper of the CommonControls).

Olaf

---

TEST Need two result: open used times,put to arr(col,rows ) used times.
three method,need six usedtime result.

if only open,need to put all rows,all cols data to arr(col,rows

Code:

---

  Set Ds = Cnn1.OpenDataSet("Select * From Invoices")
  Debug.Print "VBSQLite10-Select", New_c.Timing

---

I would like to see a DAO compatible version, but I know that most of the people use ADO and probably an ADO compatible version would be more popular.

Yes, SqliteDB of vbRichClient5 is an ADO compatible version.

Quote:

---

Originally Posted by dreammanor

Yes, SqliteDB of vbRichClient5 is an ADO compatible version.

---

IF LIKE ADO AND NOT ADO DRIVERS, IT'S EASY FOR study.

Code:

---

dim a(rows,cols)
a(0,0)=rs("filedname1")
a(0,1)=rs("filedname2")

or
a(0,0)=rs(0)
a(0,1)=rs(1)
rs.movenext
a(1,0)=rs(0)
a(1,1)=rs(1)

for best
we need a api
a=gettableArr()
msgbox a(0,0)
msgbox a(0,1)
msgbox a(1,0) & "," & a(1,1)

also,we can make new standll api:
getcsvdata():
data is string:
id,name,age
1,"tom",16
2, "lucy",17

---

After all, the running speed of VB6 is relatively slow. If you can export the results directly with VC and package them well, it will be most convenient for you to get the results directly from VB6

CHINESE:如果想做的完美，这些都需要，毕竟VB6的运行速度比较慢，如果可以用VC把结果直接导出来，包装好，VB6直接得到结果，肯定是最方便的

If you don’t like VB6 then use something else.
All your replies in all threads are all about things which can be done more easily or faster in other languages.
Please use that programming language and stop complaining and demanding faster solutions in vb6

My meaning is,change stand stdcall dll or com sqlite sources code,add some method like import csv file or ouput to csv .
you can testing:for get all cols ,all rows data ,need how much times ,by three way
in ado txf,csv,xls,xml all support.
Ofcourse we need these,sqlite.dll maybe only like .net core ,give not enough FeaturesFeatures. .

Implement them yourself, the sources are freely available

Quote:

---

Originally Posted by xiaoyao

TEST Need two result: open used times,put to arr(col,rows ) used times.
three method,need six usedtime result.

---

No, the test is perfectly fine as it is.

Since all the 3 different methods are stopping the timing exactly at a point,
when the db-table-data becomes available as a copy, in a container-structure, which is usable-in-VB6.
(e.g. the RC5-Recordset-ResultContainer is directly bindable to a VB6-DataGrid, which makes the "total time until visualizing Invoices" 11-12msec or so).

But FYI, the sqlite3_get_table call is the slowest also in case I leave the returned C-UTF8-Pointers "just sitting there"
(without the copy-over-loop into a VB6-StringArray)... the timing was then reduced to 37msec or so.

Olaf

vc++，I do n’t use these. I need a little technology. If you understand, you can easily modify it.

I test call sub or put value ,com activex dll is slowly (It takes more then 10 ~ 30 times(200%-400%).)
If you need to obtain a whole row of data, or all the data of the entire table, if this table has 10,000 rows and 30 columns, you need to call the API at least 1 million times, which is not only cumbersome but also very slow.
chinese: 我不会用vc++编程，需要一点技术，如果懂的人可以很轻松的给他修改，希望能够有高手花点时间，造福大家，非常感谢。
如果需要获取一整行的数据，或者获取整个表格的所有数据，假如这个表格有一万行,30列,那需要调用API至少100万次,这样不仅很麻烦，而且速度很慢。
for i=1 to 1000000000
a=i or class1.a =i,activex.dll(class1.a or call method)
next
BasFile_Public_A=I used: 16.727 MS
ClassA.A =125.8725 MS
ActiveXDll.Class2.A =125.5622 MS
ClassA.MethedSetB( sub) =352.0073 MS

【download】my test page url:http://www.vbforums.com/showthread.p...dll(class1-a-)

Quote:

---

Originally Posted by xiaoyao

My meaning is,change stand stdcall dll or com sqlite sources code,add some method like import csv file or ouput to csv .
you can testing:for get all cols ,all rows data ,need how much times ,by three way
in ado txf,csv,xls,xml all support.
Ofcourse we need these,sqlite.dll maybe only like .net core ,give not enough FeaturesFeatures. .

---

I ask you to make an effort and try to write in English that other people can understand. Your posts look like jargon.
You can use Google translator for the texts.
Thank you.

Quote:

---

Originally Posted by xiaoyao

...for get all cols ,all rows data ,need how much times ,by three way
in ado txf,csv,xls,xml all support.

---

Now you're talking about "serialization" (e.g. when you want to transfer Query-Results over sockets, from behind a WebServer).

A common (modern and standardized) format for that (which e.g. Browsers prefer),
is JSON in the meantime (XML less so, and CSV even less).

Code:

---

  New_c.Timing True
    Set Rs = Cnn2.OpenRecordset("Select * From Invoices")
    Dim JSONUTF8() As Byte
        JSONUTF8 = Rs.ToJSONUTF8
  Debug.Print "vbRichClient-Select", New_c.Timing

---

E.g. the above code does that - and the total time (until the Data is serialized to JSON) is just 15msec total.

And if you don't have "Browser-Clients" (which prefer JSON), but a VB6-Client instead -
one can also use "the native serialization" of the ResultSet-Containers (which allows to rebuild the entire Rs at the Clientside "as it was" at the server).

Code:

---

  New_c.Timing True
    Set Rs = Cnn2.OpenRecordset("Select * From Invoices")
    Dim Content() As Byte
        Content = Rs.Content
  Debug.Print "vbRichClient-Select", New_c.Timing

---

This is a bit faster than the JSON-serialization, with the total time now about 12msec.

Olaf

Quote:

---

Originally Posted by Schmidt

@Krool
You have still work to do at the Unicode-front... as e.g. the following SQL-Select-String (no need to create a table first)
"Select 'ü'='Ü' Collate NoCase"
...the above should return a boolean 1 (and not 0, as it does currently in your wrapper)

---

Thanks for catching the blind spot. I was not aware of this and actually shocked that this does not work "out-of-the-box".
I will look to include the ICU extension soon into sqlite3 to solve this. Thanks again .

I don't get the ICU extension to work..

Maybe it's a better idea to overload the built-in NOCASE collating sequence (using sqlite3_create_collation()) and the built-in LIKE(), UPPER(), and LOWER() functions (using sqlite3_create_function()) and let VB6 private functions do the Unicode handling for all these?

Quote:

---

Originally Posted by Krool

I don't get the ICU extension to work..

Maybe it's a better idea to overload the built-in NOCASE collating sequence (using sqlite3_create_collation()) and the built-in LIKE(), UPPER(), and LOWER() functions (using sqlite3_create_function()) and let VB6 private functions do the Unicode handling for all these?

---

Yep, that's what I did in the RC5-wrapper (ICU is quite large).

Olaf

Important update released.

The NOCASE collating sequence and the LIKE(), UPPER(), and LOWER() functions now support Unicode.

Update released.

The sqlite3 c source was upgraded from version 3.24.0 (2018-06-04) to 3.31.1 (2020-01-27).

New introduced compile-option SQLITE_DQS (=0) was defined to disable the double-quoted string literal misfeature. (recommended according to sqlite.org)

EDIT: Also compile-option SQLITE_OMIT_DEPRECATED was added to reduce exported functions.

i m a little curious, does this library support password encryption sqlite database?
does vbRichClient5 support it?

Quote:

---

Originally Posted by loquat

i m a little curious, does this library support password encryption sqlite database?
does vbRichClient5 support it?

---

No and Yes... ;)

Olaf

Excuse me, does this support browsing. SQLite files?

Quote:

---

Originally Posted by ChenLin

Excuse me, does this support browsing. SQLite files?

---

Both libs can do that ... although your word-choice of "browsing",
seems to suggest, that you think about "read-only-access"...

SQLite is a "full DB-Engine in a Dll" (supporting much more than "just browsing a file"):
https://www.sqlite.org/transactional.html

Olaf

Quote:

---

Originally Posted by Schmidt

No and Yes... ;)

Olaf

---

yes already found that

Update released.

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)..

Quote:

---

Originally Posted by Schmidt

Both libs can do that ... although your word-choice of "browsing",
seems to suggest, that you think about "read-only-access"...

SQLite is a "full DB-Engine in a Dll" (supporting much more than "just browsing a file"):
https://www.sqlite.org/transactional.html

Olaf

---

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.

Attachment 176611


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

---

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:

Code:

---

    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.

Update released.

The REGEXP operator is now included by statically linking to the regexp c extension.

The RE syntax recognized by regexp.c (from sqlite.org) is a subset of the quite large PCRE.

The REGEXP operator is case-sensititive. To make it work as case-insensitive just do..

Code:

---

lower(B) regexp A

---

where A is the pattern and B the test string. Of course all letters in the pattern A must be lower case also.

Very good project.
How Regexp works.
In the inputbox I write "regexp A" or "Lower (B)" but only the text is written

regards

seems use the trick's vbCdeclFix, we can use original sqlite3.dll

Minor convenience update.

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'.

Code:

---

SELECT 'SQLite ' || 'concat';
SELECT concat('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.

New Ax-DLL version 1.1 with below additions:

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?

Quote:

---

Originally Posted by Tech99

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)

Ouch - so LIKE query wildcard char '%' in VBSQLite has been changed, from precentage char % to asterisk char *.

VBSQLite query dialect now differs from sqlite native dialect.
https://sqlite.org/forum/info/846632411f3fd1d2

Don't know others, but i would prefer database native engine dialect, be it; ansi, postgre, oracle, sql server, access etc.

Quote:

---

Originally Posted by Tech99

Ouch - so LIKE query wildcard char '%' in VBSQLite has been changed, from precentage char % to asterisk char *.

VBSQLite query dialect now differs from sqlite native dialect.
https://sqlite.org/forum/info/846632411f3fd1d2

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.

I understand your point. Luckily, no serious harm caused from change. SQL sentences are corrected and all is ok.

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.

Quote:

---

hoe yo load memory sqlite database from vb6 resfile?

---

You can't
The load to memory of a DB file can't be done from a byte array.
It only works for physical files.

xiaoyao, your texts are so awful that I am not able to understand these. So I can't respond to you, sorry!