New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Dynamic SQL (EXECUTE STATEMENT) with parameters [CORE1211] #1635
Comments
Commented by: Sean Leyne (seanleyne) 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? |
Commented by: Alexander Tyutik (tut) 1) What will you do if your NAME will be a blob with more than than 65000 characters (o pair of long varchars)? |
Modified by: @AlexPeshkoffassignee: Alexander Peshkov [ alexpeshkoff ] |
Commented by: @AlexPeshkoff 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. |
Commented by: Alexander Tyutik (tut) Yes, your variant looks good too... Thanks!!! |
Modified by: @AlexPeshkoffpriority: Major [ 3 ] => Minor [ 4 ] |
Modified by: @pcisarWorkflow: jira [ 11883 ] => Firebird [ 15547 ] |
Commented by: @hvlad Code is already comitted. Syntax may be found there : |
Modified by: @hvladassignee: Alexander Peshkov [ alexpeshkoff ] => Vlad Khorsun [ hvlad ] status: Open [ 1 ] => Resolved [ 5 ] resolution: Fixed [ 1 ] Fix Version: 2.5 Alpha 1 [ 10224 ] |
Commented by: Alexander Tyutik (tut) Thanks for this. 20 - if you use both <query_text> and <input_parameters> then you must i think that next syntax will be much more clear EXECUTE STATEMENT :sql PARAMETERS ?p1 = 'abc', ?p2 = :second_param ... |
Commented by: @hvlad 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. |
Commented by: Alexander Tyutik (tut) With brackets for parametrs, which i replaced by word PARAMETERS: its not a problem as for me.About brackets for statement i understood. |
Submitted by: Alexander Tyutik (tut)
Is related to CORE1853
Is related to QA299
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
...
The text was updated successfully, but these errors were encountered: