RC5 Simplest way to copy table from ADODB to SQLite

[OldClock]

Hey

My program uses MariaDB through ADODB. I would like to copy a specific table (data, but if there was also a possibility to recreate the schema automatically instead of doing it by hand that would be a bonus) from MariaDB to an SQLite filedb.

So far I learned how to create tables and insert data using cMemDB and cConnection.

Now I would like to learn the simplest way to copy over data from a table from MariaDB to SQLite. It doesn't matter whether we use cMemDB or cConnection, as long as the resulting SQLite DB is stored on disk in a file.

I could iterate over each result in the Recordset from MariaDB and INSERT each row into SQLite, but that's not efficient, not the simplest solution. I have a vague memory of seeing a solution in this forum involving pointing SQLite to a Recordset or Connection to mirror all of the data in that Recordset, but I can't find this post.

Some testing code, these reside in a MariaHandler class and return the Recordset or Connection to MariaDB/ODBC:

Public Function getRs() As ADODB.Recordset
    Dim queryStr As String

    On Error GoTo Fail

    queryStr = "SELECT * FROM country;"

    Call executeQuery(queryStr, "getRs")

    Set getRs = m_rs
    Do While Not m_rs.EOF
        Debug.Print m_rs("item_name")
        m_rs.MoveNext
    Loop
    Exit Function

Fail:
    Call failHandler("getRs", queryStr, err)
End Function

Public Function getCnn() As ADODB.Connection
    Dim queryStr As String

    On Error GoTo Fail

    queryStr = "SELECT * FROM country;"

    Call executeQuery(queryStr, "getCnn")

    Set getCnn = m_cn
    Exit Function

Fail:
    Call failHandler("getRs", queryStr, err)
End Function

I tried:

g_sqliteMemDB.Cnn.CreateTableFromADORs g_sqliteMemDB.Cnn, "country", g_mhD.getRs
g_sqliteConn.CreateTableFromADORs g_sqliteConn, "country", g_mhD.getRs

Neither fails, but g_sqliteConn has no databases, and g_sqliteMemDB has a database but no tables.

The attachment shows that m_rs does indeed contain data.

[Schmidt]

You could try to convert your MariaDB in "one shot", using the cConverter-Class:

  With New_c.Converter
      .ConvertDatabase AdoCnnObject, SQLiteCnnObject
  End With

The method is known to work well with AdoConnections, based on:
- the JET 4.0 OleDB-Driver
- the ACE.OLEDB.12 Driver
- all OleDB-Drivers for the MS-SQLServer
- and IIRC correctly, also with the MS-VisualFoxPro-driver

To give more background-info, the underlying OleDB-Driver needs to support:
- AdoCnn.OpenSchema(adSchemaTables)
- AdoCnn.OpenSchema(adSchemaColumns)
- AdoCnn.OpenSchema(adSchemaIndexes)

...all 3 calls returning decent enough info in an ADO-Rs,
so that internally a properly matching SQLite-Schema can be generated from those "Schema-Rs".

Never tested it with a MariaDB ODBC-driver (wrapped via the generic OleDB-ODBC-connector) -
but the code above is easy enough, to give it a fast trial-attempt.
Maybe it works better than I think with the MariaDB-ODBC-Driver.

If it doesn't (but you have Access), you could try to import into a JET-MDB first -
as an intermediate step.

HTH

Olaf

[Schmidt]

Ok, just tested this with the newest MariaDB ODBC-Driver (3.1.x) -
and as suspected, the "full DB-Import" (via RC6.cConverter-Class) does not work,
because OpenSchema(adSchemaIndexes) is not supported by the MariaDB-ODBC-Driver.

So, what remains is direct "Table-by-Table"-Import via ADORs - like shown below:

Private AdoCnn As New Connection, MemDB As cMemDB

Private Sub Form_Load()
  With New ADODB.Connection 'ensure a new DataBase, named TestDB via a temp-Cnn-instance
    .Open "Driver={MariaDB ODBC 3.1 Driver};Server=localhost", "root", "asdf"
    .Execute "Create DataBase If Not Exists TestDB"
  End With
  
  'now open the real MariaDB-ADOCnn we do the Import from
  AdoCnn.Open "Driver={MariaDB ODBC 3.1 Driver};Server=localhost;DB=TestDB", "root", "asdf"
 
  '(re-)create a new Table with 2 records in it
  AdoCnn.Execute "Drop Table If Exists T"
  AdoCnn.Execute "Create Table T(ID Integer, Name Text)"
  AdoCnn.Execute "Insert Into T Values(12345, 'abc')"
  AdoCnn.Execute "Insert Into T Values(67890, 'xyz')"
 
  Set MemDB = New_c.MemDB 'now create a MemDB as the import-target
      MemDB.Cnn.CreateTableFromADORs MemDB.Cnn, "T", AdoCnn.Execute("Select * From T")
  
  'show, what kind of table the above call has created in the SQLite-MemDB-Connection
  Debug.Print MemDB.Cnn.DataBases("main").Tables("T").SQLForCreate; vbLf
  
  With MemDB.GetRs("Select * From T") 'check, if the Table-data was successfully copied, by reading an Rs
     Debug.Print !ID.Name; "("; TypeName(!ID.Value); ")", !Name.Name; "("; TypeName(!Name.Value); ")"
     Debug.Print !ID.Value, !Name.Value: .MoveNext
     Debug.Print !ID.Value, !Name.Value
  End With
End Sub

HTH

Olaf

[OldClock]

Hey Schmidt

How serendipitous that you answered

You could try to convert your MariaDB in "one shot"

Ah but that would be overkill, I only need one or two tables.

just tested this with the newest MariaDB ODBC-Driver (3.1.x)

I use mysql-connector-odbc-5.3.6-win32.msi - the last version of MySQL Connector/ODBC to support Windows XP.

I tested using your code (but switched it to use MySQL Connector/ODBC 5.3.6) and it worked fine.

However. I found that if I have an integer column (BIGINT data type) with a value higher than 2147483647 (which is the highest number a signed long 4-byte integer can represent), then SQLForCreate throws "Invalid procedure call or argument". That is a problem, as my ID columns are BIGINT with 15-digit values, e.g. 123456789012345.

According to "Datatypes In SQLite Version 3", BIGINT is converted to INTEGER, however this should not be a problem, as that document also states, "INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value", so there shouldn't be a problem storing 123456789012345 as a 6-byte integer.

Just as a test, I changed the MariaDB column from BIGINT to DECIMAL(15,0), set the value to 123456789012345, and SQLForCreate had no problem with that. But I can't do that to my real database, this was just to test.

Is there a way to get SQLForCreate to work with BIGINT values?

And a separate question: how do I copy just the data using a SELECT statement from MariaDB to MemDB? Something like this pseudo-code:

      MemDB.Cnn.InsertData AdoCnn "Select * From T WHERE is_deleted = 0"

[Schmidt]

...how do I copy data using a SELECT statement from MariaDB to MemDB? Something like this pseudo-code:

      MemDB.Cnn.InsertData AdoCnn "Select * From T WHERE is_deleted = 0"

The example already did include the necessary code-line, to import a Table from a Select-statement:

MemDB.Cnn.CreateTableFromADORs MemDB.Cnn, "T", AdoCnn.Execute("Select * From T")

If you want to separate Deleted and NonDeleted Records of a MariaDB-table,
I'd import them either into two separate MemDB-tables (e.g. TableName and TableName_Del) -
or include the is_deleted "Special-Field" in the import:

MemDB.Cnn.CreateTableFromADORs MemDB.Cnn, "T", AdoCnn.Execute("Select *, is_deleted From T")

Edit: The "BigInt-issue" when calling CreateTableFromADORs is now fixed in new versions:
- RC5: 5.0.80
- RC6: 6.0.6

Please re-download and re-register (in case of RC6) ...
(in case of RC5 a simple file-overwrite of the older vbRichClient5.dll should be enough).

Olaf

[OldClock]

Hi Schmidt

I confirm that the BIGINT issue is fixed in 5.0.80. Thank you very much!

For reference for anyone reading this, here is a complete example showing how to create a DB and table, populate it with data and print the results using MariaDB, then to copy that table and all or selective data to cMemDB and to print the results:

    Dim AdoCnn As ADODB.Connection
    Dim AdoCmd As ADODB.Command
    Dim AdoRs As ADODB.Recordset

    Dim MemDB As cMemDB
    Dim cRs As cRecordset

    Set AdoCnn = New ADODB.Connection
    AdoCnn.ConnectionString = "DRIVER={MySQL ODBC 5.3 UNICODE Driver}" _
        & ";SERVER=127.0.0.1" _
        & ";UID=user" _
        & ";PWD=pwd" _
        & ";PORT=3306" _
        & ";POOLING=true" _
        & ";OPTION=3"
    AdoCnn.Open
    AdoCnn.Execute "CREATE DATABASE IF NOT EXISTS testdb"
    AdoCnn.Execute "USE testdb"
    AdoCnn.Execute "DROP TABLE IF EXISTS country"
    AdoCnn.Execute "CREATE TABLE country (id BIGINT PRIMARY KEY, item_name VARCHAR(50), is_deleted TINYINT)"
    AdoCnn.Execute "INSERT INTO country VALUES (2147483647, 'Algeria', 0)"
    AdoCnn.Execute "INSERT INTO country VALUES (2147483648, 'Angola', 1)"
    AdoCnn.Execute "INSERT INTO country VALUES (123456789012345, 'Zambia', 0)"
    AdoCnn.Execute "INSERT INTO country VALUES (123456789012346, 'Zimbabwe', 1)"

    Set AdoCmd = New ADODB.Command
    AdoCmd.ActiveConnection = AdoCnn
    AdoCmd.CommandText = "SELECT * FROM country WHERE is_deleted = 0"

    Set AdoRs = AdoCmd.Execute
    Debug.Print "Data from ADODB:"
    Do Until AdoRs.EOF
        Debug.Print AdoRs.fields(0).Value & ", " & AdoRs.fields(1).Value & ", " & AdoRs.fields(2).Value
        AdoRs.MoveNext
    Loop
    Debug.Print ""

    Set MemDB = New_c.MemDB
    MemDB.cnn.CreateTableFromADORs MemDB.cnn, "country", AdoCnn.Execute("SELECT * FROM country WHERE is_deleted = 0")

    Debug.Print "Table in cMemDB:"
    Debug.Print MemDB.cnn.DataBases("main").Tables("country").SQLForCreate
    Debug.Print ""

    Set cRs = MemDB.cnn.OpenRecordset("SELECT * FROM country")
    Debug.Print "Data from cMemDB:"
    Do Until cRs.EOF
        Debug.Print cRs.fields(0).Value & ", " & cRs.fields(1).Value & ", " & cRs.fields(2).Value
        cRs.MoveNext
    Loop
    Debug.Print ""

[Schmidt]

Thanks for the confirmation.

As for your current use of the MemDB (after importing data) -
the reason this class exists is: "more convenience in read-direction".

So, your current call:
Set cRs = MemDB.cnn.OpenRecordset("SELECT * FROM Country")

could also be written this way:
Set cRs = MemDB.GetRs("SELECT * FROM Country")

or even shorter:
Set cRs = MemDB.GetTable("Country")

or to get the Count of records in Table Country:
Dim Countries As Long
Countries = MemDB.GetCount("Country")

or to get the Max-Value of Field ID in Table Country:
Dim MaxID As Variant
MaxID = MemDB.GetMax("Country", "ID")

Olaf

[OldClock]

Fantastic, thank you!

[OldClock]

Schmidt, is it possible to use a parameterized query here? Something like this:

Set cRs = MemDB.GetRs("SELECT * FROM country WHERE id = ?")
cRs.something.SetText 1, countryId

[OldClock]

And one more, how can one create a MemDB table from an AdoCnn or AdoRs but without any data? This works, but I wonder whether there's a more straight-forward way:

    MemDB.cnn.CreateTableFromADORs MemDB.cnn, "foo", AdoCnn.Execute("SELECT * FROM foo LIMIT 0")

[Schmidt]

Schmidt, is it possible to use a parameterized query here? Something like this:

Set cRs = MemDB.GetRs("SELECT * FROM country WHERE id = ?")
cRs.something.SetText 1, countryId

The following code requires 2 more lines (but not much more typing on your end):

  With MemDB.CreateSelectCommand("SELECT * FROM country WHERE id = ?")
              .SetText 1, countryId
    Set cRs = .Execute
  End With

I think you're already aware, that RC6 supports this as a one-liner:

Set cRs = MemDB.GetRs("SELECT * FROM country WHERE id = ?", countryId)

And I've recently tried to "force it, to work on XP" (in a longer session on an old VM) -
but to no avail - the C-compiler/linker I'm currently using (VC++ 2019) to produce the satellite-dll of the RC6 (cairo_sqlite.dll) -
does expect new API-functions (down to even kernel32.dll), which simply don't exist on systems below Win7.

So, a solution for this situation (RC6 not working on systems below Win7) is not really feasible -
unless I'm switching back to "VC++ 2008 or lower" (which I neither want, nor have time to do).

Not trying to patronize you - but installing VB6 on a system (or VM) newer than XP,
is not really that time-consuming as some forum-threads suggest here ...
can only encourage you, to make such an attempt.

As for your other question (to create an empty table):

MemDB.cnn.CreateTableFromADORs MemDB.cnn, "foo", AdoCnn.Execute("SELECT * FROM foo LIMIT 0")

I usually write such an SQL-string as: "SELECT * FROM foo Where 1=0"
... to be compatible with a few more SQL-dialects (since ADO can connect to "basically anything").

But otherwise, yes - that's how I'd do it.

I also often create "empty Rs, which contain only the Fields-list, but no records" in this way -
(to later perform "a bunch of Inserts" via Rs.AddNew on them, followed by an Rs.UpdateBatch)

HTH

Olaf

[wqweto]

So, a solution for this situation (RC6 not working on systems below Win7) is not really feasible -
unless I'm switching back to "VC++ 2008 or lower" (which I neither want, nor have time to do).

It's VS2015 that can target v140_xp the so called "Visual Studio 2015 - Windows XP (v140_xp)" toolset which is the latest one that supports builds targeting XP.

Another useful feature of VS2015 is that it's the latest version that can parse PDBs as produced by VB6. More recent versions of the debugger do not understand the legacy format of the PDBs that the VS6 linker produces.

I've been using VS2015 and VS2017 side by side and then upgraded VS2017 to VS2019 but still keeping VS2015 for XP targets and debugging with VB6 PDBs.

cheers,
</wqw>

[Schmidt]

It's VS2015 that can target v140_xp the so called "Visual Studio 2015 - Windows XP (v140_xp)" toolset which is the latest one that supports builds targeting XP.

Another useful feature of VS2015 is that it's the latest version that can parse PDBs as produced by VB6. More recent versions of the debugger do not understand the legacy format of the PDBs that the VS6 linker produces.

Thanks, good to know that XP compatibility can still be achieved with some higher VC++ versions
than the VS2008-version which I was using for the older vb_cairo_sqlite.dll builds.

Though despite the possibility, to make the newer cairo_sqlite.dll compatible with XP (when compiling it under VS2015),
there's still the WebView2Loader.dll from MS (which has the same "higher requirements" for Win7 and upwards) -
and well, ...I also think that "the cut" between still XP-compatible RC5 and non-compatible RC6 comes "at the right time".
(I really don't want to worry anymore, to test new Class-additions "down to XP").

BTW - made my XP-tests specifically to give your suggestion with the DelayLoad-Linker-switches a try...
(also included them in cairo_sqlite.dll) - but as said - did not work out.

Olaf

[ThiagoPSanches]

I understand that we have to look ahead... and soon Windows XP will no longer exist (just as Windows 98 no longer exists)

But, particularly for me, it has been a great loss that RC6 doesn't work on XP... not because I'm out of date... but because I made a commitment that some of my software works on XP... and there are places that still run XP, incredible as it may seem
(I believe it is because it works well and some small establishments do not think about changing computers, especially now, in a pandemic)

The losses that I miss most in RC5 are:

- Outdated sqlite (RC5 does not have "UPDATE FROM", released in sqlite 3.33)

- Lack of the option "MapDbNullToEmpty = False", it may seem little, but it helps a lot to convert an Access system to Sqlite with less effort

- Sqlite Encryption Codec Options




The RC6 is just launched and look at how many advantages!


I'm just exposing my situation... I can handle the RC5.
And, whenever I have the opportunity, I will thank you for this fantastic work shared with everyone...



--
Thiago Peres Sanches

[wqweto]

there's still the WebView2Loader.dll from MS

Do you staticly link WebView2 loader library or is this DLL an external dependency that has to be shipped separately together with MS Edge?

cheers,
</wqw>

[Schmidt]

@Thiago...
I hear you - but let's see how much efforts this would cause on my end...
(I see the WebView2 as one of the most important COMponent-pieces in the coming years)

Do you staticly link WebView2 loader library or is this DLL an external dependency that has to be shipped separately together with MS Edge?

The MS-Edge(chromium) runtime is quite large (about 90MB zipped) and sits in its own, MS-provided package.
And as Wolfgang Wolf has pointed out recently in another thread, it seems MS delivers this package now "automatically".
https://www.bleepingcomputer.com/new...view2-runtime/
Althoug I'm not entirely sure, whether "automatically" means "with MS-Office-installs only" or with "plain, regular Win-Updates".

In any case, this larger runtime is separate from the MS-WebView2Loader (which is available via a relatively small NuGet-package).
And yes, within that package are both:
- a "normal Dll" of about 130KB (which I currently use in its 32Bit-version, addressing it via a vbFriendly-adjusted typelib)
- and also a *.lib (the large version of it, not just an "entry-point-connector" *.lib)

So I could also directly link this MS-Loader-binary statically into cairo_sqlite.dll (since my VS2019 matches with the MS-Compiler-version)...
Currently the little Dll is part of the RC6BaseDlls-package and needs to be included in RC6-deployments beside RC6.dll and the other satellites.

The question now becomes, whether a static lib, which was compiled by MS in a "VC++2019-environment" (or in coming years even higher)
could be "forceably downgraded" by the linker, in a theoretical build of cairo_sqlite.dll - from within a VC++2015 environment.

I know, that this would all work without any problems, when I'd be producing (recompiling) this static Loader-lib myself -
but unfortunately, MS does *not* provide the Source for the Loader anywhere ...
(which is a bit funny, considering they are addressing an "as open as can be" chromium with it... but well, chromiums BSD-license allows that).

Olaf

[OldClock]

Schmidt, how do I set the parameters in a parameterized query without using "WITH"? For clarity, let's make it have one parameter a bool, one currency, one datetime.

    With memDB.CreateSelectCommand("SELECT * FROM country" _
        & "WHERE some_bool = ?" _
        & "AND some_currency = ?" _
        & "AND some_datetime = ?")
        .SetText 1, True
        .SetText 2, 12345678901.2345
        .SetText 3, Now
        Set cRs = .Execute
    End With

[Arnoutdv]

Then you have to assign the CreateSelectCommand to a variable/object.

  With MemDB.CreateSelectCommand("SELECT * FROM country WHERE some_bool = ? AND some_currency = ? AND some_datetime = ?")
              .SetText 1, True
              .SetText 2, 123456789012345.1234
              .SetText 3, Now
    Set cRs = .Execute
  End With

' Without With ..
Dim cCC As cSelectCommand

Set cCC = MemDB.CreateSelectCommand("SELECT * FROM country WHERE some_bool = ? AND some_currency = ? AND some_datetime = ?")
cCC.SetText 1, True
cCC.SetText 2, 123456789012345.1234
cCC.SetText 3, Now
Set cRs = cCC.Execute

[OldClock]

Thank you Arnoutdv