SO - I believe that's stackoverflow. This post appears to be a follow up of java - sqlq cannot be resolved to a variable - Stack Overflow
java.sql.SQLException: General error
doesn't say much. From the output trace it seems that the SQLException was thrown while executing ResultSet rs = st.executeQuery(sqlq);
. This corresponds to line 71 indicated by SearchBook.doPost(SearchBook.java:71)
in the stack trace.
There are a few things to check and try:
1. Manually execute (via command line or any other facilities MS Access provides) the SQL Select that is constructed below against the database to confirm that the SQL statement is fine.
String sqlq="select Section.Section_Name , Report.Report_Name , Report.Link, Contact.Contact_Name, Metrics.Metric_Name "
+ "FROM Section , Report , Contact,Metrics "
+ "WHERE Report.Contact_ID=Contact.Contact_ID and Report.Section_ID=Section.Section_ID "
+ "and Report.Report_ID IN (SELECT Metrics.Report_ID FROM Metrics WHERE Metrics.Metric_Name = Report.Report_ID') and Metrics.Metric_Segment = 'M' ";
2. The user
variables are empty strings. Is this intentional?
3. Call the close()
method for both the Statement and ResultSet objects once you have retrieved the result in the 1st try
block. See Processing SQL Statements with JDBC (The Java™ Tutorials > JDBC(TM) Database Access > JDBC Basics)
. (Note: This tutorial states, "When you are finished using a Statement, call the method Statement.close to immediately release the resources it is using. When you call this method, its ResultSet objects are closed." From my personal experience the automatic closing of the ResultSet object is dependent on the driver implementation. I know of at least 1 JDBC driver that doesn't close it as such, so it needs to be manually closed.)
I do not think that non-closing of the Statement and ResultSet objects can cause the problem here, but close them anyway as not closing them can lead to memory leak later on.
4. Remove Class.forName(driver);
in the 2nd try
block. The driver has already been loaded in the 1st try
block, so there's no need to do it again.
5. Remove con = DriverManager.getConnection(url, user, pass);
in the 2nd try
block. You can reuse the Connection object that was created earlier. In other words, the same Connection object can be used for multiple queries/updates.
6. Add better SQLException handling code. Review Handling SQLExceptions (The Java™ Tutorials > JDBC(TM) Database Access > JDBC Basics)
, and add handling code to get the SQLException's SQLState code, error code, cause, and reference to chained exceptions (if any). Model your code against the code provided in the tutorial.
After you've done the above, if the problem persists, hopefully the enhanced SQLException handling code from #6 will inform us of the cause of the problem.