mySql and Standard Edition

[ggprogram]

I am trying to connect .NET to mySQL. When I set up an oleDbConnection using an ODBC driver, I get an error message that says "It is only possible to connect to SQL Server Desktop and MS Access databases with this version". Does this mean I need the Professional version of Studio to connect to non-Microsoft products? I have searched the Studio sales site and I can't see where this limitation is mentioned anywhere. Or could this error message be errroneous?

[ggprogram]

I called MicroSoft and they say the message is misleading. They say that Standard edition does not allow you to connect using the visual tools, but that you should be able to do so through the code. So I am trying to connect using ODBC:

Imports Microsoft.Data.Odbc
Module Module1
Sub Main()
Dim MyConString As String
Dim myConnection As OdbcConnection

MyConString = "DRIVER=(People 3.51);SERVER=localhost;DATABASE=test;"
myConnection = New OdbcConnection(MyConString)
myConnection.Open()
End Sub
End Module

where People 3.51 is the DSN name. I get an error at the Open statement, but I am not sure why? So, I am still not sure what is going on. Does this code look correct?

[Iouri]

download dbProvider from eInfoDesign. It works well with vb.net
http://www.einfodesigns.com/dbProvider_info.aspx

.NET Provider
dbProvider is the managed .NET provider to the MySQLTM database. The provider is delivered as a .NET
assembly, and can be stored in the Global Assembly Cache for system-wide use.

The dbProvider product implements the full Microsoft ADO.NET Provider interface.

The following ADO.NET classes are implemented:

MySqlConnection the main connection to the MySQLTM database.
MySqlCommand enables the execution of any command against the database.
MySqlDataReader provides fast, forward-only read access to the database.
MySqlDataAdapter serves as an iterface between the MySQLTM data classes and the Microsoft DataSet.
MySqlParameter used to store dynamic parameters for a command.
MySqlTransaction used to represent a MySQLTM transaction.


eInfoDesign (EID) provider for .NET (dbProvider

Simply copy the dbProvider.dll file to your project's source path and add a .NET
reference to it from Visual Studio. You may also copy it to the General Assembly Cache if
you prefer.

To use the binary data support with BLOB fields, do the following:

1) To update or insert binary data into the database, use the following syntax:

cmd.CommandText = "UPDATE typetest SET type_longblob = @p1 WHERE id=1";
cmd.Parameters.Add("@p1",DbType.Binary);
byte []b = new byte[1000];
// store some binary information into the byte array b
cmd.Parameters["@p1"].Value = b;

2) To read binary information from the database, simply use the GetBlob() method on the
MySqlDataReader class.

byte []b2 = dr.GetBlob(0);
dr.Close();
====================================================


Imports EID.MySqlClient

Dim oMySqlConn As MySqlConnection = New MySqlConnection()
oMySqlConn.ConnectionString = "Data Source=localhost;" & _
"Server=192.168.1.4;" & _
"Database=mySQLDatabase;" & _
"User ID=myUsername;" & _
"Password=myPassword;" & _
"Command Logging=false"
oMySqlConn.Open()


Personal
The Personal Edition is for use by one developer on a small database. It does not support transactions,
connection pooling, command logging, or different character sets besides 'latin1'. Free!

Enterprise
The Enterprise Edition supports all features and is for use on one machine. $195

Site
The Site Edition is for use at a physical address, on an unlimited number of machines. It supports all of the
features of the Enterprise Edition. $495

[Dave Sell]

Can I use it to connect to a remote MySQL server? Or does MySQL have to be on my development machine?

[sixfeetsix]

Your db server can be on another machine, just change the server setting in the connectionstring to reach the other machine with the MySql server.

[techgnome]

I called MicroSoft and they say the message is misleading. They say that Standard edition does not allow you to connect using the visual tools, but that you should be able to do so through the code. So I am trying to connect using ODBC:

Imports Microsoft.Data.Odbc
Module Module1
Sub Main()
Dim MyConString As String
Dim myConnection As OdbcConnection

MyConString = "DRIVER=(People 3.51);SERVER=localhost;DATABASE=test;"
myConnection = New OdbcConnection(MyConString)
myConnection.Open()
End Sub
End Module

where People 3.51 is the DSN name. I get an error at the Open statement, but I am not sure why? So, I am still not sure what is going on. Does this code look correct?

That's because People 3.52 is the DSN name, not the name of the driver (Like Jet or MSSQL or MyODBC, or Oracle, etc)..... It should be as simple as changing the connection string to "DSN=People 3.51;"

Tg

[Dave Sell]

Your db server can be on another machine, just change the server setting in the connectionstring to reach the other machine with the MySql server.

From the local machine, I can access the MySQL database via a C# web application.

From the remote machine, running the same application, (with dbProvider installed), I get this error

Access denied for user ''@'%' to database 'csharpvqs'

If my connection string is this from both machines:

Data Source=localhost;uid=;pwd=;database=csharpvqs;

Note the blank uid and pwd. If I supply a uid and pwd I get this error:

Could not initiate communication with the server.

Any ideas why I cannot connect remotely?

Thanks!

Dave

[techgnome]

Data Source=localhost

Doesn't that mean that it's trying to find the db on the localmachine? Shouldn't that be the server name, or at least the ip of the remote machine?

Tg

[Dave Sell]

you are right, it is just a typo. The remote machine does not use localhost. It uses the name of the remote machine. Any other ideas?

[Dave Sell]

If the local machine uses "localhost" in the connect string it works from the local machine.

If the local machine uses "COMPUTERNAME" in the connect string I get the same error as the remote machine:

Access denied for user ''@'%' to database 'csharpvqs'

What is happenning? It seems to be forcing windows auth if I use "COMPUTERNAME" in the connect string...

[sixfeetsix]

Ok, first read the info provided on this site http://www.connectionstrings.com/ under the Mysql->MySqlConnection->eInfoDesigns.dbProvider section.

So according to that section, your connection string should look somewhat like this :

conn.connectionstring = "Data Source=<server's ip or fqdm >;Database=<database name>;User ID=<uname>;Password=<password>;Command Logging=false"

[Dave Sell]

I did exactly that and still the same error occurs. Remember, the connection string works from the local machine if Data Source = "localhost" but not if Data Source = "COMPUTERNAME" or "IP ADDRESS".

"localhost" is the only thing that works, and only from the same machine.

[sixfeetsix]

Sorry bout my last post, but now I understand your problem!

It looks like you did not define access rights for any user outside localhost.

Here is some doc from MySql on how to change your permissions in MySql to allow a user on compute B to access your MySql server on computer A :
http://dev.mysql.com/doc/mysql/en/re...onnection.html.

Note that when it says :

GRANT ALL ON *.* to 'myuser'@'B' IDENTIFIED BY 'mypassword';

you can allow a user to access your MySql server from anywhere by useing this query instead :

GRANT ALL ON *.* to 'myuser'@'%' IDENTIFIED BY 'mypassword';

Good luck!

[Dave Sell]

I will try this, but it should be noted that I am unable to create users in the database because I am a MySQL n00b. You think that will be a problem? Maybe I instaled it wrong, but the MySQL Admin program barfs when I try to add users saying I don't have persmissions...

From the included pics:

- MySQL_sa shows the error when I try to connect as "sa"
- MySQL_schema shoes the schema setup with the sa username
- MySQL_fetch shows that I can access the Admin program when connecting with blank uid and pwd, but it barfs when I look at usernames.

SHould I reinstall MySQL, or configure something, or just shoot myself?

[sixfeetsix]

sa in MySql is root.... try it out without a password if you made the installation without changing the root password.

What I posted earlier can be executed through the MySql client, it is a command prompt client you start this way : "mysql -uroot -p".

[pOfa]

I am using the dbProvider from eInfoDesign and whenever I try to do a test connectio after configuring the Connection String I get a "Lost connection to MySQL server during query" error. Any thoughts?

[jmcilhinney]

Just a note that, if I'm not mistaken, that connector is not free unless you accept the column count limitation. Below is a link to a free MySQL connector:

http://dev.mysql.com/downloads/connector/net/1.0.html

[pOfa]

Thanks man for the free MySQL connector link! Just a quick question for ya. How do I declare the connection with that free connector?

Thanks again.

[jmcilhinney]

I've never actually used it myself, but it follows the same namimg conventions as the standard ADO.NET namespaces I believe. You should be able to add the members to the Toolbox along with the SqlClient and OleDb eqivalents and then add them in the designer, or else add a reference to the assembly to your project and declare your objects in the code window. If I'm not mistaken, everything should be pretty much the same as the SqlClient namespace except that everything has a "My" on the front of the name, e.g. MySqlClient.MySqlConnection. The appropriate connection string is provided on www.connectionstrings.com.