Don't know how to connect to MySql

[colin shuker]

Hi there, I'm hoping someone can help me, I'm just going round in circles.

I have webhosting, my site url is www.ABC.co.uk for example.

I'm able to connect to the MySql on the server with php using:

mysql_connect("localhost", "USER_NAME", "PASS_WORD") or die(mysql_error());
mysql_select_db("colinjava") or die(mysql_error());

Note that the php page is on the same server.


Now what I'm trying to do is connect to MySql on the server from eclipse on my home pc.
I've added mysql-connector-java-5.1.7-bin.jar to my projects classpath in eclipse.
And I'm trying the code...

Connection conn = null;

try
{
    String userName = "USER_NAME";
    String password = "PASS_WORD";
    String url = "jdbc:mysql:http://www.ABC.co.uk/localhost/colinjava";

    Class.forName("com.mysql.jdbc.Driver").newInstance();
    conn = DriverManager.getConnection(url, userName, password);
    System.out.println("Database connection established");
}
catch (Exception e)
{
    System.out.println(e.getMessage());
    System.err.println("Cannot connect to database server");
}

But it never works, I'm pretty sure the url is wrong, but I don't know what to put.
I've checked a load of sites, but they just make it more confusing.

Can anyone help me fix this?
Thanks

[Bauke Scholtz]

What exactly happens? What exactly happens not?

You know, you should never ignore exceptions. Get them, read them, understand them and the solution would be obvious enough.

To start, better replace the sysout of exception's message by e.printStackTrace() or just a plain throw. This way you get the complete exception information, including at least the type, message and the stacktrace. This information should be self-explaining enough, otherwise Google can help you a lot with this by just the type and the message and if necessary the 1st line of the trace.

[colin shuker]

Thanks, it says

No suitable driver found for jdbc:mysql:http://www.colin-java.co.uk/localhost/colinjava
Cannot connect to database server

I can't figure it out just from that, I don't have a clue whats wrong, I added the jar file to the classpath.
At least I think I did it correctly.

[Bear Bibeault]

The classpath is ignored by servlet containers. Put the jar file in WEB-INF/lib like all others.

[colin shuker]

Thats true, thats what I intend doing when I try code in web application, but...

This code is in a normal java J2SE class with a main method, and it doesn't work.

Any ideas? What about port number in the url? Did I write the url correctly?

Thanks again

[Bauke Scholtz]

colin shuker wrote:Thanks, it says

No suitable driver found for jdbc:mysql:http://www.colin-java.co.uk/localhost/colinjava

This means that either the driver isn't loaded at all or that the JDBC URL is plain wrong.

As you are actually loading the driver and no exception is been thrown during the load, this part is fine. So your JDBC URL is plain wrong. Consult the documentation which come along with the JDBC driver in question for details how to construct a correct JDBC URL.

[colin shuker]

Thanks,

I've had partial success, and I can now access mySql that is intalled on my pc by using...

String url = "jdbc:mysql://127.0.0.1/colinjava";

I've looked at the read me in the driver thing it says:

The JDBC URL format for MySQL Connector/J is as follows, with
items in square brackets ([, ]) being optional:
jdbc:mysql://[host][,failoverhost...][:port]/[database] >>
[?propertyName1][=propertyValue1][&propertyName2][=propertyValue2]...

If the hostname is not specified, it defaults to 127.0.0.1.
If the port is not specified, it defaults to 3306, the
default port number for MySQL servers.

So I tried..

String url = "jdbc:mysql://ABC.co.uk/colinjava";

And this gives...

Communications link failure

Last packet sent to the server was 0 ms ago.
Cannot connect to database server

So I'm a little stuck from where to go from here, any ideas?

Thanks

[Bauke Scholtz]

colin shuker wrote:
Communications link failure

Either the hostname is wrong or there's a hardware/software firewall/router in between which blocks the connection.

So either try with just an IP address instead of hostname, or check the firewall settings.

[Kalyan Ram]

Call your host and find out if they permit remote connections to MySQL.

If you already have permission to make a remote connection, ask the host for the IP address and port of the mysql server machine. With those details try the following -

DriverManager.getConnection("jdbc:mysql://xxx.xxx.xxx.xxx:PORT_NUMBER/YOUR_DB_NAME?" + "user=USER_NAME&password=YOUR_PASSWORD")
xxx.xxx.xxx.xxx is the IP address

IMHO chapter 20.4. MySQL Connector/J will be very helpful.


regards
/k

[colin shuker]

I've figured it out.

I couldn't connect java app on my pc remotely to MySql on server, but
I can get a servlet uploaded to server to access it using...

String url = "jdbc:mysql://localhost/colinjava";

And I can get my pc to connect locally to MySql installed on pc using java application.

So that should be sufficient.

Thanks