PREPARE statement Catch22 IN Stored Proc editing and debug

[KiwiJem]

Hi Support
I am regularly trying to generate tens of thousands of new rows in a table each containing a unique serial number generated from within a stored procedure.

Using repeated INSERTS in a loop as I do at the moment is slow, so I am trying to use a multi-row INSERT, where I first create an SQL string containing the query with 1000 rows of data at a time, so then I need to use the PREPARE and EXECUTE statements to actually execute the insert statement from within the stored procedure.

I have no trouble with the multi-row insert from a command line.
MySQL is at revision 5.0.32.
Prepared statements in stored procedures have been supported since revision 5.0.13.
I am using dbForge Studio for MySQL version 3.50.305 - the very latest at present.

I have boiled my problem down to a few lines of "play" code as follows:

Code: Select all

CREATE DEFINER = 'root'@'%'
PROCEDURE pippa.PlayExecute()
BEGIN
  DECLARE tui TEXT;

  SET @tui = 'INSERT INTO Play (PlayNum) VALUES (30), (40);';
  PREPARE xh1 FROM @tui;
  EXECUTE xh1;
END

Here is my problem as I understand it:

• The MySQL statement "PREPARE statement_name FROM user_variable" requires a user variable containing the SQL statement to be executed.
  
  From an SQL command line, user variables are all undeclared and prefixed with an @ character. Within stored procedures, they are DECLARED, and should NOT be preceeded by an @
  
  dbForge Studio reports a syntax error if I do not prefix my variable with an @, in the line PREPARE xh1 FROM @tui
  
  I cannot DECLARE @tui - that is a syntax error
  If I leave out the DECLARE line, then the variables @tui below are undeclared according to dbForge, also wrong.
  
  When executing in the debugger, @tui and tui are two separate variables, and @tui is ALWAYS out of scope
  
  I have tried the SET line as SET tui=... rather than SET @tui=... , but the @tui value is then NULL
  
  When I execute the code as shown (because that is the only format in which it will "pass" syntax checking and save) with or without debug the variables tui and @tui sometimes have the same value to MySQL, mostly not.
  
  And, yes, I know I should be using the same variable name everywhere, but dbForge won't let me.

Can you please tell me how I should be doing this? I suspect that it is dbForge which is wrongly insisting I must put an @ in front of the PREPARE user_variable, but I can't test this theory, as dbForge is my only editor and debugger for stored procedures. Why should I even consider any other?

Help please?

I am impressed by the speed of turnaround on the latest release, and thank you for including a release log on the site, but while we wait for the next release, can you please suggest a work-around?

Many thanks.
Jem

[Elias]

Hello.

dbForge syntax check is based on the knowledge of the MySql language. Correct grammar of PREPARE statement is

Code: Select all

PREPARE stmt_name FROM preparable_stmt

where preparable_stmt is a SESSION VARIABLE OR a string literal.

"@tui" is a session variable, and "tui" is a procedure variable that cannot be used in the PREPARE statement.

In MySql three types of variables are used (not including parameters). They are the following:

1. Server variables. Their names are prefixed by @@. E.g. @@sql_mode. The scope of the variable is global. The instance exist through the server instance.

2. Session variables. Prefixed by @. The scope is session (connection). Variable exists while the connection is open.

3. Routine variables. Can be declared in procedures, functions, triggers and events. Have strong type.

As I said, in prepare statement only session variable can be used. The procedure cannot be saved (created) with such a statement

Code: Select all

PREPARE xh1 FROM tui

MySql reports an error so does dbForge Studio for MySql.

Now about evaluating variables while debugging. Unfortunatly dbForge Studio for MySql doesn't evaluate session variables. We added this functionality to the next version plan.

You can evaluate the session variable using a trick. Declare a variable, and then assign it by the session variable. Now you can evaluate a variable 'tui'

Code: Select all

DECLARE tui TEXT;
...
SET @tui = ...;
SET tui = @tui;
...

[KiwiJem]

Elias,
I am so grateful for your explanation. I know it is not your job to teach me things I do understand correctly, so again, thank you. I have not had much success in finding information which explains stored procedures and triggers fully in the terms you have, so that is excellent. And I am happy re the workaround. Thank you for your precious time, and for not flaming me as I deserve.
Regards

[Elias]

Not at all. We are always glad to help our users. Check our blog for useful articles and feel free to ask any questions about MySQL database development and management here!

And you also help us great with feedback and bug reports.