I am having trouble using the SQL result set with JDBC

Question

Here is my code:

ResultSet res = con.query("USE VSM; SELECT max(EmailTime) FROM Emails;");
if( ! res.next()) { lastEmailTime = new Date(0); } // if table empty set last email time to 1970
else { lastEmailTime = SQLServer.sdf.parse(res.getString(1)); } //SQL is 1-indexed

My table is currently empty, and yet the code in the else statement is running. I know this because I'm getting a null pointer exception about it trying to access the res.getString(1). Why is it failing the if condition??

It looks very strange to have `USE VSM;` in there. Can you try removing it? — Tim Biegeleisen, Mar 17 '17 at 04:31
you can only execute one statement at a time. i don't think USE will work with JDBC. If you need to specify a default schema, you need to prefix your table names: SELECT max(EmailTime) FROM VSM.Emails — slipperyseal, Mar 17 '17 at 04:33
wow okay. how do I know it will be using the correct database though? — bitter_rice, Mar 17 '17 at 04:35
*how do I know it will be using the correct database* you can specify the DB on your connection url — Scary Wombat, Mar 17 '17 at 04:38
@ScaryWombat there is "database" and "schema". for some databases, the user/schema connection gives them default operations on their own schema, but selecting another schema happens with USE or table prefixing. (I thinking about Oracle here) — slipperyseal, Mar 17 '17 at 04:43
@SlipperySeal OP did ask *how do I know it will be using the correct database though* but good point about db vs schema — Scary Wombat, Mar 17 '17 at 04:45
Here is the query method: public ResultSet query(String sql) throws SQLException { return con.createStatement().executeQuery(sql); } — bitter_rice, Mar 17 '17 at 04:47
As the table is empty, an empty field can not be converted into a proper date — Scary Wombat, Mar 17 '17 at 04:52
see http://stackoverflow.com/questions/15475059/how-to-treat-max-of-empty-table-as-0-instead-of-null — Scary Wombat, Mar 17 '17 at 04:57

Rafael Lima · Answer 1 · 2017-03-17T04:55:14.560

-1

In the jdbc url connection is already defined which database are you using. You dont need to set the database every single request;

    ResultSet res = con.query("SELECT max(EmailTime) FROM Emails;");

if( ! res.next() || res.getString(1) == null) { lastEmailTime = new Date(0); } // if table empty set last email time to 1970
else { lastEmailTime = SQLServer.sdf.parse(res.getString(1)); } //SQL is 1-indexed

edited Mar 17 '17 at 04:55

answered Mar 17 '17 at 04:39

Rafael Lima

3,079
3
41
105

thanks for the help but still have the error – bitter_rice Mar 17 '17 at 04:41
can you run the same query in the database and post the result? – Rafael Lima Mar 17 '17 at 04:43
Exception in thread "main" java.lang.NullPointerException at java.text.SimpleDateFormat.parse(Unknown Source) at java.text.DateFormat.parse(Unknown Source) at EmailProcedure.(EmailProcedure.java:26) at MainWindow.(MainWindow.java:60) at MainWindow.main(MainWindow.java:128) – bitter_rice Mar 17 '17 at 04:44
I asked you to run the query on the database, not to print the stackTrace... open sqldeveloper, phpmyadmin or any other database management interface you have and run the same query you are trying to execute – Rafael Lima Mar 17 '17 at 04:45
Running on SQL Server Management Studio just gives "Command(s) completed successfully." with no actual data returned – bitter_rice Mar 17 '17 at 04:46
Yeah an empty field can not be converted into a proper date – Scary Wombat Mar 17 '17 at 04:52
The way the data is processed and handled in the ResultSet is defined by the Driver class you are using in your connection... I've never worked with SQLServer drivers so I dont know the implemention details, but maybe as you are using an agregate function it is waiting to always show only one result... in that case one row with null value... you can solve this situation just changing the if – Rafael Lima Mar 17 '17 at 04:54
But why didn't it trigger the if condition?? WHy is the else even running? – bitter_rice Mar 17 '17 at 04:54
Changing the if how? – bitter_rice Mar 17 '17 at 04:54

I am having trouble using the SQL result set with JDBC

1 Answers1