13 Replies - 708 Views - Last Post: 29 November 2018 - 05:42 AM Rate Topic: -----

#1 makis_best   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 11
  • Joined: 19-December 16

Custom Sync Tool

Posted 27 November 2018 - 01:49 AM

Hi

How I can create one application that get results from one MS SQL Server query
compare them with one MYSQL Server query and if it is equal then do update else do insert.

For example, let say I have my source sql query
Select ID, FName, LName From TBNames

Results:
ID | FName | Lname
1 | John | Eaglin
2 | Harry | Baggerly
3 | Charlie | Abbett
4 | George | Dent

Then from Mysql query I have the result
Select ID, fName, LName from MYNames;

ID | FName | Lname
1 | John | Smith
2 | Tomas | Ford

I need to pass through ID field and update ID 1 and 2 from Smith to Eaglin
from Tomas Ford to Harry Baggerly and insert the other 2 records.
Running the same select on MYSQL I get the result
ID | FName | Lname
1 | John | Eaglin <-- Last Name changed
2 | Harry | Baggerly <-- Both Names changed
3 | Charlie | Abbett <-- Inserted
4 | George | Dent <-- Inserted
The problem is that queries are many with lot of different fields and tables.
Is there any way I can pass fields names and results as variables to the update or insert query?

Thank you

Is This A Good Question/Topic? 0
  • +

Replies To: Custom Sync Tool

#2 makis_best   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 11
  • Joined: 19-December 16

Re: Custom Sync Tool

Posted 27 November 2018 - 02:01 AM

======== Edit ========

Then from Mysql query I have the result
Select ID, fName, LName from MYNames where ID = {MSSQL_ID}; 

ID | FName | Lname
1 | John | Smith
2 | Tomas | Ford
Was This Post Helpful? 0
  • +
  • -

#3 RamonRobben   User is offline

  • D.I.C Addict
  • member icon

Reputation: 90
  • View blog
  • Posts: 590
  • Joined: 19-May 14

Re: Custom Sync Tool

Posted 27 November 2018 - 02:07 AM

You can query the results from one table and then execute an update ignore on the second mysql server.
So in steps that would look like.

Get id, fname and lname from mynames where id is blah
then with those values:
UPDATE IGNORE table_name SET column1=value, column2=value2 WHERE some_column=some_value

the update ignore statement will only execute if one of the fields is different from the given values.

This post has been edited by RamonRobben: 27 November 2018 - 02:09 AM

Was This Post Helpful? 0
  • +
  • -

#4 makis_best   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 11
  • Joined: 19-December 16

Re: Custom Sync Tool

Posted 27 November 2018 - 02:18 AM

Thank for the help.

I agree with you but the problem is that the tables are almost 2000 and the fields about 350000.
I don't want to write so many query's... I want them to build automatic.
Was This Post Helpful? 0
  • +
  • -

#5 RamonRobben   User is offline

  • D.I.C Addict
  • member icon

Reputation: 90
  • View blog
  • Posts: 590
  • Joined: 19-May 14

Re: Custom Sync Tool

Posted 27 November 2018 - 03:58 AM

The process I described can be automated by you using VB.NET.
If I were you I would look for how to get data from MySQL database and how to execute queries.
Then get all data and for each row do an update ignore on the second database.
The speed is dependend on how big the data is that is in the database.
Was This Post Helpful? 0
  • +
  • -

#6 makis_best   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 11
  • Joined: 19-December 16

Re: Custom Sync Tool

Posted 27 November 2018 - 04:54 AM

@RamonRobben

Ok I understand... I will do some Google search about it.

Thank you once more.
Was This Post Helpful? 0
  • +
  • -

#7 Sheepings   User is offline

  • Senior Programmer
  • member icon

Reputation: 155
  • View blog
  • Posts: 972
  • Joined: 05-December 13

Re: Custom Sync Tool

Posted 27 November 2018 - 09:42 AM

Perhaps you might get a starting point from these functions. Most people Prefer to do there statements inside the methods where as I prefer to pass them to the methods. The code is more or less the same bar the select statement... but all-in-all, you could break the insert, update, and delete into one method, and change the params of the method to include parameter values [email protected] and keep adding them, and you can also make use of Optional ::

(ByVal Statement As String, ByVal ConString As MySqlConnection, Optional ByVal param1 As String = Nothing)

This will allow you to add a ton of additional params and you won't be required to pass values to them when calling the methods. Code is tested and working...

Imports System.IO
Imports System.Net
Imports MySql.Data.MySqlClient

Public Class Form1
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        DBInsert(execInsertUserQuery, Con)
        DBUpdate(execUpdateNameQuery, Con)
        DBSelect(execSelectUnameQuery, Con)
        Delete(execDeleteUserQuery, Con)
    End Sub
    Private cString As String = "server=localhost;user id=root1;password=pass;persistsecurityinfo=True;database=visuals_db;port=8889"
    'The above cString is my connection string. Yours will be different.
    Private execSelectUnameQuery As String = "SELECT username FROM users WHERE [email protected]"
    Private execUpdateNameQuery As String = "UPDATE users SET [email protected] WHERE [email protected];"
    Private execInsertUserQuery As String = "INSERT INTO users (name) VALUES (@Fname)"
    Private execDeleteUserQuery As String = "DELETE FROM users WHERE [email protected]"
    'These statements are for testing only. You will need to write your own or ask for help doing so.
    Private Con As MySqlConnection = New MySqlConnection(cString)
    'Build a connection and pass the Connection string to it

    Private Function DoConnection() As Boolean
        Select Case Con.State = ConnectionState.Closed
                'Select the condition of the connection based on its state
            Case True 'if closed, we open it
                Con.Open()
                Return True
            Case False 'if open we close it
                Con.Close()
                Return False
        End Select
        Return False
    End Function
    'The insert statement
    Private Function DBInsert(ByVal Statement As String, ByVal ConString As MySqlConnection) As Boolean
        'I prefer to pass my statements to the methods
        Try
            If ConString IsNot Nothing Then
                DoConnection() 'This will open and close the connection
                Using cmd As MySqlCommand = New MySqlCommand(Statement, ConString)
                    cmd.Parameters.AddWithValue("@FName", "Value1 goes here")
                    cmd.ExecuteNonQuery() 'Execute the statement
                End Using
                DoConnection()
                Return True
            End If
            Return False
        Catch ex As Exception
            MessageBox.Show("There is an error at " & ex.StackTrace.ToString())
            Return False
        End Try
    End Function


Inserted ::

Attached Image

    Private Function DBUpdate(ByVal Statement As String, ByVal ConString As MySqlConnection) As Boolean
        Try
            If ConString IsNot Nothing Then
                DoConnection() 'This will open and close the connection
                Using cmd As MySqlCommand = New MySqlCommand(Statement, ConString)
                    cmd.Parameters.AddWithValue("@id", 60) 'This is the ID i wanted to check. Yours will be different
                    cmd.Parameters.AddWithValue("@FName", "ValueChange goes here")
                    cmd.ExecuteNonQuery()
                End Using
                DoConnection()
                Return True
            End If
            Return False
        Catch ex As Exception
            MessageBox.Show("There is an error at " & ex.StackTrace.ToString())
            Return False
        End Try
    End Function

Updated value ::

Attached Image

    Dim SelectVar As String
    Private Function DBSelect(ByVal Statement As String, ByVal ConString As MySqlConnection) As String
        Try
            If ConString IsNot Nothing Then
                DoConnection() 'This will open and close the connection
                Using cmd As MySqlCommand = New MySqlCommand(Statement, ConString)
                    cmd.Parameters.AddWithValue("@FName", "ValueChange goes here")
                    Using QueryReader As MySqlDataReader = cmd.ExecuteReader()
                        'Use a reader to read the data, and use HasRows to check if there are any
                        If QueryReader.HasRows Then
                            While QueryReader.Read()
                                SelectVar = QueryReader.GetString(0)
                            End While
                        End If
                    End Using
                End Using
                DoConnection()
                Return SelectVar
            End If
            Return SelectVar
        Catch ex As Exception
            MessageBox.Show("There is an error at " & ex.StackTrace.ToString())
            Return SelectVar
        End Try
    End Function


Selecting the value ::

Attached Image

Attached Image

    Private Function Delete(ByVal Statement As String, ByVal ConString As MySqlConnection) As Boolean
        Try
            If ConString IsNot Nothing Then
                DoConnection() 'This will open and close the connection
                Using cmd As MySqlCommand = New MySqlCommand(Statement, ConString)
                    cmd.Parameters.AddWithValue("@id", 60) 'To delete where id is 60, yours will be different
                    cmd.ExecuteNonQuery()
                End Using
                DoConnection()
                Return True
            End If
            Return True
        Catch ex As Exception
            MessageBox.Show("There is an error at " & ex.StackTrace.ToString())
            Return False
        End Try
    End Function
End Class


Deleted record ::

Attached Image

One other thing, I'm sure you know better than to run this method like so, and wonder why it doesn't work. lol
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        DBInsert(execInsertUserQuery, Con)
        DBUpdate(execUpdateNameQuery, Con)
        DBSelect(execSelectUnameQuery, Con)
        Delete(execDeleteUserQuery, Con)
    End Sub

You will need to put a break point on the code like so, and step through the code to see it execute or else move each call into its own method. Ie, button for select, delete, insert, update etc.

Attached Image

I also wouldn't worry to much about how many queries or updating, changing or receiving large volumes of records. Although this will boil down to how you use your queries. But if you need help, you can always post a new topic or update this one if its on point of the topic. Hope this helps.
Was This Post Helpful? 1
  • +
  • -

#8 makis_best   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 11
  • Joined: 19-December 16

Re: Custom Sync Tool

Posted 28 November 2018 - 09:58 AM

Sheepings

The code is perfect when it up to use for just one table with 3 fields.
How I can make the code to automatic change the parameters as many as the table fields are.

In the example you use only one parameter for the table "@FName"
What if the next table must have 20 parameters and next after that 30?
Was This Post Helpful? 0
  • +
  • -

#9 Sheepings   User is offline

  • Senior Programmer
  • member icon

Reputation: 155
  • View blog
  • Posts: 972
  • Joined: 05-December 13

Re: Custom Sync Tool

Posted 28 November 2018 - 11:10 AM

View PostSheepings, on 27 November 2018 - 04:42 PM, said:

and change the params of the method to include parameter values [email protected] and keep adding them, and you can also make use of Optional ::

(ByVal Statement As String, ByVal ConString As MySqlConnection, Optional ByVal param1 As String = Nothing)

This will allow you to add a ton of additional params and you won't be required to pass values to them when calling the methods.

Not to sound crude, but which part of that did you not understand, or didn't you read it?

In the case I wasn't clear, I will conjure up an example of what I mean in a while.
Was This Post Helpful? 1
  • +
  • -

#10 Sheepings   User is offline

  • Senior Programmer
  • member icon

Reputation: 155
  • View blog
  • Posts: 972
  • Joined: 05-December 13

Re: Custom Sync Tool

Posted 28 November 2018 - 01:08 PM

Grab a cup of tea or coffee, you will need it.

Only you know what queries you will be running, and for each query, needs a statement. Clearly, your statements need logic to test them against since you have so many of them, regardless the amount you need to run, you will still need to write logic for them. Lets start with the insert statement ::
Private execInsertUserQuery As String = "INSERT INTO users (name,email,password,username) VALUES (@Fname,@Param1,@Param2,@Param3)"

We can then execute the insert method by passing optional parameters (the ones we want to pass) using the below code:
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        DBInsert(execInsertUserQuery, Con, "Sheepings DIC", "Param A", "Param B", "Param C")
End Sub

You can see below the statement we passed with the parameters.

Attached Image

We get the parameters in the method by passing them to it.

Attached Image

PrmFName displays the value we passed to it. "Sheepings DIC"

Attached Image

Remember?
DBInsert(execInsertUserQuery, Con, "Sheepings DIC", "Param A", "Param B", "Param C")


So now here comes your problem. You can alter this method/function to include as many OPTIONAL parameters as you like, but, the BIG BUT is that your statement method needs to know how many; exactly how many parameters are to be passed to the command, because if you pass the wrong amount, it will error in this section of the code where the command is built to execute ::

cmd.ExecuteNonQuery() 'Execute the statement


If the amount of parameters exceeds what is in the statement, it will throw an exception. Now, since you said there will be many queries specifying different queries. I recommend you count how many of the parameters are not Nothing, and then use a switch statement to switch to a command for the amount of parameters required to execute the statement.

For the most part so far, the code works as changed above.

Attached Image

................................ Made some changes from above ..............................

Now back to implementing your switch statement.... Ok don't get confused, I've changed the code again from the above screenshots just slightly, to adapt passing a variable integer to the method to count how many parameters are being cast to the method. Why I done this is because you will still need to write each statement anyway, and so you will need to clarify what parameters you are passing. So while you are at it, you will count them.

Because we want the statements to execute as quickly as possible, I decided against counting each parameter which is not nothing inside the method. So it makes sense for you to pass the value of the amount of parameters being sent to the method, while you're deciding which parameters to send. (As you write them) Makes sense right?

Here is what's changed ::

Lets say you want to insert 4 fields into this

    Private execInsertUserQuery As String = "INSERT INTO users (name,email,password,username) VALUES (@Fname,@Param1,@Param2,@Param3)"


You would execute your new statement like this from your button click event
DBInsert(execInsertUserQuery, Con, 4, "Sheepings DIC", "Param A", "Param B", "Param C")

4 being the number of parameters required for the above insert statement. So based on the method I changed, which now looks like this
Private Function DBInsert(ByVal Statement As String, ByVal ConString As MySqlConnection, paramsCount As Integer, Optional ByVal PrmFName As String = Nothing, Optional ByVal Prm1 As String = Nothing, Optional ByVal Prm2 As String = Nothing, Optional ByVal Prm3 As String = Nothing, Optional ByVal Prm4 As String = Nothing, Optional ByVal Prm5 As String = Nothing) As Boolean


Remember that the Optional keyword on the method means that you don't have to pass a value to it unless you need to. If we only want FName and Param1. We need to change the statement to this
Private execInsertUserQuery As String = "INSERT INTO users (name,email) VALUES (@Fname,@Param1)"

Then we change the way we call the method/function like so
DBInsert(execInsertUserQuery, Con, 2, "Sheepings DIC1", "Param A1")

We decrease the paramsCount to 2 because we are only passing 2 parameters in the insert statement. Which are these two

"Sheepings DIC1", "Param A1"

So, when the function/method runs, the switch statement will execute only the command with two parameters. The complete function now looks like this ::

    Private Function DBInsert(ByVal Statement As String, ByVal ConString As MySqlConnection, paramsCount As Integer, Optional ByVal PrmFName As String = Nothing, Optional ByVal Prm1 As String = Nothing, Optional ByVal Prm2 As String = Nothing, Optional ByVal Prm3 As String = Nothing, Optional ByVal Prm4 As String = Nothing, Optional ByVal Prm5 As String = Nothing) As Boolean
        'I prefer to pass my statements to the methods
        Try

            If ConString IsNot Nothing Then
                DoConnection() 'This will open and close the connection
                Select Case paramsCount
                    Case 1
                        Using cmd As MySqlCommand = New MySqlCommand(Statement, ConString)
                            cmd.Parameters.AddWithValue("@FName", PrmFName)
                            cmd.ExecuteNonQuery()
                        End Using
                        DoConnection()
                        Return True 'We need to return after each execution
                    Case 2
                        Using cmd As MySqlCommand = New MySqlCommand(Statement, ConString)
                            cmd.Parameters.AddWithValue("@FName", PrmFName)
                            cmd.Parameters.AddWithValue("@Param1", Prm1)
                            cmd.ExecuteNonQuery()
                        End Using
                        DoConnection()
                        Return True
                    Case 3
                        Using cmd As MySqlCommand = New MySqlCommand(Statement, ConString)
                            cmd.Parameters.AddWithValue("@FName", PrmFName)
                            cmd.Parameters.AddWithValue("@Param1", Prm1)
                            cmd.Parameters.AddWithValue("@Param2", Prm2)
                            cmd.ExecuteNonQuery()
                        End Using
                        DoConnection()
                        Return True
                    Case 4
                        Using cmd As MySqlCommand = New MySqlCommand(Statement, ConString)
                            cmd.Parameters.AddWithValue("@FName", PrmFName)
                            cmd.Parameters.AddWithValue("@Param1", Prm1)
                            cmd.Parameters.AddWithValue("@Param2", Prm2)
                            cmd.Parameters.AddWithValue("@Param3", Prm3)
                            cmd.ExecuteNonQuery() 'Execute the statement
                        End Using
                        DoConnection()
                        Return True
                        'So on so on
                End Select
                DoConnection()
                Return True
            End If
            Return False
        Catch ex As Exception
            MessageBox.Show("There is an error at " & ex.StackTrace.ToString())
            Return False
        End Try
    End Function




I know this was a long post to follow lol, and hopefully I didn't burn out to many of your braincells with it. But as I started writing it, I needed to change some of it. And didn't want to risk further confusion by erasing what I previously wrote. I think that ship already sailed. :)

Well I hope you find this post more useful, and if you want to extend it further, with loads more parameters, just replicate the steps I've made and changed. Post back if you have any questions.

Code working and tested perfectly

Attached Image
Was This Post Helpful? 1
  • +
  • -

#11 makis_best   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 11
  • Joined: 19-December 16

Re: Custom Sync Tool

Posted 28 November 2018 - 01:38 PM

Dear Sheepings

You are more like a teacher to me....
I understand exactly what you did there.... Very smart and flexible.
Just one question....

If I create something like your example
Private execInsertUserQuery As String = "INSERT INTO users (name,email,password,username) VALUES (@Fname,@Param1,@Param2,@Param3)"


But I execute
DBInsert(execInsertUserQuery, Con, 2, "Sheepings DIC", "Param A")

that means the other parameters will be ignored or it will throw error?

If yes why?
Was This Post Helpful? 0
  • +
  • -

#12 Sheepings   User is offline

  • Senior Programmer
  • member icon

Reputation: 155
  • View blog
  • Posts: 972
  • Joined: 05-December 13

Re: Custom Sync Tool

Posted 28 November 2018 - 03:17 PM

I think you need to reread my last post, as I've already explained this to you, but I will re-explain with a little elaboration.

View Postmakis_best, on 28 November 2018 - 08:38 PM, said:

If I create something like your example
Private execInsertUserQuery As String = "INSERT INTO users (name,email,password,username) VALUES (@Fname,@Param1,@Param2,@Param3)"

The above insert statement is inserting into a table called users. It's going to be looking for the parameters for name, email, password, username, and the values it will want; it will want to retrieve from the parameter fields when the MySQL command is being constructed. Please acknowledge what the code is doing, and how it works ::
Using cmd As MySqlCommand = New MySqlCommand(Statement, ConString)

In english, this read as so... We use the MySQLCommand and pass the Statement to it, with the Connection String. So if you look at the statement again ::
"INSERT INTO users (name,email,password,username) VALUES (@Fname,@Param1,@Param2,@Param3)"



You are telling the statement there are for fields :: (name,email,password,username) with four parameter values :: VALUES (@Fname,@Param1,@Param2,@Param3)

So when you pass this statement the command builder ::
Using cmd As MySqlCommand = New MySqlCommand(Statement, ConString)

It knows how many parameters to expect because the Statement says so. If you only pass these two, as opposed to four Parameters.AddWithValue() - It will give you an error, and obviously so, because you haven't specified anything for the additional fields. And to explain what way these work ::
                        cmd.Parameters.AddWithValue("@FName", PrmFName)
                        cmd.Parameters.AddWithValue("@Param1", Prm1)


In the case of .AddWithValue("@FName", PrmFName) FName is like a place-holder the value you are referencing on the right side of the comma, PrmFName.

Does this explain what you wanted to know?

View Postmakis_best, on 28 November 2018 - 08:38 PM, said:

But I execute
DBInsert(execInsertUserQuery, Con, 2, "Sheepings DIC", "Param A")

that means the other parameters will be ignored or it will throw error?

If yes why?


If your statement has 6 fields, and six values, then you need to provide the correct number of parameters to pass to the method/function - paramsCount As Integer must equal the amount of parameters you will be passing to this ::
Private Function DBInsert(ByVal Statement As String, ByVal ConString As MySqlConnection, paramsCount As Integer, Optional ByVal PrmFName As String = Nothing, Optional ByVal Prm1 As String = Nothing, Optional ByVal Prm2 As String = Nothing, Optional ByVal Prm3 As String = Nothing, Optional ByVal Prm4 As String = Nothing, Optional ByVal Prm5 As String = Nothing) As Boolean


Note, since the function/method only takes 5 parameters, you will need to add more like so :: so after this, Optional ByVal Prm5 As String = Nothing add more :: Optional ByVal Prm6 As String = Nothing

The only part you can ignore, is if you add 50 Options Parameters to, but you are only inserting 6 parameters, well then it won't matter if you send any additional parameters to the method/function, nor would they be required providing you state 6 for the paramsCount, because the switch statement will only execute the amount of parameters expected by the paramsCount integer.

Don't forget, you will also have to extend the switch statement to reflect how many is your maximum amount of parameters your method/function takes.

Does this summarise your curiosity?

Remember to hit the green plus on any of the posts you have used or found helpful. :)
Was This Post Helpful? 1
  • +
  • -

#13 makis_best   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 11
  • Joined: 19-December 16

Re: Custom Sync Tool

Posted 29 November 2018 - 01:20 AM

Sheepings thank you for your answer...

I understand....

I 100% sure whet I need to do now...
Was This Post Helpful? 1
  • +
  • -

#14 Sheepings   User is offline

  • Senior Programmer
  • member icon

Reputation: 155
  • View blog
  • Posts: 972
  • Joined: 05-December 13

Re: Custom Sync Tool

Posted 29 November 2018 - 05:42 AM

I am glad I could help you. I admire anyone who dedicates themselves to learning experience. ;) If you need to ask any other questions, let me know, and good luck with your project.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1