infra

I've figured it out.It turns out that the resultSet will be lost if the connection is closed prematurely.

Hi,I'm creating a simple class that queries an SQL database and returns a resultSet. I'm testing the class with another java file with a main() method so I can actually create the object from my querydb class and attempt to access the resultSet.The problem is, when I try accessing the resultSet in main(), it's looking like the result set is empty. But I know for a fact that it's retrieving the records because I put a (while result.next()){ //output column_name} in the method that actually executes the SQL statement (which works in SQL btw) and I can see all the results. However, if I try to do the same in main(), it simply skips the while loop as if there are no records in the ResultSet.Here is the code of my querydb class: /*package querydb;import java.sql.*;public class Querydb { //Class variables Connection conn = null; //Connection object for MySQL connection ResultSet queryResults; //SQL results //Constructor //Query DB for events within a specific primary and secondary category public Querydb(String event_Category_Primary, String event_Category_Secondary){ getConnection(); executeStatement(event_Category_Primary, event_Category_Secondary); terminateConnection(); } //Pepares SQL statement based on arguments received by constructor //Events within a specific primary & secondary category are returned public void executeStatement(String event_Category_Primary, String event_Category_Secondary){ try{ //Prepare and execute statement for SQL query System.out.println("QUERYDB: Executing query for all elements under: " + event_Category_Primary + " | " + event_Category_Secondary); Statement stmt = conn.createStatement(); queryResults = stmt.executeQuery("SELECT * FROM mydb.events " + "WHERE event_Category_Primary = '" + event_Category_Primary + "' AND event_Category_Secondary = '" + event_Category_Secondary + "' " + "ORDER BY CASE " + "WHEN event_startYearSuffix = 'MYA' THEN 1000000 * event_Start " + "WHEN event_startYearSuffix = 'AD' THEN -1 * event_Start " + "ELSE event_Start END;"); //THIS WORKS - I CAN SEE ALL RESULTS AT THIS POINT IN queryResults while (queryResults.next()){ System.out.println("QUERYDB: event_Name = " + queryResults.getString("event_Name")); } } catch (Exception e) { System.out.println("QUERYDB: Cannot create SQL Statement: " + e); } } //Initiates DB connection with MySQL server public void getConnection(){ System.out.println("QUERYDB: Connecting to DB..."); try { String userName = "test"; String password = "test"; String url = "jdbc:mysql://localhost:3360/mydb"; Class.forName("com.mysql.jdbc.Driver"); conn = DriverManager.getConnection(url, userName, password); System.out.println("QUERYDB: Database connection established!"); } catch (Exception e) { System.err.println("QUERYDB: Cannot connect to database"); System.out.println(e); } } //Terminates the MySQL DB connection public void terminateConnection(){ //Attempt to terminate the connection try{ System.out.println("QUERYDB: Closing DB connection..."); conn.close(); } catch (Exception e){ System.out.println(e); } } } This is the main() java file that I use to try to access the resultSet package querydb;import java.sql.*;public class testInterface { public static void main(String[] args) throws SQLException{ System.out.println("TestInterface: Creating Querydb ojbect..."); Querydb qdbObj = new Querydb("What", "Arts"); String s = null; try{ //DOES NOT WORK - I CANNOT SEE THE RESULTS while(qdbObj.queryResults.next()){ System.out.println("If you can read this, queryResults is not null"); } } catch (SQLException e){} }} Again, the SQL query works and I've triple checked the prepared select statement in my querydb class. It seems to work within the querydb class only within that method. It's as if once it leaves the method, the resultSet disappears.Any ideas? This is definitely some lousy programming on my part, but any help is appreciated :)Thank you.

Hi guys,First time poster.I'm a little,... well very rusty, with my SQL. Figured you experts out there would have an idea as to how to solve my problem here.Little background on the database:-It's a historical database with events and people from time; each record has a name, description, a start year, an end year, startYear suffix*, endYear suffix**Suffix = BC or AD or MYACorresponding with the above information, here is an example record:Example#1:John Doe | Famous Musician born in the 19th century | 1839 | 1910 | AD | ADExample2:Roman Period | The Roman Empire | 63 | 476 | BC | ADExample 3:Stone Age | The Human Era | 5 | 2500 | MYA | BCThe problem:Now, this is simple enough, but I have BC and MYA dates as well. So there could technically be an 1839 AD as well as an 1839 BC date or even an 1839 MYA year.Figuring out which record to return (the AD, BC, or MYA) is not a problem by itself, but when I have to return a set of records say from the year 2000MYA to 2000AD, you can see that things might not be so clean cut if I simply order the records via ORDER BY ASC/DESC in relation to the starting year of the record.I'm hoping there is a way to do "two" or "three" selects and combine the results where if I searched from 2000MYA to 2000AD, the first part would return all records which years are from a MYA era, in this case 2000MYA, ordered in an ascending fashion all the way to 0.Then, combine the results with another search that selects all records greater than the year 0 (BC dates in this case) ordered in an descending fashion then finally list the AD dates in an ascending manner.You'd get records that show years like such:2000 MYA, 1500 MYA, 500 MYA, 30 MYA, 5 BC, 350 BC, 2000 BC, 1500 BC, 10 BC, 15AD, 1500 AD, 1993 AD, 2009 ADSo in SQL terms, the statement would look like follows (of course this statement does NOT work, but you get the idea of what I'm trying to do):select * from tablename WHERE startYearSuffix = 'MYA' ORDER BY event_Start DESCUNIONselect * from tablename WHERE startYearSuffix = 'BC' ORDER BY event_Start DESCUNIONselect * from tablename WHERE startYearSuffix = 'AD' ORDER BY event_Start ASC;Thanks a lot for the help.