How to add user-defined functions to completion lists of Expression editor and SQL Text editor?

Follow

Comments

6 comments

  • Avatar
    Petr Veselý

    Hello Sergey. I would like to ask how the evaluation of my own functions is going to be done in this way? So far, I'm doing this by replacing the value instead of the functions before the query evaluation itself. But I suppose AQB can do it alone. Thanks for the reply.

  • Avatar
    Sergey Smagin (Product manager)

    Hello, Petr.

    I am sorry, but I don't clearly understand what values to what functions you are replacing instead. Please describe your idea in detail.

  • Avatar
    Petr Veselý

    Hello,

    I have my own ETL application which is possible to run with some parameters, i. e. var_year=2018. The SQL query needs to be evaluated based on the used variable. Now I do that in a way that I add the variable (var_year) as my UDF to be able to define the query in AQB and then I replace in a string representation the variable "var_year" into "2018". Then I send the query into the database. This works fine, but in that way it is not possible to define the source table, because AQB does not evaluate the value itself. Then it is not possible to continue creating the query in editor. That is why I am asking if it is possible to not only create the function as a parameter, but if AQB is able to evaluate the UDF.

    To demonstrate, it is a similar situation as if I create a UDF function in Excel, I am then able to write it into the formula bar but then the function itself is never evaluated. In this case the UDF is useless. 

    Thanks for any ideas. 

  • Avatar
    Sergey Smagin (Product manager)

    Dear Petr,

    Sorry once again but this looks vague to me again. I don't clearly understand what exactly has to be done to "evaluate the UDF", "evaluate the query based on the used variable". Perhaps, a short example of a query with parameter and probably the desired query output will be helpful to shed the light on this matter.

    Probably you already know that Active Query Builder supports parameters. Users can type something like "SELECT * FROM Table1 WHERE Feidl1 = @Param". Active Query Builder detects usage of parameters and puts all parameters used in the query to the QueryBuilder.Parameters collection which you can examine for presence (or absence) of the needed parameters and if they satisfy your requirements, pass the needed parameter values to the SqlCommand and execute the query. 

    In the next minor version, we are planning to improve this functionality by adding a list of allowed parameters and optionally fire a parsing error in case of the presence of unwanted parameters in the query or SQL expression. Also, we will show the list of allowed parameters in the SQL Text Editor suggestion window after typing one of the parameter-starting symbols.

    For some reasons you don't want to rely on the standard parameters functionality, but go your own way, do you?

  • Avatar
    Petr Veselý

    Hello,

    I try to explain that on a simple sample. Imagine loading from excel file and I want to have a possibility to decide from which sheet I want to load data via entered parameters.

    SQLquery: SELECT * FROM @source_table WHERE Feidl1 = @var_year

    If I have QueryBuilder and sqlTextEditor linked via QueryBuilder_SQLUpdated and sqlTextEditor_TextChanged this does not work as I cannot parse the parameter because QueryBuilder does not know the parameter and therefore does not load the schema. Then it is not possible to use the graphic editor (QueryBuilder). 

     

  • Avatar
    Sergey Smagin (Product manager)

    I understand now. 

    The problem is that the @var_year is a standard usage of parameters (supported by most of the database connectors: you can pass such query for execution to it). But the @source_table is actually a macro: it should be pre-processed before executing it. The current version of Active Query Builder doesn't support macros, and usage of parameters is not allowed in the FROM clause. Despite the fact that each programmer invents his own prefixes and suffixes to delimiter macros, your way of defining them conflicts with notation of parameters (as they understood by database connectors), so we hardly can do anything in this particular case.

    We haven't decided what notation for macros to support in the future versions, but probably we will have to support multiple notations (but not all of them)

    What I recommend is either changing the notation of macros used to represent tables so it doesn't conflict with the rules of SQL syntax, for example, you can quote them using acceptable identifier quotation symbols of your SQL syntax (enclose in double quotes or square brackets):

    SELECT * FROM [@source_table] WHERE Feidl1 = @var_year

    Or you can use the Alternate Names feature to substitute logical name "source_table" to a name of existing database object. (you will have to assign the alternate name "source_table" to one of real tables, so Active Query Builder will bind it to a real object and will generate the query text with real object name)

     

Please sign in to leave a comment.