Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Any idea why this simple query is not working?

Posted on 2006-07-17
11
Medium Priority
?
247 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 1000 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

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!

Question has a verified solution.

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

Are you developing a Java application and want to create Excel Spreadsheets? You have come to the right place, this article will describe how you can create Excel Spreadsheets from a Java Application. For the purposes of this article, I will be u…
In this post we will learn different types of Android Layout and some basics of an Android App.
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
Suggested Courses

715 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