Solved

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

Posted on 2008-06-11
8
155 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Protect jar file - windows app 2 39
base64 decode encode 12 122
javap bin 2 28
MySQL  on Tomcat 8 30
Introduction Java can be integrated with native programs using an interface called JNI(Java Native Interface). Native programs are programs which can directly run on the processor. JNI is simply a naming and calling convention so that the JVM (Java…
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

895 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

12 Experts available now in Live!

Get 1:1 Help Now