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...