[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2008-06-11
8
Medium Priority
?
164 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
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!

 
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

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…
Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
Viewers learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it. Define Error Message: Line Numbers: Type of Error: Break Down…
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…
Suggested Courses
Course of the Month19 days, 5 hours left to enroll

834 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