Solved

Any idea why this simple query is not working?

Posted on 2006-07-17
11
246 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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

An old method to applying the Singleton pattern in your Java code is to check if a static instance, defined in the same class that needs to be instantiated once and only once, is null and then create a new instance; otherwise, the pre-existing insta…
In this post we will learn different types of Android Layout and some basics of an Android App.
Viewers learn about the “while” loop and how to utilize it correctly in Java. Additionally, viewers begin exploring how to include conditional statements within a while loop and avoid an endless loop. Define While Loop: Basic Example: Explanatio…
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
Suggested Courses

627 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