[RESOLVED] adodb mysql connection troubles

Hello. I am working on a project where I need to connect to a mySQL server from vb6.

I have written a php web-app for my client which has no troubles accessing the database --
I have also written several database apps with vb using data from servers that I have put together myself. BUT I can't seem to figure out the connection string (or whatever else is missing) to connect to the same database that I can access easily with php.


Below is the vb code that I am trying to connect with, followed by the php I use to access the same server. I've also provided a copy of the server info to confirm the sql version and driver compatibility)


It errors with: "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified." on the line con1.open cst


Could someone point me in the right direction?

The error message seems to me its saying two different things. ..But which one is causing the issue? I've specified a default driver (it may be incorrect, but it's specified :p ) And I'm not sure what else I would use for a data source name. Could it be having an issue with the fact that the server is routed through port 805? Is it possible there are missing drivers/software on the server that I would need to get installed? Should I be using a different method to connect from vb? Maybe it doesn't like the blank password? ... But it's complaining about the default driver and data source name... Sounds to me like its having connection issues :s

Thanks in advance for any input.


===== VB6 CODE =======
Dim con1 As New ADODB.Connection

cst = "Driver={mySQL ODBC 5.1 Driver};Data Source=xxx.xxx.xxx.xxx;Port=805; Database=db_name;User=dbusr01; Password=;"

con1.open cst



===== PHP ======
<?
$DBSERVER="xxx.xxx.xxx.xxx:805"; // database server
$DBUSER="dbusr01"; // database user
$DBPASS=""; // database password
$DBNAME="db_name"; // database name

$con_trucking = mysql_connect($DBSERVER, $DBUSER, $DBPASS) or die ("Error #721");

mysql_select_db($DBNAME, $con_trucking);
?>



===== SERVER INFO =====

MySQL

* Server: localhost via TCP/IP
* Server version: 5.1.33-community
* Protocol version: 10
* User: root@localhost
* MySQL charset: UTF-8 Unicode (utf8)

Web server

* Apache/2.2.11 (Win32) DAV/2 mod_ssl/2.2.11 OpenSSL/0.9.8i PHP/5.2.9
* MySQL client version: 5.0.51a
* PHP extension: mysql

http://www.mysql.com/products/connector/

Quote:

---

Originally Posted by DataMiser

http://www.mysql.com/products/connector/

---

Thanks for your reply, but it wasn't much help.
(how much effort have you put into your 3000+ other posts??)

I am assuming you meant for me to download and install the latest drivers for my local machine?

I just did that and I am getting the same error. It doesn't make sense to me that I would have to install these on the server.

Perhaps sharing some wisdom instead of a link would be a bit more helpful,

Thanks.

The ODBC connector needs to be on the machine where the program is executing. If the code is executing on the server the the connector must be installed on the server.

The link I gave you is to MySql.Com which contains pretty anything you would ever need to know about MySql.

The error Datasource not found happens when you either do not have the correct driver installed or possibly if you provide an incorrect or inaccessible server name/address.

As for effort in my posts I find that a bit less than polite and far from a good way to ask for help. You should be able to solve your issue with the link I provided and a little effort on your part.

Quote:

---

Originally Posted by DataMiser

...or possibly if you provide an incorrect or inaccessible server name/address.

---

That is what I thought....which is why I posted the php I am using and a detail of the server versions.

Maybe I have the syntax wrong in my connection string and something is tripping up?

What kind of things would make the server name or address inaccessible? Do I need an additional field in my connection string to define something further? Could it have to do with the port routing?

Data Source=xxx.xxx.xxx.xxx

Data Source is then name of a DSN setup in the ODBC connections for your computer.

Also, port 805??? According to you php code, the default port setting for mysql was used, so the port would be 3306.

First try getting things working through ODBC connection. If you can do that, you should be ok for the application.

Quote:

---

Originally Posted by sotoasty

Data Source=xxx.xxx.xxx.xxx

Also, port 805??? According to you php code, the default port setting for mysql was used, so the port would be 3306.

---

Doesn't this line indicate to use port 805? Im pretty sure it gave me errors when I didn't specify a port: $DBSERVER="xxx.xxx.xxx.xxx:805"; // database server

I've tried in vb to use "xxx.xxx.xxx.xxx:805" as the data source, as well as no port identification. Also tried the connection string with and without the port definition parameter.

I've seen some implementations of the odbc connection string and they use a "server:xxx;" and an "address:xxx" parameter. There's obviously a difference between the two? or would this just be for different versions of the odbc connector? Could I be missing something like this?

I didn't see that in the PHP code, sorry. But your error is probably in the

Data Source=xxx.xxx.xxx.xxx


Data Source should be your DSN name.

Browse around www.connectionstrings.com

Quote:

---

Originally Posted by sotoasty

Data Source=xxx.xxx.xxx.xxx

First try getting things working through ODBC connection. If you can do that, you should be ok for the application.

---

I've tried on an XP and win7, but neither will successfully pass the "test" button. Not only do they not pass, they make the mysql software stop responding (5 minutes, before I stopped the process).

If I put xxx.xxx.xxx.xxx:805 for the server, it errors instantly. So I'm keeping the 805 where it should be, in the port input field.

My drivers appear to be current and working fine, as I can connect to other servers with no issue.

It seems to me I am missing some sort of credentials in the login? or maybe the server has a weird configuration restricting port access? what else should I be considering?

Could it maybe not like the username? or the blank password? -- I figured there would be a different error message if this was the case though.

Ask the administrator to provide valid credentials. Pretty much impossible to guess with partial info.

Also, take a look:

http://paulbradley.tv/37/

Quote:

---

Originally Posted by dglienna

Ask the administrator to provide valid credentials. Pretty much impossible to guess with partial info.

---

I'm not trying to guess. I'm trying to establish the reason as to why I am having this issue.

I tried connecting again following the code from the link you provided (which was pretty much the same as my many other attempts).... BUT it still timesout and stops responding.

Sure, I could ask the admin for "valid credentials"... but as far he and I are concerned, I shouldn't need anything more from him. If I am going to ask for "better credentials" I would at least like to know what I am asking for. I have root access to phpadmin and can create new users and databases for the sql server. I don't know what else he would be able to provide that would be able to help.

...what would make the mySql software stop responding and make vb stop responding and crash when I try to connect? I didn't have this issue with connections from other projects I have built.

Well, just a maybe stupid question:
Is that

Code:

---

cst = "Driver={mySQL ODBC 5.1 Driver};Data Source=xxx.xxx.xxx.xxx;Port=805; Database=db_name";User=dbusr01; Password=;"

---

the actual statement, or did you just mistype it here in the forum?
Please follow the flow of the quotes and there is qite oviously an error.
I only guess, that db_name is a variable holding the actual name of the database. So a properly concatenated open-string would be:

Code:

---

cst = "Driver={mySQL ODBC 5.1 Driver};Data Source=xxx.xxx.xxx.xxx;Port=805; Database=" & db_name & ";User=dbusr01; Password=;"

---

Quote:

---

Originally Posted by WoF

Well, just a maybe stupid question:
Is that the actual statement, or did you just mistype it here in the forum?

---

Hi, thanks for actually taking the time to read what I posted :)

Yes, it was a typo just on the forum post.

In my actual application I am using a variable for the db_name, but for now I am just trying to get a connection to work with pretty much a blank form and a command button in vb (to narrow down possible issues).

I can't establish a connection through the mySql connector software either. - It just 'stops responding'. I'm pretty sure I don't have any typos when filling in the required input fields.

It feels like I'm missing a piece of the equation, but I don't know what it is...

Are you sure you have access to connect to the database from a remote machine?

Back now.

It appears that you probably do not have the rights to access this db remotely. Try downloading the mysql workbench from the mysql site, and setting up your connection in that. If you can connect with that, you can connect through ODBC. If you can't your need to grant privileges on your user to access the MySQL server remotely. I am not sure how to do it through PHPAdmin, so you might have to google that. If you have direct access to the server, see here.

http://dev.mysql.com/doc/refman/5.0/en/grant.html

I think I have the privileges setup accordingly... I've been able to connect to the database just fine with the php web-app running on servers at various locations (my testing server as well, an off-site mirror as well as their in-house server)



==== mySql User Privileges Table =====
User Host Password Global privileges 1 Grant

Any % No USAGE No
Any localhost No USAGE No
pma localhost No USAGE No
root 127.0.0.1 Yes ALL PRIVILEGES Yes
root localhost Yes ALL PRIVILEGES Yes
dbusr01 % No ALL PRIVILEGES Yes
dbusr01 localhost No ALL PRIVILEGES Yes

I've also tried setting up a password for dbusr01, and it still doesn't want to work.

I installed the mySql workbench, and it still isn't connecting and doesn't offer any better error message and just stops responding.

I'm starting to agree that the server may not be properly configured for remote access? (but why would it work from php?)

Maybe I should give up for now and talk with the guy who setup the server to see if he can figure it out.

dbusr01 &#37; No ALL PRIVILEGES Yes

This should mean you can connect from anywhere. On your local system, do you have a firewall on that could be blocking outgoing ports?

Are all the servers that can connect on a local LAN or from anywhere. Is your system on the local lan? What is between your system and the MySQL database? Could something there be blocking access?

I think this is probably more of a networking/firewall issue now. Try this

Open a command prompt on your system.

Type in

telnet xxx.xxx.xxx.xxx 805

Do you get anything back? If so you are talking to the mysql computer and it is not a networking issue it is the setup in MySQL. If not, somthing is blocking you from talking to the mysql computer.

Quote:

---

Originally Posted by sotoasty

On your local system, do you have a firewall on that could be blocking outgoing ports?

---

-- No. I don't run anything on this machine (xp) except for an outdated version of avast. But I've tried from my windows 7 machine too, and same deal.

Quote:

---

Originally Posted by sotoasty

Are all the servers that can connect on a local LAN or from anywhere. Is your system on the local lan? What is between your system and the MySQL database? Could something there be blocking access?

---

The computers that can connect with php are in various cites in the clients offices. I am fairly certain they are not setup on a local LAN. I am 600km away connecting as I usually do through the internet.

Quote:

---

Originally Posted by sotoasty

Type in

telnet xxx.xxx.xxx.xxx 805

Do you get anything back?

---

From telnet at the command prompt, I see it quickly flash "connection successful" and then just a blank screen. I tried using the HyperTerminal gui, and it also seems to connect. Are there any command in particular I should be trying to send?

When I type "exit" (or even "hello") and hit return from the command prompt version, i get the following:

===============

<br />
<b>Notice</b>: Undefined index: HTTP_HOST in <b>C:\xampp\htdocs\index.ph
p</b> on line <b>7</b><br />
<br />
<b>Warning</b>: Cannot modify header informat
ion - headers already sent by (output started at C:\xampp\htdocs\index.php:7) in
<b>C:\xampp\htdocs\index.php</b> on line <b>8</b><br />


Connection to host lost.

D:\USERS\SCOTTS~1>
================

That's a php error. It usually has to do with whitespace before initializing a new session or a header() command after output. The index page it's complaining about though is never used -- we use one in a deeper directory as the main index for the webapp. Just the same, I've renamed the existing index.php, and replaced it with a file containing <?php echo "hello"; ?>. Odly, however, I still get the same error message (I did the update via ftp - it should have had an immediate effect, right?)... so how is it still finding and reading this file? And how could this have anything to do with establishing an odbc connection to the mySql database?

It means the problem is likely in the driver. Telnet can connect, as the message told you. You can't really do anything else on that port...

To connect to a mysql server what you should get is something like...

Connected to 192.168.2.11
Escape character is '^]'.
4
5.0.77

What you are seeing is a connection to some sort of web server. My next question is what happens if you telnet to the same server at port 3306?

To re-cap, it is not your odbc setup, at least at this point, it is finding what port your MySQL server is on and how to access it.

ETA:

So if you have access to the phpadmin, does it show you the config file for mysql?? If so, could you post that, or possible check to maybe the PORT that mysql is running on?

Here is my configuration file....

Code:

---

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0
skip-host-cache
skip-name-resolve

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

---

As you can tell it is very basic, with most things set up by default.

Quote:

---

Originally Posted by sotoasty

...is what happens if you telnet to the same server at port 3306?

---

it takes a very long time, then finally returns with an error saying "...Could not open connection to the host, on port 3306: Connect failed"

Quote:

---

Originally Posted by sotoasty

So if you have access to the phpadmin, does it show you the config file for mysql??

---

I've looked all through php with no luck. I searched as high as I can go in the directory structure from ftp access as well, with no luck.

I have sent an email to the server admin asking him to have a look at the config file for me. I agree that it really sounds like a port problem. That's kind of the direction I was leaning in from the start, but I wanted to be sure of it before asking. Hopefully I will hear back from him early next week and I will let you know what we find out... thanks for the input so far :)

Try this: https://www.grc.com/id/idserve.htm

Hello to anyone who might come across this thread as well as the people that contributed to this point...

This is what the server administrator replied back to me with:
=========
Among other things, I deleted all of my port forwarding and binding and started from scratch but that didn't make a difference. Looked at the permissions for the database, and everything looked good there as well. Finally found that I had to make changes to the mysql.ini file, specifically specify tcp/ip there and bind an IP address there as well.
=========

So things are finally back to normal on my end.... :D Problem solved! :D