f



java.sql.SQLException: ResultSet is from UPDATE. No Data.

need some help here


                 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?
0
Zhane
12/6/2008 4:21:21 AM
comp.lang.java.databases 3049 articles. 0 followers. samyaksulabh (16) is leader. Post Follow

12 Replies
2558 Views

Similar Articles

[PageSpeed] 59

On Fri, 05 Dec 2008 20:21:21 -0800, Zhane wrote:

> 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       |
0
Martin
12/6/2008 1:56:16 PM
In article 
<279af25b-bf8b-4920-bd38-be50ad24d563@n33g2000pri.googlegroups.com>,
 Zhane <zhane84@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/
0
John
12/6/2008 2:25:53 PM
I read the metadata to check for number of columns to prevent rs2.next
() from reading when it is 0 (an update);
0
Zhane
12/6/2008 4:46:37 PM
Zhane wrote:
> 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
0
Lew
12/6/2008 5:08:21 PM
In article 
<c0df6549-23ae-46c0-9496-45be55a5591c@z27g2000prd.googlegroups.com>,
 Zhane <zhane84@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>

-- 
John B. Matthews
trashgod at gmail dot com
http://home.roadrunner.com/~jbmatthews/
0
John
12/6/2008 5:31:48 PM
Whatever driver returns a result set from an executeQuery() call whose
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



0
joeNOSPAM
12/6/2008 6:37:06 PM
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 ;

0
Zhane
12/7/2008 12:55:32 AM
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:

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();
   }


0
joeNOSPAM
12/7/2008 4:04:03 PM
On Dec 8, 12:04=A0am, "joeNOS...@BEA.com" <joe.weinst...@gmail.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:
>
> findFoodCode.setString(1,input);
> findFoodCode.setString(2,request.getParameter("country"));
>
> boolean getResultSetNow =3D findFoodCode.execute();
> =A0 =A0int updateCount =3D -1;
>
> =A0 =A0while (true) { // handle all the in-line results from any stored
> procedure or SQL
> =A0 =A0 =A0if (getResultSetNow) {
> =A0 =A0 =A0 =A0ResultSet r =3D findFoodCode.getResultSet();
> =A0 =A0 =A0 =A0ResultSetMetaData metaData =3D r.getMetaData();
> =A0 =A0 =A0 =A0int col_cmt =3D metaData.getColumnCount();
> =A0 =A0 =A0 =A0while (r.next()) {
> =A0 =A0 =A0 =A0 =A0// fully process result set before calling getMoreResu=
lts()
> again!
> =A0 =A0 =A0 =A0 =A0for(int i=3D1;i<=3Dcol_cnt;i++)
> =A0 =A0 =A0 =A0 =A0{
> =A0 =A0 =A0 =A0 =A0 =A0out.print(r.getString(i)+";");
> =A0 =A0 =A0 =A0 =A0}
> =A0 =A0 =A0 =A0}
> =A0 =A0 =A0 =A0r.close();
> =A0 =A0 =A0} else {
> =A0 =A0 =A0 =A0updateCount =3D findFoodCode.getUpdateCount();
> =A0 =A0 =A0 =A0if (updateCount =A0!=3D -1) { // it's a valid update count
> =A0 =A0 =A0 =A0 =A0System.out.println("Reporting an update count of " +
> updateCount);
> =A0 =A0 =A0 =A0}
> =A0 =A0 =A0}
> =A0 =A0 =A0if ((!getResultSetNow) && (updateCount =3D=3D -1)) break; // d=
one
> with loop, finished all the returns
> =A0 =A0 =A0getResultSetNow =3D findFoodCode.getMoreResults();
> =A0 =A0}

thanks

this more or less solved it.. although i dont know why =3D) but thanks
0
Zhane
12/10/2008 2:28:18 PM
Zhane wrote:
> 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
0
Lew
12/11/2008 2:46:05 AM
In article <ghputd$5oe$9@news.albasani.net>, Lew <noone@lewscanon.com> 
wrote:

> Zhane wrote:
> > 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.

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.

-- 
John B. Matthews
trashgod at gmail dot com
http://home.roadrunner.com/~jbmatthews/
0
John
12/11/2008 6:46:48 AM
On Dec 10, 6:46=A0pm, Lew <no...@lewscanon.com> wrote:
>
> Oh-ho - it did not return a 'ResultSet'. =A0Must've been an update.
>
> >> =A0 =A0 =A0 =A0updateCount =3D 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
0
joeNOSPAM
12/11/2008 3:49:50 PM
Reply: