Toad World® Forums

Problem on MySQL SQL Script Generated by Generate DDL Script Function


#1

Hi

I am experiencing a problem on using SQL Script that generated by Generate DDL Script Function for MySQL. The Create table and indexing sql script seems to work fine. Unfortunately, I cannot run the generated SQL Script for the stored procedure on Mysql Query Browser to create the stored procedure.

I am wondering if it is a bug? or I did it wrong? or I should not use MySQL Query browser to create the stored procedure.

Please let me know if you want me to explain more.

Many thanks

Chi


#2

Hi

I believe you can run scripts using MySQL Query browser (currently don’t have installed on my PC). All you have to do is to click New Script instead of New Query item from the menu.

You can also try another product. What about Toad for MySQL (freeware version) ?

Regards,

Vaclav


#3

Hi vfrolik

I have tried you suggestion to use New Script instead of New Query,
It does not seems to work : (
I have receive the following error message.
Script line: 1 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘’ at line 7
Script line: 8 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘END’ at line 1

Please let me know if you want me to explain more.

Many thanks

Chi


#4

Hi Chi,

it might be a problem of used terminator. Just guessing… Can you post the procedure here, or send it to modeling@quest.com, please?

Regards,

Vaclav


#5

Hi Vaclav

Here it is one of the Stored procedure that generate by Reverse Engineering Wizard

CREATE DEFINER = root@localhost PROCEDURE Update_LoginTIme
(IN varEmail varchar(255))
SQL SECURITY DEFINER
BEGIN
UPDATE users_tbl
SET lastLogin = NOW()
WHERE email=varEmail;
END;

I guess the problem is SQL SECURITY DEFINER.

Many thanks

Chi


#6

Hi Chi,

it seems to be a problem of the terminator. My co-worker Mario changed the terminator to something else and then he was able to execute the procedure (using different tool, not the Query Browser application.)

Regards,

Vaclav


#7

Hi Vaclav,

I would be grateful if you can get it work on MySQL Query Browser as well, as this is the the Tools that release by MySQL, Just like MS SQL management Studio release by Microsoft.

If this is the case of Terminate, does it mean I have to use two different Terminator in order to run the Create Table Script and Stored Procedure script?

Many thanks

Chi


#8

Hi Chi,

look at the following article:
http://www.databasedesign-resource.com/mysql-stored-procedures.html

It shows that stored procedures can be executed via Query Browser. It is only necessary to use the DELIMITER command in the body of stored procedures.

Regards,

Vaclav


#9

Hi Vaclav

Thanks for the information. Would it be possible for TDM to include the DELIMITER Command in the DDL script when generating the MySQL’s stored procedure? In this case, it should solve the problem I have.

Many thanks

Chi


#10

Hi Chi,

you will have to use the same terminator in the body of your stored procedures (in the example above, it’s $$) - that’s why I believe it would be just a partial solution. BTW: people might use various terminators.

I think it’s a nice example for customization - you can write a script that will generate one line with the DELIMITER $$ command above the definition of stored procedures.

Regards,

Vaclav


#11

Hi Chi,

my apologies… you don’t have to use the terminator in your stored procedures. TDM3 adds terminators automatically (In version 2 you had to define terminators, it made me confused, sorry.).

So, you can do the following. Write the DELIMITER $$ command to the Before Script section. Click Model | Model Properties, and write the DELIMITER $$ command to the Before Script tab.

Then try to execute the SQL code, on tab What to Generate (first tab in version 3.1 BETA), select the Before Script item, on tab Detail Settings (second tab in version 3.1 BETA) write the $$ into the Terminator field.

Regards,

Vaclav