Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Set SQLGetTypeInfo Searchable Property to SEARCHABLE for Custom Connector (MongoDB)

Hello I'm back again within a few hours of my last question. I am trying to create a custom Power BI connector to set up a DirectQuery connection from my local MongoDB environment to Microsoft Power BI. I created a custom connector using the sample ODBC connector from Microsoft (https://github.com/microsoft/DataConnectors/tree/master/samples). I am using the latest MongoDB ODBC Driver and MongoDB ODBC Driver for BI Connector. I created a System Data Source that is used as the input for my custom connector.

 

I am bringing in the following collection to Power BI for DirectQuery: tbllinktrafficdata. I try to create a Slicer with the LastUpdate (timestamp) field. When I filter on one LinkID (which is a field), I get the following error.

 

"ErrorMessage":"Data Type of column LinkID with searchable property UNSEARCHABLE should be SEARCHABLE or ALL_EXCEPT_LIKE. You can override the supported data types from ODBC driver using SQLGetTypeInfo."

 

I tried changing the SEARCHABLE column in the SqlGetTypeInfo function as instructed in the trace logs, but it does not to seem to work. Here is my function call in the connector code. OdbcSearchable is set to 3 which is pulled from the OdbcConstants.pqm file

 

        SQLGetTypeInfo = (types) => 
            let
                OdbcSearchable = ODBC[SQL_SEARCHABLE][SEARCHABLE],
                OdbcAllExceptLike = ODBC[SQL_SEARCHABLE][ALL_EXCEPT_LIKE],
                
                SetSearchable = (dataType) =>
                        OdbcSearchable, // SQL_SEARCHABLE = 3
                        //OdbcAllExceptLike, // SQL_ALL_EXCEPT_LIKE = 2
                // Outputting the entire table might be too large, and result in the value being truncated.
                // We can output a row at a time instead with Table.TransformRows()
                //rows = Table.TransformRows(types, each Diagnostics.LogValue("SQLGetTypeInfo " & _[TYPE_NAME], _)),
                //toTable = Table.FromRecords(rows),

                //Update SEARCHABLE column to SQL_SEARCHABLE
                Transform = Table.TransformColumns(types, { { "SEARCHABLE", SetSearchable } })
            in
                Transform, 

 

More trace logs if useful:

 

OdbcQuery/FoldingWarning {"Start":"2019-09-04T19:48:44.7842276Z","Action":"OdbcQuery/FoldingWarning","HostProcessId":"25156","Function Name":"VisitInvocation","ProductVersion":"2.72.5556.801 (19.08)","ActivityId":"ee0b51e3-2141-4a72-8f49-87a3e688314a","Process":"Microsoft.Mashup.Container.NetFX45","Pid":20256,"Tid":1,"Duration":"00:00:00.0000217"}
OdbcQuery/FoldingWarning {"Start":"2019-09-04T19:48:44.7842661Z","Action":"OdbcQuery/FoldingWarning","HostProcessId":"25156","Function Name":"VisitValueEqualsShared","ProductVersion":"2.72.5556.801 (19.08)","ActivityId":"ee0b51e3-2141-4a72-8f49-87a3e688314a","Process":"Microsoft.Mashup.Container.NetFX45","Pid":20256,"Tid":1,"Duration":"00:00:00.0000031"}
OdbcQuery/FoldingWarning {"Start":"2019-09-04T19:48:44.7842729Z","Action":"OdbcQuery/FoldingWarning","HostProcessId":"25156","Function Name":"VisitColumnAccess","ProductVersion":"2.72.5556.801 (19.08)","ActivityId":"ee0b51e3-2141-4a72-8f49-87a3e688314a","Process":"Microsoft.Mashup.Container.NetFX45","Pid":20256,"Tid":1,"Duration":"00:00:00.0000023"}
OdbcQuery/FoldingWarning {"Start":"2019-09-04T19:48:44.7842799Z","Action":"OdbcQuery/FoldingWarning","HostProcessId":"25156","ErrorMessage":"Data Type of column LinkID with searchable property UNSEARCHABLE should be SEARCHABLE or ALL_EXCEPT_LIKE. You can override the supported data types from ODBC driver using SQLGetTypeInfo.","ProductVersion":"2.72.5556.801 (19.08)","ActivityId":"ee0b51e3-2141-4a72-8f49-87a3e688314a","Process":"Microsoft.Mashup.Container.NetFX45","Pid":20256,"Tid":1,"Duration":"00:00:00.0002237"}
OdbcQueryDomain/ReportFoldingFailure {"Start":"2019-09-04T19:48:44.7848477Z","Action":"OdbcQueryDomain/ReportFoldingFailure","HostProcessId":"25156","Exception":"Exception:\r\nExceptionType: Microsoft.Mashup.Engine1.Runtime.FoldingFailureException, Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35\r\nMessage: Folding failed. Please take a look the information in the trace.\r\nStackTrace:\n   at Microsoft.Mashup.Engine1.Library.Odbc.OdbcQueryExpressionVisitor.VisitColumnAccess(ColumnAccessQueryExpression expression)\r\n   at Microsoft.Mashup.Engine1.Library.Odbc.OdbcQueryExpressionVisitor.VisitValueEqualsShared(InvocationQueryExpression expression, Boolean nullable)\r\n   at Microsoft.Mashup.Engine1.Library.Odbc.OdbcQueryExpressionVisitor.VisitInvocation(InvocationQueryExpression expression)\r\n   at Microsoft.Mashup.Engine1.Library.Odbc.OdbcQuery.SelectRows(FunctionValue function)\r\n\r\n\r\n","ProductVersion":"2.72.5556.801 (19.08)","ActivityId":"ee0b51e3-2141-4a72-8f49-87a3e688314a","Process":"Microsoft.Mashup.Container.NetFX45","Pid":20256,"Tid":1,"Duration":"00:00:00.0001043"}
1 REPLY 1
Anonymous
Not applicable

Running into nearly the same issue still!

https://stackoverflow.com/questions/64944320/3-issues-with-sqlgettypeinfo-for-custom-connector-power...


Does *not* look like a well documented feature within: 
https://docs.microsoft.com/en-us/power-query/odbc#overriding-sqlgettypeinfo

Specifically, I'm more having the issue that I dont even know what the type is within the driver so that I can create a mapping for it.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors