Sqlite-VBForums
Results 1 to 18 of 18

Thread: Sqlite

  1. #1

    Thread Starter
    Member
    Join Date
    Oct 2015
    Posts
    40

    Sqlite

    Hi
    Can VB6 database app use SQLITE or another embedded SQL server ?
    Elico

  2. #2
    Hyperactive Member
    Join Date
    Jul 2013
    Posts
    310

    Re: Sqlite

    Yes. For SQLite the best way I know is by using Olaf's vbRichClient. You can get it here

  3. #3
    PowerPoster
    Join Date
    Feb 2006
    Posts
    17,884

    Re: Sqlite

    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:

    Version 3.9.2 of SQLite is recommended for all new development.
    And that'll change again in a week or two.

    Recent fixes: SQLite Release 3.9.2 On 2015-11-03

  4. #4
    Hyperactive Member
    Join Date
    Jul 2013
    Posts
    310

    Re: Sqlite

    Just look at these scary bugs fixed in 3.9.2
    Dilettante is a good fellow but doesn't like Olaf *sigh*

  5. #5

    Thread Starter
    Member
    Join Date
    Oct 2015
    Posts
    40

    Re: Sqlite

    Quote Originally Posted by Carlos Rocha View Post
    Just look at these scary bugs fixed in 3.9.2
    Dilettante is a good fellow but doesn't like Olaf *sigh*
    Thanks
    Elico

  6. #6
    Frenzied Member
    Join Date
    Jun 2015
    Posts
    1,605

    Re: Sqlite

    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

  7. #7
    PowerPoster
    Join Date
    Feb 2006
    Posts
    17,884

    Re: Sqlite

    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.

  8. #8
    PowerPoster
    Join Date
    Jun 2013
    Posts
    3,212

    Re: Sqlite

    Quote Originally Posted by dilettante View Post
    Jet is a fine choice.
    The Jet-Engine is not bad - but SQLite is better (faster) in almost any regard -
    and it supports features the JET-engine simply doesn't have.
    - InMemory-DBs
    - Triggers
    - Userdefinable SQL-functions
    - CTEs
    - Full-Text-Search
    - JSON-queries

    Quote Originally Posted by dilettante View Post
    ... 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.
    SQLite is not "a database-engine written for a single platform" -
    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
    Last edited by Schmidt; Dec 3rd, 2015 at 03:43 AM.

  9. #9

    Thread Starter
    Member
    Join Date
    Oct 2015
    Posts
    40

    Re: Sqlite

    Quote Originally Posted by Schmidt View Post
    The Jet-Engine is not bad - but SQLite is better (faster) in almost any regard -
    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" -
    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

    Many thanks you all
    Elico

  10. #10

    Thread Starter
    Member
    Join Date
    Oct 2015
    Posts
    40

    Re: Sqlite

    Many thanks
    You all

    Elico

  11. #11
    Frenzied Member
    Join Date
    Jan 2010
    Posts
    1,086

    Re: Sqlite

    FYI. Hijack this thread for my future reference.
    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
    Edited: Attached the DLL source.
    Attached Files Attached Files
    Last edited by Jonney; Dec 9th, 2015 at 04:14 AM.

  12. #12
    PowerPoster
    Join Date
    Feb 2006
    Posts
    17,884

    Re: Sqlite

    Quote Originally Posted by Schmidt View Post
    SQLite is officially part of Win10 - and will receive regular updates on this platform now too.
    I can't find evidence that any of that is true.

    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?

  13. #13
    PowerPoster
    Join Date
    Feb 2006
    Posts
    17,884

    Re: Sqlite

    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.
    Last edited by dilettante; Dec 9th, 2015 at 03:54 AM.

  14. #14
    PowerPoster
    Join Date
    Jun 2013
    Posts
    3,212

    Re: Sqlite

    Quote Originally Posted by dilettante View Post
    I can't find evidence that any of that is true.
    It's quite recent - but contained now even in the Win10-Desktop-package
    (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:




    Olaf

  15. #15
    PowerPoster
    Join Date
    Feb 2006
    Posts
    17,884

    Re: Sqlite

    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.

  16. #16
    Frenzied Member
    Join Date
    Jan 2010
    Posts
    1,086

    Re: Sqlite

    attach the dll source #11.
    Never used SQLite. Just a backup at here.

  17. #17
    PowerPoster
    Join Date
    Jun 2013
    Posts
    3,212

    Re: Sqlite

    Quote Originally Posted by dilettante View Post
    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.
    Yep, that's the "raw" SQLite-lib which is now contained - so, for comfortable access
    (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

  18. #18
    PowerPoster
    Join Date
    Feb 2006
    Posts
    17,884

    Re: Sqlite

    Quote Originally Posted by Schmidt View Post
    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).
    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.

    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.