Solved

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

Posted on 2008-06-11
8
156 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
  • 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

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

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
split53 challenge 7 98
tomcat not starting 6 46
Java: The Public Class Main 4 17
JAVA API design with micro service cloud in mind 1 19
For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
Viewers learn about the scanner class in this video and are introduced to receiving user input for their programs. Additionally, objects, conditional statements, and loops are used to help reinforce the concepts. Introduce Scanner class: Importing…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.

831 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