Java won't send SQL query

[Zach Burnham]

I'm trying to have Java read some basic information from a MySQL database and echo it. I've searched online and as far as I can tell my code matches what I've found. However, the result is a null returnSet.

A couple of classes here:

import java.sql.*;
public class mySQLHandler {
String action, mySQLQuery, username, pass;
String[] result;
String url;
Connection con;
Statement stmt;
boolean loginSuccess;
Exception e;
//public mySQLHandler() {
//action = "read";
//}
public Connection openConnection (String username, String pass, String location, String database) {
boolean connectionSuccess = false;
//boolean loginSuccess = false;
loginSuccess = false;
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
System.out.println("Registered driver.");
connectionSuccess = true;
} catch (Exception e) {
System.out.println("An error occurred, could not register the driver");
e.printStackTrace();
//break;
}
if (connectionSuccess == true){
try {
Connection con = DriverManager.getConnection((location + database), username, pass);
loginSuccess = true;
} catch (Exception f) {
loginSuccess = false;
}
}
return con;
}
public boolean addData (String[][] data) {
try {
stmt = con.createStatement();
} catch (Exception e){
e.printStackTrace();
}
return true;
}
}

import java.sql.*;
public class mySQLHandlerTest {
public static void main (String[] args) {
String location = "jdbc:mysql://localhost:3306/";
String database = "junkdb";
//String url = "jdbc:mysql://localhost:3306/junkdb";
String username = "root";
String pass = "*****";
Connection con;
Statement st;
ResultSet rs = null;
mySQLHandler m = new mySQLHandler();
con = m.openConnection(username, pass, location , database);
if (m.loginSuccess == true) {
System.out.println("Hooray, it worked!");
} else {
System.out.println("Dammit.");
m.e.printStackTrace();
}
try {
st = con.createStatement();
rs = st.executeQuery("Select name from foo where 1");
//while(rs.next()) {
//String name = rs.getString(1);
//int param1 = rs.getInt(2);
//int param2 = rs.getInt(3);
//System.out.println(name + " " + param1 + " " + param2);
//}
System.out.println(rs);
} catch (Exception g) {
System.out.println(g.getMessage());
}
}
}

The output looks like this:

Registered driver.
Hooray, it worked!
null

The output from the mySQL console:

mysql>select * from foo;
+-------+--------+--------+
| name | param1 | param2 |
+-------+--------+--------+
| Jerry | 42 | 56 |
| Tom | 123 | 456 |
| Bugs | 321 | 654 |
+-------+--------+--------+

From the mySQL log:

060301 13:03:00 4 Connect root@localhost on junkdb
4 Query SET NAMES latin1
4 Query SET character_set_results = NULL
4 Query SHOW VARIABLES
4 Query SHOW COLLATION
4 Query SET autocommit=1

I don't get it. The query is good, and returns results on the console, but it looks like Java is never even sending the query; it sets some default values but never sends the query.

Anyone got a thought?

Z

[Ernest Friedman-Hill]

Maybe the "null" is the result of printing g.getMessage() in the last handler; exceptions do, indeed, have null messages sometimes, especially system ones like NullPointerException and ClassCastException. You've got a couple of event handlers that neglect to call ex.printStackTrace() -- change them so that they do, and I bet the results will give you a very strong hint about what to do next.

[Zach Burnham]

Ok so I changed the last handler (in mySQLHandlerTest) to look like this:

catch (Exception g) {
System.out.println(g.getMessage());
g.printStackTrace();
}

and now I get:

java.lang.NullPointerException at mySQLHandlerTest.main(mySQLHandlerTest.java:26)

Which leads me to conclude that 'con' is never made or the connection object isn't passed back to mySQLHandlerTest properly. I'd suspect the latter, since

if (connectionSuccess == true){
try {
Connection con = DriverManager.getConnection((location + database), username, pass);
loginSuccess = true;
} catch (Exception f) {
loginSuccess = false;
}
}
return con;
}

doesn't throw the exception. It looks like the Connection object 'con' isn't being passed back properly, but I'm not sure how to check.

Z

Edit: Whoops, looks like I never set up the boolean loginSuccess in mySQLHandler.java . Working on it now.

[ March 01, 2006: Message edited by: Zach Burnham ]

Edit 2: Uh. Well when you instantiate an object twice it gets grouchy. Especially when the method tries to return that object when it's out of its scope. So I edited line 36 of mySQLHandler.java to read

con = DriverManager.getConnection((location + database), username, pass);

instead of Connection con = etc

Hopefully someone will learn from my dunderhead newbie mistake
[ March 01, 2006: Message edited by: Zach Burnham ]

[Jeff Albertson]

Your error is a common oversight: in openConnection you define a local variable:

[B]Connection[B] con = DriverManager.getConnection((location + database), username, pass);

But at the end of the method, you return the null instance field:

return con; //a different con!

[Ernest Friedman-Hill]

 

Originally posted by Zach Burnham:
It looks like the Connection object 'con' isn't being passed back properly, but I'm not sure how to check.

Now that you mention it, I see one classic mistake that everyone makes once, but only once: there are two "Connection con" variables in the mysqlHandler class. One is a member variable, and one is a local in openConnection. The local is declared inside the try block, which means its scope is limited to the try block. The one you return from the routine is the member -- which is always null.

To fix this, turn the "con" inside that try block from a declaration into a simple assignment -- remove the word "Connection", so that "con" there refers to the member, not a new local variable.

Make sense?

[Zach Burnham]

Yes. See above

[Vlado Zajac]

Unrelated to your error:

1. Using exceptions to handle errors would be better that error code or boolean success/fail variables (loginSuccess).
2. You should close the connection, restult set and statement (using finally).