[TIP] Simple method to migrate data from one db to another

[Karel Broer]

Sometimes it's neccesary to migrate your data from one database to another. For example from MySQL to Sybase or the other way around.

Wouldn't it be nice to have a global method that could do the migration for you?

This is how you could do that:
In this example we copy all table data from a Sybase db to a MySQL schema.

1. Create a new solution that have 2 forms: 1 based upon a Sybase db, another one upon a MySQL schema (make sure your db connections are available in the solution).

2. Run this method:

Code: Select all

//get sybase servername
var vSybaseServer = forms.form_on_sybase.controller.getServerName()

//get mysql servername
var vMySQLServer = forms.form_on_mysql.controller.getServerName()

//get all table names from mysql db
var vMySQLTables = databaseManager.getTableNames(vMySQLServer)

//init vars
var vMySQLTableName
var jTable
var vTableFields
var vTableField
var vSybaseSet
var vSybaseRecord
var vMySQLRecord
var vMySQLSet

//loop through mysql tables
for(var t in vMySQLTables){
   vMySQLTableName = vMySQLTables[t]
   
     //in this example we don't want to copy the i18n table data
   if(vMySQLTableName == 'messages'){
     continue;
   }
   
   jTable = databaseManager.getTable(vMySQLServer, vMySQLTableName)

   //get fields from current mysql table
   vTableFields = jTable.getColumnNames()

   //update status text
   application.setStatusText( 'Copying table ' + vMySQLTableName)

   //load all sybase records
   vSybaseSet = databaseManager.getFoundSet(vSybaseServer, vMySQLTableName)
   vSybaseSet.loadAllRecords()
   
   //create mysql foundset
   vMySQLSet = databaseManager.getFoundSet(vMySQLServer, vMySQLTableName)
   vMySQLSet.clear()
   
   //loop through sybase foundset
   for(var i = 1 ; i <= vSybaseSet.getSize(); i++){
   
      //get sybase record
      vSybaseRecord = vSybaseSet.getRecord(i)
      
      //create new mysql record
      vMySQLRecord = vMySQLSet.getRecord(vMySQLSet.newRecord())
      
   
      //loop through fields from current mysql table
      for(var f in vTableFields){
         vTableField = vTableFields[f]
         
         //set sybase field data in mysql record, if field exists in sybase
         if(vSybaseRecord[vTableField]){
            vMySQLRecord[vTableField] = vSybaseRecord[vTableField]
         }
      }
      
      databaseManager.saveData()
   }
}

application.setStatusText('Ready')

Done. And as simple as that..

NOTE: I don't know if this also would work with PostgreSQL or Oracle,
but I know Sybase, MySQL and SQL Server are no problem to do this.
Another thing to be aware of is that this method also copies the primairy key data from one db to another,
so if you're using Servoy sequences you need to update the table seqeuences after performing the 'copy' method.

O yah... you need Servoy 3.5 for this...

[Karel Broer]

With the databaseManager.copyMatchingColumns() function, it's not necesarry anymore to loop through the columns to copy them!
Here's a modified version:

Code: Select all

//get sybase servername
var vSybaseServer = forms.form_on_sybase.controller.getServerName()

//get mysql servername
var vMySQLServer = forms.form_on_mysql.controller.getServerName()

//get all table names from mysql db
var vMySQLTables = databaseManager.getTableNames(vMySQLServer)

//init vars
var vMySQLTableName
var jTable
var vTableFields
var vTableField
var vSybaseSet
var vSybaseRecord
var vMySQLRecord
var vMySQLSet

//loop through mysql tables
for(var t in vMySQLTables){
   vMySQLTableName = vMySQLTables[t]
   
     //in this example we don't want to copy the i18n table data
   if(vMySQLTableName == 'messages'){
     continue;
   }
   
   jTable = databaseManager.getTable(vMySQLServer, vMySQLTableName)

   //get fields from current mysql table
   vTableFields = jTable.getColumnNames()

   //update status text
   application.setStatusText( 'Copying table ' + vMySQLTableName)

   //load all sybase records
   vSybaseSet = databaseManager.getFoundSet(vSybaseServer, vMySQLTableName)
   vSybaseSet.loadAllRecords()
   
   //create mysql foundset
   vMySQLSet = databaseManager.getFoundSet(vMySQLServer, vMySQLTableName)
   vMySQLSet.clear()
   
   //loop through sybase foundset
   for(var i = 1 ; i <= vSybaseSet.getSize(); i++){
   
      //get sybase record
      vSybaseRecord = vSybaseSet.getRecord(i)
     
      //create new mysql record
      vMySQLRecord = vMySQLSet.getRecord(vMySQLSet.newRecord())
     
      //copy fields from Sybase to MySQL record
      databaseManager.copyMatchingColumns( vSybaseRecord,  vMySQLRecord,  true)
     
      databaseManager.saveData()
   }
}

application.setStatusText('Ready')

[patrick]

This works with all databases (although there are some minor issues, that are not Servoy related). We use this kind of techniques all the time. You have to be aware of a few things however:

1. You should not have any constraints in the target DB (create them after the migration)
2. The size of the foundsets is a memory problem (we load the data in chunks and clear the target foundset once in a while)
3. It is not the absolute fastest way, but by far the most reliable I have used

Patrick