Quantcast

multiple result sets problem

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

multiple result sets problem

Gupta, Abhishek

Hello,

 

I was having some problems getting sql to return multiple result sets.

I am using jTDS 1.1 driver to connect to Sybase 12.5 server.

 

After debugging the problem seems that the getMoreResults method can change the updateCount. So getUpdateCount should be called after getMoreResults.

 

This can solve this problem on Sybase but I am not sure about other databases.

 

I made the changes as in the attached patch for this to work. The patch is against the latest snapshot zip file.

 

Thanks

Abhishek


patch.txt (974 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

RE: multiple result sets problem

Gupta, Abhishek

Totally worthless patch !!

Does not work with single result sets.

My apologies.

 


From: [hidden email] [mailto:[hidden email]] On Behalf Of Gupta, Abhishek
Sent: Wednesday, November 02, 2005 2:45 PM
To: [hidden email]
Subject: [Squirrel-sql-users] multiple result sets problem

 

Hello,

 

I was having some problems getting sql to return multiple result sets.

I am using jTDS 1.1 driver to connect to Sybase 12.5 server.

 

After debugging the problem seems that the getMoreResults method can change the updateCount. So getUpdateCount should be called after getMoreResults.

 

This can solve this problem on Sybase but I am not sure about other databases.

 

I made the changes as in the attached patch for this to work. The patch is against the latest snapshot zip file.

 

Thanks

Abhishek

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: multiple result sets problem

Gerd Wagner
In reply to this post by Gupta, Abhishek
Hi Abhishek,

your patch has a problem with simple Select statements. For simple
Select statements getMoreResults() returns false and getUpdateCount()
returns -1 at their first call. So with your patch the while loop will
break before any result is shown.

I tested your patch against PostgreSQL and DB2.


By the way it took us quite some trials to get this while loop to work
right with most databases. So this is not an easy area. Nonetheless if
it could be improved we would do it.

Gerd


Gupta, Abhishek schrieb:

> Hello,
>
>  
>
> I was having some problems getting sql to return multiple result sets.
>
> I am using jTDS 1.1 driver to connect to Sybase 12.5 server.
>
>  
>
> After debugging the problem seems that the getMoreResults method can
> change the updateCount. So getUpdateCount should be called after
> getMoreResults.
>
>  
>
> This can solve this problem on Sybase but I am not sure about other
> databases.
>
>  
>
> I made the changes as in the attached patch for this to work. The patch
> is against the latest snapshot zip file.
>
>  
>
> Thanks
>
> Abhishek
>
>
> ------------------------------------------------------------------------
>
> 301a299
>
>>         boolean moreResults = _stmt.getMoreResults();
>
> 302a301,308
>
>>         if (!moreResults && -1 == updateCount)
>>         {
>>            // There is no need to close result sets if we call _stmt.getMoreResults() because it
>>            // implicitly closes any current ResultSet.
>>            // ON DB2 version 7.1 it is even harmful to close a ResultSet explicitly.
>>            // _stmt.getMoreResults() will never return true anymore if you do.
>>            break;
>>         }
>
> 360,368d365
> <             break;
> <          }
> <
> <          if (!_stmt.getMoreResults() && -1 == updateCount)
> <          {
> <             // There is no need to close result sets if we call _stmt.getMoreResults() because it
> <             // implicitly closes any current ResultSet.
> <             // ON DB2 version 7.1 it is even harmful to close a ResultSet explicitly.
> <             // _stmt.getMoreResults() will never return true anymore if you do.



-------------------------------------------------------
SF.Net email is sponsored by:
Tame your development challenges with Apache's Geronimo App Server. Download
it for free - -and be entered to win a 42" plasma tv or your very own
Sony(tm)PSP.  Click here to play: http://sourceforge.net/geronimo.php
_______________________________________________
Squirrel-sql-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

RE: multiple result sets problem

Gupta, Abhishek
In reply to this post by Gupta, Abhishek
Yes I found the problem soon after I finished working with stored procs.
Sent a mail regarding this also. I am sorry for wasting your time.

However I do think that getMoreResults should be called before calling
getUpdateCount.

I will try to make it work but I can only test on Sybase and MySQL.

Thanks
Abhishek

-----Original Message-----
From: Gerd Wagner [mailto:[hidden email]]
Sent: Thursday, November 03, 2005 4:50 AM
To: Gupta, Abhishek
Cc: [hidden email]
Subject: Re: [Squirrel-sql-users] multiple result sets problem

Hi Abhishek,

your patch has a problem with simple Select statements. For simple
Select statements getMoreResults() returns false and getUpdateCount()
returns -1 at their first call. So with your patch the while loop will
break before any result is shown.

I tested your patch against PostgreSQL and DB2.


By the way it took us quite some trials to get this while loop to work
right with most databases. So this is not an easy area. Nonetheless if
it could be improved we would do it.

Gerd


Gupta, Abhishek schrieb:

> Hello,
>
>  
>
> I was having some problems getting sql to return multiple result sets.
>
> I am using jTDS 1.1 driver to connect to Sybase 12.5 server.
>
>  
>
> After debugging the problem seems that the getMoreResults method can
> change the updateCount. So getUpdateCount should be called after
> getMoreResults.
>
>  
>
> This can solve this problem on Sybase but I am not sure about other
> databases.
>
>  
>
> I made the changes as in the attached patch for this to work. The patch
> is against the latest snapshot zip file.
>
>  
>
> Thanks
>
> Abhishek
>
>
> ------------------------------------------------------------------------
>
> 301a299
>
>>         boolean moreResults = _stmt.getMoreResults();
>
> 302a301,308
>
>>         if (!moreResults && -1 == updateCount)
>>         {
>>            // There is no need to close result sets if we call
_stmt.getMoreResults() because it
>>            // implicitly closes any current ResultSet.
>>            // ON DB2 version 7.1 it is even harmful to close a ResultSet
explicitly.
>>            // _stmt.getMoreResults() will never return true anymore if
you do.

>>            break;
>>         }
>
> 360,368d365
> <             break;
> <          }
> <
> <          if (!_stmt.getMoreResults() && -1 == updateCount)
> <          {
> <             // There is no need to close result sets if we call
_stmt.getMoreResults() because it
> <             // implicitly closes any current ResultSet.
> <             // ON DB2 version 7.1 it is even harmful to close a
ResultSet explicitly.
> <             // _stmt.getMoreResults() will never return true anymore if
you do.


-------------------------------------------------------
SF.Net email is sponsored by:
Tame your development challenges with Apache's Geronimo App Server. Download
it for free - -and be entered to win a 42" plasma tv or your very own
Sony(tm)PSP.  Click here to play: http://sourceforge.net/geronimo.php
_______________________________________________
Squirrel-sql-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: multiple result sets problem

Rob Manning
Gupta, Abhishek wrote:

>Yes I found the problem soon after I finished working with stored procs.
>Sent a mail regarding this also. I am sorry for wasting your time.
>
>However I do think that getMoreResults should be called before calling
>getUpdateCount.
>
>I will try to make it work but I can only test on Sybase and MySQL.
>  
>
Abhishek,

Let me know when you get it working.  I have access to HSQLDB, Derby,
Firebird and
Oracle instances.  If you describe to me your test case and what I
should look for to confirm
that the patch works correctly, I'll be happy to test it against these
databases and report back
whether or not it works.

Rob Manning


CollabraSpace - Revolutionary Collaboration
Visit us at http://www.collabraspace.com
This message has been scanned for viruses by
ClamAV v0.83




-------------------------------------------------------
SF.Net email is sponsored by:
Tame your development challenges with Apache's Geronimo App Server. Download
it for free - -and be entered to win a 42" plasma tv or your very own
Sony(tm)PSP.  Click here to play: http://sourceforge.net/geronimo.php
_______________________________________________
Squirrel-sql-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

RE: multiple result sets problem

Gupta, Abhishek
In reply to this post by Gupta, Abhishek
The attached patch now works for me.

Rob,
The test case is as follows:
1. Execute any sql statement which returns a *single* result set. This
should be displayed properly by squirrel. This case is for regression.
2. Execute any sql statement which returns *multiple* result sets. I had
some stored procedures which returned multiple result sets. Squirrel should
open multiple result panels for each returned result set.

Just for info, the comments in this class also talk about the problem I
faced.

<quote>
If getMoreResults() returns false, we don't know if we have more results, we
only know that it isn't a result set. Since we called getUpdateCount()
before getMoreResults() because we would like to know the update count of
the first result, we might not be allowed to call getUpdateCount() again.
</quote>

I hope this will solve this problem.

Thanks
Abhishek

Here is the text of the patch (for a quick look).

302c303,322
<          int updateCount = _stmt.getUpdateCount();
---
>     int updateCount = -1;
>             boolean moreResults = true;
>             if (inFirstLoop && !firstResultIsResultSet)
>             {
>                 // in first loop if the first result is not a result set.
>                 updateCount = _stmt.getUpdateCount();
>             } else if (!inFirstLoop)
>             {
>                 // If not in first loop call getMoreResults before calling
getUpdateCount
>                 moreResults = _stmt.getMoreResults();
>                 updateCount = _stmt.getUpdateCount();
>             }
>             if (!moreResults && -1 == updateCount)
>             {
>                // There is no need to close result sets if we call
_stmt.getMoreResults() because it
>                // implicitly closes any current ResultSet.
>                // ON DB2 version 7.1 it is even harmful to close a
ResultSet explicitly.
>                // _stmt.getMoreResults() will never return true anymore if
you do.
>                break;
>             }
309c329
<          else if(false == inFirstLoop)
---
>          else if( !inFirstLoop )
363,370d382
<          if (!_stmt.getMoreResults() && -1 == updateCount)
<          {
<             // There is no need to close result sets if we call
_stmt.getMoreResults() because it
<             // implicitly closes any current ResultSet.
<             // ON DB2 version 7.1 it is even harmful to close a ResultSet
explicitly.
<             // _stmt.getMoreResults() will never return true anymore if
you do.
<             break;
<          }

-----Original Message-----
From: Rob Manning [mailto:[hidden email]]
Sent: Thursday, November 03, 2005 10:02 AM
To: Gupta, Abhishek
Cc: Gerd Wagner; [hidden email]
Subject: Re: [Squirrel-sql-users] multiple result sets problem

Gupta, Abhishek wrote:

>Yes I found the problem soon after I finished working with stored procs.
>Sent a mail regarding this also. I am sorry for wasting your time.
>
>However I do think that getMoreResults should be called before calling
>getUpdateCount.
>
>I will try to make it work but I can only test on Sybase and MySQL.
>  
>
Abhishek,

Let me know when you get it working.  I have access to HSQLDB, Derby,
Firebird and
Oracle instances.  If you describe to me your test case and what I
should look for to confirm
that the patch works correctly, I'll be happy to test it against these
databases and report back
whether or not it works.

Rob Manning


CollabraSpace - Revolutionary Collaboration
Visit us at http://www.collabraspace.com
This message has been scanned for viruses by
ClamAV v0.83


patch.txt (1K) Download Attachment
Loading...