cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
cwc52 Frequent Visitor
Frequent Visitor

MongoDB Custom Connector Issue with DirectQuery - Failed to Convert BIGINT to INTEGER

I am setting 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 field. When I filter on one LinkID (which is a field), I get the following error.

"ErrorMessage":"Failed to convert type bigint to integer, expression `LinkID` to 1506780."

"ErrorMessage":"This ODBC driver doesn't support SQL_FN_CVT_CONVERT or SQL_FN_CVT_CAST. You can override this by using SqlGetInfo for SQL_CONVERT_FUNCTIONS."


It looks like I'm seeing a conversion error, but I'm not sure how to resolve the issue within the connector code.

Here is my SqlGetInfo function:

 

SQLGetInfo = [
// place custom overrides here
SQL_SQL92_PREDICATES = ODBC[SQL_SP][All],
SQL_AGGREGATE_FUNCTIONS = ODBC[SQL_AF][All],
SQL_CONVERT_FUNCTIONS = ODBC[SQL_CVT][BIGINT]
],

//this is from OdbcConstants file that is called in Power Query file
SQL_CVT = [
BIGINT = 0x00004000
]

 

Here is more of the Power BI Trace Log:

 

SqlTranslator/SqlParser/Parse {"Start":"2019-09-

03T19:55:54.6245555Z","Action":"SqlTranslator/SqlParser/Parse","HostProcessId":"12228","SQL":"\nSELECT MAX([t29].[LastUpdate])\n AS [a0],MIN([t29].[LastUpdate])\n AS [a1]\nFROM \n(\n(SELECT * FROM [tbllinktrafficdata (2)])\n)\n AS [t29]\nWHERE \n(\n[t29].[LinkID] = 1506780\n)\n ","ProductVersion":"2.72.5556.801 (19.08)","ActivityId":"3ef90ded-79fc-4726-83bb-c98d8ccf5be7","Process":"Microsoft.Mashup.Container.NetFX45","Pid":22304,"Tid":1,"Duration":"00:00:00.1356012"}
SqlExpressionTranslator/Translate {"Start":"2019-09-03T19:55:54.7606768Z","Action":"SqlExpressionTranslator/Translate","HostProcessId":"12228","IsRecognized":"True","Result":"(environment) => Table.RenameColumns(let\r\n t1133 = Table.RenameColumns(Table.PrefixColumns(environment[#\"tbllinktrafficdata (2)\"], \"tbllinktrafficdata (2)\"), {{\"tbllinktrafficdata (2)._id\", \"t29._id\"}, {\"tbllinktrafficdata (2).AgencyID\", \"t29.AgencyID\"}, {\"tbllinktrafficdata (2).DataType\", \"t29.DataType\"}, {\"tbllinktrafficdata (2).LastUpdate\", \"t29.LastUpdate\"}, {\"tbllinktrafficdata (2).LinkID\", \"t29.LinkID\"}, {\"tbllinktrafficdata (2).Occupancy\", \"t29.Occupancy\"}, {\"tbllinktrafficdata (2).Speed\", \"t29.Speed\"}, {\"tbllinktrafficdata (2).TravelTime\", \"t29.TravelTime\"}, {\"tbllinktrafficdata (2).Volume\", \"t29.Volume\"}}),\r\n t1135 = Table.SelectRows(t1133, (t1134) => Value.NullableEquals(t1134[t29.LinkID], 1506780)),\r\n t1140 = Table.Group(t1135, {}, {{\"a0\", (t1136) => List.Max(t1136[t29.LastUpdate])}, {\"a1\", (t1137) => List.Min(t1137[t29.LastUpdate])}}),\r\n t1141 = Table.SelectColumns(t1140, {\"a0\", \"a1\"})\r\nin\r\n t1141, {{\"a0\", \"a0\"}, {\"a1\", \"a1\"}})","ProductVersion":"2.72.5556.801 (19.08)","ActivityId":"3ef90ded-79fc-4726-83bb-c98d8ccf5be7","Process":"Microsoft.Mashup.Container.NetFX45","Pid":22304,"Tid":1,"Duration":"00:00:00.1279102"}
SimpleDocumentEvaluator/GetResult/Evaluate {"Start":"2019-09-03T19:55:54.6208186Z","Action":"SimpleDocumentEvaluator/GetResult/Evaluate","HostProcessId":"12228","ProductVersion":"2.72.5556.801 (19.08)","ActivityId":"3ef90ded-79fc-4726-83bb-c98d8ccf5be7","Process":"Microsoft.Mashup.Container.NetFX45","Pid":22304,"Tid":1,"Duration":"00:00:00.2835266"}
OdbcQuery/FoldingWarning {"Start":"2019-09-03T19:55:54.9495926Z","Action":"OdbcQuery/FoldingWarning","HostProcessId":"12228","Function Name":"VisitInvocation","ProductVersion":"2.72.5556.801 (19.08)","ActivityId":"3ef90ded-79fc-4726-83bb-c98d8ccf5be7","Process":"Microsoft.Mashup.Container.NetFX45","Pid":22304,"Tid":1,"Duration":"00:00:00.0000162"}
OdbcQuery/FoldingWarning {"Start":"2019-09-03T19:55:54.9498087Z","Action":"OdbcQuery/FoldingWarning","HostProcessId":"12228","Function Name":"VisitValueEqualsShared","ProductVersion":"2.72.5556.801 (19.08)","ActivityId":"3ef90ded-79fc-4726-83bb-c98d8ccf5be7","Process":"Microsoft.Mashup.Container.NetFX45","Pid":22304,"Tid":1,"Duration":"00:00:00.0000058"}
OdbcQuery/FoldingWarning {"Start":"2019-09-03T19:55:54.9498281Z","Action":"OdbcQuery/FoldingWarning","HostProcessId":"12228","ErrorMessage":"Failed to convert type bigint to integer, expression `LinkID` to 1506780.","ProductVersion":"2.72.5556.801 (19.08)","ActivityId":"3ef90ded-79fc-4726-83bb-c98d8ccf5be7","Process":"Microsoft.Mashup.Container.NetFX45","Pid":22304,"Tid":1,"Duration":"00:00:00.0057948"}
OdbcQuery/FoldingWarning {"Start":"2019-09-03T19:55:54.9498205Z","Action":"OdbcQuery/FoldingWarning","HostProcessId":"12228","Function Name":"AdjustForCompatibility","ProductVersion":"2.72.5556.801 (19.08)","ActivityId":"3ef90ded-79fc-4726-83bb-c98d8ccf5be7","Process":"Microsoft.Mashup.Container.NetFX45","Pid":22304,"Tid":1,"Duration":"00:00:00.0000034"}
OdbcQuery/FoldingWarning {"Start":"2019-09-03T19:55:54.9556351Z","Action":"OdbcQuery/FoldingWarning","HostProcessId":"12228","Function Name":"AdjustNumberValuesToPreventOverflow","ProductVersion":"2.72.5556.801 (19.08)","ActivityId":"3ef90ded-79fc-4726-83bb-c98d8ccf5be7","Process":"Microsoft.Mashup.Container.NetFX45","Pid":22304,"Tid":1,"Duration":"00:00:00.0000045"}
OdbcQuery/FoldingWarning {"Start":"2019-09-03T19:55:54.9556433Z","Action":"OdbcQuery/FoldingWarning","HostProcessId":"12228","Function Name":"SoftConvertSeries","ProductVersion":"2.72.5556.801 (19.08)","ActivityId":"3ef90ded-79fc-4726-83bb-c98d8ccf5be7","Process":"Microsoft.Mashup.Container.NetFX45","Pid":22304,"Tid":1,"Duration":"00:00:00.0000034"}
OdbcQuery/FoldingWarning {"Start":"2019-09-03T19:55:54.9556496Z","Action":"OdbcQuery/FoldingWarning","HostProcessId":"12228","ErrorMessage":"This ODBC driver doesn't support SQL_FN_CVT_CONVERT or SQL_FN_CVT_CAST. You can override this by using SqlGetInfo for SQL_CONVERT_FUNCTIONS.","ProductVersion":"2.72.5556.801 (19.08)","ActivityId":"3ef90ded-79fc-4726-83bb-c98d8ccf5be7","Process":"Microsoft.Mashup.Container.NetFX45","Pid":22304,"Tid":1,"Duration":"00:00:00.0002851"}
OdbcQueryDomain/ReportFoldingFailure {"Start":"2019-09-03T19:55:54.9566845Z","Action":"OdbcQueryDomain/ReportFoldingFailure","HostProcessId":"12228","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.CallConvertOrCast(SqlExpression from, OdbcTypeMap toType)\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcQueryExpressionVisitor.TryVisitConvert(OdbcTypeInfo fromType, OdbcTypeInfo toType, SqlExpression expression, SqlExpression& convertedExpression)\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcQueryExpressionVisitor.TryConvert(OdbcTypeInfo typeInfo, OdbcScalarExpression expression, OdbcScalarExpression& convertedExpression)\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcQueryExpressionVisitor.<SoftConvertSeries>d__191.MoveNext()\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcQueryExpressionVisitor.AdjustNumberValuesToPreventOverflow(OdbcScalarExpression left, OdbcScalarExpression right, Precision precision)\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcQueryExpressionVisitor.AdjustNumberValuesForCompatibility(OdbcScalarExpression left, OdbcScalarExpression right, Precision precision)\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcQueryExpressionVisitor.AdjustForCompatibility(OdbcScalarExpression left, OdbcScalarExpression right, Precision precision)\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcQueryExpressionVisitor.VisitEquals(OdbcSqlExpression leftExpression, OdbcSqlExpression rightExpression, Precision precision, Boolean nullable)\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":"3ef90ded-79fc-4726-83bb-c98d8ccf5be7","Process":"Microsoft.Mashup.Container.NetFX45","Pid":22304,"Tid":1,"Duration":"00:00:00.0036644"}

 

1 ACCEPTED SOLUTION

Accepted Solutions
cwc52 Frequent Visitor
Frequent Visitor

Re: MongoDB Custom Connector Issue with DirectQuery - Failed to Convert BIGINT to INTEGER

I was able to work around this by transforming in SqlColumn function. Odbc SQL types taken from ODBC constants SQL_TYPE (included below)

SQLColumns = (catalogName, schemaName, tableName, columnName, source) =>
let
OdbcSqlType.BIG_INT = -5,
OdbcSqlType.INTEGER = 4,

FixDataType = (dataType) =>
if dataType = OdbcSqlType.BIG_INT then
OdbcSqlType.INTEGER
else
dataType,
Transform = Table.TransformColumns(source, { { "DATA_TYPE", FixDataType } })
in
// the if statement conditions will force the values to evaluated/written to diagnostics
if (Diagnostics.LogValue("SQLColumns.TableName", tableName) <> "***" and Diagnostics.LogValue("SQLColumns.ColumnName", columnName) <> "***") then
let
// 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(Transform, each Diagnostics.LogValue("SQLColumns", _)),
toTable = Table.FromRecords(rows)
in
Value.ReplaceType(toTable, Value.Type(Transform))
else
Transform,

SQL Data Types

SQL_TYPE =
[
// Base data types (sql.h)
UNKNOWN = 0,
NULL = 0,
CHAR = 1,
NUMERIC = 2,
DECIMAL = 3,
INTEGER = 4,
SMALLINT = 5,
FLOAT = 6,
REAL = 7,
DOUBLE = 8,
DATETIME = 9, // V3 Only
VARCHAR = 12,

// Unicode types (sqlucode.h)
WCHAR = -8,
WVARCHAR = -9,
WLONGVARCHAR = -10,

// Extended data types (sqlext.h)
INTERVAL = 10, // V3 Only
TIME = 10,
TIMESTAMP = 11,
LONGVARCHAR = -1,
BINARY = -2,
VARBINARY = -3,
LONGVARBINARY = -4,
BIGINT = -5,
TINYINT = -6,
BIT = -7,
GUID = -11, // V3 Only

// One-parameter shortcuts for date/time data types.
TYPE_DATE = 91,
TYPE_TIME = 92,
TYPE_TIMESTAMP = 93,

// SQL Server Types -150 to -159 (sqlncli.h)
SS_VARIANT = -150,
SS_UDT = -151,
SS_XML = -152,
SS_TABLE = -153,
SS_TIME2 = -154,
SS_TIMESTAMPOFFSET = -155
],

View solution in original post

1 REPLY 1
cwc52 Frequent Visitor
Frequent Visitor

Re: MongoDB Custom Connector Issue with DirectQuery - Failed to Convert BIGINT to INTEGER

I was able to work around this by transforming in SqlColumn function. Odbc SQL types taken from ODBC constants SQL_TYPE (included below)

SQLColumns = (catalogName, schemaName, tableName, columnName, source) =>
let
OdbcSqlType.BIG_INT = -5,
OdbcSqlType.INTEGER = 4,

FixDataType = (dataType) =>
if dataType = OdbcSqlType.BIG_INT then
OdbcSqlType.INTEGER
else
dataType,
Transform = Table.TransformColumns(source, { { "DATA_TYPE", FixDataType } })
in
// the if statement conditions will force the values to evaluated/written to diagnostics
if (Diagnostics.LogValue("SQLColumns.TableName", tableName) <> "***" and Diagnostics.LogValue("SQLColumns.ColumnName", columnName) <> "***") then
let
// 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(Transform, each Diagnostics.LogValue("SQLColumns", _)),
toTable = Table.FromRecords(rows)
in
Value.ReplaceType(toTable, Value.Type(Transform))
else
Transform,

SQL Data Types

SQL_TYPE =
[
// Base data types (sql.h)
UNKNOWN = 0,
NULL = 0,
CHAR = 1,
NUMERIC = 2,
DECIMAL = 3,
INTEGER = 4,
SMALLINT = 5,
FLOAT = 6,
REAL = 7,
DOUBLE = 8,
DATETIME = 9, // V3 Only
VARCHAR = 12,

// Unicode types (sqlucode.h)
WCHAR = -8,
WVARCHAR = -9,
WLONGVARCHAR = -10,

// Extended data types (sqlext.h)
INTERVAL = 10, // V3 Only
TIME = 10,
TIMESTAMP = 11,
LONGVARCHAR = -1,
BINARY = -2,
VARBINARY = -3,
LONGVARBINARY = -4,
BIGINT = -5,
TINYINT = -6,
BIT = -7,
GUID = -11, // V3 Only

// One-parameter shortcuts for date/time data types.
TYPE_DATE = 91,
TYPE_TIME = 92,
TYPE_TIMESTAMP = 93,

// SQL Server Types -150 to -159 (sqlncli.h)
SS_VARIANT = -150,
SS_UDT = -151,
SS_XML = -152,
SS_TABLE = -153,
SS_TIME2 = -154,
SS_TIMESTAMPOFFSET = -155
],

View solution in original post

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors