Re: How to create my own VB backup program for a remote MySQL DB
From: Mark J. McGinty (mmcginty_at_spamfromyou.com)
Date: 11/05/04
- Next message: Mona \(Grapecity\): "Re: HI"
- Previous message: Mark J. McGinty: "Re: Wscript within VBA"
- In reply to: Astra: "How to create my own VB backup program for a remote MySQL DB"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 4 Nov 2004 21:22:44 -0800
MySQL has an OLEDB driver, correct? And is therefore supports/is supported
by ADO, and is capable of batch updates? If that is the case you can write
a backup routine in 3 lines of per-table code, plus connection, iteration
and file name generation code:
' assumes cn is an open connection object, connected to a MySQL database
' assumes filename is a valid o/s file name for the file system on which
your app is running
' assumes tablename is the name of a table
Dim rs as ADODB.Recordset
Set rs = New ADODB.Recordset
[place holder for iteration code]
rs.Open "SELECT * FROM " & tablename
rs.Save filename, adPersistXML
rs.Close
[place holder for closing statement of loop construct]
Both data and schema are stored in the output file. In the event you needed
to restore, you can open a recordset using the XML file as source, loop
through the fields collection, and generate DDL based on that. You would
then need to alter the XML object to create an rs:insert element, and move
all of the rs:data elements to make them children of rs:insert. (I can post
a function if you're interested.) Then set the ActiveConnection property of
the recordset to an open connection, call UpdateBatch and ADO and the server
do the rest for you.
The only downside I know of is there's no way to derive indexes from the
persisted recordset.
-Mark
"Astra" <info@NoEmail.com> wrote in message
news:%23NkCqWbwEHA.3276@TK2MSFTNGP15.phx.gbl...
> Hi All
>
> I understand that the basic principles to create a TCP/IP backup program
> for
> a remote MySQL DB are:
>
> a) Query/retrieve the schema.
>
> b) Query/retrieve each table and create inserts for each row of data.
>
> The way I want to do it is even more basic, as I know what my schema is so
> I
> only need to reterieve and create the insert rows to put into my backup
> text
> file, which is basically a large DDL.
>
> The problems that I am experiencing are I want to keep the schema in an
> external text file so that I'm not storing it in the actual app and I
> thought I would also have another external file to store each 'select *
> from
> tablex' query.
>
> Is this a good way of doing it?
>
> How would I merge the results together to make a new (and 3rd) external
> text
> file backup?
>
> Any help is appreciated.
>
> Rgds
>
> Robbie
>
>
- Next message: Mona \(Grapecity\): "Re: HI"
- Previous message: Mark J. McGinty: "Re: Wscript within VBA"
- In reply to: Astra: "How to create my own VB backup program for a remote MySQL DB"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
