Odbc AstVisitor Custom Connector

[zua]

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

[Anonymous]

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]

Thank you @Anonymous.

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