entercite
asked on
sql problem using like
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
}
}
catch (SQLException e)
{
System.err.println("SQl Error");
e.printStackTrace();
}
}
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
}
}
catch (SQLException e)
{
System.err.println("SQl Error");
e.printStackTrace();
}
}
What kind of error ? the statement looks right to me.
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')
The statement
stmt.executeUpdate("Update
should be
stmt.executeUpdate("Update
if you want to use the value of rVal.
stmt.executeUpdate("Update
should be
stmt.executeUpdate("Update
if you want to use the value of rVal.
... or :
stmt.executeUpdate("Update
stmt.executeUpdate("Update
ASKER
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.getConnectio
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
stmt.executeUpdate("Update
System.out.println("update
}
}
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.JdbcOdbcResu
at sun.jdbc.odbc.JdbcOdbcResu
at ErrTrkr.OOB(ErrTrkr.java:1
at ErrTrkr.doGet(ErrTrkr.java
at javax.servlet.http.HttpSer
at javax.servlet.http.HttpSer
at com.sun.web.core.ServletWr
)
at com.sun.web.core.InvokerSe
at javax.servlet.http.HttpSer
at com.sun.web.core.ServletWr
)
at com.sun.web.core.Context.h
at com.sun.web.server.Connect
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.getConnectio
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
stmt.executeUpdate("Update
System.out.println("update
}
}
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.JdbcOdbcResu
at sun.jdbc.odbc.JdbcOdbcResu
at ErrTrkr.OOB(ErrTrkr.java:1
at ErrTrkr.doGet(ErrTrkr.java
at javax.servlet.http.HttpSer
at javax.servlet.http.HttpSer
at com.sun.web.core.ServletWr
)
at com.sun.web.core.InvokerSe
at javax.servlet.http.HttpSer
at com.sun.web.core.ServletWr
)
at com.sun.web.core.Context.h
at com.sun.web.server.Connect
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
BR,
kapi
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.getConnectio
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
stmt.executeUpdate("Update
System.out.println("update
}
}
ErrTrkr: init
date created
result 1
update has run
SQl Error
java.sql.SQLException: ResultSet is closed
at sun.jdbc.odbc.JdbcOdbcResu
at sun.jdbc.odbc.JdbcOdbcResu
at ErrTrkr.OOB(ErrTrkr.java:1
at ErrTrkr.doGet(ErrTrkr.java
at javax.servlet.http.HttpSer
at javax.servlet.http.HttpSer
at com.sun.web.core.ServletWr
at com.sun.web.core.InvokerSe
at javax.servlet.http.HttpSer
at com.sun.web.core.ServletWr
at com.sun.web.core.Context.h
at com.sun.web.server.Connect
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.getConnectio
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
stmt.executeUpdate("Update
System.out.println("update
}
}
ErrTrkr: init
date created
result 1
update has run
SQl Error
java.sql.SQLException: ResultSet is closed
at sun.jdbc.odbc.JdbcOdbcResu
at sun.jdbc.odbc.JdbcOdbcResu
at ErrTrkr.OOB(ErrTrkr.java:1
at ErrTrkr.doGet(ErrTrkr.java
at javax.servlet.http.HttpSer
at javax.servlet.http.HttpSer
at com.sun.web.core.ServletWr
at com.sun.web.core.InvokerSe
at javax.servlet.http.HttpSer
at com.sun.web.core.ServletWr
at com.sun.web.core.Context.h
at com.sun.web.server.Connect
ASKER
Hmm your right... That does seem to work now.
while(rs.next())
{
Statement stmt2 = con.createStatement();
logno = rs.getInt(1);
System.out.println("result
stmt2.executeUpdate("Updat
System.out.println("update
stmt2.close();
}
let me mess with this for a bit...
while(rs.next())
{
Statement stmt2 = con.createStatement();
logno = rs.getInt(1);
System.out.println("result
stmt2.executeUpdate("Updat
System.out.println("update
stmt2.close();
}
let me mess with this for a bit...
use getInt(), and yes, possibly from the same statement object used by you
rs = stmt.executeQuery("Select * from tbl_Err where ErrorMsg like '%out of balance%'");