The following was written in order to tie a VB.NET program into a SQLite3 database without all the extra weight associated with other SQLite / .NET solutions. It is STRICTLY a wrapper to SQLite3 DLL. It does NOT encompass every function of the C API - as there are issues related to callbacks and notifications from the DLL back to VB.NET (incompatible calling conventions - required calling convention not directly supported by .NET.) Aggregates and adding of SQL Functions is also not supported - for the same reason.
John Clymer
' The author or authors of this code dedicate any and all copyright interest in this code to the public domain.
' We make this dedication for the benefit of the public at large and to the detriment of our heirs and successors.
' We intend this dedication to be an overt act of relinquishment in perpetuity of all present and
' future rights to this code under copyright law.
' SQLite3 VB.Net wrapper
' John Clymer
' June 2013
'
' Module provides a wrapper between VB.NET and SQLITE3.DLL
'
'
' This is NOT a 100% translation of the full API - it is only a basic wrapper
' It is not easily possible to support callbacks from the DLL due to calling convention differences
' between .NET and the stock SQLITE3.DLL
'
' sql_exec is simulated by a VB.Net routine - thus allowings simulated callback for row results
'
' This unit DOES provide the "full" "intro" level C/C++ API as per: http://www.sqlite.org/cintro.html
'
' The wrapper is simply a wrapper - it does NOT take the place of system.data.sqlite
'
' Unsupported: Callbacks, Hooks, Virtual Tables, Creating Functions,
'
Imports System.Runtime.InteropServices
Public Module SQLite3Wrapper
#Region " Basic Result Codes "
' result codes
Public Const SQLITE_OK As Integer = 0
Public Const SQLITE_ERROR As Integer = 1
Public Const SQLITE_INTERNAL As Integer = 2
Public Const SQLITE_PERM As Integer = 3
Public Const SQLITE_ABORT As Integer = 4
Public Const SQLITE_BUSY As Integer = 5
Public Const SQLITE_LOCKED As Integer = 6
Public Const SQLITE_NOMEM As Integer = 7
Public Const SQLITE_READONLY As Integer = 8
Public Const SQLITE_INTERRUPT As Integer = 9
Public Const SQLITE_IOERR As Integer = 10
Public Const SQLITE_CORRUPT As Integer = 11
Public Const SQLITE_NOTFOUND As Integer = 12
Public Const SQLITE_FULL As Integer = 13
Public Const SQLITE_CANTOPEN As Integer = 14
Public Const SQLITE_PROTOCOL As Integer = 15
Public Const SQLITE_EMPTY As Integer = 16
Public Const SQLITE_SCHEMA As Integer = 17
Public Const SQLITE_TOOBIG As Integer = 18
Public Const SQLITE_CONSTRAINT As Integer = 19
Public Const SQLITE_MISMATCH As Integer = 20
Public Const SQLITE_MISUSE As Integer = 21
Public Const SQLITE_NOLFS As Integer = 22
Public Const SQLITE_AUTH As Integer = 23
Public Const SQLITE_FORMAT As Integer = 24
Public Const SQLITE_RANGE As Integer = 25
Public Const SQLITE_NOTADB As Integer = 26
Public Const SQLITE_NOTICE As Integer = 27
Public Const SQLITE_WARNING As Integer = 28
Public Const SQLITE_ROW As Integer = 100
Public Const SQLITE_DONE As Integer = 101
#End Region
#Region " Conflict Resolution Result Codes "
' Conflict Resolution result codes
Const SQLITE_ROLLBACK As Integer = 1
Const SQLITE_IGNORE As Integer = 2
Const SQLITE_FAIL As Integer = 3
'Const SQLITE_ABORT As Integer = 4
Const SQLITE_REPLACE = 5
#End Region
#Region " Column Type Codes "
' column type codes
Const SQLITE_INTEGER = 1
Const SQLITE_FLOAT = 2
Const SQLITE_TEXT = 3
Const SQLITE3_TET = 3
Const SQLITE_BLOB = 4
Const SQLITE_NULL = 5
#End Region
#Region " Column Status Constants "
Public Const SQLITE_DBSTATUS_LOOKASIDE_USED = 0
Public Const SQLITE_DBSTATUS_CACHE_USED = 1
Public Const SQLITE_DBSTATUS_SCHEMA_USED = 2
Public Const SQLITE_DBSTATUS_STMT_USED = 3
Public Const SQLITE_DBSTATUS_LOOKASIDE_HIT = 4
Public Const SQLITE_DBSTATUS_LOOKASIDE_MISS_SIZE = 5
Public Const SQLITE_DBSTATUS_LOOKASIDE_MISS_FULL = 6
Public Const SQLITE_DBSTATUS_CACHE_HIT = 7
Public Const SQLITE_DBSTATUS_CACHE_MISS = 8
Public Const SQLITE_DBSTATUS_CACHE_WRITE = 9
Public Const SQLITE_DBSTATUS_MAX = 8
#End Region
#Region " Statement Status Constants "
Public Const SQLITE_STMTSTATUS_FULLSCAN_STEP = 1
Public Const SQLITE_STMTSTATUS_SORT = 2
Public Const SQLITE_STMTSTATUS_AUTOINDEX = 3
#End Region
Const DLLName = "c:\disks\sqlite\sqlite3.dll"
Delegate Function SQLite3Callback(ByRef InstParm As Object, ByVal NumCols As Integer, ByVal FieldNames() As String, ByVal FieldValues() As Object)
#Region " DLL Export Declarations "
#Region " Basic Connection Functions "
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_open(<MarshalAs(UnmanagedType.LPStr)> ByVal Filename As String, ByRef sqlite3 As IntPtr) As Integer
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_open_v2(<MarshalAs(UnmanagedType.LPStr)> ByVal Filename As String, ByRef sqlite3 As IntPtr, ByVal IntFlags As Int32, ByRef VFSName As String) As Integer
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_close(ByVal sqlite3 As IntPtr) As Integer
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_close_v2(ByVal sqlite3 As IntPtr) As Integer
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_prepare(ByVal sqlite3 As IntPtr, ByVal SQLQuery As String, ByVal nByte As Integer, ByRef ppStmt As IntPtr, ByRef pzTail As String) As Integer
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_prepare_v2(ByVal sqlite3 As IntPtr, ByVal SQLQuery As String, ByVal nByte As Integer, ByRef ppStmt As IntPtr, ByRef pzTail As String) As Integer
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_step(ByVal ppStmt As IntPtr) As Integer
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_reset(ByVal ppStmt As IntPtr) As Integer
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_finalize(ByVal ppStmt As IntPtr) As Integer
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_get_autocommit(ByVal sqlite3 As IntPtr) As Integer
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Sub sqlite3_interrupt(ByVal sqlite3 As IntPtr)
End Sub
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_last_insert_rowid(ByVal sqlite3 As IntPtr) As Int64
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_sql(ByVal ppStmt As IntPtr) As String
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_stmt_busy(ByVal ppStmt As IntPtr) As Integer
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_stmt_readonly(ByVal ppStmt As IntPtr) As Integer
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_stmt_status(ByVal ppStmt As IntPtr, ByVal op As Int32, ByVal ResetFlag As Int32) As Integer
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_total_changes(ByVal sqlite3 As IntPtr) As Int32
End Function
#End Region
#Region " Result Column Handling "
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_column_count(ByVal ppStmt As IntPtr) As Integer
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_column_name(ByVal ppStmt As IntPtr, ByVal N As Integer) As String
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_column_type(ByVal ppStmt As IntPtr, ByVal N As Integer) As Integer
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_column_int(ByVal ppStmt As IntPtr, ByVal N As Integer) As Int32
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_column_int64(ByVal ppStmt As IntPtr, ByVal N As Integer) As Int64
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_column_double(ByVal ppStmt As IntPtr, ByVal N As Integer) As Double
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_column_text(ByVal ppStmt As IntPtr, ByVal N As Integer) As String
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_column_blob(ByVal ppstmt As IntPtr, ByVal n As Integer) As IntPtr
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_column_bytes(ByVal ppStmt As IntPtr, ByVal N As Integer) As Integer
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_column_database_name(ByVal ppStmt As IntPtr, ByVal ColNum As Int32) As Integer
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_column_table_name(ByVal ppStmt As IntPtr, ByVal ColNum As Int32) As Integer
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_column_origin_name(ByVal ppStmt As IntPtr, ByVal ColNum As Int32) As Integer
End Function
#End Region
#Region " Column Binding Functions "
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_clear_bindings(ByVal ppStmt As IntPtr) As Integer
End Function
' BLOB - FreePtr MUST EQUAL SQLITE_TRANSIENT - as we can not provide a callback to free the memory
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_bind_blob(ByVal ppStmt As IntPtr, ByVal N As Integer, ByRef b() As Byte, ByVal bsz As Integer, ByVal FreePtr As IntPtr) As Integer
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_bind_double(ByVal ppStmt As IntPtr, ByVal N As Integer, ByRef val As Double) As Integer
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_bind_int(ByVal ppStmt As IntPtr, ByVal N As Int32, ByRef val As Int32) As Integer
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_bind_int64(ByVal ppStmt As IntPtr, ByVal N As Int32, ByRef val As Int64) As Integer
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_bind_null(ByVal ppStmt As IntPtr, ByVal N As Int64) As Integer
End Function
' BLOB - FreePtr MUST EQUAL SQLITE_TRANSIENT - as we can not provide a callback to free the memory
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_bind_text(ByVal ppStmt As IntPtr, ByVal N As Integer, ByVal val As String, ByVal bsz As Integer, ByVal FreePtr As IntPtr) As Integer
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_bind_zero_blob(ByVal ppStmt As IntPtr, ByVal N As Int32, ByRef blobsize As Int32) As Integer
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_bind_parameter_count(ByVal ppStmt As IntPtr) As Integer
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_bind_parameter_index(ByVal ppStmt As IntPtr, ByVal idxname As String) As Integer
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_bind_parameter_name(ByVal ppStmt As IntPtr, ByVal N As Int32) As Integer
End Function
#End Region
#Region " Blob Handling "
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_blob_open(ByVal sqlite3 As IntPtr, ByVal zDb As String, ByVal zTable As String, ByVal zColumn As String, ByVal iRowNum As Int64, ByVal Flags As Int32, ByRef blobptr As IntPtr) As Integer
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_blob_close(ByVal blobptr As IntPtr) As Integer
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_blob_bytes(ByVal blobptr As IntPtr) As Integer
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_blob_read(ByVal blobptr As IntPtr, ByRef b() As Byte, ByVal N As Int32, ByVal Offset As Int32) As Integer
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_blob_write(ByVal blobptr As IntPtr, ByRef b() As Byte, ByVal N As Int32, ByVal Offset As Int32) As Integer
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_blob_reopen(ByVal blobptr As IntPtr, ByVal NewRowNum As Int64) As Integer
End Function
#End Region
#Region " Miscellaneous Functions "
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_limit(ByVal sqlite3 As IntPtr, ByVal Id As Int32, ByVal NewVal As Int32) As Integer
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_db_filename(ByVal sqlite3 As IntPtr, ByVal dbName As String) As String
End Function
Public Function sqlite3_db_filename(ByVal sqlite3 As IntPtr) As String
Return sqlite3_db_filename(sqlite3, "main")
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_db_handle(ByVal ppStmt As IntPtr) As IntPtr
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_db_readonly(ByVal sqlite3 As IntPtr, ByVal dbName As String) As Integer
End Function
Public Function sqlite3_db_readonly(ByVal sqlite3 As IntPtr) As Integer
Return sqlite3_db_readonly(sqlite3, "main")
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_db_release_memory(ByVal sqlite3 As IntPtr) As Integer
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_db_status(ByVal sqlite3 As IntPtr, ByVal op As Int32, ByRef CurrVal As Int32, ByRef HighWater As Int32, ByVal ResetFlag As Int32) As Integer
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_enable_shared_cache(ByVal val As Int32) As Integer
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_release_memory(ByVal amt As Integer) As Integer
End Function
#End Region
#Region " Extension Handling "
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_load_extension(ByVal sqlite3 As Int32, ByVal zFileName As String, ByVal EntryPoint As Int32, ByRef ErrMsg As String) As Integer
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_enable_load_extension(ByVal sqlite3 As IntPtr, ByVal OnOrOff As Int32) As Integer
End Function
#End Region
#Region " Error Handling and Info "
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_errcode(ByVal sqlite3 As IntPtr) As Integer
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_extended_errcode(ByVal sqlite3 As IntPtr) As Integer
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_errmsg(ByVal sqlite3 As IntPtr) As String
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_errstr(ByVal ErrCode As Int32) As String
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_extended_result_codes(ByVal sqlite3 As IntPtr, ByVal OnOrOff As Integer) As Integer
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_libversion() As String
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_sourceid() As String
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_libversion_number() As Int32
End Function
#End Region
#Region " String Handling, Testing and Comparing "
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_strglob(ByVal zGlob As String, ByVal zStr As String) As Int32
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_stricmp(ByVal zStr1 As String, ByVal zStr2 As String) As Int32
End Function
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl)>
Public Function sqlite3_strnicmp(ByVal zStr1 As String, ByVal zStr2 As String, ByVal nCount As Int32) As Int32
End Function
#End Region
#End Region
#Region "SQL_Exec Emulator and internal usage"
' internal (to the DLL) sqlite_exec - can NOT be used for queries returning results !!! (cdecl callbacks not supported.)
<DllImport(DLLName, SetLastError:=True, CallingConvention:=CallingConvention.Cdecl, EntryPoint:="sqlite3_exec")>
Public Function sqlite3_int_exec(ByVal sqlite3 As IntPtr, <MarshalAs(UnmanagedType.LPStr)> ByVal SQLQuery As String, ByVal CallBack As Integer, ByVal Arg1 As Integer, <MarshalAs(UnmanagedType.LPStr)> ByVal ErrMsg As String) As Integer
End Function
Public Function sqlite3_exec_noresults(ByVal sqlite3 As IntPtr, ByVal SQLQuery As String, ByVal ErrMsg As String) As Integer
Return sqlite3_int_exec(sqlite3, SQLQuery, 0, 0, ErrMsg)
End Function
Public Function sqlite3_exec(ByVal sqlite3 As IntPtr, ByVal SQLQuery As String, ByVal Callback As SQLite3Callback, ByVal CallbackParm As Object, ByVal ErrMsg As String) As Integer
' VB.Net does NOT support callbacks with the cdecl calling convention - so we roll up our sleves and implement the exec function ourselves from the primitives
Dim res As Integer = SQLITE_OK
Dim stmt As IntPtr
res = sqlite3_prepare_v2(sqlite3, SQLQuery, Len(SQLQuery), stmt, Nothing)
If res = SQLITE_OK Then
res = sqlite3_step(stmt)
Do While res = SQLITE_ROW
' res = sqlite3_reset(stmt)
Dim nCols As Integer = sqlite3_column_count(stmt)
Dim i As Integer, ColType As Integer
Dim Values(nCols - 1) As Object
Dim Names(nCols - 1) As String
For i = 0 To nCols - 1
Names(i) = sqlite3_column_name(stmt, i)
ColType = sqlite3_column_type(stmt, i)
Select Case ColType
Case SQLITE_INTEGER
Values(i) = sqlite3_column_int(stmt, i)
Case SQLITE_TEXT
Values(i) = New String(sqlite3_column_text(stmt, i))
Case SQLITE_FLOAT
Values(i) = sqlite3_column_double(stmt, i)
Case SQLITE_BLOB
Values(i) = New String("BLOB")
Case SQLITE_NULL
Values(i) = Nothing
End Select
Next
If Callback IsNot Nothing Then
' invode the callback function
If Callback(CallbackParm, nCols, Names, Values) <> 0 Then
Exit Do
End If
End If
res = sqlite3_step(stmt)
Loop
End If
Return res
End Function
#End Region
End Module