Updating databases

[Claire]

Hi,
Firstly, Im a solo applications developer who's only database development
experience has been this project.
I've been developing a small MySQL client/server application and, to date,
when there have been table changes an installation engineer uses my small
database utility on-site to compare old and new versions making any
structural changes manually.
My company have decided they want the process to be done automatically
during installation.
I thought of running a pre-created script to create a temporary database,
import the old data into the new, destroy the old, rename the temp one.
Beyond that, Im a bit stuck knowing what's possible and where it should be
done.
I think I have a choice between creating a script or doing it via code.
My main application uses MySQL's ADO.net connector components and I'm
comfortable using these, but I don't know how to disable constraints while I
do the import using sql queries.
Creating a script worries me because I don't feel I have enough xp, above
studying MySQL Administrators script generation, on what should be put into
a script. I also don't know at the moment how data is written to a script
for BLOB fields eg images.
Would someone advise me please.
Thank you

 

[Peter Morris]

01: Backup DB
02: Check the DB version number (you could have a single row in a table with
a number in it)
03: Run all scripts from that version up to the current version

if (version < 2)
upgradetov2;
if (version < 3)
upgradetov3;

etc


Pete

 

[jp2msft]

Hi Claire,

Will your app have access to both databases? If they are both in the same
format, that certainly helps!

In your installer, create two DataAdapters and two DataTables. Fill each
DataTable with its DataAdapter's Fill method, then start going through a loop:

for (int i = 0; (i < MyDataTable.Rows.Count) && (i <
TheirDataTable.Rows.Count); i++)
{
for (int j = 0; j < ColumnsInTable; j++)
{
if (MyDataTable.Row[j] != TheirTable.Row[j])
{
// Update whatever table you want to keep
}
}
}

Note: The code above is untested. Row[j] will return an object, and I
haven't tried comparing objects like that.

Hope that helps!