CONFUSED: ADO Connection String not working... 3

[MissouriTiger]

DETAILS:
VB6 program running on Win2000 client in Missouri, US. Trying to connect to mySQL DB running on Unix server in Canada, via ADO. (See further details below.)

I have read everything available here on Tek-tips, plus books, and several web articles, plus mySQL documentation, but these various sources disagree widely on which fields need to be included in my connection string. I have tried many, many combinations, but nothing works. I just cannot get this connection to work. I obviously fail to understand some fundamental thing about these connections. WOULD SOMEBODY PLEASE CLARIFY THIS FOR ME?

DB DETAILS:
Location:

http://gregnorris.phpwebhosting.com

Server Name: kane.phpwebhosting.com
DB Version: MySQL 4.0.18 - "standard running on -
localhost as gregnorris@localhost"
Port: 3306
DB Name: gregnorris
Username: gregnorris
Password: ABC 'not the real pasword

(The name of my DB is the same as my username, which is also the same as my domain.)

Various articles conflict a great deal regarding which fields need to be in the connection String. Plus all of them are generic, with junk like this "Provider=myProv; DataSource=myDataSource". What kind of help is that???

Here is the conneciton string I'm currently trying:

strConn = "DRIVER={MySQL ODBC 4.0.18 Driver};" & _
"SERVER=

http://kane.phpwebhosting.com;"

& _
"Port=3306;" & _
"DATABASE=gregnorris;" & _
"UID=gregnorris;" & _
"PWD=winner;"

I get the following error message:

"[MICROSOFT][ODBC Driver Manager]Data source name not found and no default driver specified."

Other connection string configurations I've tried have included fields such as "RemoteProvider", "Provider",
"DataSource", "remoteServer", "UserID", etc, but nothing has worked.

Based on the details above, could someone please put together the right connection string for me (except for the proper password)?





_______________________________________
constructed from 100% recycled electrons

 

[ingresman]

I put this together and it works

sConnect ="DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;PORT=3306;DATABASE=tanc;USER=;PASSWORD=;OPTION=3;"
sSQL = "select count(*) as cnt from staff"

set confs=createobject("adodb.recordset")
confs.open sSQL, sConnect
do while not confs.eof
 wscript.echo confs("cnt") 
 confs.movenext
loop
confs.close

I used

http://www.connectionstrings.com

for the base info
Create yourself a file (fred.vbs the .vbs is important), paste this code into and customise as appropriate.
From the cmd prompt CD to where the script is and type
cscript fred.vbs
at least you will see if it will work at all for you
Hope it works out for you

 

[dalchri]

Yeah, I agree that isn't the text I use for the driver. Go to your control panel, Administrative tools, Data Sources (ODBC). Under the driver tab, use the exact text that you see listed for the MySQL ODBC driver.

 

[MissouriTiger]

Forgive me for my ignorance, I am still learning, but it appears that your code is for a client-side DB. Am I correct? Doesn't my connection string require the following fields in order to connect to the remote server:

"RemoteProvider", "Provider", "remoteServer",

Why do I need a mySQL driver on the client if the DB is running on a remote server (Unix)? Is this really necessary? Like I said, I really don't understand ADO works.

One last thing, my username and password are shown above in myh original post. Everything is there. Can you connect to my DB and retrieve records?

BTW, this is an exercise for a job interview, and I am just about out of time. I hope I can fake it!


_______________________________________
constructed from 100% recycled electrons

 

[dalchri]

The server is actually running (or could be running) on a separate machine. The SERVER= tells you what machine that would be as this should be the IP address or a dns resolvable name. The reason that there is a driver on the client is so the client knows how to "talk" to the database.

That ought to dispose of the RemoteProvider and remoteServer.

The reason that there is no provider listed in that by default ADO uses Provider=MSDASQL (this is ODBC not SQL server) as the default provider if none is listed.

I would not be able to connect to this database unless I knew the public Internet IP address or DNS name. You have this listed as localhost in your connection string which would be your own computer.

 

[ingresman]

I just downloaed the driver and i get catastrophic failure messages- very odd.
kane.phpwebhosting.com is not you r domain, thats gregnorris.net and if I were you Id change my password very quickly !

 

[MissouriTiger]

I really appreciate all the help, but it still isn't working.

Well, it comes as a big help to know that I actually needed a client-side driver. I mistakenly thought ODBC enabled disparate clients & DB-servers to communicate without need for any special drivers or programs. Too bad this isn't the case.

I have installed the driver, but it doesn't appear to change anything. When I run the following connection string, I get the error below:

strConnString = "Provider=MS Remote;" & _
"Server=

http://www.gregnorris.net;"

& _
"Port=3306;" & _
"DRIVER={MySQL ODBC 3.51 Driver};" & _
"DataSource=gregnorris;" & _
"USER=gregnorris;" & _
"PASSWORD=****;"

ERROR MESSAGE:

"RDS Server Error: The server has denied access to the default RDS Handler used to access this page. See the server Administrator for more information about server security settings."

This is an error I've encountered before. Researching this error hasn't revealed anything helpful. As I've said before, I've tried every conceivable connection string before, including the one I'm trying now. Adding the driver doesn't seem to change a thing.

I never had any problem connecting to that DB with Perl scripts residing on the server.

Any suggestions?

BTW, I changed my password.





_______________________________________
constructed from 100% recycled electrons

 

[ingresman]

Take out the provider=ms remote and put back in the mysql stuff as shown in the example.
If you need a quick outline of ODBC and database commumication drop back and I'll put some notes together for you

 

[dalchri]

Also keep in mind your security setup. Just try to create a System DSN first in the Data Sources (ODBC) under the control panel, administrative tools first. This will allow you to test your connection using ODBC without the complication of the connection string.

When I tested your database this way I got access denied. This indicates that your database and the ODBC driver are working properly but the connection was refused because of my credentials.

If you are using MySQL 4.1, you have to take special steps to get it to work with older clients like ODBC. You will have to store your passwords like this because the new version uses a longer encryption on the passwords:

UPDATE mysql.user
SET Password = OLD_PASSWORD('MyPassword')
WHERE User = 'MyUser';
FLUSH PRIVILEGES;

There is also a command line option that will force 4.1 to act like the previous versions.

Another problem may be that your username is not valid over all domains. For example, if your username is gregnorris@localhost, you won't be able to log on from a different computer from your database server, for example gregnorris@mydevelopmentcomputer. You can check this by looking at the host field in the user table under the mysql database. You can use wildcards like % to enable access from all hosts.

 

[ingresman]

When I tested it out I could connect directly to gregs databases via mysql tools (he's changed his password since then).
Odd thing I got was a "catastrohoc error" message from the ODBC whether I went to his server or my own.

 

[MissouriTiger]

I just want to thank you both for all the help. This whole thing just turned out to be more complicated than expected.

I gave you both stars. Have a great weekend!

_______________________________________
constructed from 100% recycled electrons

 

[TomTT]

I've just started working with MYSQL and have it installed on my personal machine. I can create databases and populate using MySQLyog.

Where I am running up against a wall is with getting a connection string put together to connect to the database from an application.

I'm used to using Access and SQL Server as the back end so strings like:
Dim Conn as new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & Server.MapPath("DB/BookOrders.mdb"))

make sense to me.

I try something like this with MySQL and I keep getting messages like:

The 'MySQL.ODBC.3.51. Driver' provider is not registered on the local machine.

I downloaded the driver and installed it and checked it in the Data Sources of the Admin Tools in the Control Panel. I have configured MySQLConn in the User DSN, System DSN and File DSN but using this DSN name doesn't work any better than naming the driver.

Obviously, I'm missing something (probably simple). I checked the FAQ on this site and have searched the posts and tried some of the suggestions. So far, I'm still not connecting to my DB.

Sugestions "Greatly" Appreciated

Thanks!

 

[ingresman]

try

sConnect ="DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;PORT=3306;DATABASE=tanc;USER=;PASSWORD=;OPTION=3;"
sSQL = "select count(*) as cnt from staff"

set confs=createobject("adodb.recordset")
confs.open sSQL, sConnect
do while not confs.eof
 wscript.echo confs("cnt") 
 confs.movenext
loop
confs.close

Inserting the values specific to you database.
In your post you had full-stops(dots,period) in the name of the driver, take them out. In any connect string you provide the name of the driver that got registered, look in the drivers tab in the adminsistrator tool. I guess the registry is searched for that value.
The code I've posted does work, try that and come back with errors/problems

 

[TomTT]

Ingresman,

Thanks!!! for taking the time to re-post your instructions. You are, of course, correct. I was a bit (or a lot) dense yesterday when I was working on this. It was right there all along "ODBC".

I just took an existing ASP.Net page and used it as a starting point for a Q&D attempt at connecting with the mySQL database......Big mistake.

The page imported everything needed, including
"Imports System.Data.oleDB" (oops!!). I never noticed it.

24 hour later, it finally sunk in.

I imported the correct namespace "Imports System.Data.ODBC" and Wa-La!! Good connection and I can query the database.

Thanks again for the help. Sometimes it helps to just walk away for a while.

Tom T