Solved

Any idea why this simple query is not working?

Posted on 2006-07-17
11
207 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
Comment Utility
> } catch (SQLException sqle) { sqle.printStackTrace(); }
are you sure this statement is not printing anything (checkout catalina.out)
0
 
LVL 30

Expert Comment

by:mayankeagle
Comment Utility
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
Comment Utility
Can you give an example of how the finally block should be set into my code above?
0
 
LVL 35

Expert Comment

by:girionis
Comment Utility
> catch (SQLException sqle) { sqle.printStackTrace(); }
%>


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

Author Comment

by:g118481
Comment Utility
> {
   ... 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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 35

Accepted Solution

by:
girionis earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
> 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
Comment Utility
Use "conn" and "stat" instead.
0
 
LVL 30

Expert Comment

by:mayankeagle
Comment Utility
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:mayankeagle
Comment Utility
:-) no points for suggestions?
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
The viewer will learn how to implement Singleton Design Pattern in Java.

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now