Using SQLite in Visual Basic

You cannot use SQLite from Visual Basic without making some changes to the DLL. There are probably some workarounds, but I prefer to stick with a single DLL instead of extra ActiveX solutions. If I want an ActiveX DLL, I could just as well use Microsoft Access?

The first time that I saw VB using SQLite was at this site www.ag-software.com when I was starting on a software project I noticed that the version is way behind the current version of SQLite. Therefore, I have tried to port the C code to support the new SQLite version. Quite some of the credit goes to the code that be found here: http://www.ag-software.com/?tabindex=8&tabid=14

It should take like 15-20 minutes, depending on your experience with the Visual Studio IDE.

Downloadable source

If you want to download my SQLiteVB project, which should (hopefully work), then download this file which contains the Visual Studio project and code.

Note: Someone else has made another great tutorial for SQLite3 and VB. Check it out here: http://www.tannertech.net/sqlite3vb/

SQLite

First download SQLite3 at www.sqlite.org. Get the one with full source code included, and the windows version. At he time of writing I used "sqlite-source-3_2_2.zip"

Creating your Visual Studio project

We need a new Visual Studio project, which will contain the modified source code of SQLite and which will compile to our DLL. I'm using Visual Studio .NET for this.

New project:

DLL type

Unzip the SQLite source code in the created directory, for example E:\SQLiteVB\source. After all files are unzipped, go to Windows Explorer and order the files by file type.

  
• Drag the ".c" and the ".def" file into the "Source files" folder of your Visual Studio project.
  
• Drag and drop the ".h" files in the "Header files" folder in the Visual Studio IDE.

All the necessary files are now in the Visual Studio project. Now we need to do a little more work.

Converting the code

The C method of exporting (making accessible) functions is different than VB can use. Therefore, some changes have to be made to support this other calling convention.

sqlite3.h

Add the following include files to the sqlite3.h file:

  #include <windows.h> /* Needed for wrapper */
  #include <stdio.h>
  #include <io.h>
  #include <oleauto.h>

  #include <wtypes.h>

They should be placed near the top, just under some lines that look like these:
#ifndef _SQLITE3_H_
#define _SQLITE3_H_


Then, find the following lines of code:

const char *sqlite3_libversion(void);
int sqlite3_libversion_number(void);

int sqlite3_close(sqlite3 *);

sqlite_int64 sqlite3_last_insert_rowid(sqlite3*);

int sqlite3_changes(sqlite3*);

int sqlite3_open(
const char *filename, /* Database filename (UTF-8) */
sqlite3 **ppDb /* OUT: SQLite db handle */
);
int sqlite3_open16(
const void *filename, /* Database filename (UTF-16) */
sqlite3 **ppDb /* OUT: SQLite db handle */
);


Change these to:

BSTR __stdcall sqlite3_libversion(void);
BSTR __stdcall sqlite3_libversion_number(void);

sqlite_int64 __stdcall sqlite3_last_insert_rowid(sqlite3*);

int __stdcall sqlite3_changes(sqlite3*);

int __stdcall sqlite3_open(
const char *filename, /* Database filename (UTF-8) */
sqlite3 **ppDb /* OUT: SQLite db handle */
);
int __stdcall sqlite3_open16(
const void *filename, /* Database filename (UTF-16) */
sqlite3 **ppDb /* OUT: SQLite db handle */
);


After you have changed these header files, it's time to change the actual code. This is done in the "main.c" file.

Change:

sqlite_int64 sqlite3_last_insert_rowid(sqlite3 *db)

To:

sqlite_int64 __stdcall sqlite3_last_insert_rowid(sqlite3 *db)

Change:

int sqlite3_changes(sqlite3 *db)

To:

int __stdcall sqlite3_changes(sqlite3 *db)

Change:

int sqlite3_close(sqlite3 *db)

To:

int __stdcall sqlite3_close(sqlite3 *db)

Change:

int sqlite3_open

To:

int __stdcall sqlite3_open

Change:

int sqlite3_open16

To:

int __stdcall sqlite3_open16

Change:

const char *sqlite3_libversion(void){ return sqlite3_version; }

To:

BSTR __stdcall sqlite3_libversion(void){ return SysAllocStringByteLen( sqlite3_version,strlen( sqlite3_version) ); }

Change:

int sqlite3_libversion_number(void){ return SQLITE_VERSION_NUMBER; }

To:

  BSTR __stdcall sqlite3_libversion_number(void){ return SQLITE_VERSION_NUMBER; }

We're almost there!

There is just one single thing left to do. We need to be able to get data back from for example "SELECT" queries. VB uses "SafeArray's" internally to represent an array. We need to have SQLite put the data in such an array as well.

Add two new files (by right clicking on the appropriate header/source folder, and choosing "Add file":

It is VERY important that your file has ".c" as extension and NOT ".cpp"

We got two empty files now, add the following code to the .h file:

#include <windows.h>
#include <stdio.h>
#include <io.h>
#include <oleauto.h>
#include <wtypes.h>
#include "sqlite3.h"

#define VB_SQLITE_VERSION "3.0.0"

SAFEARRAY * __stdcall sqlite_get_table(sqlite3 * , const char *, BSTR * );
BSTR __stdcall     sqlite_libversion(void);
int __stdcall     number_of_rows_from_last_call(void);


The first function (sqlite_get_table) will be used t oexecute all our queries. Sqlite_libversion, simply returns the VB_SQLITE_VERSION constant eventually and "number_of_rows_from_last_call" returns the number of results matching your select query.

I made a few changes to the original code from A-G Software, which is necessary for SQLite3.

Open the VBSQL.c file and place the following code:

#include "vbsql.h"

int NumberofRowsReturned=0;

int _stdcall number_of_rows_from_last_call(void) {
  return NumberofRowsReturned;
}
SAFEARRAY * __stdcall sqlite_get_table(
  sqlite3 *db, /* The database on which the SQL executes */
  const char *zSql, /* The SQL to be executed */          
         BSTR *ErrMsg        /* Write error messages here */
) /* Return the SAFEARRAY */
{

     // Temp result fields
  char **SQL_Results;
  char *ErrMessage= 0;
  int NumberofCols;
  int NumberRows;
  int rc;
  SAFEARRAY *resultp=NULL;
  NumberofRowsReturned=0;
  rc=sqlite3_get_table(
    db ,
    zSql ,
    &SQL_Result
s,     &NumberRows,
    &NumberofCols,
    &ErrMessage);

     if( rc==SQLITE_OK ) {
    NumberofRowsReturned=NumberRows;
    if (NumberofCols> 0 ) {
      //We have a resultset so transform this into a SAFEARRAY
      // Create SAFEARRAY
      SAFEARRAYBOUND SA_Bounds[2];
      //SAFEARRAY FAR* resultp = NULL;
      BSTR bstrTemporyStringHolder = NULL;
      VARIANT tmpVariant;
      BSTR bstr1 = NULL;
      HRESULT hr;
      LPOLESTR* TempWideDataHolder = NULL;
      ULONG WideCharacterLength;
      ULONG intCurrentColumn ; // Tempory counter for looping
      ULONG intCurrentRow ; // Tempory counter for looping
      ULONG sqlite_return_array_int=0;

             TempWideDataHolder = CoTaskMemAlloc(1);
      // Set up array bounds
      SA_Bounds[0].cElements = NumberRows + 1;
      SA_Bounds[0].lLbound = 0;
      SA_Bounds[1].cElements = NumberofCols;
      SA_Bounds[1].lLbound = 0;

      //Create array
      resultp = SafeArrayCreate(VT_VARIANT, 2, SA_Bounds);
             // Start Place column headers in first row
      for (intCurrentColumn=0;intCurrentColumn< (ULONG)NumberofCols ;intCurrentColumn++) {
        long indices[] = {0,intCurrentColumn};
        VariantInit(&tmpVariant);
        // Start Convert to unicode
        WideCharacterLength=strlen(SQL_Results[intCurrentColumn]) + 1 ;
        *TempWideDataHolder = (LPOLESTR) CoTaskMemAlloc(WideCharacterLength*2);
        MultiByteToWideChar( CP_ACP, 0, SQL_Results[intCurrentColumn],
          strlen(SQL_Results[intCurrentColumn])+1, *TempWideDataHolder,
          WideCharacterLength );
        //Convert the VT_Object element to the BSTR - Here we may change if SQLite type casts
        tmpVariant.bstrVal=SysAllocString(*TempWideDataHolder);
        V_VT(&tmpVariant) = VT_BSTR;
        CoTaskMemFree(*TempWideDataHolder);
        // End convert to unicode
        // Safearray Column headers  
        hr = SafeArrayPutElement(resultp, indices, &tmpVariant);
        SysFreeString(tmpVariant.bstrVal);
        VariantClear(&tmpVariant);
      }

      // End Place column headers in first row
      // Start Loop through array and fill SAFEARRAY
      for (intCurrentRow=1;intCurrentRow<=(ULONG)NumberRows ;intCurrentRow++) {
        for (intCurrentColumn=0;intCurrentColumn< (ULONG)NumberofCols ;intCurrentColumn++) {
          long indices[] = {intCurrentRow , intCurrentColumn};
          // set return array index
          VariantInit(&tmpVariant);
          sqlite_return_array_int= (intCurrentRow * (ULONG)NumberofCols) + intCurrentColumn ;
          // Start Convert to unicode
          if (SQL_Results[sqlite_return_array_int]) {
            WideCharacterLength=strlen(SQL_Results[sqlite_return_array_int]) + 1 ;
            *TempWideDataHolder= (LPOLESTR) CoTaskMemAlloc(WideCharacterLength*2);
            MultiByteToWideChar( CP_ACP, 0, SQL_Results[sqlite_return_array_int],
              strlen(SQL_Results[sqlite_return_array_int])+1, *TempWideDataHolder,
              WideCharacterLength );

                         //Convert the VT_Object element to the BSTR - Here we may change if SQLite type casts
            tmpVariant.bstrVal=SysAllocString(*TempWideDataHolder);
            V_VT(&tmpVariant) = VT_BSTR;
            CoTaskMemFree(*TempWideDataHolder);
            // End convert to unicode
            hr = SafeArrayPutElement(resultp, indices, &tmpVariant);
            SysFreeString(tmpVariant.bstrVal);
            VariantClear(&tmpVariant);
          }
        }
      }
      // END Loop through array and fill SAFEARRAY
    }
  }
  else
  {
    *ErrMsg = SysAllocStringByteLen( ErrMessage,strlen( ErrMessage) );
  }

  sqlite3_free(ErrMessage);
  sqlite3_free_table(SQL_Results);
  return resultp;
}

BSTR __stdcall sqlite_libversion()
{
  return SysAllocStringByteLen( VB_SQLITE_VERSION,strlen( VB_SQLITE_VERSION) );
}


I've only changed a few things in here, so SQLite3 is supported.
All code changes are done now. But before we start compiling, we need to change a few more configuration properties.

Right click on the project name in the project explorer (SQLLiteVB). Then, make sure the preprocessor directives are like these:

I guess the "SQLITEVB_EXPORTS" is the important one.

Then go to "Inputs" and add the export file:

After you have done this, you only need to add our 'home made' functions to the Exports file: Double-click on "sqlite3.def", and you will see a list of function names. Scroll down, and add at the bottom: sqlite_get_table
sqlite_libversion
number_of_rows_from_last_call


Save the file

If you want to use "TCL", and have the files for this, you can compile. If you have no idea what TCL is delete the following file from your project:

  
• Tclsqlite.c

Now try to compile!

The compiled .dll should be available in a \debug directory somewhere in your project directory. When in release mode, it will be in \release.

If you get a compile error, saying that an include file could not be found, you might have the "VBSQL.h and VBSQL.c" in the wrong directory. Or relocate them (and add them to your project again) or change the "Additional include directories" under the project properties (C/C++->General).

When everything compiles OK, change the build type to "Release" and change the project properties with the earlier explained steps. (Debug and release use two separate configurations). Release builds do not contain debug information and should work faster than debug builds.

The following declarations can now be used in VB:

'// SQL Lite dll declarations:

Private Declare Sub sqlite3_open Lib "SQLiteVB.dll" (ByVal FileName As String, ByRef handle As Long)
Private Declare Sub sqlite3_close Lib "SQLiteVB.dll" (ByVal DB_Handle As Long)
Private Declare Function sqlite3_last_insert_rowid Lib "SQLiteVB.dll" (ByVal DB_Handle As Long) As Long
Private Declare Function sqlite3_changes Lib "SQLiteVB.dll" (ByVal DB_Handle As Long) As Long

Private Declare Function sqlite_get_table Lib "SQLiteVB.dll" (ByVal DB_Handle As Long, ByVal SQLString As String, ByRef errstr As String) As Variant()
Private Declare Function sqlite_libversion Lib "SQLiteVB.dll" () As String ' Now returns a BSTR

'// This function returns the number of rows from the last sql statement. Use this to ensure you have a valid array
Private Declare Function number_of_rows_from_last_call Lib "SQLiteVB.dll" () As Long


SQLite3 offers more functions than that our DLL exports. But the above functions should take care of most functions for you. Use sqlite_get_table to execute queries, whether it's SELECT or INSERT or UPDATE, etc.