RC5 Simplest way to copy table from ADODB to SQLite
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.
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:
Code:
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
Re: RC5 Simplest way to copy table from ADODB to SQLite
You could try to convert your MariaDB in "one shot", using the cConverter-Class:
Code:
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.
Re: RC5 Simplest way to copy table from ADODB to SQLite
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:
Code:
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
Re: RC5 Simplest way to copy table from ADODB to SQLite
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:
Code:
MemDB.Cnn.InsertData AdoCnn "Select * From T WHERE is_deleted = 0"
Last edited by OldClock; Mar 26th, 2021 at 02:55 PM.
Re: RC5 Simplest way to copy table from ADODB to SQLite
Originally Posted by OldClock
...how do I copy data using a SELECT statement from MariaDB to MemDB? Something like this pseudo-code:
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:
Code:
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:
Code:
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
Last edited by Schmidt; Mar 26th, 2021 at 03:04 PM.
Re: RC5 Simplest way to copy table from ADODB to SQLite
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:
Code:
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 ""
Re: RC5 Simplest way to copy table from ADODB to SQLite
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:
Code:
MemDB.cnn.CreateTableFromADORs MemDB.cnn, "foo", AdoCnn.Execute("SELECT * FROM foo LIMIT 0")
Re: RC5 Simplest way to copy table from ADODB to SQLite
Originally Posted by OldClock
Schmidt, is it possible to use a parameterized query here? Something like this:
Code:
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):
Code:
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:
Code:
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):
Code:
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)
Re: RC5 Simplest way to copy table from ADODB to SQLite
Originally Posted by Schmidt
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.
Re: RC5 Simplest way to copy table from ADODB to SQLite
Originally Posted by wqweto
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.
Re: RC5 Simplest way to copy table from ADODB to SQLite
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...
Re: RC5 Simplest way to copy table from ADODB to SQLite
@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)
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).
Re: RC5 Simplest way to copy table from ADODB to SQLite
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.
Code:
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
Re: RC5 Simplest way to copy table from ADODB to SQLite
Then you have to assign the CreateSelectCommand to a variable/object.
Code:
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