[11.1.125 Pro] - Q: Script DLL

[11.1.125 Pro] - Q: Script DLL

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?

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.

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.

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.

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.