How to code in vb.net with 2 mysql connection from localhost and remote internet database server?

How about creating a local table that references data of remote machine?

CREATE TABLE TABLE_NAME(

<SAME SCHEMA AS REMOTE TABLE>

)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://UNAME:PASSWORD@remote_host:PORT/DATABASE/TABLENAME';

Now, you can connect treat this table as local, and queries executed on this will be executed on remote database.
Speed may be an issue to look for.

What version of mysql you are using?

Thank you

I am using the latest version of mysql for windows:

I have attached a code snippet that has a trailed ?mark to fill in the blanks of what I have mess.

I guess the solution is ok, But if we talk about speed, it will be to slow using that method.

Because the real thing that I want to do is to INSERT a record where compared and found, like this:

INSERT INTO LOCALSERVERenroll(ID)
SELECT INTERNETSERVERenroll.ID
FROM INTERNETSERVERenroll
WHERE INTERNETSERVERenroll.ID NOT IN
(SELECT LOCALSERVERenroll.ID FROM LOCALSERVERenroll)

And in-case if there is a mysql command code better than that, for speed and performance. It would be great.

I don't find performance issue for single insert.
Please try this. Till than we may found a better solution.

Hello

You are trying to say that I'll create a new table for local and copy all the contents from the internet data table.

What if the contents of the internet data table consist of a million records, lets say 80 million records and each record to download is 600 bytes long.

And another minute for comparing both local table that has a million record.

Probably a minute to an hour will finish the transaction query.

But I appropriate your idea, but it seems the method is much traffic and delays.

This is our expected records to reach in our internet database server:
80 million records, and each record consist of 600 bytes long.

This is our expected records to reach in our local database server.
79.X million records, and each record consist of 600 bytes long.

So, therefore a comparison of both records from internet database to local database.

Records from local database table will be updated from the record of the internet database table every time when the computer starts or the application program open.

No, Just create table with the same schema as follows:

CREATE TABLE TABLE_NAME(

<SAME SCHEMA AS REMOTE TABLE>

)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://UNAME:PASSWORD@remote_host:PORT/DATABASE/TABLENAME';

No need to copy content, mysql will do it for you.

suggested readings:- http://dev.mysql.com/doc/refman/5.0/en/federated-storage-engine.html

Thank you very much, I'll try to understand the link, and have a follow up question from you.


Because I can't hardly understand what your trying to do with the code.

As I understand, I have to make a table using Mysql Query Browser or Mysql  Administrator? And Set the engine of the table to FEDERATED?

Ok, I'll try.

Thanks :)

Well use any tool and create the table with the same schema of the remote table.
and yes, what additional you'll do here is:-
1) ENGINE=FEDERATED
2) CONNECTION='mysql://UNAME:PASSWORD@remote_host:PORT/DATABASE/TABLENAME';

Importantly for 2), just note that
Whenever you'll fire any query on local server with on this table, mysql will take it to the "remote_host" on "DATABASE"."TABLENAME" using credentials: "UNAME" and "PASSWORD".
Replace them accordingly.

OK, I have not put an option of --federated, I'll do some move for this.

I'm not sure what connection to use for the two connection from local or remote.

But can you point where to put the code or debug/edit the code below.

Thanks for making it easy.

Protected lmyconn As MySqlConnection
Protected imyconn As MySqlConnection
 
Protected XmyCom As New MySqlCommand
 
Protected Const lConStr As String = "server=" & "localhost" & ";" _
& "port=" & "3306" & ";" _
& "user id=" & "xid" & ";" _
& "password=" & "xpw" & ";" _
& "database=LOCALDB"
 
Protected Const iConStr As String = "server=" & "203.177.108.33" & ";" _
& "port=" & "3306" & ";" _
& "user id=" & "yid" & ";" _
& "password=" & "ypw" & ";" _
& "database=INTERNETDB"
 
 
''LOCALDB Table is tableX, Field1 is ID, Field2 is XPicture
''INTERNETDB Table is tableY, Field1 is ID, Field2 is YPicture
 
Private Sub somecommands()
 Dim sqlSELECT As String=
"
INSERT INTO LOCALSERVERenroll(ID)
SELECT INTERNETSERVERenroll.ID
FROM INTERNETSERVERenroll
WHERE INTERNETSERVERenroll.ID NOT IN
(SELECT LOCALSERVERenroll.ID FROM LOCALSERVERenroll)
"
 ?myCom = New MySqlCommand(sqlSELECT, ?myconn)
 
 Dim myReader As MySqlDataReader
 myReader = lmyCom.ExecuteReader()
 While myReader.Read
 End While
 myReader.Close()
End Sub
 
Please Fill in the trailed ?Mark.

Select allOpen in new window

Also one of my colleague told me that I can use dotnet remoting.

What do you recommend? This federated engine? or the dotnet remoting?

Thanks.

I'm finding a link to download a mysql server that can handle up to 100gig of database data and to setup with the --federated.

:)

OK Thanks

BUT can you just edit or debug the code that I've inserted in the snippet.  So I can go faster with no-hassle in mind, I know your the expert Master vb.net.

Please
:)

Protected lmyconn As MySqlConnection
Protected imyconn As MySqlConnection
 
Protected ?myCom As New MySqlCommand
 
Protected Const lConStr As String = "server=" & "localhost" & ";" _
& "port=" & "3306" & ";" _
& "user id=" & "xid" & ";" _
& "password=" & "xpw" & ";" _
& "database=LOCALDB"
 
Protected Const iConStr As String = "server=" & "203.177.108.33" & ";" _
& "port=" & "3306" & ";" _
& "user id=" & "yid" & ";" _
& "password=" & "ypw" & ";" _
& "database=INTERNETDB"
 
''LOCALDB Table is tableX, Field1 is ID, Field2 is XPicture
''INTERNETDB Table is tableY, Field1 is ID, Field2 is YPicture
 
Private Sub somecommands()
imyconn = New MySqlConnection()
          imyconn.ConnectionString = iConStr
          imyconn.Open()
lmyconn = New MySqlConnection()
          lmyconn.ConnectionString = lConStr
          lmyconn.Open()
 
Dim sqlSELECT As String=
" 
?
INSERT INTO LOCALSERVERenroll(ID)
SELECT INTERNETSERVERenroll.ID
FROM INTERNETSERVERenroll
WHERE INTERNETSERVERenroll.ID NOT IN
(SELECT LOCALSERVERenroll.ID FROM LOCALSERVERenroll)
?
"
 
?myCom = New MySqlCommand(sqlSELECT, ?myconn)
 
Dim myReader As MySqlDataReader
myReader = ?myCom.ExecuteReader()
While myReader.Read
?
End While
myReader.Close()
?myconn.close()
?myconn.dispose()
End Sub

Select allOpen in new window

Were afraid to use Federated Engine :) So many reports on Bugs.
http://bugs.mysql.com/search.php?cmd=display&bug_type=Server%3A+Federated&status=Active&search_for=federated&os=0&bug_age=0&order_by=severity&direction=ASC&limit=50

BUT can you just edit or debug the code that I've inserted in the snippet.  So I can go faster with no-hassle in mind, after all your a  Master in vb.net.

Please
:)

Hello theGhost_k8:

Would please Edit the code or debug it,  it's just a matter of copy and paste to the snippet, then change some code that you know what is correct.

Please help easily for easy request.

Thanks

Hi rionroc,

Please post the question to .net zone.

Thanks.

Thank you Ghost

Can you snip a code like: selecting a record NOT IN a hashtable?

eq:
SELECT id FROM xtable WHERE id NOT IN (hashtable)

hashtable contains:
1,2,3,4,5

xtable xcontains:
1,2,3

RESULT:
4,5

Is this possible?
Can you issue a small sql command with a hashtable?
I hope someone can.
But I'm trying it now.
:)

I've found the solution

Thanks Sir Ghost :)

well was on looooong vacation....
but u may put the solution here....if u can..
thanks :)