Forum Discussion

zua's avatar
zua
Frequent Visitor
2 years ago

Odbc AstVisitor Custom Connector

Hello, 

 

I want to develop a connector to access a database with ODBC. 

This database has got some special features (like input parameters), that require special SQL commands.

 

I saw that the astVisitor property might be appropriate for this purpose, therefore I want to override this property. 

Does anyone has got some experience with this? Is this appropriate to modify a SQL statement (for example to append a text after select * from schema.table1 {here should be the additional statement};)?

 

And how can an astVisitor function been called when it should be applied to a table/SQL Statement?

 

Thank you!! 

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi zua ,
    Based on your description, first, AstVisitor contains one or more rewritten records for controlling SQL query generation. The most common use of this field is to provide logic to generate LIMIT/OFFSET clauses for drivers that do not support TOP. This property can be overridden to append or modify parts of the SQL statement.
    To accomplish this, you can define a function and then call it in the join section. You can try this sample:

    let
    MyAstVisitor = (ast) =>
        let
            // Modify the AST here
            modifiedAst = ast & " ADDITIONAL SQL STATEMENT"
        in
            modifiedAst,
    Source = Odbc.DataSource("YourConnectionString", [AstVisitor = MyAstVisitor])
in
    Source

    To apply the function to a table or SQL statement, it is usually included in the option record when it is called. This ensures that any SQL generated by Power Query will be handled by the custom function.

    You can also check out these documents for more information
    Parameters for the Odbc.DataSource function - Power Query | Microsoft Learn

    Enabling DirectQuery for an ODBC-based Power Query connector - Power Query | Microsoft Learn

    Power Query ODBC connector - Power Query | Microsoft Learn

     

    Best regards,
    Albert He

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

    • zua's avatar
      zua
      Frequent Visitor
      2 years ago

      Thank you Anonymous.

      Can you give me an example for applying the custom function to a table and SQL statement?