Home

Updated July 2008

How it's Done - The Snags
How it's Done - Modifying the SQLite code
Demonstration Data Files
SQLite Performance and Virus-Scanners
Demonstration Application
Visual BASIC Declaration and Programming Guidelines
Handling Unicode and UTF-8
Conclusion
Updates and News
Things Left To Do

Downloads

Windows is supported from NT4.0/SP6a to Windows XP

VB Declares are included with the demo project

SQLite 3.5.9 VB DLL and Demo
Download the optimal XP/Vista VC++ 9.0 version

SQLite 3.5.9 VB DLL and Demo
Download the NT/2K legacy-compatible VC++ 5.0 version

Quick-Demo project (VC9 - Windows XP)

Quick-Demo Project (VC5 - NT/2k)

Download the Example Database and SQL Query Separately

A brief offshoot from the VBToolbox project was having a quick look at SQLite 3 and example code-wrappers for Visual BASIC. I already included a distro of SQLite with MicroApache as it is awesomely tiny and well-designed. Whilst the examples and tutorials were good they were mainly targeted at VB6 or higher. The main show-stopper for me using VB5 was the need to return a Variant array via an external DLL declare. My copy of VB 5 simply wouldn't let me do that. As far as I could see it wasn't possible to return a String or Variant array from an externally declared function. The two tutorials at Tannertech and Persistent Realities were very useful and informative but I just couldn't use the code in VB5 and they were probably intended for VB 6 only.

This kinda stumped me but I knew I already had reliable code working which returned Variant String arrays either via function body or via a function parameter so I bit the bullet and completely rewrote the code in "C" using my recently-acquired free copy of Visual C++ 2008 Express Edition. It turned out to be a major headache and far more difficult than described in many tutorials but here's the resulting DLL running SQLite version 3.5.9 ...

The Albums demo database being queried successfully in the VB5 IDE using the rewritten code and my SQLite DLL

How it's Done - The Snags

This may be an ongoing "howto" as there is so much fiddly stuff to put in especially if not using an older C compiler.

The main issues I found were...

• Visual BASIC 5 won't let me declare this: ...
  
  Private Declare Function sqlite_get_table Lib "SQLite3.dll" (ByVal DB_Handle As Long, ByVal SQLString As String, _
  ByRef ErrStr As String) As Variant()
  
  Instead, I can only have this (which isn't actually as big a problem as you may may think) ...
  
  Private Declare Function sqlite_get_table Lib "SQLite3.dll" (ByVal DB_Handle As Long, ByVal SQLString As String, _
  ByRef ErrStr As String) As Variant
  
  As long as the returned Variant is properly checked then a non-dimensioned Variant - Dim v As Variant - can be handled as an array when returned from a function-call with no problems whatsoever. There is sufficient information stored internally within the Variant to reference any array-contents.
  
• VC++ 2008 - Unicode has to be disabled: Project->Properties->Configuration Properties->General->Project Defaults->Character Set (set to "Not Set")
  Failing to do this I found that you can run into problems with unexpected Unicode strings starting with a NULL (0x00) character which result in "empty" C strings
  
• If you're not sure about UNICODE status then these declares may be helpful during compilation...
  

  #ifdef UNICODE #pragma message( "UNICODE is enabled: " __FILE__ ) #else #pragma message( "UNICODE is NOT enabled: " __FILE__ ) #endif #undef UNICODE // You can always disable UNICODE support using these two undefs #undef _UNICODE

  Choosing the wrong return-type for a function, regardless of whether the VB declare matches the "C" prototype led to corrupted function parameter strings. I wasted about 3 hours on this after finding that strings contained nothing but 0x00,0x00,0x00...  Changing the return type by experimenting cured this problem. I guess the stack or heap can get affected sometimes even when the declarations are legit. Everything has to be just right.  Abandoning the use of SAFEARRAY returns and deciding to completely rewrite the example code cured this huge headache.

• If you use the amalgamated SQLite "C" source for SQLite 3 then changing definitions to suit VB can be complex and tedious. You may also miss the function-pointer aliases which are buried deep in the amalgamated source block:
  

  struct sqlite3_api_routines {     // Others omitted...     int (_stdcall *close)(sqlite3*);     const char* (_stdcall *libversion)(void);     int (_stdcall *libversion_number)(void);     int (_stdcall *open)(const char*,sqlite3**);     int (_stdcall *open16)(const void*,sqlite3**);    // et. al. };

  Download the amalgamated SQLite source code from here (direct ZIP link)

• A DEF file is required. Get one from the DLL ZIP download here (direct ZIP link).
  You need to unzip it, copy the DEF file to your project folder,
  drag the DEF file to your project from there,
  then tell VC++ to use this file. Project->Properties->Configuration Properties->Linker->Input->Module Definition File (enter sqlite3.def)
  
• Several exports need to be disabled from your DEF file as they cause compile errors. AFAIK these are only used internally by SQLite
  The def file should contain all functions with these caveats (v1.00 shown below)
  
  EXPORTS new exports //////////////
  GetArrayDimensions  // Non-SQLite Toolbox Function to return the dimensions of a Variant
  GetArrayRows   // Non-SQLite Toolbox Function to return the number of rows in a 2x matrix Variant
  sqlite_get_table
  sqlite_rowcount
  ;changed functions //////////////
  sqlite3_libversion // Now returns a LPSTR
  sqlite3_libversion_number // Now returns a long/DWORD (not an int)
  
  ;EXPORTS disabled //////////////
  ;sqlite3_config
  ;sqlite3_initialize
  ;sqlite3_next_stmt
  ;sqlite3_shutdown
  ;sqlite3_status;
  ;sqlite3_os_end
  ;sqlite3_os_init

• Spurious 64-bit integer warnings in the SQLite code using MSVC 2008
  Disable these using: Project -> Properties -> C/C++ -> General -> Detect 64-bits portability Issues -> NO

• Mountains of compiler warnings with the SQLite code using MSVC 2008. Many of these could have been prevented by use of judicious casting within the code. However, to criticise the work of someone who's a far better coder than I would be churlish.
  Disable these by adding the following to "C" modules:
  

  #pragma warning(disable : 4244 4018 4311 4267 4267 4312)       // Most dubious assignments etc. #pragma warning(disable : 4996)       // Localtime

• Some of the example code seems to contain some bizarre errors such as returning an int as a string for the SQL version number. As the pages are quite old now (2007?) I'm guessing that writing the wrapper was sufficiently tedious for the code not to have been updated a great deal. It is always worth scanning through code you find on the net, never assume it is free from conceptual, logical or other errors (that definitely goes for my code too).

• I decided to dump VB_SQLITE_VERSION constant as given in the Tannertech code and use the actual SQLite version string and DWORD values

• Code which is actually active is "greyed-out" as not being active by pre-processor definition in VC++ 2008. I was getting complaints about code which was supposed to be "disabled" and assumed the compiler was being strict, but the code was, in actual fact was being compiled up in the amalgamated C source. I'm guessing VC got confused?  Whilst writing this up I noted a block of #defined where the colour had changed without due cause half way through a block of 12 statements.

• libversion_number is listed as returning an int but the value returned is too high to be contained with an int and returns a negative value. This may be due to flawed assumptions on the size of an integer. I changed this to DWORD wherever possible (although MSVC++ 2008 did complain). Where this was not possible it was changed to long (const long - see below)

• Several areas have constants being returned as non "const". These need to be changed. I don't believe it is good practice to permit const values to be changed. Even if VB allows it the underlying "C" code should reflect the intentions.

• For some reason BSTRs are used with the possible assumption that VB cannot or will not cast Unicode strings to ANSI. Reading the Microsoft MSDN contents and doing a lot of experimentation during the development of VBToolbox confirms that strings are passed just fine as ANSI. This means that some of the recommended changes from char* to BSTR aren't really needed.

• Conversion to wide character (Unicode). Is often shown in examples converting to CP_ACP (ANSI). Combine this with an assumption that the result will be a byte-pair string in Unicode format led to some peculiar and possibly difficult to spot bugs where the allocated string length was twice that which was actually needed. Converting ANSI to ANSI doesn't need a larger string. Maybe it make for good forward-compatibility and may be more code needs to be added to check for code-page expansion. Debug testing showed that the excess string simply returned a double-length string back to VB which was packed with random garbage. I dumped the need for BSTRs wherever possible and dealt with raw ANSI. This may mean that Unicode support quality may vary in other locales but hey, I don't have all week to spend on this and UK/US does me fine. If you want Chinese or Korean locale then get out your compiler and add the extra code-page conversion code!.

• SAFEARRAY strings are very badly documented. I'm still not 100% sure that SafeArrayPutElement() allocates memory internally for any BSTR etc. which is passed to it or whether it simply allocates space for the pointer. I am assuming that it does allocate space which VB then reclaims when it destroys the Variant as it goes out of scope.
  For something this critical to reliability the Microsoft documentation really should make it absolutely crystal-clear.  I've read the contents of countless forums (particularly on many of the issues above and its clear that even some of the guys from Microsoft get it wrong. This shows in some example code too which is often just plain wrong.  E.g. the VB4 Variant example code which resorts to using global pointers for SAFEARRAY returns instead of correctly allocating to variant (pparray or parray). This means that subsequent calls to your function overwrites the contents of any previous VARIANT (oops!).

• Use of intermediary VARIANTs to store a BSTR in SAFEARRAY seemed a bit pointless to me since the examples return everything in a 2-dimensional string array (rows and columns of strings). The only need I could see for this was to be able to return a Variant() array. I wasn't able to do this so I was able to dump a fair bit of code.

• I also decided to dump the OLE temporary memory allocation functions seen in example-code and use SysAllocStringLen and SysFreeString instead.

• Assigning rows and columns can be confusing and as far as I can recall I had to reverse the row/column order in order to be able to use the VB function Ubound() to retrieve the number of columns. This left the SQLite row-count function to return the rows but I decided also to add another useful VARIANT function which returns the number of rows in a 2-dimensional VARIANT array which dispenses with the need for that SQLite call and deals directly with the VARIANT object returned. This means I can also reuse the code elsewhere as a generic function. Therefore
  
  UBound(<Variant>) returns the number of columns
  GetArrayDimensions(<Variant>) returns the number of rows

• MSVC 2008 rejected the following code which I commonly use to create MessageBoxes to help debug a DLL. No idea why but it thinks the const isn't a const!
  

  const int MSG_MAX=256; char msg[MSG_MAX+1]=""; // MSVC says MSG_MAX isn't a const - const expected ?

• Not checking or inadequate checking of API returns. I'm a bit paranoid about checking everything as I hate spending time tracking down problems. Finding unexpected problems within a DLL can be a pain and very time-consuming so it pays to trap for all exceptions from the get-go and it doesn't mean that much extra code.

• The modified code throws a warning 60993 in static const sqlite3_api_routines sqlite3Apis = { ...   but it doesn't seem to matter.

• I needed to compile some of my own code up using CPP enhancements over "C" but the MSVC 2008 compiler defaults to "C". Since it excepts comments and uses C++ commenting I can't fathom this. Nor could I find a useful #pragma which could enable CPP for a single module. Its either global or nothing which means that linked "C" code would probably break. In the end I renamed my SQLite-VB.C file as SQLite-VB.CPP which resolved the problem. A pragma would have been far better.

• I ran into the old problem of the missing CHM help files for listview objects. I'm sure I found them once upon a time but far too often doing stuff with ListViews is guesswork due to poor, or in this case, absent, documentation.

• Re-include blocks need to be added to prevent accidental re-inclusion e.g:
  

  #ifndef SQLITE_VB_H #define SQLITE_VB_H #ifndef SQLITE_VB_C #define SQLITE_VB_C

• Finally, the returned object is not a SAFEARRAY but a VARIANT (not a VARIANT far* either) which means that I have to create a VARIANT instance within the function and assign the correct descriptors to it before returning the whole object via the function-body. Fingers firmly-crossed that VB destroys it properly.

Page top

How It's Done - Modifying the SQLite Code and Adding New Code

Most of the Tannertech and Persistent Realities tutorial steps are valid but my specific needs for VB and some other concerns I had meant I deviated a fair bit with some of the function specs.

My changed declares are as follows (These need to be changed in SQLite3.C and SQLite3.H and in the function pointer struct mentioned above)
AFAIK _stdcall is the same as __stdcall (1, versus 2 underscore prefixes)

In SQLite-VB.H I added the following, although I've left sqlite_get_table to self-prototype (I don't believe in pointless prototyping)

The SQLite-VB.C (or CPP depending on your compiler needs) file needs to have some substantial code added to create the VARIANT matrix, some helper VARIANT functions and the modifications recommended in the other tutorials such as...

A few more changes were made to the demo code. I completely rewrote the listview generation code and tidied up the interface a little bit as well as adding the ability to generate a demo database, use the clipboard and a few other features. The listview will automatically-resize if you change the size of the form and the columns auto-resize to match the input data.

Page top

Demonstration DataFiles

Example Files

So few demo programs like this come with a datafile you can sensibly query so I knocked up a small music CD database using information from Wikipedia. You can download the DB and an SQL query file from here (ZIP format). You can view the SQL file here  The Tannertech site offers a small one with their own, older SQLite v3.4.2  DLL project.

Demo SQL Files

WARNING: The demo SQL file DROPS both demo tables in the current database without prompting (the database  in the filename input area). These tables are called Albums and CDs. * NEVER RUN ANY SQL FILE WITHOUT READING THE SQL SCRIPT FULLY!!! *

I added the ability to load an SQL script file from within the SQLite-VB interface. This affords the ability to have REMarks in the file which are not loaded into the query window. Please, always read the raw SQL file using Notepad. SQLite uses two hyphens "--" as a remark. REM is not part of the SQLite syntax.

All of the examples from Nikolai Shokhirev's SQL tutorial pages should be useable on this test database including complex JOINs etc.

Create the tables using (you can use both consecutively separated by a semi-colon) ...

Insert records using say...

Query the database using ...

Or, to match an exact Artist Name ...

If you want to see if SQLite will "fall-over" try this (it returns over 5,000 rows) ...
(AFAIK it's called the Cartesian Product)

Finally, drop the tables (not other tables in the same database file) when you have finished using ...

You can also "wrap" the entire transaction in the following for vastly-improved speed ...

If you want to play with the application to any greater extent you will need to refer to the SQLite variant of SQL.

You can create and drop tables as well as insert and query data. Unfortunately I'm not an SQL expert so you will have to excuse any poor SQL demo which accompanies the demo program.The small CD Music database has about 50 records.

SQLite has no SHOW TABLES command and one needs to use .tables at the SQLITE> prompt. However it can be emulated using

SELECT name FROM sqlite_master WHERE type = 'table'

or ...

SELECT * FROM sqlite_master

Here are a few test-queries for the Tannertech database should you care to use it: (MyPrograms6.db)

Page top

Virus-Scanners and SQLite Performance Problems

Also, I found that adding data natively in Windows XP was extremely slow. Many seconds, possibly a minute, just to add 50 records. This might be due to the method of structuring the query or perhaps may be a side-effect of the interface. Bizarrely, I found that creating and inputting the test (Albums.db) data set was almost instantaneous on Windows NT running under QEMU 8 in XP/SP2 as well - so its either specific to the file-system, CPU (emulated on QEMU), virus-scanner or operating system on XP.

Looking up further inserts can be speeded up massively by using a BEGIN; ... COMMIT; transaction-wrapper but NT is still about 10 times faster so this doesn't explain why a PC emulator running a 12 year old operating system is faster than native XP on a 1800Ghz CPU!. NT wasn't running a virus-scanner though so I thought I'd do some checks also with the AVG-Professional 7.5 resident shield disabled.

The results are most interesting - it appears to be the virus-scanner at fault... Turning off scanning of files with no filename-extension dramatically improved performance.
SQLite also creates a file called <databasename>.db-journal.  I excluded *.db and *.db-journal but it had no effect so I probably had the syntax wrong. Excluding db-journal,db seemed to have an effect.

Performance - Atomic Transactions

I also expected to be able to run a DROP TABLE followed by CREATE TABLE, INSERT and query all in one file but that didn't seem to work, then I discovered a thing called "atomic commit transactions" and a BEGIN;... COMMIT wrapper.. You need to run DROP TABLE separate from CREATE TABLE and queries separate to both of those unless you use transaction-wrappers.

Can SQLite and the VBSQLite DLL - or Demo Goes "Bang"?

Here's a screenshot of a cross-join on the "SQLSpy" demo "world.db3" database. The cross-join resulted in just under a million rows (974,881 rows) being returned and the query consumed around 1.5Gb (all of my XP system RAM!). The VM took around 1.5Gb and CPU maxed-out at about 90% during accumulation of the results during which RAM was being consumed at about 1Mb/sec. .The query took 23 minutes and I had to shut down most of my apps. I also suspect AVG 7.5 resident shield may have been scanning the DB3 file. Yet, VBSQLite didn't fall over. Note that you can check the count using SELECT COUNT (*) from city,country

Page top

Demonstration Application

All Versions of Windows

The demo app uses a ListView and CommonDialog control. You need to have Microsoft Common Controls (MSCOMCTL32.OCX) and Common Dialog (COMDLG32.OCX) installed on your system. Usually in the \WINDOWS\SYSTEM32 folder. There is no setup program as yet - just the DLL and test EXE.

Copy any OCXs you need to your Windows\System32\ folder then register them on your system using REGSVR32.EXE <filename>.OCX

The EXE and DLL files are UPX-compressed. If you suspect problems then uncompress them using UPX -d <filename>

Windows XP and Vista You will also need to have MSVCR90.DLL etc. on your system (MSVC++ runtime 9.0) as the DLL was compiled with MSVC 9.0 Download the optimal VC++ 9.0 version of VBSQLite.DLL and test program (207Kb ZIP)

<No image>

Windows NT 4.0 and Windows 2000 MSVC++ runtime 9.0 is most probably not going to be installed (or supported?) for NT/2K so I have compiled a separate version of the DLL using MSVC 5.0 (Visual Studio 97) If like me you are a fan of Windows NT for use as a portable O/S then you may be interested. I use NT4.0/SP6a with QEMU as my portable O/S in preference to Linux so I have compiled this version for my own use. Error 53: Not found <dllname> where the DLL is present in the PATH means that one of the DLL's dependencies rather than the DLL itself are missing. You can check this using a copy of Dependency Walker. Image: SQLite VB 1.x and test app running on Windows NT4.0/SP6a (opposite) - click for larger image. Download the legacy-compatible VC++ 5.0 version of VBSQLite.DLL and test program (220Kb ZIP)

Visual BASIC Declarations

The following essential export declarations apply to DLL VBSQlite3.dll release v1.04 - sqlite_get_table() or sqlite_exec() are the main functions you need to use.

REM SQLite functions //

Private Declare Function sqlite3_libversion Lib "vbsqlite3.dll" () _
         As String

Private Declare Function sqlite3_libversion_number Lib _
         "vbsqlite3.dll" () As Long

Public Declare Function sqlite3_open Lib "vbsqlite3.dll" ( _
         ByVal FileName As String, _
         ByRef DB_Handle As Long) As Integer

Public Declare Function sqlite3_open_v2 Lib "vbsqlite3.dll" ( _
         ByVal FileName As String, _
         ByRef DB_Handle As Long, ByVal Flags As Integer, _
         Optional ByVal zVfs As String = vbNullString) As Integer

Public Declare Function sqlite3_close Lib "vbsqlite3.dll" ( _
         ByRef DB_Handle As Long) As Integer

Private Declare Function sqlite3_last_insert_rowid Lib _
         "vbsqlite3.dll" (ByVal DB_Handle As Long) As Long

Private Declare Function sqlite3_changes Lib "vbsqlite3.dll" _
         (ByVal DB_Handle As Long) As Long

Private Declare Function sqlite_get_table Lib "vbsqlite3.dll" ( _
         ByVal DB_Handle As Long, _
         ByVal SQLString As String, _
         ByRef SQLErrorString As String, _
         ByRef SQLReturnCode As Integer) As Variant

REM Must Call SQLString USing StrPtr(SQLString & vbNullChar)
Public Declare Function sqlite_get_table_unicode Lib _
        "vbsqlite3.dll" _
        (ByVal DB_Handle As Long, _
        ByVal SQLString As Long, _
        ByRef SQLErrorString As String, _
        ByRef SQLReturnCode As Integer) As Variant

Public Declare Sub sqlite_progress_handler Lib "vbsqlite3.dll" ( _
         ByVal DB_Handle As Long, _
         ByVal OpcodeCount As Integer, _
         ByVal pCallbackFunct As Any)

REM sqlite_progress_handler() must Call a Function prototyped As: 
REM Public Function SQLiteProgressCallback() As Integer

Public Declare Function sqlite_exec Lib "vbsqlite3.dll" ( _
         ByVal DB_Handle As Long, _
         ByVal SQLString As String, _
         ByVal pCallbackFunct As Any, _
         ByRef pArgument As Any, _
         ByRef SQLErrorString As String) As Integer

REM Must Call SQLString USing StrPtr(SQLString & vbNullChar)
Public Declare Function sqlite_exec_unicode Lib "vbsqlite3.dll" ( _
         ByVal DB_Handle As Long, _
         ByVal SQLString As Long, _
         ByVal pCallbackFunct As Any, _
         ByRef pArgument As Any, _
         ByRef SQLErrorString As String) As Integer

REM sqlite_exec() must Call a VB Function prototyped As:
REM Function ExecCallback(pArgument As Any, ByRef Cols As Integer, _
REM	    ByRef sValues() As String, ByRef sNames() As String) _
REM         As Integer

Private Declare Function sqlite_rowcount Lib "vbsqlite3.dll" () _
        As Long

Public Declare Function sqlite_exec_rowcount Lib _
        "vbsqlite3.dll" () As Long

REM Does Not trigger switch For exec_query_unicode etc.
REM Affects only the resulting data. 
REM You can Call a non-Unicode query With Unicode results

Public Declare Sub SetUnicodeConversion Lib "vbsqlite3.dll" 
         (ByVal BoolState As Boolean) 


REM Toolbox VB5+ functions //
Private Declare Function GetArrayDimensions Lib "vbsqlite3.dll" _
         (ByRef V As Variant) As Long

Private Declare Function GetArrayRows Lib "vbsqlite3.dll" _
         (ByRef V As Variant) As Long

REM DLL Version control //
Private Declare Function VBSQLLibTimeStamp Lib "vbsqlite3.dll" () _
        As String

Private Declare Function VBSQLLibVersion Lib "vbsqlite3.dll" () _
        As Integer

Private Declare Function MSVCVersion Lib "vbsqlite3.dll" () _
        As Integer

Object calling or database-querying within VB is as simple as this...

Dim V As Variant
Dim i As Integer
Dim DB As Long
Dim ErrorString As String
Dim ReturnCode As Integer
Dim QueryString As String
QueryString="Select * from files"
sqlite3_open DBFile, DB
If DB>0 Then
  V= sqlite_get_table(DB, QueryString, ErrorString,ReturnCode)
  If Not IsEmpty(V) Then
    Debug.Print "Table has "; Ubound(V); " column(s)"
    Debug.Print "Table is of type "; TypeName(V)
    Debug.Print "Table has "; GetArrayDimensions(V); " dimensions - should be 2"
    Debug.Print "Table has "; GetArrayRows(V); " row(s)" ' +1 higher than rowcount
    Debug.Print "Query returned "; sqlite_query_rowcount(); " row(s)"
    For i=LBound(V) To UBound(V)
       Debug.Print "Header row:"; i; "=" V(i,0) ' Array Is addressed As Column,Row
    Next
    If GetArrayRows(V)>1 Then
       ' // Address and use the data
    Endif
  Endif
Endif

Download the Quick-Demo project to try a very simple VB query (ZIP format) - view the brief source code here

Page top

Programming Guidelines

• You should never access the Variant returned  from sqlite_get_table() unless it has been checked using IsEmpty()
• The returned Variant is addressed in Column/Row order V(c,r). This is a necessity of being able to use UBound() and LBound() on array zero
• The column and row array offsets are base 0 with row 0 being the header row and data starting at row #1
• Note that the table rowcount will always be +1 higher than the returned number of SQL rows (since it contains the table headers)
• You should not attempt to access the table data unless the table rowcount has been checked and is >0 (or if the array rowcount is >1)
• You cannot iterate through the returned Variant using a For Each ... Next loop. It is not a collection of Variants but a 2D string-array held in a Variant
• Although most SQLite functions are exported none of the others listed in the DEF file have been tested under VB5
• Doing data-conversion from string to numeric values by fetching data from a ListView will be a real pain. ListViews are a pain anyway
  If you aren't comfortable with the data being held in a Variant or have problems with re-entrancy or data-overwriting then you may be better-off fetching the data into a local 2D string-array and writing a function which takes a string array matrix and populates your ListView. That way you can fetch the data from the String array and address it directly for any maths and conversions you need to do.
• Where callback functions are required do NOT change the signature of the called-back VB function or your program will crash

I intend to do a little more testing then release the compiled DLL for public use - with no warranties obviously and I can't guarantee that there are no bugs left in the code or regressions due to changing the SQLite code. The resulting DLL should be useable not only in VB5 but other versions of VB and other dialects of BASIC.

Page top

Handling Unicode and UTF-8

SQLite uses UTF-8 internally (with the ability to use UTF-16), whereas Windows has, for some years now, used UTF-16 or "Unicode" as a multi-byte character set default. That is, unlike ANSI and ASCII each character is represented with more than one byte. There may be some confusion as Microsoft tend to refer to single-character mapped character sets as "multibyte" and those represented by more than one character as "wide". The term is applied as the normal 8-bit character is represented by a "wider" 16-bit character.

Although UTF-8 can represent a very wide range of international character-sets it can be used for most everyday purposes by those in the West freely intermingled with ANSI. The only problem someone in the UK or US might get is with the occasional accented characters. Anyone using a non-English character set will probably not be able to make much use of an SQLite interface unless UTF-8 or UTF-16 is supported.

The problem with Visual BASIC is that, whilst it stores strings internally as Unicode it automatically translates into ANSI and back into Unicode when calling DLLs. This means that special arrangements have to be made in order to pass a UTF-8 encoded string to a DLL so that we can query using non-ANSI characters such as accents. Also, while SQLite returns nicely-encoded UTF-8 data in it's results this is lost when VB strips the data back into ANSI at the end of it's return-journey unless arrangements are made within the DLL to handle this. From v1.04 this DLL supports Unicode or "wide" character strings in the form of UTF-8.

The drawback to Unicode is that each character takes two bytes (two ANSI characters/16-bits) to represent. This may not seem much of a deal until you return say half-a-million records in your results wanting twice the memory space you have available. SQL can be very greedy for memory. Since ANSI is far more lightweight on memory use the default for the library is for Unicode support to be disabled by default. You can turn it on by calling SetUnicodeConversion() with a value of True or False to enable/disable Unicode results. This only affects returned data though; if you want to send a Unicode (UTF-8) query with special characters to SQLite then use one of the two Unicode variant searches, either sqlite_get_table_unicode() or sqlite_exec_unicode(). Unless you have good reasons for using the Unicode versions and cannot do without them then then you may be able to make more efficient use of memory by using the standard VBSqlite functions.

You should also be aware that if calling one of the * _unicode() search functions that since you will be forcing VB to send a Unicode string that it will not bother to null-terminate the string so you will need to do this so the DLL knows how long the string it receives is. You should therefore call the enhanced Unicode search functions like this...

It is possible to test the functionality using the SQLSpy "world.db3" demo-database with the following query for a location with an accented name (São Paulo) ...

If you run the same query in the demo app with Unicode disabled (unticked) then you should get no results returned.

Conclusion

Although fairly painful to convert the resulting code seems to work nicely; although having everything returned as a string may be a pain it is exactly the same end-result as for the previous tutorials. Other SQLite functions may possibly be used to determine the type of each column and convert as required. Conversion from Variant String is pretty simple. The result here is a modest test program and a useful DLL.

During long queries, taking perhaps 10 minutes or more, some callback interface is required to provide a progress-bar or other reassurance to the user.

It is clear that returning the entire query-result in a single variant is a memory-greedy method. Where this is transferred into say a ListView or other display/handling object then there will, at some point, be three copies of that table held in memory. One by SQLite, the second in the returned Variant and the third in the display object. This is of no consequence for small queries but for those returning maybe a million rows it can prove to be a problem. The alternative is to query SQLite then fetch the rows into a Variant a row-at-a-time and place into the query object before (optionally) deleting the Variant. This should reduce the memory footprint by 30%. A test "cross-join" showed the memory used in a small table to be about 750Mb so a ballpark guess is a reduction of say 250Mb on a query of this size.

I noted that adding items to the listview using sqlite_exec() used large amounts of  Virtual Memory but little physical memory until the search was terminated. At that point most of the VM was transferred and physical memory was swallowed up. A 300,000 row cross-join query on the world.db3 file used 1.2Gb of VM which was then transferred into physical memory. Fortunately I had 1.5Gb installed so I could limp along and terminate the app. I added the ability to clear the ListView object and doing this then showed used memory to fall right back down to 35Mb so I'm confident that this is an indictment of the greediness of ListView objects for storage rather than the symptom of a memory-leak in the sqlite_exec() callback code. The moral is, if you are storing any more than a few thousand rows it may be more sensible to write out the results to a temporary file or another database. Although I confess I am no SQL expert I can see that cross-joins (or Cartesian products) are excellent for trying to stress a DB application to death.

There is huge potential for the use of the SQLite engine and some of the the VBToolbox console/CGI features to quickly generate server back-end CGI applications. Remember that you can also link to PHP and PHP databases since PHP already handles SQLite very well. You may also be able to port data between MS-Access and SQLite.

Finally, SQLite is a powerful, incredibly compact and fast database to use with Visual BASIC (once any virus-scanner issues are resolved)

Using SQLite 2 Databases With This DLL

You can export your SQLite 2 database into an SQL backup file and should be able to re-import the file back into a database for testing purposes up to the size limit of the text box input area which is coded to workaround the artificial 64Kb Visual BASIC limit . Better still use the command-line EXE downloadable from SQLite.org

Other SQLite Add-Ons

Firefox has a very nice SQLite database-manager add on (XPI) available from here

SQLSpy is also extremely good and you may want to look at SQLite Code Factory

Page top

Updates and News

See above for the latest VB declares

• v1.04 - 28/07/2008 - Added Unicode query support via sqlite_get_table_unicode() and sqlite_exec_unicode()
  Please note that both of these methods require the use of StrPtr() rather than converting the query to a byte-array
  Also added the Quick-Demo project - a very simple VB5 project including source, EXE, DLL and example database
• v1.03 - 26/07/2008 - Demo app version update - adds callbacks to sqlite_get_table, cached updates to sqlite_exec. Added limits to number of records displayed in the listview with option to export the whole data set as a CSV file if larger. Now works fine with up to a million rows. Remember this is only a demo/test app. Added Unicode (UTF-16) support to the DLL with configurable Unicode support on the test app.
• v1.02 - 24/07/2008 - (Pending release) Added sqlite_exec() which calls sqlite3_exec with a callback interface to a VB function of your own choosing
  The SQLite/VB callback function can cancel the transaction, which may be useful should you inadvertently submit a silly query
• v1.01 - 22/07/2008 - A few bugfixes. Added code to report more version information. VC9 and VC5 versions to support NT/2K
  Legacy Windows NT/2K support
• v1.00 - 21/07/2008 - First version offering sqlite_get_data rewritten from inspiration by other code to return a Variant() array
  Very fast but no ability to cancel a transaction which can be dangerous if you submit one which uses all your system memory

Things Left To Do

Update: April 2010 - This updates will probably never get done now as there has been no general interest in the project, nor have I found a use for the module with MicroApache. It was interesting to do at the time but my time is now swallowed up with other things.

• Persuade someone to do some in-the-field testing - especially for memory-leaks
• Functions to find the data type for each SQL column and map to VB types for conversion
• Functions to detect what tables are in the DB etc.
• Rather than editing the SQLite source, in future I will rewrite the lot as a set of wrapper-functions
• Add bugfixes and other essential functions exported from SQLite
• Document the functionality of each exported function
• Provide the ability to save/export data in the demo application so it may be of some practical use

• DONE Allow UTF-8 queries
• DONE Handle UTF-8 conversion to something more useful than plain ANSI
• DONE Write a version string function for the wrapper DLL to enable calling apps to do version control handling
• DONE Generate a test database I can release for public use (the others available out there may be copyrighted)
• DONE Do some performance testing on hard disk and RAMdisk
• DONE Query without complete table return combined with fetch-table row to reduce memory-footprint
• DONE Implement a progress callback-handler for sqlite_get_table()
• DONE Implement query with callback for progress-bars

  void sqlite3_progress_handler(sqlite3* pDB, int nCalls, int(*funct)(void*), void* pCalledOK);

• DONE Implement sqlite_cancel() to cancel "apparently hung" queries

Page top

Copyright Information

This page and software written by the page owner, unless otherwise stated, is Copyright (c) M Shaw 2008-2021 but released as freeware
SQLite is in the Public Domain under free licence, and is the product of D Richard Hipp and his team at Hwacl Applied Software Research
The example source-code in this page was highlighted statically using CGIHighlight HTML source code highlighter