[Squirrel-sql-users] multi-statement SQL statements

[Neville Rowe]

I hope I'm not barking up the wrong tree here.

SquirrelSQL has always had problems for me with multiple statements - e.g.
select * into #tmp1 from table1
select * from #tmp1
where there may be (multiple) results sets interspersed with (multiple)
update counts.

Enclosed is a diff with the 1.2.b4 source code which seems to make it
work.... I hope this is useful.....

Neville Rowe

$ diff -w SQLExecuterTask.java SQLExecuterTask.orig
279,283c279,280
< int updateCount = -1;
< if (! rc)
< updateCount = _stmt.getUpdateCount();
< boolean loopAround = rc || (updateCount != -1);
< while (loopAround) {
---

if (rc)
{

288c285
< if (rc) {
---
292a290,291

while (rs != null)
{

324a324,330

if (_stmt.getMoreResults())
{
rs = _stmt.getResultSet();
}
else
{

338a345,346

}
}

346,350d353
< rc = _stmt.getMoreResults();
< if (! rc)
< updateCount = _stmt.getUpdateCount();
< loopAround = rc || (updateCount != -1);
< }
$




--------------------------------------------------------------------------------
The information contained herein is confidential and is intended solely for the
addressee. Access by any other party is unauthorised without the express
written permission of the sender. If you are not the intended recipient, please
contact the sender either via the company switchboard on [phone], or
via e-mail return. If you have received this e-mail in error or wish to read our
e-mail disclaimer statement and monitoring policy, please refer to
http://www.drkw.com/disc/email/ or contact the sender.
--------------------------------------------------------------------------------

[Colin Bell]

Date: Wednesday, November 19, 2003 11:15 pm
Subject: [Squirrel-sql-users] multi-statement SQL statements
I hope I'm not barking up the wrong tree here.
SquirrelSQL has always had problems for me with multiple
statements - e.g.
select * into #tmp1 from table1
select * from #tmp1
where there may be (multiple) results sets interspersed with
(multiple)
update counts.

Can you give me some details about the problems you're having? The following SQL

select * into #tmp1 from authors;
select * from #tmp1

when executed gives me the following msgs:

23 Rows Updated
Query 1 elapsed time (seconds) - Total: 0.031, SQL query: 0.031, Building output: 0
Query 2 elapsed time (seconds) - Total: 0.594, SQL query: 0.031, Building output: 0.563

which seems to be correct to me.

This is using Microsoft SQL server 7.0

--
Colin Bell
http://squirrel-sql.sf.net

[Rowe, Neville]

Ah

You are using a ';' delimiter between the two statements which I hadn't
realised was necessary for Squirrel SQL - if you do it without the ';' so
Squirrel SQL treats the two statements as a single statement for the
database (which is valid for both Sybase and MS SQL Server, not sure about
anything else) then you get the update count but not the results set.

You also get the problem if you execute a stored procedure which returns an
update count before a results set, so if there is an SP which runs those two
sql statements and you run that sp, again you only get the update count
returned.

An example SP might be (MS SQL Server)

create procedure dbo.Get_Test as
begin
select
convert(int, 1) as val1,
convert(int, 2) as val2
into #tmp1
select * from #tmp1
drop table #tmp1
end

if you then run in Squirrel (note the exec is optional)
exec dbo.Get_Test

Then you get
1 Rows Updated
Query 1 elapsed time (seconds) - Total: 0.151, SQL query: 0.151,
Building output: 0

Which misses the results table. The issue being that the code in
SQLExecutorTask assumes that one execute on the database will return one (or
more) results sets or one update count (I think - I haven't got the source
to hand). This isn't necessarily true - one jdbc execute call can return
multiple update counts and results sets in any order. There is some
discussion on how to process this in the javadoc for java.sql.Statement
under the execute method and the getMoreResults method.

N.

-----Original Message-----
From: Colin Bell [mailto:***@telstra.com]
Sent: 20 November 2003 00:10
To: squirrel-sql-***@lists.sourceforge.net
Subject: Re: [Squirrel-sql-users] multi-statement SQL statements

Date: Wednesday, November 19, 2003 11:15 pm
Subject: [Squirrel-sql-users] multi-statement SQL statements
I hope I'm not barking up the wrong tree here.
SquirrelSQL has always had problems for me with multiple
statements - e.g.
select * into #tmp1 from table1
select * from #tmp1
where there may be (multiple) results sets interspersed with
(multiple)
update counts.

Can you give me some details about the problems you're having? The following
SQL

select * into #tmp1 from authors;
select * from #tmp1

when executed gives me the following msgs:

23 Rows Updated
Query 1 elapsed time (seconds) - Total: 0.031, SQL query: 0.031, Building
output: 0
Query 2 elapsed time (seconds) - Total: 0.594, SQL query: 0.031, Building
output: 0.563

which seems to be correct to me.

This is using Microsoft SQL server 7.0

--
Colin Bell
http://squirrel-sql.sf.net





-------------------------------------------------------
This SF.net email is sponsored by: SF.net Giveback Program.
Does SourceForge.net help you be more productive? Does it
help you create better code? SHARE THE LOVE, and help us help
YOU! Click Here: http://sourceforge.net/donate/
_______________________________________________
Squirrel-sql-users mailing list
Squirrel-sql-***@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users


--------------------------------------------------------------------------------
The information contained herein is confidential and is intended solely for the
addressee. Access by any other party is unauthorised without the express
written permission of the sender. If you are not the intended recipient, please
contact the sender either via the company switchboard on [phone], or
via e-mail return. If you have received this e-mail in error or wish to read our
e-mail disclaimer statement and monitoring policy, please refer to
http://www.drkw.com/disc/email/ or contact the sender.
--------------------------------------------------------------------------------

[Colin Bell]

Hi Nevile,

I'll take a proper look at your patch some time this week, but at first
look I think I'll be applying it.

Thanks
--
Colin Bell
http://squirrel-sql.sf.net

Post by Rowe, Neville
Ah
You are using a ';' delimiter between the two statements which I hadn't
realised was necessary for Squirrel SQL - if you do it without the ';' so
Squirrel SQL treats the two statements as a single statement for the
database (which is valid for both Sybase and MS SQL Server, not sure about
anything else) then you get the update count but not the results set.
You also get the problem if you execute a stored procedure which returns an
update count before a results set, so if there is an SP which runs those two
sql statements and you run that sp, again you only get the update count
returned.
An example SP might be (MS SQL Server)
create procedure dbo.Get_Test as
begin
select
convert(int, 1) as val1,
convert(int, 2) as val2
into #tmp1
select * from #tmp1
drop table #tmp1
end
if you then run in Squirrel (note the exec is optional)
exec dbo.Get_Test
Then you get
1 Rows Updated
Query 1 elapsed time (seconds) - Total: 0.151, SQL query: 0.151,
Building output: 0
Which misses the results table. The issue being that the code in
SQLExecutorTask assumes that one execute on the database will return one (or
more) results sets or one update count (I think - I haven't got the source
to hand). This isn't necessarily true - one jdbc execute call can return
multiple update counts and results sets in any order. There is some
discussion on how to process this in the javadoc for java.sql.Statement
under the execute method and the getMoreResults method.
N.
-----Original Message-----
Sent: 20 November 2003 00:10
Subject: Re: [Squirrel-sql-users] multi-statement SQL statements

Date: Wednesday, November 19, 2003 11:15 pm
Subject: [Squirrel-sql-users] multi-statement SQL statements
I hope I'm not barking up the wrong tree here.
SquirrelSQL has always had problems for me with multiple
statements - e.g.
select * into #tmp1 from table1
select * from #tmp1
where there may be (multiple) results sets interspersed with
(multiple)
update counts.

Can you give me some details about the problems you're having? The following
SQL
select * into #tmp1 from authors;
select * from #tmp1
23 Rows Updated
Query 1 elapsed time (seconds) - Total: 0.031, SQL query: 0.031, Building
output: 0
Query 2 elapsed time (seconds) - Total: 0.594, SQL query: 0.031, Building
output: 0.563
which seems to be correct to me.
This is using Microsoft SQL server 7.0
--
Colin Bell
http://squirrel-sql.sf.net