Details
-
Task
-
Status: Closed (View Workflow)
-
Blocker
-
Resolution: Fixed
-
None
Description
Current — 10.1.1 — implementation of OUT parameters in PREPARE is as follows:
PREPARE .... "SELECT ... INTO ?, ?, ..."; |
EXECUTE .... USING @var1, @var2, ... |
It is non-standard. And it's not strictly necessary, as there are easy workarounds.
Standard syntax is
PREPARE .... "SELECT ... "; |
EXECUTE ... INTO @var1, @var2, ... |
The workaround is:
PREPARE .... "SELECT ... INTO @var1, @var2, ..."; |
EXECUTE .... |
We should consider changing the syntax to be standard or removing this feature completely.
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Status | In Progress [ 3 ] | Stalled [ 10000 ] |
| Component/s | Prepared Statements [ 10804 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Workflow | MariaDB v2 [ 57107 ] | MariaDB v3 [ 64100 ] |
@serg, are we going to see a return of this? Or something similar?
Parameterised stored procedure SQL would be very VERY useful for dynamic table names, infile/outfile names, etc.
it's not about parameterised stored procedure SQL, it's output parameters in PREPARE, dynamic SQL.
Hi @serg, yes I understand.I see there were some great changes made in MDEV-10866.
This is roughly what I mean, instead of create extremely large concat statements for analytics work loads to generate new tables and outfiles.
e.g:
SET @source_table=source_table_name;
|
SET @new_table_name=CONCAT("source_table_name", CURDATE());
|
|
|
PREPARE stmt FROM "CREATE TABLE @new_table AS SELECT ... FROM @source_table";
|
Also for outfiles!
PREPARE out_stmt FROM "SELECT * FROM @table INTO OUTFILE '@outfile_name' @TERMINATION_PARMATERS;" |
Does that make sense? Our analytics sql is hundreds of lines long, I generally wrap SQL with something like python to generate data instead of using stored procedures.
It makes sense. But the problem is that a parameter — OUT or IN, in PREPARE or in the protocol level prepared statements, even in stored procedures — it's an expression and can be used anywhere where an expression is allowed. In particular, table names cannot be parameters.
| Workflow | MariaDB v3 [ 64100 ] | MariaDB v4 [ 132445 ] |
removed completely