Solved

sql problem using like

Posted on 2001-06-03
11
254 Views
Last Modified: 2010-04-16
Hello experts,

 I have what I thought was a simple SQL statement using like in the where clause. I am using an Access DB and I have tested the same statement in a query and it works returning 10 rows. When I try it from the servlet it returns no results.

public void DisplayErrors()
{
     try
     {
          String rVal = new String();
          rs = stmt.executeQuery("Select * from tbl_Err where ErrorMsg like '*out of balance*'"); //problem
                         
          while(rs.next())
          {
               rVal = rs.getString(1);
               stmt.executeUpdate("Update tbl_err Set CaseType = '1', Resolved ='Yes' where LoadNo = rVal");
          }
        }
     catch (SQLException e)
     {
             System.err.println("SQl Error");
          e.printStackTrace();
     }
}
0
Comment
Question by:entercite
  • 5
  • 3
  • 2
  • +1
11 Comments
 

Expert Comment

by:Buckenhofer
ID: 6151476
% is the standard multiple char wildcard. So use % instead of *.

rs = stmt.executeQuery("Select * from tbl_Err where ErrorMsg like '%out of balance%'");
0
 
LVL 9

Expert Comment

by:Ovi
ID: 6151492
What kind of error ? the statement looks right to me.
0
 
LVL 9

Expert Comment

by:Ovi
ID: 6151498
Reading the example above it seems to me that you use the rVal string to fill the LoadNo field, which it seems to be int. Is right ? are you sure that rVal value must be String or rs.getString(1) return you a String ? I usually use rs.getString('ColumnName'). Maybe the error is not from the execution of the first statement.
0
Does Powershell have you tied up in knots?

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

 

Expert Comment

by:Buckenhofer
ID: 6151535
The statement

stmt.executeUpdate("Update tbl_err Set CaseType = '1', Resolved ='Yes' where LoadNo = rVal"

should be

stmt.executeUpdate("Update tbl_err Set CaseType = '1', Resolved ='Yes' where LoadNo = " + rVal + ";"

if you want to use the value of rVal.
0
 
LVL 9

Expert Comment

by:Ovi
ID: 6151544
... or :
stmt.executeUpdate("Update tbl_err Set CaseType = '1', Resolved ='Yes' where LoadNo ='" + rVal + "'"
0
 

Author Comment

by:entercite
ID: 6152054
Yes, you are correct lodno is an integer. If a string holds number characters would it matter to a SQL statement? I could cast it into a second variable and test this. Or should I use int logno and use getInteger vs getString?

I have tried using % wild characters in access and that works too. When I tested this statement inside access this retured 10 rows of data.

Here is an update:

try
{
               
     Connection con = DriverManager.getConnection("jdbc:odbc:Error");
     Statement stmt = con.createStatement();
     ResultSet rs;
     String logno = new String();
     rs = stmt.executeQuery("Select * from tbl_Err where ErrorMsg like '%out of balance%'");

     while(rs.next())
     {
          logno = rs.getString(1);
          System.out.println("result 1");
          stmt.executeUpdate("Update tbl_err Set CaseType = '1', Resolved ='Yes' where LoadNo = " + logno + ";");
          System.out.println("update has run");
     }
}
catch (SQLException e)
{
        System.err.println("SQl Error");
     e.printStackTrace();
}

This has worked somewhat. It updated 1 row then it bombed here is the error:

ErrTrkr: init
date created
result 1
update has run
SQl Error
java.sql.SQLException: ResultSet is closed
        at sun.jdbc.odbc.JdbcOdbcResultSet.checkOpen(Unknown Source)
        at sun.jdbc.odbc.JdbcOdbcResultSet.next(Unknown Source)
        at ErrTrkr.OOB(ErrTrkr.java:124)
        at ErrTrkr.doGet(ErrTrkr.java:108)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:715)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:840)
        at com.sun.web.core.ServletWrapper.handleRequest(ServletWrapper.java:140
)
        at com.sun.web.core.InvokerServlet.service(InvokerServlet.java:169)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:840)
        at com.sun.web.core.ServletWrapper.handleRequest(ServletWrapper.java:140
)
        at com.sun.web.core.Context.handleRequest(Context.java:375)
        at com.sun.web.server.ConnectionHandler.run(ConnectionHandler.java:135)
0
 

Expert Comment

by:kapitany
ID: 6152089
Yes it's closed because you use the same statement variable. It was an update the 2nd time and there is no result set returned. Use a different statement to update, but don't forget to close the statements in the end of your function.

BR,
kapi
0
 
LVL 9

Accepted Solution

by:
Ovi earned 50 total points
ID: 6152095
use getInt(), and yes, possibly from the same statement object used by you
0
 

Author Comment

by:entercite
ID: 6152164
ok I will try getInt... but I thought that while(rs.next()) would advanced to the next result and therefor logno = rs.getString(1) would hold a different value and update another row? I know there are 10 rows not just one.

You say to "Use a different statement to update, but don't forget to close the statements in the end of your function." What other statement is there to use? And do not forget to close my statement in the end of my function... how do I close it and if I dont does it stay in memory? Should I create a stmt2 and use that within the loop? Or are you saying that I should destroy it at the end and create new at the begining of the loop?

try
{
               
     Connection con = DriverManager.getConnection("jdbc:odbc:Error");
     Statement stmt = con.createStatement();
     ResultSet rs;
     int logno = 0;
     rs = stmt.executeQuery("Select * from tbl_Err where ErrorMsg like '%out of balance%'");

     while(rs.next())
     {
          logno = rs.getInt(1);
          System.out.println("result 1");
          stmt.executeUpdate("Update tbl_err Set CaseType = '1', Resolved ='Yes' where LoadNo = " + logno + ";");
          System.out.println("update has run");
     }
}

ErrTrkr: init
date created
result 1
update has run
SQl Error
java.sql.SQLException: ResultSet is closed
        at sun.jdbc.odbc.JdbcOdbcResultSet.checkOpen(Unknown Source)
        at sun.jdbc.odbc.JdbcOdbcResultSet.next(Unknown Source)
        at ErrTrkr.OOB(ErrTrkr.java:124)
        at ErrTrkr.doGet(ErrTrkr.java:108)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:715)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:840)
        at com.sun.web.core.ServletWrapper.handleRequest(ServletWrapper.java:140)
        at com.sun.web.core.InvokerServlet.service(InvokerServlet.java:169)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:840)
        at com.sun.web.core.ServletWrapper.handleRequest(ServletWrapper.java:140)
        at com.sun.web.core.Context.handleRequest(Context.java:375)
        at com.sun.web.server.ConnectionHandler.run(ConnectionHandler.java:135)
0
 

Author Comment

by:entercite
ID: 6152193
Hmm your right... That does seem to work now.

while(rs.next())
{
     Statement stmt2 = con.createStatement();
     logno = rs.getInt(1);
     System.out.println("result 1");
     stmt2.executeUpdate("Update tbl_err Set CaseType = '1', Resolved ='Yes' where LoadNo = " + logno + ";");
     System.out.println("update has run");
     stmt2.close();
}

let me mess with this for a bit...
0
 
LVL 9

Expert Comment

by:Ovi
ID: 6152205
use getInt(), and yes, possibly from the same statement object used by you
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
groupSumClump challenge 9 117
rhino JavaScript import, load 25 85
servlet requestdispatcher include and forward differences 1 32
Chrome and Firefox Java 5 34
By the end of 1980s, object oriented programming using languages like C++, Simula69 and ObjectPascal gained momentum. It looked like programmers finally found the perfect language. C++ successfully combined the object oriented principles of Simula w…
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 basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.

832 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