[Logik22]

I have a QNap TS-410 NAS. I host a MySQL server off of it. It is connected to my router via Ethernet. I'm trying to write data to it in a program on my PC. I'm not sure what address to use to reference my DB so my program can connect to it.
DB Name: MPG
table name: mpgdata
URL to access phpMyAdmin: 192.168.1.22/phpMyAdmin
Once I'm logged in at the top of the page (not in the URL bar) it says 127.0.0.1

	//Establish database connection
	public void connectDB() {
   	Connection conn = null;
   	String address = "jdbc:msql://127.0.0.1/";
   	String name = "MPG";
   	String driver = "com.mysql.jdbc.Driver";
   	String username = "user";
   	String password = "user";
  	 
   	try {
       	Class.forName(driver).newInstance();
       	conn = DriverManager.getConnection(address+name, username, password);
       	try {
       		//Write data to table
       		Statement st = conn.createStatement();
       		int val = st.executeUpdate("Insert into mpgdata (dbDate, dbStation, dbOctane, dbMiles, dbGallons, dbMPG) values ("+strStation+","+strOctane+", "+strMiles+", "+strGallons+", "+strMPG+")");
       		JOptionPane.showMessageDialog(null, "Record has been added!");  
       	} catch (SQLException s) {
       		JOptionPane.showMessageDialog(null, "Data has NOT been written to table.");  
       	};
   	} catch (Exception e) {
       	JOptionPane.showMessageDialog(null, "You have received a Database Connection Error.");       	 
   	}
  	 
	}
	

When I try to run this it says "You have received a Database Connection Error.". My gut tells me it is the address/url of the database causing the issue because that's the only thing I'm really unsure of.

[Dogstopper]

I thought the driver string was:
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver")

You may also just use "localhost" as your adress, but you may or may not have to append the jdbc protocals.

[Logik22]

Dogstopper, on 01 July 2012 - 09:32 PM, said:

I thought the driver string was:
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver")

You may also just use "localhost" as your adress, but you may or may not have to append the jdbc protocals.

I still get the same error with the updated code. I'm using this tutorial as a reference to my code...
http://www.roseindia...qlConnect.shtml

It's from 2007 so I wouldn't be surprised if there's a different way about this. Thoughts?

	//Establish database connection
	public void connectDB() {
   	Connection conn = null;
   	String address = "jdbc:mysql://localhost:3306/";
   	String name = "MPG";
   	String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
   	String username = "root";
   	String password = "admin";
  	 
   	try {
       	Class.forName(driver).newInstance();
       	conn = DriverManager.getConnection(address+name, username, password);
       	try {
       		//Write data to table
       		Statement st = conn.createStatement();
       		int val = st.executeUpdate("Insert into mpgdata (dbDate, dbStation, dbOctane, dbMiles, dbGallons, dbMPG) values ("+strStation+","+strOctane+", "+strMiles+", "+strGallons+", "+strMPG+")");
       		JOptionPane.showMessageDialog(null, "Record has been added!");  
       	} catch (SQLException s) {
       		JOptionPane.showMessageDialog(null, "Data has NOT been written to table.");  
       	};
   	} catch (Exception e) {
       	JOptionPane.showMessageDialog(null, "You have received a Database Connection Error.");       	 
   	}
  	 
	}

[g00se]

a. That's not the driver class for MySql (it's the jdbc-odbc bridge)
b. You're using the wrong ip address. Check the docs for the correct connection string format for database 'X' on 192.168.1.22 and make sure the MySql server is listening on that address and that you have configured your account to be able to connect to it remotely

Don't use the root account - it's not good security practice

This post has been edited by g00se: 02 July 2012 - 01:12 AM
Reason for edit:: best practice

[Dogstopper]

Ok. I apologize. I got that Driver name from an older book that I was using at the time. I wasn't thinking at all about what it actually was doing. g00se has said it all.

By the way, don't use roseindia; it's FULL of horrible coding practices and hacks that only work one way. After I learned it was unreliable, I've never used it since.

[Logik22]

Dogstopper, on 02 July 2012 - 05:25 AM, said:

Ok. I apologize. I got that Driver name from an older book that I was using at the time. I wasn't thinking at all about what it actually was doing. g00se has said it all.

By the way, don't use roseindia; it's FULL of horrible coding practices and hacks that only work one way. After I learned it was unreliable, I've never used it since.

Shucks, I really like the layout of Roseindia. I like the real world examples. Most of tutorials/examples I use confuse me because I'm not sure what a Java keyword and what is a user specific word. Is there anywhere else that you can recommend that has a similar layout?

[Dogstopper]

Ummm? No. But Dream In Code has tons of great tutorials too and our layout is pretty sweet.

[Logik22]

Dogstopper, on 02 July 2012 - 05:34 AM, said:

Ummm? No. But Dream In Code has tons of great tutorials too and our layout is pretty sweet.

I do like Dream In Code but the more, the merrier.

[Logik22]

I'm still having some trouble and I'm hoping I can get some input on this.

I'm using the Dream In Code tutorial found here:
http://www.dreaminco...bc-odbc-bridge/

I'm on Step 4 under "Installing the Driver".
http://i112.photobuc...ging/dialog.jpg

I make up a data source name, input the IP (External IP), por 3306, correct user name and password, and then my Database ("MPG"). I get an error that says

Connection Failed: [HY000][MySQL][ODBC 5.1 Driver]Host 'Wireless_Broadband_Router' is not allowed to connect to this MySQL Server'

So it sounds like it can make a connection but it's denying it due to permissions. So does that mean it is trying to log in with something other than what I specified in the user and password field? If my logic is correct my computer is trying to access the QNap server so it goes to my routers external IP (specified in the TCP/IP Server field), which see's the port it is trying to go to and forwards the request to the MySQL server. So is the MySQL getting confused because it's trying to log in with the router? I can only assume 'Wireless_Broadband_Router' is referring to my Verizon router.

Any thoughts?

[g00se]

Quote

I'm using the Dream In Code tutorial found here:

Why you would want to connect (even if it is possible) to a MySql db with a jdbc-odbc bridge driver is beyond me. There are already perfectly good pure Java drivers available. Use the Connector/J for instance

Quote

... and that you have configured your account to be able to connect to it remotely

Looks like you didn't. Check the MySql docs (that's not a Java problem), in particular, the CREATE USER command.
I would advise you to ensure that you can connect using the standard mysql command line client before you start coding it in Java

This post has been edited by g00se: 04 July 2012 - 02:52 AM
Reason for edit:: create user

[Logik22]

g00se, on 04 July 2012 - 02:48 AM, said:

Quote

I'm using the Dream In Code tutorial found here:

Why you would want to connect (even if it is possible) to a MySql db with a jdbc-odbc bridge driver is beyond me. There are already perfectly good pure Java drivers available. Use the Connector/J for instance

Quote

... and that you have configured your account to be able to connect to it remotely

Looks like you didn't. Check the MySql docs (that's not a Java problem), in particular, the CREATE USER command.
I would advise you to ensure that you can connect using the standard mysql command line client before you start coding it in Java

Alright, I understand why it may be smarter to try to connect through a different method before trying to implement it into my code. Would using MySQL workbench be sufficient?



As far as the Connector/J method goes, does it look something like this:

http://docs.oracle.c...connecting.html
http://stackoverflow...sql-connector-j
http://www.stardevel...03090401&page=1

"The following is the database connection URL syntax for MySQL Connector/J:

jdbc:mysql://[host][,failoverhost...]
[:port]/[database]
[?propertyName1][=propertyValue1]
[&propertyName2][=propertyValue2]...

host:port is the host name and port number of the computer hosting your database. If not specified, the default values of host and port are 127.0.0.1 and 3306, respectively.
database is the name of the database to connect to. If not specified, a connection is made with no default database.
failover is the name of a standby database (MySQL Connector/J supports failover).
propertyName=propertyValue represents an optional, ampersand-separated list of properties. These attributes enable you to instruct MySQL Connector/J to perform various tasks.

This post has been edited by Logik22: 05 July 2012 - 10:46 AM

[g00se]

Yes, all that seems fine

[Logik22]

I think I was able to make the connection to the database. What I did:

-Made a new user, made sure they had rights to my MPG database
-Adding JDBC driver to Eclipse
-Right click on your project, select "Build Path" -> "Configure Build Path". Inside Libraries hit the tab to "Add External Jars".
-Browsed to my Connector/J download.
-http://dev.mysql.com/downloads/connector/j/
-I used "Connector/J 5.1.21"

I believe that is all.


My code looks like this:

    //Establish database connection
    public void connectDB() {
      Connection conn = null;
      String address = "jdbc:mysql://[b]<External IP Here>[/b]:[b]<Port>[/b]/";
      String name = "MPG";
      String driver = "com.mysql.jdbc.Driver";
      String username = "user";
      String password = "user";
      
      try {
          Class.forName(driver).newInstance();
          conn = DriverManager.getConnection(address+name, username, password);
          try {
              //Write data to table
              Statement st = conn.createStatement();
              int val = st.executeUpdate("Insert into mpgdata (dbDate, dbStation, dbOctane, dbMiles, dbGallons, dbMPG) values ("+strStation+","+strOctane+", "+strMiles+", "+strGallons+", "+strMPG+")");
              JOptionPane.showMessageDialog(null, "Record has been added!");  
          } catch (SQLException s) {
              JOptionPane.showMessageDialog(null, "Data has NOT been written to table.");  
          };
      } catch (Exception e) {
          JOptionPane.showMessageDialog(null, "You have received a Database Connection Error.");            
      }
      
    }

Now I get the prompt saying the data has not been added but at least that's a step further then I was getting. I didn't get a chance to troubleshoot that yet so we'll see where it ends up.

[g00se]

It won't get added - you're not closing your Statement and Connection. Using a PreparedStatement will make parameters easier and cleaner