Solved

Any idea why this simple query is not working?

Posted on 2006-07-17
11
234 Views
Last Modified: 2010-03-31
In this query I am trying to capture the j_username from the authentication, then match it up with the VZID in the database table, and then list the USER_NAME from the table.

Any idea why this is not working?
I am not getting any errors, except the common 500 error from the browser.

<%
String username1 = (String)session.getAttribute("j_username");
String username2 = username1.trim();
%>


<%
try
{
javax.sql.DataSource dss;
java.sql.Connection conn = null;
Context icc = new InitialContext();
dss = (javax.sql.DataSource) icc.lookup("java:comp/env/jdbc/mydb2");
conn = dss.getConnection();
conn.setAutoCommit(false);
  java.sql.Statement stat = conn.createStatement();
  java.sql.ResultSet cols = stat.executeQuery("SELECT * FROM CBSTDTFI.CS_USER_INFO WHERE VZID = '"+username2+"' FOR FETCH ONLY WITH UR ");
 while(cols.next()) {
String USER_NAME  = cols.getString("USER_NAME");
%>

<%= USER_NAME %>, <%= username2 %>
<% } // close while loop %>
 
<%
cols.close();
stat.close();
conn.close();
 
} catch (SQLException sqle) { sqle.printStackTrace(); }
%>
0
Comment
Question by:g118481
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 23

Expert Comment

by:Ajay-Singh
ID: 17127400
> } catch (SQLException sqle) { sqle.printStackTrace(); }
are you sure this statement is not printing anything (checkout catalina.out)
0
 
LVL 30

Expert Comment

by:Mayank S
ID: 17128466
BTW you should be closing everything in a finally block, not in the try block. You're probably also running out of available connections because the old ones are not closed.
0
 
LVL 1

Author Comment

by:g118481
ID: 17129325
Can you give an example of how the finally block should be set into my code above?
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 35

Expert Comment

by:girionis
ID: 17129331
> catch (SQLException sqle) { sqle.printStackTrace(); }
%>


catch (SQLException sqle) { sqle.printStackTrace(); }
finally
{
   ... do your stuff here
}
%>
0
 
LVL 1

Author Comment

by:g118481
ID: 17129451
> {
   ... do your stuff here
}


What stuff here?  I asume I should put a print writer for the error message.
Can you give an example that will work with this code?

Cheers
0
 
LVL 35

Accepted Solution

by:
girionis earned 250 total points
ID: 17129487
You should do final cleaning up in there, like

catch (SQLException sqle) { sqle.printStackTrace(); }
finally
{
   try
   {
     // The connection object
     if (con != null)
     {
        con.close()
     }
     // Statement object
     if (statement != null)
     {
         statement.close();
     }
   }
   catch (Exception e)
  {
      e.printStatckTrace();
  }
}
0
 
LVL 1

Author Comment

by:g118481
ID: 17129573
I added your last suggestion, but get "JavaCompile:  con cannot be resolved."
I get the same compile message for "statement"
0
 
LVL 35

Expert Comment

by:girionis
ID: 17129652
> I added your last suggestion, but get "JavaCompile:  con cannot be resolved."
> I get the same compile message for "statement"

These should be your *own* connection and statement objects. "con" and "statement" are just examples, you should replace them with your own variables.

0
 
LVL 35

Expert Comment

by:girionis
ID: 17129655
Use "conn" and "stat" instead.
0
 
LVL 30

Expert Comment

by:Mayank S
ID: 17129657
You need to declare them outside the try block so that they are accessible in the finally block as well. You can initialize them as null.
0
 
LVL 30

Expert Comment

by:Mayank S
ID: 17136268
:-) no points for suggestions?
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Java had always been an easily readable and understandable language.  Some relatively recent changes in the language seem to be changing this pretty fast, and anyone that had not seen any Java code for the last 5 years will possibly have issues undeā€¦
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
This video teaches viewers about errors in exception handling.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question