Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old July 3rd, 2003, 12:24 PM
Authorized User
 
Join Date: Jun 2003
Location: Barcelona, , Spain.
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default Mysql OLE DB Connection String

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
Reply With Quote
  #2 (permalink)  
Old July 3rd, 2003, 01:45 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 256
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #3 (permalink)  
Old July 3rd, 2003, 02:03 PM
Authorized User
 
Join Date: Jun 2003
Location: Barcelona, , Spain.
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #4 (permalink)  
Old March 14th, 2004, 10:05 AM
Registered User
 
Join Date: Mar 2004
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

try this:

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

Zeus

Reply With Quote
  #5 (permalink)  
Old March 16th, 2004, 04:39 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Copenhagen, , Denmark.
Posts: 440
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.


Reply With Quote
  #6 (permalink)  
Old December 11th, 2007, 08:28 AM
Registered User
 
Join Date: Dec 2007
Location: London, , United Kingdom.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #7 (permalink)  
Old May 30th, 2008, 03:14 AM
Registered User
 
Join Date: May 2008
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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...

Reply With Quote
  #8 (permalink)  
Old July 15th, 2010, 11:01 AM
Registered User
 
Join Date: Jul 2010
Location: Johannesburg South Africa
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to chimwemwe
Default This should do the trick

"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



Quote:
Originally Posted by nmiddleweek View Post
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
Reply With Quote
  #9 (permalink)  
Old July 15th, 2010, 11:04 AM
Registered User
 
Join Date: Jul 2010
Location: Johannesburg South Africa
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to chimwemwe
Default This should do the trick

"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
Reply With Quote
  #10 (permalink)  
Old September 4th, 2016, 09:40 AM
Registered User
Points: 3, Level: 1
Points: 3, Level: 1 Points: 3, Level: 1 Points: 3, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2016
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default oledb connection with mysql for lookup

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
rohitsethi123@gmail.com
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
OLE-DB grstad Classic ASP Basics 7 January 28th, 2007 09:25 AM
c# connection string for .Net 1.1 with Mysql srkarthik_82 General .NET 0 January 17th, 2007 06:58 AM
db connection string Alexi ASP.NET 2.0 Basics 1 October 5th, 2006 06:25 AM
If any one knows How to develop ole db anjicafe Visual C++ 0 June 15th, 2005 10:01 PM
Err with OLE Db Connection DARSIN General .NET 2 June 10th, 2005 12:21 AM



All times are GMT -4. The time now is 03:43 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.