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
Triage: Needs Triage: D5 (Feature request) / R3 (Medium) / E3 (Medium)

[14 Nov 2009 19:45] Shawn Green
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.
[14 Nov 2009 20:40] Konstantin Osipov
WL#3696 "Dynamic Compound Statements"
[15 Nov 2009 11:44] Simon Mudd
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.
[15 Nov 2009 11:47] Simon Mudd
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.
[15 Nov 2009 11:57] Simon Mudd
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.
[27 May 2010 3:33] Valeriy Kravchuk
Bug #54000 was marked as a duplicate of this one.
[19 Jul 2011 11:46] Valeriy Kravchuk
Bug #61895 was marked as a duplicate of this one.
[11 Oct 2011 12:09] Valeriy Kravchuk
Bug #62679 was marked as a duplicate of this one.
[16 Mar 2012 18:27] Mircea LUTIC
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 ...