SQuirreL SQL Client › Users

using anonymous procedures

Classic

List

Threaded

3 messages Options

Bram Gadeyne

using anonymous procedures

Hi,

Is there an option in Squirrel so that it would ignore the statement separator ; for code that is between “begin” and “end” statements?

I’d like to execute something like this code below against a Teiid JDBC connection but it fails.

begin

create local temporary table tmp_curresult(

res integer

);

LOOP ON (SELECT 1 as col1 union select 2) AS cur1

BEGIN

insert into tmp_curresult(res)

values(cur1.col1);

END

select * from tmp_curresult;

end;

I can see that squirrel only sends this part at first:

begin

create local temporary table tmp_curresult(

res integer

)

With kind regards

Bram Gadeyne

------------------------------------------------------------------------------
Find and fix application performance issues faster with Applications Manager
Applications Manager provides deep performance insights into multiple tiers of
your business applications. It resolves application problems quickly and
reduces your MTTR. Get your free trial!
https://ad.doubleclick.net/ddm/clk/302982198;130105516;z
_______________________________________________
Squirrel-sql-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users

Neville Rowe

Re: using anonymous procedures

Yes, sort of.
Under the db-specific preferences there is the option to distinguish between statement separators and procedure separators, the idea is within the procedure you use the db-native statement separator and around the proc you use something different.
This tends to be set by the db-specific plugin however - it is definitely supported by the Oracle, Sybase and MS SQL plugins. I am not aware of a Teiid plugin though, we would probably need one to make it work.
N.

> On 26 Apr 2016, at 13:31, Bram Gadeyne <[hidden email]> wrote:
>
>
> Hi,
>
> Is there an option in Squirrel so that it would ignore the statement separator ; for code that is between “begin” and “end” statements?
>
> I’d like to execute something like this code below against a Teiid JDBC connection but it fails.
>
> begin
> create local temporary table tmp_curresult(
> res integer
> );
> LOOP ON (SELECT 1 as col1 union select 2) AS cur1
> BEGIN
> insert into tmp_curresult(res)
> values(cur1.col1);
> END
> select * from tmp_curresult;
> end;
>
> I can see that squirrel only sends this part at first:
>
> begin
> create local temporary table tmp_curresult(
> res integer
> )
>
> With kind regards
>
> --
> Bram Gadeyne
> ------------------------------------------------------------------------------
> Find and fix application performance issues faster with Applications Manager
> Applications Manager provides deep performance insights into multiple tiers of
> your business applications. It resolves application problems quickly and
> reduces your MTTR. Get your free trial!
> https://ad.doubleclick.net/ddm/clk/302982198;130105516;z
> _______________________________________________
> Squirrel-sql-users mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users

Neville Rowe

Re: using anonymous procedures

Hi Bram,

Right, having checked, the default statement separator can be changed in the 'New Session Preferences' dialog - the statement separator property on the SQL tab. Therefore the quick solution to the below is to change the statement separator to something different. Squirrel will then ignore the semi-colons in the block and pass that as one to the db.

However I've been unhappy with how Squirrel handles this for a while, so I'm about to check in some code for the default SQL parser which understands begin / end. That should mean Squirrel doesn't treat semi-colon within a block as a statement terminator. It needs some quite significant testing and others have to review it so might take some time to appear in a build. It certainly won't be part of the 3.7.1 release.

Neville

> From: [hidden email]

> Subject: Re: [Squirrel-sql-users] using anonymous procedures
> Date: Tue, 26 Apr 2016 14:21:05 +0100
> To: [hidden email]
> CC: [hidden email]
>
> Yes, sort of.
> Under the db-specific preferences there is the option to distinguish between statement separators and procedure separators, the idea is within the procedure you use the db-native statement separator and around the proc you use something different.
> This tends to be set by the db-specific plugin however - it is definitely supported by the Oracle, Sybase and MS SQL plugins. I am not aware of a Teiid plugin though, we would probably need one to make it work.
> N.
>
> > On 26 Apr 2016, at 13:31, Bram Gadeyne <[hidden email]> wrote:
> >
> >
> > Hi,
> >
> > Is there an option in Squirrel so that it would ignore the statement separator ; for code that is between “begin” and “end” statements?
> >
> > I’d like to execute something like this code below against a Teiid JDBC connection but it fails.
> >
> > begin
> > create local temporary table tmp_curresult(
> > res integer
> > );
> > LOOP ON (SELECT 1 as col1 union select 2) AS cur1
> > BEGIN
> > insert into tmp_curresult(res)
> > values(cur1.col1);
> > END
> > select * from tmp_curresult;
> > end;
> >
> > I can see that squirrel only sends this part at first:
> >
> > begin
> > create local temporary table tmp_curresult(
> > res integer
> > )
> >
> > With kind regards
> >
> > --
> > Bram Gadeyne
> > ------------------------------------------------------------------------------
> > Find and fix application performance issues faster with Applications Manager
> > Applications Manager provides deep performance insights into multiple tiers of
> > your business applications. It resolves application problems quickly and
> > reduces your MTTR. Get your free trial!
> > https://ad.doubleclick.net/ddm/clk/302982198;130105516;z
> > _______________________________________________
> > Squirrel-sql-users mailing list
> > [hidden email]
> > https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users