Copying records across database servers.

[abhijit]

I often need to copy complete transactions from the production system into the test system. The database structure is the same. Some of the fields in a table might have NULL values.

My objective is to write a program that will fetch records associated with a particular transaction and copy them over to the test system.

The database is Oracle 9i (production system) and Oracle 10G (on the test system).

The records will exist in multiple tables.

A simple example -->
MEMBER_MASTER
MEMBER_ID
JOIN_DATE
FIRSTNAME
LASTNAME

MEMBER_ADDRESS
MEMBER_ID
ADDRESS_ID
STREET_NAME
APTNO
CITY
ZIP

If I give a specific member id, it should fetch all records in MEMBER_MASTER and MEMBER_ADDRESS tables and insert these records in the test system.

What is the best way of developing this app? Currently I use sqlloader to do this.

I want to build a front-end and I am thinking I could learn some VB .NET along the way.

[mickey_pt]

A good way to do that, it's using two data tables, fill them with the data returned by the sql command from both of the tables of the production system, and then use that same data tables to update the test system.

[abhijit]

A good way to do that, it's using two data tables, fill them with the data returned by the sql command from both of the tables of the production system, and then use that same data tables to update the test system.

Can you attach a data table to another connection object without losing the data in it?

[mickey_pt]

Take a quick look to the merge method, it's available for the datasets and for the datatables...

DS1 Production Filtered data, DS2 Test System, DS2.Merge(DS1).

[abhijit]

I am working with one single table for now. Here's my code so far.
I am working with Oracle 10G and 9i and ODP.NET

        Dim strSQL As String = "select * from quote_header where quote_no = :quote_no"
        Dim cmdQH As OracleCommand
        Dim prm As OracleParameter
        Dim orc As OracleDataReader
        'Dim orDt As OracleDataAdapter
        Dim dataTab As New System.Data.DataTable
        Dim ds As New System.Data.DataSet("Quote_Header")
        Try
            cmdQH = New OracleCommand(strSQL)
            cmdQH.CommandType = CommandType.Text
            cmdQH.Connection = gOracleConProd
            prm = cmdQH.CreateParameter()
            prm.ParameterName = "pQuoteNo"
            prm.OracleDbType = OracleDbType.Varchar2
            prm.Direction = ParameterDirection.Input
            prm.Size = 6
            prm.Value = iQuoteNo
            cmdQH.Parameters.Add(prm)
            orc = cmdQH.ExecuteReader()
            dataTab.Load(orc)
            dataTab.WriteXml("d:\datatab.xml") '<--Got this in an XML File.
            ds.Tables.Add(dataTab) '<-- Disconnected dataset has been populated.

What do I do with my disconnected dataset?

I started off with this line of code, but teh conversion between a dataReader and an OracleDataReader is unacceptable.

Dim newRd As OracleDataReader = ds.CreateDataReader()

This has to be something simple. I have two open database connections at this time. One to the test system and another to the production system.

[abhijit]

I have succeeded in making this code work. I would like to tweak it a bit.
My code essentially uses a dataset to get this done.

There are two functions.

'This fetches information from the production database.
Private Sub FetchQuoteInfo(ByVal iQuoteNo As String)
        Dim strSQL As String = "select * from quote_header where quote_no = :quote_no"
        Dim cmdQH As OracleCommand
        Dim prm As OracleParameter
        Dim orc As OracleDataReader
        Dim dataTab As New System.Data.DataTable
        Try
            cmdQH = New OracleCommand(strSQL)
            cmdQH.CommandType = CommandType.Text
            cmdQH.Connection = gOracleConProd
            prm = cmdQH.CreateParameter()
            prm.ParameterName = "pQuoteNo"
            prm.OracleDbType = OracleDbType.Varchar2
            prm.Direction = ParameterDirection.Input
            prm.Size = 6
            prm.Value = iQuoteNo
            cmdQH.Parameters.Add(prm)
            orc = cmdQH.ExecuteReader()
            dataTab.Load(orc)
            dataTab.WriteXml("d:\datatab.xml", WriteSchema, True)
            UpdateDev()
            orc.Dispose()
            cmdQH.Dispose()
        Catch ex As Exception
            Debug.Print(ex.ToString)
        End Try
    End Sub

 'This procedure updates a table in a database based on information in an XML file.
    'Ado equivalent of using a disconnected recordset.
    Private Sub UpdateDev()
        Dim oda As OracleDataAdapter = New OracleDataAdapter()
        Dim strSQl As String = "SELECT * FROM QUOTE_HEADER"
        Dim qData As New DataSet
        Try
            oda.SelectCommand = New OracleCommand(strSQl, gOracleConDev)
            Dim ocb As New OracleCommandBuilder(oda)
            qData.ReadXml("d:\dataTab.xml", XmlReadMode.Auto)
            MsgBox(oda.Update(qData, "Quote_Header"), MsgBoxStyle.Information)
            oda.Dispose()
        Catch ex As Exception
            Debug.Print(ex.ToString)
        End Try
    End Sub

I would like to know if there's any way I can avoid writing to a file.

[stanav]

Connect to the 1st DB and fill a datatable. Change the filled datatable's schema to match that of the table in 2nd DB (rename the column names/adding/removing columns as needed). Loop thru rows in the datatable and set their rowstate to added by calling datarow.SetAdded() method.
Now connect to the 2nd DB, using a dataadapter, set its insertcommand and update that modified datatable to your 2nd DB.

[abhijit]

Connect to the 1st DB and fill a datatable. Change the filled datatable's schema to match that of the table in 2nd DB (rename the column names/adding/removing columns as needed). Loop thru rows in the datatable and set their rowstate to added by calling datarow.SetAdded() method.
Now connect to the 2nd DB, using a dataadapter, set its insertcommand and update that modified datatable to your 2nd DB.

How do I change the filled datatable's schema?
The column names and data types are exactly similar.

[stanav]

Something like this:

'Change the name of the 1st column
table.Columns(0).ColumnName = "NewName"
'Adding a new column named "myNewColumn" of type string to the table
table.Columns.Add("myNewColumn", GetType(String))
'Remove the 3rd column from the table
table.Columns(2).Remove()