dcsimg
Results 1 to 9 of 9

Thread: Copying records across database servers.

  1. #1

    Thread Starter
    PowerPoster abhijit's Avatar
    Join Date
    Jun 1999
    Location
    Chit Chat Forum.
    Posts
    3,219

    Copying records across database servers.

    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.
    Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
    save a blobFileStreamDataTable To Text Filemy blog

  2. #2
    Frenzied Member mickey_pt's Avatar
    Join Date
    Sep 2006
    Location
    Corner of the Europe :)
    Posts
    1,957

    Re: Copying records across database servers.

    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.
    Last edited by mickey_pt; Jul 31st, 2009 at 07:51 PM.

    Rate People That Helped You
    Mark Thread Resolved When Resolved

  3. #3

    Thread Starter
    PowerPoster abhijit's Avatar
    Join Date
    Jun 1999
    Location
    Chit Chat Forum.
    Posts
    3,219

    Re: Copying records across database servers.

    Quote Originally Posted by mickey_pt View Post
    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?
    Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
    save a blobFileStreamDataTable To Text Filemy blog

  4. #4
    Frenzied Member mickey_pt's Avatar
    Join Date
    Sep 2006
    Location
    Corner of the Europe :)
    Posts
    1,957

    Re: Copying records across database servers.

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

    Rate People That Helped You
    Mark Thread Resolved When Resolved

  5. #5

    Thread Starter
    PowerPoster abhijit's Avatar
    Join Date
    Jun 1999
    Location
    Chit Chat Forum.
    Posts
    3,219

    Re: Copying records across database servers.

    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

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

    Code:
    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.
    Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
    save a blobFileStreamDataTable To Text Filemy blog

  6. #6

    Thread Starter
    PowerPoster abhijit's Avatar
    Join Date
    Jun 1999
    Location
    Chit Chat Forum.
    Posts
    3,219

    Re: Copying records across database servers.

    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.
    Code:
    '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
    Code:
     '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.
    Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
    save a blobFileStreamDataTable To Text Filemy blog

  7. #7
    PowerPoster stanav's Avatar
    Join Date
    Jul 2006
    Location
    Providence, RI - USA
    Posts
    9,292

    Re: Copying records across database servers.

    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.
    Let us have faith that right makes might, and in that faith, let us, to the end, dare to do our duty as we understand it.
    - Abraham Lincoln -

  8. #8

    Thread Starter
    PowerPoster abhijit's Avatar
    Join Date
    Jun 1999
    Location
    Chit Chat Forum.
    Posts
    3,219

    Re: Copying records across database servers.

    Quote Originally Posted by stanav View Post
    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.
    Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
    save a blobFileStreamDataTable To Text Filemy blog

  9. #9
    PowerPoster stanav's Avatar
    Join Date
    Jul 2006
    Location
    Providence, RI - USA
    Posts
    9,292

    Re: Copying records across database servers.

    Something like this:
    Code:
    '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()
    Let us have faith that right makes might, and in that faith, let us, to the end, dare to do our duty as we understand it.
    - Abraham Lincoln -

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width