MySQL Bugs: #48777: Interactive Procedure SQL

Bug #48777	Interactive Procedure SQL
Submitted:	14 Nov 2009 19:45	Modified:	14 Nov 2009 19:46
Reporter:	Shawn Green	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S4 (Feature request)
Version:	any	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
This is a feature request to parse the same procedural SQL that we have in the STORED PROCEDURES  and SQL FUNCTION definitions within the context of an interactive user session. 

Many RDBM systems have procedural statements as part of their syntax. MySQL has procedural SQL but it is limited in scope to only operate within the limits of a defined stored procedure or function. 

This request is to make that SQL grammar available directly to the client as though each session was running within the context of a stored procedure. 

How to repeat:
none

Suggested fix:
Enhance the parser and session manager to allow for all of our procedural SQL to be executed outside of a stored procedure or function.

WL#3696 "Dynamic Compound Statements"

Example usage: to improve the mysql_upgrade procedure when upgrading more one version of mysql to another. You don't need to store the "upgrade functionality in the server" for a one-off run.

Example usage: A server "warm-up procedure" using init_script can be much more complex if you can do conditional behaviour. It allows you to use a common script to warm up various types of instance without just doing SELECT COUNT(*) FROM TABLE1; SELECT COUNT(*) FROM TABLE2; ....

Example usage: Application upgrades can use information in the database to selectively perform actions to upgrade the database schema ready for a newer version of the application. Doing that now just inside SQL is pretty hard.

I wonder how complex this actually is to implement. Parsing the syntax for stored procedures is already possible inside a stored procedure, so most of the work is done. It just requires that parser to recognise the code (and execute it) directly from the command line without the CREATE PROCEDURE ... "wrapper". An instead of parsing and storing it, the text should be parsed and executed immediately.

Example Usage: We have plenty of cron jobs which often need to do some complex things. The result is a mixture in our case of perl/DBI and standard SQL. The perl/DBI really is to allow us to do the conditional or more complex activities easily rather than writing a stored procedure and executing that.

Bug #54000 was marked as a duplicate of this one.

Bug #61895 was marked as a duplicate of this one.

Bug #62679 was marked as a duplicate of this one.

I need either 
alter table somename add column IF NOT EXISTS ...
or inline if on a session :
IF NOT colExists('declsoc','AjVmg') then alter table somename add column ...

Has this bug been fixed?

This would be very useful.