java.sql.SQLException: ResultSet is from UPDATE. No Data.
Zhane
findFoodCode.setString(1,input);
findFoodCode.setString(2,request.getParameter
("country"));
ResultSet rs2 = findFoodCode.executeQuery();
ResultSetMetaData metaData = rs2.getMetaData();
while(rs2.next()){
for(int i=1;i<metaData.getColumnCount()+1;i++)
{ out.print(rs2.getString(i)+";");
}
out.println("|"); // mark the end
}
Above is my code. I keep on getting the exception as stated in the
topic when i reach rs2.next(). My sql function when error happens
return nothing... but the error keeps on coming.
is there anyway for me to determine where the resultset is from?
whether it is an update or is it a data? or is there some other way to
go about it?
Martin Gregorie
> Above is my code. I keep on getting the exception as stated in the topic
> when i reach rs2.next(). My sql function when error happens return
> nothing... but the error keeps on coming.
>
What happens when you run the query as interactive SQL?
--
martin@ | Martin Gregorie
gregorie. | Essex, UK
org |
John B. Matthews
<279af25b-bf8b-4920...@n33g2000pri.googlegroups.com>,
Zhane <zha...@gmail.com> wrote:
[...]
> findFoodCode.setString(1,input);
> findFoodCode.setString(2,request.getParameter("country"));
>
> ResultSet rs2 = findFoodCode.executeQuery();
> ResultSetMetaData metaData = rs2.getMetaData();
>
> while(rs2.next()){
[...]
Presumably, findFoodCode is a PreparedStatement containing an UPDATE
statement instead of a SELECT statement, as expected by executeQuery().
Different JDBC drivers may produce different error messages for this. An
sscce <http://pscode.org/sscce.html> would be helpful.
For example, given the following DDL and Java:
<code>
create table customer(
id integer, name varchar(10), phone varchar(10), last date);
insert into customer values(1, 'Jones', '2125551212', '2008-12-03');
insert into customer values(2, 'Smith', '2125551212', '2008-12-04');
insert into customer values(3, 'Wesson', '2125551212', '2008-12-05');
import java.sql.*;
/** @author John B. Matthews */
class Phone {
public static void main (String args []) throws Exception {
Class.forName("org.h2.Driver");
Connection conn = DriverManager.getConnection
("jdbc:h2:tcp://localhost/src/java/jdbc/test", "sa", "");
PreparedStatement ps = conn.prepareStatement(
"SELECT name, phone, last FROM Customer WHERE name = ?");
ps.setString(1, "Smith");
ResultSet rset = ps.executeQuery();
while (rset.next ()) {
String name = rset.getString(1);
String phoneNumber = rset.getString(2);
String lastCall = rset.getDate(3).toString();
System.out.println(name
+ " " + phoneFormat(phoneNumber)
+ " " + lastCall);
}
}
private static String phoneFormat(String phone) {
if (phone.length() == 10) return
String.format("(%1$s) %2$s-%3$s",
phone.substring(0, 3),
phone.substring(3, 6),
phone.substring(6));
else return "Invalid";
}
}
</code>
Execution produces the following output:
<console>
Smith (212) 555-1212 2008-12-04
</console>
--
John B. Matthews
trashgod at gmail dot com
http://home.roadrunner.com/~jbmatthews/
Zhane
() from reading when it is 0 (an update);
Lew
> I read the metadata to check for number of columns to prevent rs2.next
> () from reading when it is 0 (an update);
That reply doesn't seem connected to the response:
John B. Matthews wrote: >> Presumably, findFoodCode is a PreparedStatement
containing an UPDATE
>> statement instead of a SELECT statement, as expected by executeQuery().
This should tell you that
- you need to tell us what went into 'findFoodCode', preferably as part of an
SSCCE: <http://sscce.org/>
because
- 'findFoodCode' doesn't hold a query, rather it holds an update statement,
thus 'executeQuery()' will always throw an exception.
I'm not clear how that you are reading the metadata pertains to the comments
you've received. Presumably (one must presume because you haven't given us
the entire error message) the error message refers to the line of code that
threw the exception, and that exception occurred prior to the 'getMetaData()'
call.
Quite aside from the fact that it's fairly difficult to get help from people
to whom you've given so little information, although we're really trying, the
debugging process for oneself involves using all the information that the
system gives you. Line numbers, details of the contents of the SQL statement,
stack traces - all these data point you to the actual problem, and thence to
the solution.
Please indicate how you've actually used the answers you've received here next
time you post on this topic, and provide more details if you're still having
trouble. For example - what is the SQL that you're trying to execute? (Hint:
UPDATE does not return a result set.)
--
Lew
John B. Matthews
<c0df6549-23ae-46c0...@z27g2000prd.googlegroups.com>,
Zhane <zha...@gmail.com> wrote:
> I read the metadata to check for number of columns to prevent rs2.next
> () from reading when it is 0 (an update);
This would appear to be a contradiction: executeQuery() throws
SQLException if the SQL statement does not return a ResultSet object,
while executeUpdate() throws SQLException if the SQL statement returns a
ResultSet object:
<http://java.sun.com/javase/6/docs/api/java/sql/PreparedStatement.html>
joe.we...@gmail.com
SQL does not include an actual query (request for data rows), is a
broken driver. I know this includes some big name drivers. Instead
it should throw an exception, not deliver some dummy resultset.
If you don't know what the SQL is doing, use execute(), and observe
what
it returns to know what to do first, then step through any possible
remaining returns using getMoreResults() and what it returns.
Here is the ideal code for running SQL you don't know about through
JDBC.
boolean getResultSetNow = stmt.execute();
int updateCount = -1;
while (true) { // handle all the in-line results from any stored
procedure or SQL
if (getResultSetNow) {
ResultSet r = stmt.getResultSet();
while (r.next()) {
// fully process result set before calling getMoreResults()
again!
}
r.close();
} else {
updateCount = stmt.getUpdateCount();
if (updateCount != -1) { // it's a valid update count
System.out.println("Reporting an update count of " +
updateCount);
}
}
if ((!getResultSetNow) && (updateCount == -1)) break; // done
with loop, finished all the returns
getResultSetNow = stmt.getMoreResults();
}
// if this is a CallableStatement, get output parameters now, after
the loop
Joe Weinstein at Oracle
Zhane
sorry...
this is the full error msg
-----------------------------------------------------------------------------------------
java.sql.SQLException: ResultSet is from UPDATE. No Data.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:
1056)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:
957)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:
927)
at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:7014)
at servlet.doPost(servlet.java:262)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:
738)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:
831)
at
org.apache.catalina.core.ApplicationFilterChain.servletService
(ApplicationFilterChain.java:411)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter
(ApplicationFilterChain.java:317)
at org.apache.catalina.core.ApplicationFilterChain.doFilter
(ApplicationFilterChain.java:198)
at
org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter
(MonitorFilter.java:390)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter
(ApplicationFilterChain.java:230)
at org.apache.catalina.core.ApplicationFilterChain.doFilter
(ApplicationFilterChain.java:198)
at org.apache.catalina.core.StandardWrapperValve.invoke
(StandardWrapperValve.java:288)
at org.apache.catalina.core.StandardContextValve.invokeInternal
(StandardContextValve.java:271)
at org.apache.catalina.core.StandardContextValve.invoke
(StandardContextValve.java:202)
at org.apache.catalina.core.StandardPipeline.doInvoke
(StandardPipeline.java:632)
at org.apache.catalina.core.StandardPipeline.doInvoke
(StandardPipeline.java:577)
at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:
94)
at org.apache.catalina.core.StandardHostValve.invoke
(StandardHostValve.java:206)
at org.apache.catalina.core.StandardPipeline.doInvoke
(StandardPipeline.java:632)
at org.apache.catalina.core.StandardPipeline.doInvoke
(StandardPipeline.java:577)
at org.apache.catalina.core.StandardPipeline.invoke
(StandardPipeline.java:571)
at org.apache.catalina.core.ContainerBase.invoke
(ContainerBase.java:1080)
at org.apache.catalina.core.StandardEngineValve.invoke
(StandardEngineValve.java:150)
at org.apache.catalina.core.StandardPipeline.doInvoke
(StandardPipeline.java:632)
at org.apache.catalina.core.StandardPipeline.doInvoke
(StandardPipeline.java:577)
at org.apache.catalina.core.StandardPipeline.invoke
(StandardPipeline.java:571)
at org.apache.catalina.core.ContainerBase.invoke
(ContainerBase.java:1080)
at org.apache.coyote.tomcat5.CoyoteAdapter.service
(CoyoteAdapter.java:272)
at
com.sun.enterprise.web.connector.grizzly.DefaultProcessorTask.invokeAdapter
(DefaultProcessorTask.java:637)
at
com.sun.enterprise.web.connector.grizzly.DefaultProcessorTask.doProcess
(DefaultProcessorTask.java:568)
at
com.sun.enterprise.web.connector.grizzly.DefaultProcessorTask.process
(DefaultProcessorTask.java:813)
at
com.sun.enterprise.web.connector.grizzly.DefaultReadTask.executeProcessorTask
(DefaultReadTask.java:341)
at
com.sun.enterprise.web.connector.grizzly.DefaultReadTask.doTask
(DefaultReadTask.java:263)
at
com.sun.enterprise.web.connector.grizzly.DefaultReadTask.doTask
(DefaultReadTask.java:214)
at com.sun.enterprise.web.connector.grizzly.TaskBase.run
(TaskBase.java:265)
at
com.sun.enterprise.web.connector.grizzly.ssl.SSLWorkerThread.run
(SSLWorkerThread.java:106)
-----------------------------------------------------------------------------------------
Error Happens when doing this :
where findFoodCode = connection.prepareStatement("CALL
getFoodCode(?,?)");
-----------------------------------------------------------------------------------------
findFoodCode.setString(1,input);
findFoodCode.setString(2,request.getParameter
("country"));
ResultSet rs2 = findFoodCode.executeQuery();
ResultSetMetaData metaData = rs2.getMetaData();
// if(metaData.getColumnCount()!=0){
while(rs2.next()){
for(int i=1;i<metaData.getColumnCount()+1;i++)
{ out.print(rs2.getString(i)+";");
}
}
// }
-----------------------------------------------------------------------------------------
GetFoodCode
-----------------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS getFoodCode;
delimiter $$
CREATE PROCEDURE getFoodCode (IN ifoodcode varchar(20),IN icountry
varchar(20))
BEGIN
DECLARE returnvar INT;
SET returnvar = (SELECT foodID FROM foodTable
WHERE foodCode = ifoodcode);
IF returnvar IS NOT NULL THEN
select
foodcode,foodname,fooddescription,foodrating,locationCountry,locationCurrency,ROUND
(AVG(locationPrice),2),manufacturerName,lastUpdated from
manufacturerTable,foodtable,locationsTable,food_locationTable where
foodtable.foodID = returnvar and food_locationTable.foodID=returnvar
and locationsTable.locationID=food_locationTable.locationID and
locationsTable.locationCountry = icountry and
manufacturerTable.manufacturerID =
(select manufacturerID from foodTable where
foodtable.foodID = returnvar);
END IF;
END $$
delimiter ;
---
* Synchronet * The Whitehouse BBS --- whitehouse.hulds.com --- check it out free usenet!
--- Synchronet 3.15a-Win32 NewsLink 1.92
Time Warp of the Future BBS - telnet://time.synchro.net:24
joe.we...@gmail.com
it is passed a bogus food code. You need to try the JDBC code
I presented, eg:
findFoodCode.setString(1,input);
findFoodCode.setString(2,request.getParameter("country"));
boolean getResultSetNow = findFoodCode.execute();
int updateCount = -1;
while (true) { // handle all the in-line results from any stored
procedure or SQL
if (getResultSetNow) {
ResultSet r = findFoodCode.getResultSet();
ResultSetMetaData metaData = r.getMetaData();
int col_cmt = metaData.getColumnCount();
while (r.next()) {
// fully process result set before calling getMoreResults()
again!
for(int i=1;i<=col_cnt;i++)
{
out.print(r.getString(i)+";");
}
}
r.close();
} else {
updateCount = findFoodCode.getUpdateCount();
if (updateCount != -1) { // it's a valid update count
System.out.println("Reporting an update count of " +
updateCount);
}
}
if ((!getResultSetNow) && (updateCount == -1)) break; // done
with loop, finished all the returns
getResultSetNow = findFoodCode.getMoreResults();
}
Zhane
wrote:
thanks
this more or less solved it.. although i dont know why =) but thanks
Lew
> this more or less solved it.. although i [sic] dont know why =) but thanks
Let me take a stab at the "why", though I might not get everything exactly.
"joeNOS...@BEA.com" wrote:
>> Well it's clear that this procedure may not return anything if
>> it is passed a bogus food code. You need to try the JDBC code
>> I presented, eg:
What did Joe do differently that worked?
>> findFoodCode.setString(1,input);
>> findFoodCode.setString(2,request.getParameter("country"));
>>
>> boolean getResultSetNow = findFoodCode.execute();
He used 'execute()'. It returns a 'boolean' that indicates whether the
execution returned a 'ResultSet' or not.
>> int updateCount = -1;
>>
>> while (true) { // handle all the in-line results from any stored
>> procedure or SQL
>> if (getResultSetNow) {
Ah-hah - it did return a 'ResultSet'. Must've been a query.
>> ResultSet r = findFoodCode.getResultSet();
>> ResultSetMetaData metaData = r.getMetaData();
>> int col_cmt = metaData.getColumnCount();
>> while (r.next()) {
>> // fully process result set before calling getMoreResults()
>> again!
>> for(int i=1;i<=col_cnt;i++)
>> {
>> out.print(r.getString(i)+";");
>> }
>> }
>> r.close();
>> } else {
Oh-ho - it did not return a 'ResultSet'. Must've been an update.
>> updateCount = findFoodCode.getUpdateCount();
>> if (updateCount != -1) { // it's a valid update count
>> System.out.println("Reporting an update count of " +
>> updateCount);
>> }
>> }
>> if ((!getResultSetNow) && (updateCount == -1)) break; // done
>> with loop, finished all the returns
>> getResultSetNow = findFoodCode.getMoreResults();
>> }
and so on.
--
Lew
John B. Matthews
wrote:
Thank you for this cogent analysis. It sheds light on Joe's insight that
the OP's original driver failed to meet the contract for executeQuery().
In effect, the move to the MySQL JDBC driver revealed a latent bug in
the original system.
joe.we...@gmail.com
>
> Oh-ho - it did not return a 'ResultSet'. Must've been an update.
>
> >> updateCount = findFoodCode.getUpdateCount();
> Lew
Thanks Lew. The only thing I'd change is to say here "OK, *if* there's
any more in-line results coming from my execute(), it's not a
result set. Either it's an update count, or if it's -1 that means
there are no more results at all. (results are done when getMoreResults
()
returns false and then a call to getUpdateCount() returns -1.
For instance, a Sybase or SQLServer stored procedure could
return a long unpredictable series of queries and/or updates:
if ( random number is even )
begin
update tableA -- returns an 'update' count
select foo from tableb -- returns result set
select bar from tablec -- returns result set
delete tablec where ... -- returns an 'update' count
end
else
begin
select foo from tableb
delete tablec where ...
select bar from tablec
update tableA
end
Joe