dhSQLite – New SQLite COM Wrapper

I’ve just come across a new COM wrapper for SQLite from German company Datenhaus (like the name, maybe I should call myself Datenschmidt?). I’ve started to use the COM free PSSQLITE.DLL in my Excel VBA projects but I may consider using the Datenhaus dhSQLite library in certain situations as it includes a database encryption option and it also is capable of being used without registration by using it along side DirectCOM, another tool from the same company. In fact DirectCOM allows for the registration-free deployment of any COM library, could be very useful in these days of locked-down corporate desktops.

Both tools are free but closed-source (but they’re open to offers if anybody wishes to purchase the code!). Also, no details on what the dhSQLite encryption methods are, so maybe not suitable for very sensitive data (always a problem with closed-source products that offer encryption).

UPDATE:

Check out the two SQLite based Excel-focused ETL tools I’ve developed…

Advertisements

17 responses to “dhSQLite – New SQLite COM Wrapper

  1. Hi gobansaor,

    thanks for this neat comments about dhSQLite.

    Please note the new version 1.2, wich now contains a nearly complete Set of builtin VB(A)-Functions as for example, DateDiff, DatePart, Format$, Instr, etc… you can also define your own userdefined Functions with ease – it also contains support for FTS2 now, wich is the Fulltext-Search-Engine of the original SQLite Source-Tree.

    Regarding your question about the encryption-method – it is
    nearly similar to the encryption-method in Robert Simpsons ADO.NET-Provider, wich is based on RC4 and covers the full
    DB (working at Page-Read/Write-Level).
    Another way of encryption is built into our dhRPCServer, wich in combination with dhSQLite gives you full RDBMS-capabilities for multiuser-scenarios in a LAN or over the Internet. The encryption there is working at protocol-, respective socket-level (protocol-compression is also supported by the server).

    So, enough “tec-speak” for now… 😉

    Regards,

    Olaf Schmidt

  2. Pingback: dhSQLite - now with FTS2 and VB UDFs « Gobán Saor

  3. How to insert piture into the sqlite database via VB 6.0

  4. @pradeep

    Haven’t done so myself but I’m sure its possible with dhSQLite, just a matter of passing in a Byte Array as the value I would think.

    Tom

  5. I tried to import that COM DLL into my C++ project to get the interface definition, but it got compiler “out of head space” error.
    fatal error C1060: compiler is out of heap space
    Increasing heap space won’t help. Did anyone import it successfully? The site says this works to import. There is no C++ sample code or document.

    Thanks.

  6. @Charlie,

    The DLL is a VB6 COM DLL and as such should be callable by C++, having said that I’ve never called COM from C++ ATL so I can’t help you as such. But looking at the interfaces exposed via OLE Viewer, everything looks OK, with all the usual COM stuff IDispatch and IUnknown interfaces etc.

    I may be teaching my Grandmother to suck eggs here, but this may be of help http://msdn.microsoft.com/en-us/library/727z646z(VS.71).aspx

    Tom

  7. Hi Sirs,
    So how to use SQLite with VBA, lets say EXCEL. Download the wrapper, register the dlls, use it as COM?
    greetings

  8. @Matthias

    SQLite itself is not callable directly by VBA, so yes you need to pick a wrapper (or write one yourself). Most wrappers like dhSQLite use COM, so yes you would first register the DLL (although datenhaus also provide a registry-free way of using COM dlls).

    For a list of other wrappers see http://blog.gobansaor.com/projects/xlite/#comment-4830

    Tom

  9. Hi Tom,
    thanks for the reply! Do i have to install any ODBC Drivers for SQLite seperatly or are they coming with e.g. dhSQlite? Third possibility: I don’t need them at all!
    regards
    Matthias

  10. Matthias,

    dhSQLite and the other wrappers I mentioned are not ODBC drivers so all you will need is the wrapper’s DLL (sometimes,as with PSSQLITE, you will need the SQLITE3.DLL itself, but most compile this into the wrapper’s DLL).

    If you wish to use SQLite via ODBC, then get http://www.ch-werner.de/sqliteodbc/ this is an excellent open source ODBC driver and it seems to work very well (have used it occasionally myself).

    Tom

  11. Hi Tom,
    thanks, you are great help, because there is not so much on the net about VBA use of SQLite.
    I hope these rookie questions are not too boring for you…but…
    how do i proceed after downloading the dhSQLite Wrapper?
    I know how to make a reference in VBA and what to do with it, but i don’t know how to get the dhSQLite dll in the references? Should i use regasm to register which dll?
    greetings
    Matthias

  12. @ Matthias

    You’ll need to register the dhsqlite.dll using regsvr32 (regasm is for .NET assemblies) see http://consumerdocs.installshield.com/selfservice/viewContent.do?externalId=Q108199&sliceId=1

    Also ensure the sqlite3_engine.dll is on your “path” (which can be quite tricky when dealing with Excel – see http://en.allexperts.com/q/Excel-1059/Calling-DLL-VBA-Excel-1.htm )

    Tom

  13. Hi everyone,

    just released a new version of dhSQLite (wrapper-version is now 3.0.2 – sqlite-engine-version is now 3.6.12).

    Please read: http://www.TheCommon.net/3.html
    and http://www.TheCommon.net/2.html for details…

    All Classes are now part of the new dhRichClient3.dll but will remain calling-compatible to your existing VB(A)-source – just register dhRichClient3.dll on your developer-machine and change your project-reference from dhSQLite to dhRichClient3.
    Then everything should work as before.

    For installation: please read the Readmes in the Download-package – but normally it should be enough, if you place this 3-Dll-set in a Folder (e.g. C:\dhRichClient3\…)
    and register only dhRichClient3.dll there.

    Regards,

    Olaf Schmidt

  14. @Olaf

    Good to hear, I’ve twittered the news to the world http://twitter.com/gobansaor/status/1425381593 😉

    Also good to hear that you may open source part or all of it in the future, and also like the direction of the project towards reducing dependence on MS’s COM toolset.

    Tom

  15. @Olaf,

    And I REALLY like the ultimate goal of a new VB6 compatible compiler http://www.thecommon.net/index.html 🙂

    Tom

  16. Hi Tom,

    regarding the “ultimate goal” (cross-platform – with the main-target Linux) – I’d think that many developers (although yet using windows) are on a similar way with their “migration-plans” currently – we will see the appropriate effects over the next years.

    But that’s IMO a longer process, which (in case you already have larger investments into Code that uses “the MS-toolstack”) needs some time – so it has to be a “soft migration”, starting on the Windows-platform itself.

    But the longer trend seems clear to me – all the infrastructure-stuff (databases, internet- or RPC-technologies, development-platforms, office-components) should (and will) be cross-platform in the future (and based on opened sources).

    BTW, nice allegory in your “SQLite=MP3” article… 😉

    And let me know, if I should write an XL-VBA-Demo, how to use the features of the new wrapper from within Excel.
    There’s for example a SQLiteRs.GetADORsFromContent now, which can be used, to transfer the RS-Content directly into an XL-Cellrange – or CnnObj.CreateTableFromRsContent, which is useful e.g. against an InMemoryDB, to beam a resultset directly into memory as a DB-Table – or easy coding for userdefinable functions, which can be implemented in a VBA-Class over
    Implements IFunction, etc…

    Regards,

    Olaf Schmidt

  17. Hi Olaf,

    I think how you are approaching it via a “soft migration” is the best way.

    And yes, an Excel VBA example would be very useful.

    I’ve built simple C functions to enable me to call VBA user defined functions and to load/unload disk-based databases to/from :memory: dbs, plus a few helper functions such as load to and from CSV files/streams & ranges etc. But would consider changing over to using DHSQLite if/when the core becomes open source.

    Tom