Sending data from sql database to mysql database

Question

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

It would probably help you understand the specifics of what is happening if you just handle the data directly and don't use data adapters. If your interested in using something other than data adapters, let me know.and I'll post as answer. — John
– John, Commented Jul 20, 2017 at 14:11
thanks for all the assistance, i think the only way to pull through all the information and insert them into the correct columns will be to do a sub query. — Jelly Bean
– Jelly Bean, Commented Jul 20, 2017 at 14:58

John · Accepted Answer · 2017-07-20 14:39:35Z

1

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.

answered Jul 20, 2017 at 14:39

John

1,3383 gold badges34 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Jelly Bean Over a year ago

Thank you so much ^^ think it will work much better than the way i was trying to do it.

John Over a year ago

If you need help getting it implemented, write back and I'll give you the specifics. I use this process of handling data a lot. I also transfer a good bit of data between MySQL and SQL server a lot as well.

Jelly Bean Over a year ago

Could I give you my email for that ? I wasn't able to implement it but I managed to get the result i needed by doing a sub query

Jelly Bean Over a year ago

could you help me understand what the following is there for ? column_array.Add(dbReader(x)) item_count = dbReader.FieldCount

John Over a year ago

To answer your question: dbReader is reading the items from the database. It reads items one column at a time. So 'x' is the index of the column it is currently on. If there are multiple columns, then x starts at 0 and increases by one for each column. FieldCount is the number of columns that were found when the data reader read the row. column_array.add(), adds each column to your array list, one field at a time. Once all of the fields have been read, the whole list gets added to the table_data arraylist as a new row table_data.add(your row).

Jelly Bean · Accepted Answer · 2019-06-11 13:43:05Z

0

I managed to split the data i need into their own columns by using the following sub query. Thought i would post it

SELECT kjuy6_posts.ID, kjuy6_posts.post_status, kjuy6_woocommerce_order_items.order_item_name, b.meta_value AS PaidDate, b.meta_value AS MobileNumber
FROM kjuy6_posts INNER JOIN kjuy6_woocommerce_order_items ON kjuy6_woocommerce_order_items.order_id = kjuy6_posts.ID INNER JOIN
                             (SELECT post_id, meta_value
                               FROM kjuy6_postmeta
                               WHERE (meta_key = '_billing_phone') b ON b.post_id = kjuy6_posts.ID
WHERE kjuy6_posts.post_type = 'shop_order'

edited Jun 11, 2019 at 13:43

answered Jul 21, 2017 at 11:16

Jelly Bean

1,2191 gold badge13 silver badges24 bronze badges

Collectives™ on Stack Overflow

Sending data from sql database to mysql database

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related