Hi
Can VB6 database app use SQLITE or another embedded SQL server ?
Elico
Printable View
Hi
Can VB6 database app use SQLITE or another embedded SQL server ?
Elico
Yes. For SQLite the best way I know is by using Olaf's vbRichClient. You can get it here
You can also look for free or commercial ODBC Drivers for SQLite. There are good commercial OLEDB Providers too, but the only free one I have found seems a bit buggy. These can be preferable to kludgey ad-hoc APIs because they play well with VB6 controls and data binding and anything you learn is transferable to other databases.
One thing to be careful of is the version of the SQLite engine a given "solution" makes use of. Many embed the engine which means unless they issue new updates frequently you can be stuck with some nasty, nasty bugs. SQLite gets fairly serious bug fixes on a regular basis.
As it says at the official site:
And that'll change again in a week or two. ;)Quote:
Version 3.9.2 of SQLite is recommended for all new development.
Recent fixes: SQLite Release 3.9.2 On 2015-11-03
Just look at these scary bugs fixed in 3.9.2 :eek:
Dilettante is a good fellow but doesn't like Olaf *sigh* :rolleyes:
ThanksQuote:
Originally Posted by Carlos Rocha
Elico
you could always use the standard VB6 Embedded Database engine... that's available on every windows installation.
Then you won't need to install or update anything - as it's already part of the OS.
https://en.wikipedia.org/wiki/Micros...atabase_Engine
Jet is a fine choice. Stable, powerful, supports normal VB data types, gets regular updates as part of Windows.
But it isn't much help if you have to work with a SQLite database from another platform.
The Jet-Engine is not bad - but SQLite is better (faster) in almost any regard -Quote:
Originally Posted by dilettante
and it supports features the JET-engine simply doesn't have.
- InMemory-DBs
- Triggers
- Userdefinable SQL-functions
- CTEs
- Full-Text-Search
- JSON-queries
SQLite is not "a database-engine written for a single platform" -Quote:
Originally Posted by dilettante
it's an engine which supports *all* platforms - and its DB-File-format
is universally accessible on any of them.
Besides, SQLite is officially part of Win10 - and will receive regular updates on this platform now too.
Olaf
Quote:
Originally Posted by Schmidt
Many thanks you all
Elico
Many thanks
You all
Elico
FYI. Hijack this thread for my future reference.
Edited: Attached the DLL source.Code:
Option Explicit
Public Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (pDst As Any, pSrc As Any, ByVal ByteLen As Long)
Public Declare Sub sqlite_free_table Lib "SQLite" (ByVal ResultPtr As String)
Public Declare Sub sqlite_initialize Lib "SQLite" ()
Public Declare Sub sqlite_interrupt Lib "SQLite" (ByVal Hdb As Long)
Public Declare Sub sqlite_free Lib "SQLite" (ptr As Any)
Public Declare Function sqlite_open Lib "SQLite" (ByVal pwsFileName As String, ByRef Hdb As Long) As Long ' PtrDb
Public Declare Function sqlite_close Lib "SQLite" (ByVal Hdb As Long) As Long
Public Declare Function sqlite_finalize Lib "SQLite" (ByVal hstmt As Long) As Long
Public Declare Function sqlite_prepare Lib "SQLite" (ByVal Hdb As Long, ByVal pwsSql As String, ByRef hstmt As Long) As Long
Public Declare Function sqlite_sleep Lib "SQLite" (ByVal Times As Long) As Long
Public Declare Function sqlite_column_int Lib "SQLite" (ByVal hstmt As Long, ByVal iCol As Long) As Long
Public Declare Function sqlite_column_ptext Lib "SQLite" Alias "sqlite_column_text" (ByVal hstmt As Long, ByVal iCol As Long) As Long
Public Declare Function sqlite_step Lib "SQLite" (ByVal hstmt As Long) As Long
Public Declare Function sqlite_exec Lib "SQLite" (ByVal Hdb As Long, ByVal pwsSql As String) As Long
Public Declare Function sqlite_get_table Lib "SQLite" (ByVal db As Long, ByVal zSql As String, ByRef pazResult As String, ByRef pnRow As Long, ByRef pnColumn As Long) As Long
Public Declare Function sqlite_limit Lib "SQLite" (ByVal db As Long, ByVal Id As Long, ByVal NewVal As Long) As Long
Public Declare Function sqlite_libversion Lib "SQLite" () As Long
Public Declare Function sqlite_libversion_number Lib "SQLite" () As Long
Public Declare Function sqlite_reset Lib "SQLite" (ByVal hstmt As Long) As Long
Public Declare Function sqlite_errcode Lib "SQLite" (ByVal db As Long) As Long
Public Declare Function sqlite_changes Lib "SQLite" (ByVal db As Long) As Long
Public Declare Function sqlite_total_changes Lib "SQLite" (ByVal db As Long) As Long
Public Declare Function sqlite_malloc Lib "SQLite" (ByVal nByte As Long) As Long
Public Declare Function sqlite_errmsgchar Lib "SQLite" Alias "sqlite_errmsg" (ByVal db As Long) As Long
Public Declare Function sqlite_column_database_pname Lib "SQLite" Alias "sqlite_column_database_name" (ByVal hstmt As Long, ByVal iCol As Long) As Long
Public Declare Function sqlite_column_table_pname Lib "SQLite" Alias "sqlite_column_table_name" (ByVal hstmt As Long, ByVal iCol As Long) As Long
Public Declare Function sqlite_column_origin_pname Lib "SQLite" Alias "sqlite_column_origin_name" (ByVal hstmt As Long, ByVal iCol As Long) As Long
Public Declare Function sqlite_column_declptype Lib "SQLite" Alias "sqlite_column_decltype" (ByVal hstmt As Long, ByVal iCol As Long) As Long
Public Declare Function sqlite_data_count Lib "SQLite" (ByVal hstmt As Long) As Long
Public Declare Function sqlite_complete Lib "SQLite" (ByVal zSql As String) As Long
Public Declare Function sqlite_column_count Lib "SQLite" (ByVal hstmt As Long) As Long
Public Declare Function sqlite_column_ptype Lib "SQLite" Alias "sqlite_column_type" (ByVal hstmt As Long, ByVal iCol As Long) As Long
Public Declare Function sqlite_column_pname Lib "SQLite" Alias "sqlite_column_name" (ByVal hstmt As Long, ByVal iCol As Long) As Long
Public Declare Function sqlite_column_blob Lib "SQLite" (ByVal hstmt As Long, ByVal iCol As Long) As Long
Public Declare Function sqlite_column_bytes Lib "SQLite" (ByVal hstmt As Long, ByVal iCol As Long) As Long
Public Declare Function sqlite_column_double Lib "SQLite" (ByVal hstmt As Long, ByVal iCol As Long) As Double
Public Declare Function sqlite_column_value Lib "SQLite" (ByVal hstmt As Long, ByVal iCol As Long) As Long
Public Declare Function sqlite_bind_parameter_count Lib "SQLite" (ByVal hstmt As Long) As Long
Public Declare Function sqlite_bind_parameter_pname Lib "SQLite" Alias "sqlite_bind_parameter_name" (ByVal hstmt As Long, ByVal paramIndex As Long) As Long
Public Declare Function sqlite_bind_parameter_index Lib "SQLite" (ByVal hstmt As Long, ByVal zName As String) As Long
Public Declare Function sqlite_bind_null Lib "SQLite" (ByVal hstmt As Long, ByVal paramIndex As Long) As Long
Public Declare Function sqlite_bind_blob Lib "SQLite" (ByVal hstmt As Long, ByVal paramIndex As Long, Dataptr As Any, ByVal nByte As Long) As Long
Public Declare Function sqlite_bind_zeroblob Lib "SQLite" (ByVal hstmt As Long, ByVal paramIndex As Long, ByVal nByte As Long) As Long
Public Declare Function sqlite_bind_double Lib "SQLite" (ByVal hstmt As Long, ByVal paramIndex As Long, ByVal nByte As Double) As Long
Public Declare Function sqlite_bind_int Lib "SQLite" (ByVal hstmt As Long, ByVal paramIndex As Long, ByVal nByte As Long) As Long
Public Declare Function sqlite_bind_ptext Lib "SQLite" Alias "sqlite_bind_text" (ByVal hstmt As Long, ByVal paramIndex As Long, ByVal lpStr As String) As Long
Public Declare Function sqlite_bind_value Lib "SQLite" (ByVal hstmt As Long, ByVal paramIndex As Long, ByVal NewVal As Long) As Long
Public Declare Function sqlite_clear_bindings Lib "SQLite" (ByVal hstmt As Long) As Long
Public Declare Function sqlite_last_insert_rowid Lib "SQLite" (ByVal Hdb As Long) As Long
Public Declare Function sqlite_backup_init Lib "SQLite" (ByVal pDestDC As Long, ByVal zDestName As String, ByVal pSourceDC As Long, ByVal zSourceName As String) As Long
Public Declare Function sqlite_backup_step Lib "SQLite" (ByVal pBackup As Long, ByVal nPage As Long) As Long
Public Declare Function sqlite_backup_finish Lib "SQLite" (ByVal pBackup As Long) As Long
Public Declare Function sqlite_backup_remaining Lib "SQLite" (ByVal pBackup As Long) As Long
Public Declare Function sqlite_backup_pagecount Lib "SQLite" (ByVal pBackup As Long) As Long
Public Declare Function CopyStr Lib "kernel32" Alias "lstrcpynA" (ByVal lpStringDestination As String, ByVal lpStringSource As Long, ByVal lngMaxLength As Long) As Long
Public Declare Function MultiByteToWideChar Lib "kernel32" (ByVal CodePage As Long, ByVal dwFlags As Long, ByVal lpMultiByteStr As Long, ByVal cbMultiByte As Long, ByVal lpWideCharStr As Long, ByVal cchWideChar As Long) As Long
Public Declare Function WideCharToMultiByte Lib "kernel32" (ByVal CodePage As Long, ByVal dwFlags As Long, ByVal lpWideCharStr As Long, ByVal cchWideChar As Long, ByVal lpMultiByteStr As Long, ByVal cbMultiByte As Long, ByVal lpDefaultChar As Long, ByVal lpUsedDefaultChar As Long) As Long
Public Declare Function lstrcpynW Lib "kernel32" (ByVal pwsDest As Long, ByVal pwsSource As Long, ByVal cchCount As Long) As Long
Public Declare Function lstrcpyW Lib "kernel32" (ByVal pwsDest As Long, ByVal pwsSource As Long) As Long
Public Declare Function lstrlenW Lib "kernel32" (ByVal pwsString As Long) As Long
Private Const CP_UTF8 As Long = 65001
Enum SQLITE_RETURN
SQLITE_OK = 0
SQLITE_ERROR = 1
SQLITE_INTERNAL = 2
SQLITE_PERM = 3
SQLITE_ABORT = 4
SQLITE_BUSY = 5
SQLITE_LOCKED = 6
SQLITE_NOMEM = 7
SQLITE_READONLY = 8 ' Attempt to write a readonly database
sqlite_interruptD = 9 ' Operation terminated by sqlite3_interrupt()
SQLITE_IOERR = 10 ' Some kind of disk I/O error occurred
SQLITE_CORRUPT = 11 ' The database disk image is malformed
SQLITE_NOTFOUND = 12 ' (Internal Only) Table or record not found
SQLITE_FULL = 13 ' Insertion failed because database is full
SQLITE_CANTOPEN = 14 ' Unable to open the database file
SQLITE_PROTOCOL = 15 ' Database lock protocol error
SQLITE_EMPTY = 16 ' Database is empty
SQLITE_SCHEMA = 17 ' The database schema changed
SQLITE_TOOBIG = 18 ' Too much data for one row of a table
SQLITE_CONSTRAINT = 19 ' Abort due to contraint violation
SQLITE_MISMATCH = 20 ' Data type mismatch
SQLITE_MISUSE = 21 ' Library used incorrectly
SQLITE_NOLFS = 22 ' Uses OS features not supported on host
SQLITE_AUTH = 23 ' Authorization denied
SQLITE_FORMAT = 24 ' Auxiliary database format error
SQLITE_RANGE = 25 ' 2nd parameter to sqlite3_bind out of range
SQLITE_NOTADB = 26 ' File opened that is not a database file
SQLITE_ROW = 100 ' sqlite3_step() has another row ready
SQLITE_DONE = 101 ' sqlite3_step() has finished executing
End Enum
Public Const SQLITE_TRANSIENT As Long = 0
Public Function UTF8StringFromPtr(ByVal pUtf8String As Long) As String
Dim cSize As Long
UTF8StringFromPtr = ""
cSize = MultiByteToWideChar(CP_UTF8, 0, pUtf8String, -1, 0, 0)
If cSize > 1 Then
UTF8StringFromPtr = Space(cSize - 1) 'String(cSize - 1, " ")
MultiByteToWideChar CP_UTF8, 0, pUtf8String, -1, StrPtr(UTF8StringFromPtr), cSize
End If
End Function
Public Function sqlite_bind_text(hstmt As Long, iCol As Long, lpStr As String) As Long
sqlite_bind_text = sqlite_bind_ptext(hstmt, iCol, StrConv(lpStr, vbUnicode))
End Function
Private Function BytesFromPtr(ByVal lAddr As Long, ByVal lSize As Long) As Byte()
ReDim bvData(lSize - 1) As Byte
CopyMemory bvData(0), ByVal lAddr, lSize
BytesFromPtr = bvData
End Function
Public Function SQLite_Vacuum(h_DB As Long) As Boolean
Dim hstmt As Long, lpReturn As SQLITE_RETURN
lpReturn = sqlite_exec(h_DB, "VACUUM")
SQLite_Vacuum = (lpReturn = SQLITE_OK)
End Function
Public Function sqlite3_column_name(hstmt As Long, iCol As Long) As String
Dim lpReturn As Long
lpReturn = sqlite_column_name(hstmt, iCol)
sqlite3_column_name = UTF8StringFromPtr(lpReturn)
End Function
Public Function sqlite_column_text(hstmt As Long, iCol As Long) As String
Dim lpReturn As Long
lpReturn = sqlite_column_ptext(hstmt, iCol)
sqlite_column_text = UTF8StringFromPtr(lpReturn)
End Function
Public Function sqlite_column_database_name(hstmt As Long, iCol As Long) As String
Dim lpReturn As Long
lpReturn = sqlite_column_database_pname(hstmt, iCol)
sqlite_column_database_name = UTF8StringFromPtr(lpReturn)
End Function
Public Function sqlite_column_table_name(hstmt As Long, iCol As Long) As String
Dim lpReturn As Long
lpReturn = sqlite_column_table_pname(hstmt, iCol)
sqlite_column_table_name = UTF8StringFromPtr(lpReturn)
End Function
Public Function sqlite_column_origin_name(hstmt As Long, iCol As Long) As String
Dim lpReturn As Long
lpReturn = sqlite_column_origin_pname(hstmt, iCol)
sqlite_column_origin_name = UTF8StringFromPtr(lpReturn)
End Function
Public Function sqlite_column_decltype(hstmt As Long, iCol As Long) As String
Dim lpReturn As Long
lpReturn = sqlite_column_declptype(hstmt, iCol)
sqlite_column_decltype = UTF8StringFromPtr(lpReturn)
End Function
Public Function sqlite_errmsg(Hdb As Long) As String
Dim lpReturn As Long
lpReturn = sqlite_errmsgchar(Hdb)
sqlite_errmsg = UTF8StringFromPtr(lpReturn)
End Function
Public Function sqlite_column_type(hstmt As Long, iCol As Long) As String
Dim lpReturn As Long
lpReturn = sqlite_column_ptype(hstmt, iCol)
sqlite_column_type = UTF8StringFromPtr(lpReturn)
End Function
Public Function sqlite_column_name(hstmt As Long, iCol As Long) As String
Dim lpReturn As Long
lpReturn = sqlite_column_pname(hstmt, iCol)
sqlite_column_name = UTF8StringFromPtr(lpReturn)
End Function
Public Function sqlite_bind_parameter_name(hstmt As Long, paramIndex As Long) As String
Dim lpReturn As Long
lpReturn = sqlite_bind_parameter_pname(hstmt, paramIndex)
sqlite_bind_parameter_name = UTF8StringFromPtr(lpReturn)
End Function
Public Function sqlite_next(hstmt As Long) As Boolean
sqlite_next = (sqlite_step(hstmt) = SQLITE_ROW)
End Function
I can't find evidence that any of that is true.Quote:
Originally Posted by Schmidt
The closest thing I've found is an unofficial add-on package that is only usable in Windows 10 Metro applets (renamed as "Universal" for marketing reasons though they are anything but universal and indeed live within a tiny software ghetto): SQLite for Universal App Platform. This is not part of the OS and there isn't even a suggestion there about how it will be serviced with updates. It looks more like tomorrow's abandonware.
But maybe you are referring to yet another copy of SQLite that does come in Windows 10 and is stuck in there somewhere else?
Ok, after much digging I found this:
Shipping a New Mindset with SQLite in Windows 10
Then the question is whether it matters to VB6 programs at all.
Edit:
It appears the answer is not at all. This is still Metro/UWP only.
It's quite recent - but contained now even in the Win10-Desktop-packageQuote:
Originally Posted by dilettante
(since the last big Win10-Upgrade)...
Here's the MS-statement for the reasons:
http://engineering.microsoft.com/201...in-windows-10/
And here a screenshot of a recently upgraded Win10 - on what the Sys(Wow) Folders
from now on will contain:
http://vbRichClient.com/Downloads/SQLiteWin10.png
Olaf
Fair enough.
But the average VBer is probably still going to be far better off using a wrapper library like yours, or one of the ODBC or OLEDB wrappers.
attach the dll source #11.
Never used SQLite. Just a backup at here.
Yep, that's the "raw" SQLite-lib which is now contained - so, for comfortable accessQuote:
Originally Posted by dilettante
(e.g. Auto-UTF8-to-VBString-conversions, or typed ResultSet-delivery in a nice Object-Container)
one will need a "translating" COM-Layer in-between... (meaning ADO/OleDB or ADO/ODBC
or for those who want to decouple entirely from MS-provided layers -> vbRichClient5, which
comes with its own "ADO-like usable replacement for Command and Recordset-Classes").
And as it seems, what ships and gets updated with Win10 will perhaps always be a few
more versions behind the most recent one.
I just find the MS-statement (commitment) remarkable and noteworthy - seems like they
have finally seen "the Lite" (not only with regards to SQLite - but also with regards to
their more open approach and policy to OpenSource-software in general).
SQLite *does* have some big advantages over the JET-engine (especially when we talk
about "interaction with Web-stuff" - which I find myself incorporating into my VB6-apps
more and more).
Olaf
I don't see a need for shipping whole databases between platforms very often but that could be one use. If somebody gives you a SQLite DB you use the SQLite DB. More often though it only gets used as a datastore within a single application for internal use.Quote:
Originally Posted by Schmidt
But if you need to write code for varying platforms it offers a consistent target: one SQL dialect to learn and one set of quirks to deal with.
However most people coming here who need any of that are already aware of it.
The rest are still struggling to figure out text file I/O. When they do use a database it is often in the most primitive ways imaginable. Quite often they just cram everything into one table. Thus they have no relations, have never heard of normalization, and may not even have any keys except a PK they only include by rote and don't understand how to use.
There is no single tool for every job, but some are easier for a casual beginner to deal with. For them Jet is easy since it is already there and they can find mountains of books, tutorials, and sample programs in VB6 using it. Most of its limitations will never impact them.
The real red herring is SQL Server, which is far more clumsy to deal with and a poor fit for the sorts of data most casual coders have to store.