Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 248
  • Last Modified:

Any idea why this simple query is not working?

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
g118481
Asked:
g118481
  • 4
  • 3
  • 3
  • +1
1 Solution
 
Ajay-SinghCommented:
> } catch (SQLException sqle) { sqle.printStackTrace(); }
are you sure this statement is not printing anything (checkout catalina.out)
0
 
Mayank SAssociate Director - Product EngineeringCommented:
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
 
g118481Author Commented:
Can you give an example of how the finally block should be set into my code above?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
girionisCommented:
> catch (SQLException sqle) { sqle.printStackTrace(); }
%>


catch (SQLException sqle) { sqle.printStackTrace(); }
finally
{
   ... do your stuff here
}
%>
0
 
g118481Author Commented:
> {
   ... 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
 
girionisCommented:
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
 
g118481Author Commented:
I added your last suggestion, but get "JavaCompile:  con cannot be resolved."
I get the same compile message for "statement"
0
 
girionisCommented:
> 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
 
girionisCommented:
Use "conn" and "stat" instead.
0
 
Mayank SAssociate Director - Product EngineeringCommented:
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
 
Mayank SAssociate Director - Product EngineeringCommented:
:-) no points for suggestions?
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now