Re: [dtp-dev] delimiter and stored procedures

[Kirill Kranke <kranke.kirill@xxxxxxxxx>]

The most common I have seen in internet for MySQL is two of follows:
//            <- in SQL comments begin with --
@@       <- actually, some variables starts with @@

"GO\s*\r\n"    <- standard delimiters for MS SQL (at least at 2005 and 2008): 'GO' from new line, \s* - white-spaces.

Parsing begin and end requires at least counting them (one can have nested begin/end).

Setting delimiter is the easiest way, but, yeep, you are right - it is not the correct solution, because leads to ambiguity.

The most exiting is that in mysqladmin they use DELIMITER directive as well.

In fact, I think it is a problem of MySQL, why they do not use 'newline' symbol to separate statements inside of stored procedure as MS SQL does? Only to make parsing easier?

There are 3 possibilities, wait until MySQL corrected this (is it really possible?), make DTP SQL parser more clever (parse begin and end as well) or give user possibility to change delimiter.

What do you think about it, Roy?

Kirill

2009/12/23 Roy Ganor <roy@xxxxxxxx>

Well, it is not a delimiter settings problem. For example if you could add a delimiter, what delimiter would you add? “begin” ? “end”?

This would not  help the parser since the whole statement structure should be formulated in another way.

 

Roy

From: dtp-dev-bounces@xxxxxxxxxxx [mailto:dtp-dev-bounces@xxxxxxxxxxx] On Behalf Of Kirill Kranke
Sent: Wednesday, December 23, 2009 7:12 PM

To: DTP development mailing list
Subject: Re: [dtp-dev] delimiter and stored procedures

 

So!

Probably I had to be more specific from the beginning. Just tried to make question shorter.

The solution I see : give DTP user possibility to set SQL parser delimiter.

2009/12/23 Roy Ganor <roy@xxxxxxxx>

Yep, I see the problem.

The generic parser cuts statements according to a specified delimiter (can be any character or a word),

Maybe I did not search good enough. How can I set this delimiter character to something but ';'? I did not find!! Sorry if it is my fault.

but I couldn’t allocate a scope mode which is required by your example to nest the “begin”/”end” tokens.

I suggest you open a bug against the data tools SQLTools sub-project, or maybe someone else have another idea?

 

For example in phpMyAdmin when you execute MySQL SQL statement you can specify parser delimiter in GUI. The given example can be executed there like this:

DROP PROCEDURE IF EXISTS `ats`.`p_ins_rdata`@@
CREATE DEFINER = 'ATS'@'%'
PROCEDURE ats.p_ins_rdata(
  IN  _symbol        varchar(16),
  IN _value INT
)
BEGIN
  DECLARE object_id int ;
 
  ... some more code ...
END @@

 with Parser delimiter = '@@'.

Regards,

Roy

_______________________________________________
dtp-dev mailing list
dtp-dev@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/dtp-dev