SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[11.1.125 Pro] - Q: Script DLL

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[11.1.125 Pro] - Q: Script DLL
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 1741

Post [11.1.125 Pro] - Q: Script DLL Reply with quote
Exporting DDL scripts from a MySQL server creates scripts that use $$$ as delimiters. Is there a way to override that?

There's also a comment section added to each file stating when it has been created by which version of SQL Assistant and the source. That's usually nice to have but I want to compare a large number of exported files for the (mostly) same objects from two different servers I cannot connect to at the same time and that comment section gets in the way when marking the differences. Is it possible to disable adding that comment to the exported DDL?
Fri Jun 19, 2020 4:01 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7141

Post Reply with quote
May I ask you to provide a screenshot. I'm not totally sure I'm following on the version header.

There is a setting for batch delimiters in in DB Options -> SQL Assistant -> MySQL -> Batch Delimiter. Please try that one.

The only place where I know the version is inserted is in the code formatting function. If you uncheck "(header)" rule, it will not insert that header. This rule is more of a placeholder, it can be customized if needed, or just deactivated. Yet, I'm not sure how it is used with DDL exporting. Maybe when I see your screenshot I will get a better idea.
Mon Jun 22, 2020 3:06 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 1741

Post Reply with quote
Sure. Here it is:



I've found the setting for batch delimiter in DB Options but I'm a bit puzzled. It adds those $$$ to the exported script but it seems to kinda defeat its own purpose. You see, the exported script runs happily in SQL Editor. But none of those will execute when I try to run them from eg. MySQL CLI or using Flyway for database deployment, because the batches might be but the statements are not terminated.

I can change that to ';' instead of '$$$' but that won't be without issues either.

I usually create my scripts for stored procedure deployment following a similar template:

Code:

DROP PROCEDURE IF EXISTS `usp_my_proc`;

DELIMITER $$$

CREATE PROCEDURE `usp_my_proc`()
f:BEGIN
    SELECT 1 AS a;
    SELECT 2 AS b; -- and other arbitrary statements here
END;
$$$

DELIMITER ;


This runs fine (almost) everywhere, including SQL Editor. But when creating a stored procedure from SQL Editor, it's much more convenient to omit those "ornaments" writing it as:
Code:

DROP PROCEDURE IF EXISTS `usp_my_proc`;
CREATE PROCEDURE `usp_my_proc`()
f:BEGIN
    SELECT 1 AS a;
    SELECT 2 AS b; -- and other arbitrary statements here
END;

simply because it works and it is much less ugly. Now, if I replace '$$$' with ';' so that it doesn't break scripts running elsewhere, the simplified version for creating stored procedure will no longer work in SQL Editor.

Perhaps configuring and adding (when necessary) both batch and statement delimiters would work. Using only one of them by adding '$$$' sometimes as a batch delimiter, other times as a statement delimiter creates confusion.
Thu Jun 25, 2020 9:17 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7141

Post Reply with quote
Thank you. It's now clear.

The header is hard-wired, it cannot be removed. And the primary purpose of generated scripts is of course to be "understood" by SQL Assistant, I guess it's not designed for cross application compatibility. Perhaps it's not super convenient to do that every time, but the $$$ batch delimiters can be easily replaced with something else after the script is generated. and the header can be removed too. For example, the built-in Edit\Advanced Text Processor utility can be used for that purpose. It can perform pretty sophisticated text changes, and I think it should be fairly easy to create a new transformation with a couple of transformation rules for what you described above - removing the header, and replacing batch delimiter. That transformation will be available in the Edit menu and can be applied to scripts in the editor.
Thu Jun 25, 2020 11:30 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 1741

Post Reply with quote
I see. It makes sense. The Advanced Text Processor seemed to be a good idea but I couldn't manage to make it work on batches of files and manually running it on 2000+ files is a no-go. Never mind, I've found a way to do it in another text editor using regexp. While it only takes a few relatively simple steps, it still tends to be tedious so I hope I won't have to do it very frequently.
Fri Jun 26, 2020 7:04 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.