Hi friends,

Can anyone show me the way to express in the connection string for the Mysql OLE DB provider that the database server is in a different machine from localhost.

I can correctly connect with a local database server with a ConnectionString like this:

"Provider=MySQLProv;Data Source=myDBname;User Id=myDBUser;Password=myDBPwd"

But, how do I express that the Mysql server is in a different machine from the one running the Web Server, let's say the machine corresponding to 192.168.0.5 local IP?

The documentation says that you can do it:
>>Provider=MySQLProv; Data Source=xxx, where xxx is
>> "server=<server name></server>;DB=<database name>

but I have tried it a thousand times with no result apart from that heavy message: "Catastrophic error".

Can any one write the correct connection string for me?

Thanx in advance

Tomas

__________________
Tomàs Jiménez
OranginaLab

Hi, have a go with:

Driver={mysql};Server=<host>.<domain>;PORT=3306;UI D=myDBUser;PWD=myDBPwd;OPTION=16386;

3306 is the remote port used by MySQL and PostgreSQL.

Unless you are expecting loads of over 10+ simultaneous active connections, you could probably use a System DSN without any performance loss, if my memory serves me well.

HTH
Dan

Hi Dan,

Thanks for your quick response but, if I'm not wrong, the connection string you propose refers to an ODBC driver.

I'm trying to use OLE DB instead, because I've heard it offers a better perfomance. Besides, I've tried the ODBC 3.51 driver, and though it connects ok it does not offer pagination facilities at Recordset level (things like RS.AbsolutePage=x and so on).

So I'm really interested in using OLE DB driver.

Thank you any way!

Tomas

try this:

Provider=MySQLProv;Location=<<SERVER>>;Data Source=<<DB>>;USER ID=<<USER>>;PASSWORD=<<PASSWORD>>;option=3;port=33 06;

Zeus

If you have not found the answer yet you can try and have a look at this thread...

http://p2p.wrox.com/topic.asp?TOPIC_ID=7054

which links to this thread...

http://p2p.wrox.com/topic.asp?TOPIC_ID=4444

hope it helps

Jacob.

Hello,

Did anyone find the answer to this problem?

Oranginalab wanted to connect to MySQL using OLE DB but all the replies were geared around using ADO or ODBC...

I'm after this solution too... :)

Cheers,
Nick

try this....
sample code

Imports System.Data.OleDb
'Imports MySql.Data.MySqlClient

Public Class Form1
    Inherits System.Windows.Forms.Form

    Dim conData As New OleDbConnection
    Dim daData As OleDbDataAdapter
    Dim dsData As DataSet = New DataSet
    Dim cbData As OleDbCommandBuilder
    Dim dtData As DataTable
    Dim drData As DataRow
    Dim AddEdit As String
    Dim DataID As Integer = 0
    Dim rowIndex As Integer = 0
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

       conData.ConnectionString = "Provider=MySQLProv.3.9;Location=<<IP address/computer name>>;Data Source=<<database name ex. mysql -- default database when you install mysql>>;USER ID=<<>user id>>;Password=<<optional/>>;port=<<33o6 mysql deafault>>;"

        conData.Open()
        daData = New OleDbDataAdapter("SELECT * FROM proc", conData)
        daData.Fill(dsData, "proc")
        dtData = dsData.Tables("proc")
        DataGrid1.DataSource = dtData
    End Sub

    Private Sub DataGrid1_Navigate(ByVal sender As System.Object, ByVal ne As System.Windows.Forms.NavigateEventArgs) Handles DataGrid1.Navigate

    End Sub


End Class

the problem with the connection is the firewall setting and the user configuration of mysql
try adding this to mysql user....


INSERT INTO `user` (`Host`, `User`, `Password`, `Select_priv`, `Insert_priv`, `Update_priv`, `Delete_priv`, `Create_priv`, `Drop_priv`, `Reload_priv`, `Shutdown_priv`, `Process_priv`, `File_priv`, `Grant_priv`, `References_priv`, `Index_priv`, `Alter_priv`, `Show_db_priv`, `Super_priv`, `Create_tmp_table_priv`, `Lock_tables_priv`, `Execute_priv`, `Repl_slave_priv`, `Repl_client_priv`, `Create_view_priv`, `Show_view_priv`, `Create_routine_priv`, `Alter_routine_priv`, `Create_user_priv`, `ssl_type`, `ssl_cipher`, `x509_issuer`, `x509_subject`, `max_questions`, `max_updates`, `max_connections`, `max_user_connections`) VALUES
('%', 'chris', '*FD571203974BA9AFE270FE62151AE967ECA5E0AA', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '', '', 0, 0, 0, 0);


and then go to control panel, then security center, windows firewall exception, add port, add name example mysqlserver, then add port 3306 for default mysql port be sure its TCP then click ok...

or just turn the firewall off but that would be suicide.....

remember that the user id in the example is chris and the password is 111111

after doing this restart mysql services and you are ready to go...

"Provider=MySQL Provider; Data Source=mySqlServer; User ID=myId; Password=myPassword; Initial Catalog=myTestDB;"

Just remember to download the ole db provider, you can get one from http://cherrycitysoftware.com, but you will need to get an activation code as well, you can get one for free on the site

"Provider=MySQL Provider; Data Source=mySqlServer; User ID=myId; Password=myPassword; Initial Catalog=myTestDB;"

Just remember to download the ole db provider, you can get one from http://cherrycitysoftware.com, but you will need to get an activation code as well, you can get one for free on the site

I want to connect the ssis oldbe my sql like for lookup I have download myoldeb it show's mysql.provider in then oledb of lookup but do not show the tables in lookup . is there any way to solve I want to use ssis lookup for mysql but avoid use of cache in lookup pls guide me
[email protected]