Quantcast

using anonymous procedures

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

using anonymous procedures

Bram Gadeyne

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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: using anonymous procedures

Neville Rowe
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
------------------------------------------------------------------------------
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: using anonymous procedures

Neville Rowe
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

------------------------------------------------------------------------------
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
Loading...