Issue Details (XML | Word | Printable)

Key: CORE-1211
Type: New Feature New Feature
Status: Resolved Resolved
Resolution: Fixed
Priority: Minor Minor
Assignee: Vlad Khorsun
Reporter: Alexander Tyutik
Votes: 0
Watchers: 2
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Dynamic SQL (EXECUTE STATEMENT) with parameters

Created: 17/Apr/07 03:36 PM   Updated: 23/Feb/11 11:59 AM
Component/s: None
Affects Version/s: None
Fix Version/s: 2.5 Alpha 1

Issue Links:
Relate
 


 Description  « Hide
It would be nice to have ability to run dynamic SQL commands with parameters, for example

  ...
  DECLARE SQL VARCHAR(1000);
AS
  ...
  SQL = 'SELECT ... FROM MY_TABLE WHERE ID = :ID AND NAME = :NAME';
  
  FOR EXECUTE STATEMENT SQL PARAMETERS NAME = 'BLA_BLA_BLA', ID = :ID INTO ... DO
  ...

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Sean Leyne added a comment - 17/Apr/07 03:48 PM
Since the SQL variable (from the example provided) can be already be constructed as required, like:

SQL = 'SELECT ... FROM MY_TABLE WHERE ID = ' || ID || ' AND NAME = ''' || NAME ''';

Why is it necessary to extend the functionality to support parameters?

Alexander Tyutik added a comment - 17/Apr/07 05:04 PM
1) What will you do if your NAME will be a blob with more than than 65000 characters (o pair of long varchars)?
2) What will you do if your NAME will contain special characters or ' (quote) character?
3) If future it will be possible to cache such commands in commands pool (hope it will be in FB3)
4) I think there are can be some problems with blobs
5) In MS SQL we have problems with dates converted to string (server decode them other way). In FB i didn't see such problems, but it is not good practice...
6) Mayby some problems with round of floats and doubles - i don't know...

Alexander Peshkov added a comment - 18/Apr/07 01:50 AM
There is one serious problem with this feature - names of variables are already lost at the moment, when text of statement to execute becomes known. Therefore syntax with :NAME is very hard to implement. It will require adding variables' names to both BLR and Jrd::Node levels, which in most cases will be just a waste of memory. But it's quite possible to have native interbase style of parameters definition, using '?'. In your case it will look like:

EXECUTE STATEMENT 'SELECT ... FROM MY_TABLE WHERE ID = ? AND NAME = ?' (ID, 'BLA_BLA_BLA') INTO ... ;

And this is quite real for firebird 3.

Alexander Tyutik added a comment - 18/Apr/07 03:59 AM
Yes, your variant looks good too... Thanks!!!

Vlad Khorsun added a comment - 21/Apr/08 10:13 AM

Alexander Tyutik added a comment - 21/Apr/08 10:36 AM
Thanks for this.
But syntax is strange as for me:

   20 - if you use both <query_text> and <input_parameters> then you must
   21 enclose <query_text> into round brackets, for example :
   22 EXECUTE STATEMENT (:sql) (p1 := 'abc', p2 := :second_param) ...

i think that next syntax will be much more clear

EXECUTE STATEMENT :sql PARAMETERS ?p1 = 'abc', ?p2 = :second_param ...


Vlad Khorsun added a comment - 21/Apr/08 10:58 AM
I see no reason to make syntax of input parameters of EXECUTE STATEMENT different than of EXECUTE PROCEDURE or of EXECUTE BLOCK.

As for brackets around <query_text> - this is technical issue, without it btyacc parser produced big amount of "warnings" (shift/reduce and reduce/reduce conflicts). Perhaps it will be fixed before release, but i can't promise.

Alexander Tyutik added a comment - 21/Apr/08 11:19 AM
With brackets for parametrs, which i replaced by word PARAMETERS: its not a problem as for me.About brackets for statement i understood.
But operator ':=' can confuse people. But i'm sure that you choose best possible solution :-)