cancel
Showing results for 
Search instead for 
Did you mean: 

cannot folder expression - data connector and PostgreSQL

I want connect to Postgres ODBC data source with direct query enabled. So I took this sample project, https://github.com/Microsoft/DataConnectors/tree/master/samples/ODBC/SqlODBC, point Config_DriverName variable to "PostgreSQL Unicode(x64)" (name of my psqlODBC driver, version 03.51), and set ConnectionString based on a working ODBC DSN.

 

I am using Power BI Desktop, Version 2.61.5192.601 64-bit (August 2018), and there is no problem to add one table to my Power BI report.

 

But whenever I put a column into a table (or any other viz), I got this error:

OLE DB or ODBC error: [Expression.Error] We couldn't fold the expression to the data source. Please try a simpler expression.

And it happened to any column, varchar, number or date.

 

My original post on github: https://github.com/Microsoft/DataConnectors/issues/143

Status: Accepted
Comments
Moderator

Hi @xsgao_pbi,

 

Which modification you made in SqlODBC sample project? Would you please share the whole project files with us? 

 

Best Regards,
Qiuyun Yu 

Moderator
Status changed to: Needs Info
 
Visitor
Moderator

Hi @xsgao_pbi,

 

Thanks for your project. I gave reported this issue internally: CRI 82027633. Will update here once I get any information. 

 

Best Regards,
Qiuyun Yu 

Moderator
Status changed to: Accepted
 
Moderator

Hi @xsgao_pbi,

 

I got information below: 

 

Folding and DQ of custom connectors is not something that we support. If the customer wants to participate in the folding stack, they have to implement Table.View, instead of wrapping PostgreSQL connector. 

 

Best Regards,
Qiuyun Yu 

Visitor

We got this fix by adding SQLGetTypeInfo and SQLColumns functions. PostgreSQL ODBC driver returns some duplicate types with same type names, and making them unique fixed the problem - no "cannot fold expression" errors now.

Visitor

Hi @xsgao_pbi,

 

I am running into the same problem that you encountered. I created a custom connector to connect to postgres ODBC datasource with direct query enabled.

I get the 'Cannot fold expression to the datasource" error. But once I add the SQLGetTypeInfo and SQLColumns functions, not even able to load data from a table in PBI desktop due to an error - "Expression error - Token literal expected"

Could you please elaborate on your solution. Should the SQLGetTypeInfo function be modified to return only unique types? And any changes required in SQLColumns function?

 

 

Occasional Visitor

Even i am running into the same problem.

I have tried using Table.Distinct to remove duplicate type name, but still getting the same problem.

 

@xsgao_pbi  can you please share how you got it working?

 

 

Visitor

Please refer to https://github.com/microsoft/DataConnectors/blob/master/docs/odbc.md, Section "SQLGetTypeInfo using a static table".

 

You don't have to support/map all PostgreSQL data types, only those you need to support.

Idea Statuses