Mysqlloader conflictoptions doesn`t work

[Denker]

Hi, i have this code

Code: Select all

Public Property options As New MySqlLoaderOptions

sub test ()
        Dim cmd As New MySqlCommand
        Dim datatable As New MySqlDataTable
        Dim mloader As New MySqlLoader
        options.ConflictOption = MySqlLoaderConflictOption.Replace
        cmd.Connection = con
        cmd.open
        cmd.CommandText = "Select " & auswahl & " from " & table & " where id > 10 "
        datatable.SelectCommand = cmd
        datatable.Fill()
        mloader.Connection = conlokal
        mloader.TableName = table
        mloader.Open()
        mloader.CreateColumns()
        mloader.LoadTable(datatable)
        mloader.Close()
end sub

Con and conlokal are different Server.
Createcolumns and replace doesn`t work with loadtable, i will replace the conlokal database with the Data from con.

Can this the Mysqlloader class?
Thanks
Denker

[Pinturiccio]

You create the MySqlLoaderOptions object options. Then you create the mloader object of the MySqlLoader type. But these two objects are not connected. The mloader object does not know anything about the options object. You can connect MySqlLoader and MySqlLoaderOptions objects only when loading data from a file. mloader.LoadTable(...) can use any settings of the options object.

As a workaround, you can write your MySqlDataTable into a file and then load the data from the file with the settings of the options object.
I'm posting below your sample with some changes. When you write data into a file all columns should be separated with the vbTab symbol and each row should end with the vbNewLine symbol.

The changed sample:

Code: Select all

Public Property options As New MySqlLoaderOptions

    Sub Main()
        Dim cmd As New MySqlCommand
        Dim datatable As New MySqlDataTable
        Dim mloader As New MySqlLoader
        options.ConflictOption = MySqlLoaderConflictOption.Replace
        Dim con As New MySqlConnection("your first connection string")
        cmd.Connection = con
        con.Open()
        cmd.CommandText = "select * from dept where deptno=10"
        datatable.SelectCommand = cmd
        datatable.Fill()

        Dim conlokal As New MySqlConnection("your second connection string")
        conlokal.Open()
        Dim sb As New StringBuilder
        For Each row As DataRow In datatable.Rows
            For i As Integer = 0 To datatable.Columns.Count - 1 Step 1
                sb.Append(row(i))
                If i < datatable.Columns.Count - 1 Then
                    sb.Append(vbTab)
                End If
            Next
            sb.Append(vbNewLine)
        Next
        Dim str As String = sb.ToString()
        Console.WriteLine(str)
        System.IO.File.WriteAllText("D:\tmp\temp.txt", str)

        mloader.TableName = "Dept"
        mloader.Delayed = False
        mloader.Connection = conlokal
        mloader.Open()
        mloader.CreateColumns()
        mloader.LoadDataPlanText("D:\tmp\temp.txt", options)

        mloader.Close()
        con.Close()
        conlokal.Close()
    End Sub