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

[Rowe, N. <Nev...@dr...>]

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:col...@te...]
Sent: 20 November 2003 00:10
To: squ...@li...
Subject: Re: [Squirrel-sql-users] multi-statement SQL statements



> From: Neville Rowe <Nev...@dr...>
> 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
Squ...@li...
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 +44 (0)20 7623 8000, 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.
--------------------------------------------------------------------------------