mysqldatabasewordpressvb.netselect-query

Sending data from sql database to mysql database


I need to get the data from the sql database and send it to a mysql database programmaticly how could I do this?

Here is what i'm currently trying

    Try
        con3.ConnectionString = MyConnectionString
        con3.Open()
        Dim cmd As New MySqlCommand _
        ("SELECT kjuy6_postmeta.meta_value, kjuy6_posts.ID, kjuy6_posts.post_status, kjuy6_woocommerce_order_items.order_item_name  FROM kjuy6_postmeta INNER JOIN kjuy6_posts ON kjuy6_postmeta.post_id = kjuy6_posts.ID And kjuy6_postmeta.post_id = kjuy6_posts.ID, kjuy6_woocommerce_order_items WHERE kjuy6_posts.post_type = 'shop_order' AND kjuy6_postmeta.meta_key = '_paid_date' OR kjuy6_postmeta.meta_key = '_billing_phone'")
        cmd.Connection = con3
        'Dim reader As MySqlDataReader = cmd.ExecuteReader
        Dim da As New MySqlDataAdapter(cmd)
        da.Fill(ds)

        Dim a = ds.Tables(0).Rows(0).Item("meta_value")
        Dim b = ds.Tables(0).Rows(0).Item("meta_value")
        Dim c = ds.Tables(0).Rows(0).Item("post_status")
        Dim d = ds.Tables(0).Rows(0).Item("order_item_name")

        If Not IsNothing(cmd) Then
            con.Open()
            ExecuteData("INSERT INTO BillingInfo (vchUsername, vchExpiryDate, vchOrderStatus, vchSubscriptionType, intSubscriberid) VALUES('" & a & "','" & b & "','" & c & "','" & d & "', '" & SubscriberId & "')")
        End If

    Catch ex As Exception
        Console.WriteLine(ex.Message)
    Finally
        con3.Close()
        con.Close()
    End Try

the following is to seperate the meta values and to use the variable in the insert query as the values

 Dim da As New MySqlDataAdapter(cmd)
        da.Fill(ds)
        Dim a = ds.Tables(0).Rows(0).Item("meta_value")
        Dim b = ds.Tables(0).Rows(0).Item("meta_value")
        Dim c = ds.Tables(0).Rows(0).Item("post_status")
        Dim d = ds.Tables(0).Rows(0).Item("order_item_name")

the problem with this is assigning the correct meta value to a variable so that it can be inserted into the correct column


Solution

  • Assuming you already have the information you need to make a connection to the database, which it looks like you have, set up your reader like this:

    cmd.CommandText = "SELECT kjuy6_postmeta.meta_value, kjuy6_posts.ID, kjuy6_posts.post_status, kjuy6_woocommerce_order_items.order_item_name  FROM kjuy6_postmeta INNER JOIN kjuy6_posts ON kjuy6_postmeta.post_id = kjuy6_posts.ID And kjuy6_postmeta.post_id = kjuy6_posts.ID, kjuy6_woocommerce_order_items WHERE kjuy6_posts.post_type = 'shop_order' AND kjuy6_postmeta.meta_key = '_paid_date' OR kjuy6_postmeta.meta_key = '_billing_phone'"
    

    Now create an ArrayList to hold all of the data:

    Dim column_data as New Arraylist
    Dim table_data as New Arraylist
    

    Then read the data directly into your arraylists

    Dim dbReader As MySqlDataReader = Cmd.ExecuteReader
    While dbReader.Read()
      For x As Integer = 0 To dbReader.FieldCount - 1
         column_array.Add(dbReader(x))
         item_count = dbReader.FieldCount
      Next
      table_data.Add(column_data.ToArray)
      column_data.Clear()
     End While
     dbReader.Close()
     con3.Close()
    

    Now you have all of your data nice and neat in a two dimensional array called table_data. So you can simply go through the rows and columns and extract the data you want to work with. Remembering that arrays are 0 indexed, the first element of the first row would be table_data(0)(0) and so on. The number of items in a row is stored in item_count and the number of rows is table_data.count()

    Any other item you want to reach would be accessed by table_data(row)(col)

    Now you can put the data into another table by establishing a connection and iterating through the data:

    dim insert_string as string = ""
    For x as integer = 0 to table_data.count -1
      insert_string = "insert into mytable (meta_value1, meta_value2,
      post_status, order_item_name) values ("+ table_data(x)(0) +"," +  
      table_data(x)(1) +","+ table_data(x)(2)+","+ table_data(x)(3) + ")"
      cmd.commandtext = insert_string
      cmd.executeNonQuery()
    Next x
    

    I know the process is more involved than just simply filling a data adapter, but it gives you much better control of the data so you can use it as you like.

    Hope that helps.