?
Solved

No matter what I do, can not get the record count.  Help!

Posted on 2008-06-11
8
Medium Priority
?
160 Views
Last Modified: 2010-07-27
I have a query that I have tested and know works correctly.
What I want this code to do, is return the count, which will be "0" or "1"
and if it is "0", then do the re-direct.

The problem I am having, is that the query is finding the result to be "1" (for one record), but the counter says it is "0"
I have racked my head with this for half a day now, trying several ways, but still cannot get the correct results with finding the correct count and outputting the correct result.

Can someone help me with this?


<%@ page import="java.sql.*,javax.sql.*,javax.naming.*"%>
<%@ page errorPage = "errpage.jsp" %>
<%@ page import="java.util.*" %>
<%@ page import="javax.servlet.http.Cookie" %>
<% String db2username=(String)request.getRemoteUser(); %>
<%
try {
javax.sql.DataSource ds;
java.sql.Connection conns = null;
Context ic = new InitialContext();
ds = (javax.sql.DataSource) ic.lookup("java:comp/env/jdbc/mydb2");
conns = ds.getConnection();
conns.setAutoCommit(false);
  java.sql.Statement stats = conns.createStatement();
 
java.sql.ResultSet rs = stats.executeQuery("SELECT COUNT(*) COUNT FROM MYTABLE WHERE GROUPS LIKE '%ICM%' AND ID='"+db2username+"' FOR FETCH ONLY WITH UR");
 
 
Object[] COUNT = null;
int counter = 0;
while(rs.next()) {
  if(COUNT == null) {
 
String redirectURL = "cdspec.jsp?&pre_page=control5";
response.sendRedirect(redirectURL); 
 
%>
<% } else { %>
 FOUND A MATCH.  NOW DO SOMETHING HERE.
<% } %>
 
<% } // close while loop %>
<%
rs.close();
stats.close();
conns.close();
 
} catch (SQLException e) {
    }
 
%>

Open in new window

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
  • 4
8 Comments
 
LVL 92

Expert Comment

by:objects
ID: 21765725
you never set counter;

if (rs.next())
{
   counter = rs.getInt(1);

0
 
LVL 1

Author Comment

by:g118481
ID: 21765766
I do not understand your statement.
Please put it in my code snippet above so I can understand your suggestion, and so I will have a visual of exactly what to change.

Thx
0
 
LVL 92

Expert Comment

by:objects
ID: 21765776
if (rs.next())
{
   counter = rs.getInt(1);
   if (counter==1)
   {
        // found a match
   }
   else
   {
        // no match
   }
}

0
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 
LVL 1

Author Comment

by:g118481
ID: 21765816
I put your code suggestion into my code snippet below.
Is this what you mean?
<%@ page import="java.sql.*,javax.sql.*,javax.naming.*"%>
<%@ page errorPage = "errpage.jsp" %>
<%@ page import="java.util.*" %>
<%@ page import="javax.servlet.http.Cookie" %>
<% String db2username=(String)request.getRemoteUser(); %>
 
<%--/////////////////////////////////////////////////////////////////////////////////////////////--%>
<%
try {
javax.sql.DataSource ds;
java.sql.Connection conns = null;
Context ic = new InitialContext();
ds = (javax.sql.DataSource) ic.lookup("java:comp/env/jdbc/mydb2");
conns = ds.getConnection();
conns.setAutoCommit(false);
  java.sql.Statement stats = conns.createStatement();
 
java.sql.ResultSet rs = stats.executeQuery("SELECT COUNT(*) COUNT FROM MYTABLE WHERE GROUPS LIKE '%ICM%' AND ID='"+db2username+"' FOR FETCH ONLY WITH UR");
 
if (rs.next())
{
   counter = rs.getInt(1);
   if (counter==1)
   {
 
String redirectURL = "cdspec.jsp?&pre_page=control5";
response.sendRedirect(redirectURL); 
 
} else { %>
 
 FOUND A MATCH.  NOW DO SOMETHING HERE.
 
<% } %>
<% } // close while loop %>
<%
rs.close();
stats.close();
conns.close();
 
} catch (SQLException e) {
    }
 
%>

Open in new window

0
 
LVL 92

Expert Comment

by:objects
ID: 21765827
if count is 1 have you found a match?
the way u have it, u execute "FOUND A MATCH.  NOW DO SOMETHING HERE." if count is *not* equal to 1

When did u want to execute that code?

0
 
LVL 92

Accepted Solution

by:
objects earned 2000 total points
ID: 21765843
just read your code again :)

looks like it should be:

if (rs.next())
{
   int counter = rs.getInt(1);
   if (counter==0)
   {
 
String redirectURL = "cdspec.jsp?&pre_page=control5";
response.sendRedirect(redirectURL);
 
} else { %>
 
 FOUND A MATCH.  NOW DO SOMETHING HERE.
 
<% } %>
<% } // close while loop %>
<%

0
 
LVL 1

Author Comment

by:g118481
ID: 21768866
Great!

Your last suggestion works perfect.
I really appreciate your time an efforts!

Thanks
0
 
LVL 1

Author Closing Comment

by:g118481
ID: 31466421
Thank you for the excellent advice!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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…
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…
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses
Course of the Month13 days, 10 hours left to enroll

800 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