Skip to content
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

Closed
firebird-automations opened this issue Apr 17, 2007 · 14 comments
Closed

Comments

@firebird-automations
Copy link
Collaborator

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
...

@firebird-automations
Copy link
Collaborator Author

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?

@firebird-automations
Copy link
Collaborator Author

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)?
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...

@firebird-automations
Copy link
Collaborator Author

Modified by: @AlexPeshkoff

assignee: Alexander Peshkov [ alexpeshkoff ]

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: Alexander Tyutik (tut)

Yes, your variant looks good too... Thanks!!!

@firebird-automations
Copy link
Collaborator Author

Modified by: @AlexPeshkoff

priority: Major [ 3 ] => Minor [ 4 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Workflow: jira [ 11883 ] => Firebird [ 15547 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

Code is already comitted. Syntax may be found there :
http://firebird.cvs.sourceforge.net/firebird/firebird2/doc/sql.extensions/README.execute_statement2?view=markup

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

assignee: Alexander Peshkov [ alexpeshkoff ] => Vlad Khorsun [ hvlad ]

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 2.5 Alpha 1 [ 10224 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

Link: This issue is related to CORE1853 [ CORE1853 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Alexander Tyutik (tut)

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 ...

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

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.
But operator ':=' can confuse people. But i'm sure that you choose best possible solution :-)

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA299 [ QA299 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants