database transfer between diff platforms

[amNewatVB]

hi all....
ok this is very tricky ...

i have to make a project in vb.net which will transfer all the data in a MS Access database on a WIN platform to a mySQL database on a Linux platform....

please ppl this is really urgent n very important..
thnx in advance

[unisse]

XML maybe?

/Nisse

[amNewatVB]

i dont have to use XML...just VB.Net.
please ppl i need to do it urgently

[Pirate]

Originally posted by amNewatVB
i dont have to use XML...just VB.Net.
please ppl i need to do it urgently

All what you need to do is :
After getting all data from the database into a dataset , then use

Yourdataset.WriteXml(path)

This exports a XML file that should be easily read by SQL Server through the dataset obj with this method :

Yourdataset.ReadXml(path)

[amNewatVB]

will this do on a linux platform...coz thatz where i want to copy the database ???
also this means i'll have to take one table at a time.....plz can u explain in more detail

[Pirate]

Originally posted by amNewatVB
will this do on a linux platform...coz thatz where i want to copy the database ???
also this means i'll have to take one table at a time.....plz can u explain in more detail

If I'm not wrong , XML is cross-platforms file . Your database file (mdb) can be exported to XML file . You just keep iteration the dataset obj from the source , if you have more than one table , then add them to the tables collection of the dataset . At the end , use the methods I showed you above .(haven't done tables collection part yet but I've read about it) .

Or

You can put each table in separate XML file by changing table name used in the dataset .

[unisse]

XML should be cross platform. And it would surprise me if yhe SQL for linux platform handles XML differently from Windows based SQL.

/Nisse

[amNewatVB]

ok...as of now i've generated the xml and saved it in a text file using the code below.

mydataset.writexml(path)

in this the path is the text file.

now how do i transfer it to mysql on linux.

in the code

mydataset.readxml(path)

what path shud i give ??? the connection string to mysql database ?? help me here guys

n will it also work if i have to create that same database ?? i mean can a xml be generated that contains the table structure, so that using some code i can create the table and then insert the data ???

[Pirate]

Originally posted by amNewatVB
ok...as of now i've generated the xml and saved it in a text file using the code below.

mydataset.writexml(path)

in this the path is the text file.

now how do i transfer it to mysql on linux.

in the code

mydataset.readxml(path)

what path shud i give ??? the connection string to mysql database ?? help me here guys

n will it also work if i have to create that same database ?? i mean can a xml be generated that contains the table structure, so that using some code i can create the table and then insert the data ???

mysql connection string

When you read XML into dataset , then you're able to add /delete records , then write again the XML file .

[amNewatVB]

i'm sorry but i didnt get u..
in the code

mydataset.readxml(path)

the path is the file where the xml document is saved, right ?
now to transfer it to the mysql database on linux platform, do i give the same file or do i give the connection string for that database ??
can u give me the code plz ??
thnx...n waiting for a response

[Pirate]

Originally posted by amNewatVB
i'm sorry but i didnt get u..
in the code

mydataset.readxml(path)

the path is the file where the xml document is saved, right ?
now to transfer it to the mysql database on linux platform, do i give the same file or do i give the connection string for that database ??
can u give me the code plz ??
thnx...n waiting for a response

the path is the file where the xml document is saved, right ?

Yes .

Then , again , try loading XML file into dataset using ReadXml () method , then connect to mysql server and update the database there . I've not tried this , so I can't give a working code .

[amNewatVB]

ok...will try that
now as i asked earlier...can i get the table structure the same way ???
this method will copy the data, right ??? what if i wanna copy the structure n create the table altogether ???

[Pirate]

ReadXmlSchema() of the dataset will read the structure only .
ReadXML() read data and structure ofcourse .

the same applies to :
write.. write structure only
writeXML data and structure .

[amNewatVB]

with ReadXML....i jget the data and the column headers.....what i want is their datatype and size as well so that i can create a replica of the table elsewhere (mysql database)

[amNewatVB]

hey pirate..i'm facing a prob while reading the XML too !!!
this is what i'm doing

Private Sub btnConvert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConvert.Click
        Try
            myAdapter.Fill(MyDataset1)
            MyDataset1.WriteXml("c:\test.txt")
            MsgBox("Data transferred")
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
End Sub

here myAdapter is the oledbadapter to an access database.

Private Sub btnSend_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSend.Click
        Try
            DataSet21.ReadXml("c:\test.txt")
            myAdapter1.Update(DataSet21)
            MsgBox("Read")
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

here myAdapter1 is an oledbadapter to another access database (doing it just to see if it works with same kind of database)

the problem is that the other database is not being filled with the data...what am i doing wrong ??

[Pirate]

You should export XML file not text file , then load XML not text file .

[amNewatVB]

ok now i did

myDataSet1.ReadXML("C:\test.xml")

and

myDataSet2.WriteXML("C:\test.txt")

still the same thing..the other database has no records

[Pirate]

Yes .

[amNewatVB]

nothing happens

[Pirate]

What do you mean the other database ??

[amNewatVB]

the database where i want the data to be placed. for now it is another ms access database.

[Pirate]

I've just tried it and both methods works (write xml and load it) . I don't know what the hell is your problme , Is that the only code you have there ?

[amNewatVB]

after writing, i've called the update method of the dataadapter

myAdapter2.Update(dataset)

[Pirate]

I did little demo , this code works for me .

'Class level variables 
Private DBName As String
    Private dbConnection As OleDbConnection
    Private sqlstring As String = "SELECT * FROM SecurityTab"
    
 
    Private Sub WriteXMLFile()
        Try
            DBName = "c:\db1.mdb"
            dbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBName & ";Persist Security Info=False")            
            Dim ds As New DataSet
            Dim adp As New OleDb.OleDbDataAdapter
 
            dbConnection.Open()
 
            adp.SelectCommand = New OleDbCommand("SELECT * FROM SecurityTab", dbConnection)
            adp.Fill(ds)
            ds.WriteXml("c:\test.xml")
            MessageBox.Show("Finished writing XML File")
 
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
 
 
 Private Sub ReadXMLFile()
        Try
            Dim ds2 As New DataSet
            Dim adp2 As New OleDb.OleDbDataAdapter(Me.sqlstring, Me.dbConnection)
            Dim builder As New OleDbCommandBuilder(adp2)            
        ds2.ReadXml("c:\test.xml")
            adp2.Update(ds2)
        MessageBox.Show(ds2.Tables(0).Rows(0).Item(0))
 
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
 
'Lastly , do this 
 Private Sub Button1_Click(ByVal sender As System.Object, ByVal 
e As System.EventArgs) Handles Button1.Click
        WriteXMLFile()
        ReadXMLFile()
    End Sub