Invalid data type in cast

[ghost]

I connected Exasol in DirectQuery mode. I often get this error.

OLE DB or ODBC error: [DataSource.Error] ODBC: ERROR [42000] [EXASOL][EXASolution driver]invalid data type in cast

[Hugoamorar]

Hi! I have tested the connector and have had no problem to date. When are you having this problem? (when connecting to the DB or when executing queries?). Maybe if you share some sample data I should try to replicate the error. And also, which version of ODBC drivers and PBI Desktop are you using?
Best regards, Hugo.

[ghost]

@Hugoamorar ,
Thanks for the quick response.
I'm getting an error when I execute queries.
Power BI - Nov 2018(64 bit)
EXASOL_ODBC-6.1.0 - (64 bit)

Here is one case,
I have a slicer to filter Month. I have two fields - Monthname(Text) and Month(Decimal)
It throws an error when I add Month into slicer(with dropdown option).

[Hugoamorar]

Hello siva44 :)

Maybe could you make a single test, changing the datatype of "Month" from Decimal to Integer?

Also, is there any information under the "Copy details" link? of so, please share in order to see if something there could help to understand your problem.

And, if you have access to Exasol DB, could you capture the query? Not sure, but maybe you should trap the query that PBI sends to Exasol and evaluate what is wrong.

Hope you solve your problem, and if possible, share here with us :)

Best regards and have success! Hugo.

[ghost]

Hi Hugo,

Changing the datatype is not giving any progress.

I do have access to Exasol DB. Can you help me to trace the query which send by PBI?

[rhein]

I am experiencing this as well. The problem seems to be a wrong order of arguments in the function CONVERT() - the Connector (or maybe the odbc-driver?) creates a query that contains CONVERT($fieldname, $type) while it should be CONVERT($type, $fieldname).
So, the problem comes up any time a cast is needed to match power-bi column data format to exasol db data format.
A quickfix would be to use a pre-processing function on all queries that simply swaps the arguments of all CONVERT() calls it sees.

In order to trace the offending query, look into the result of select * from exa_dba_audit_sql where success=false; (that is a table in EXA_STATISTICS).

[ThomasBestfleisch]

@rhein Do you have a minimal example, e.g. Table DDL + a few lines of data+ what actions you did in Power BI to raise the exception, so we can easily reproduce this case. I will then build a fix directly into the connector. The Problem is that the CONVERT function in Microsoft SQL Server has a different argument order then Exasol.

[rhein]

@ThomasBestfleisch : we actually discussed this with a sales engineer already (the solution is his to be honest), he will be in touch with you for details.

[ghost]

@ThomasBestfleisch ,

Here is the query that generated by Power BI,

select sum("Sale (Dollars)") as "C1"
from
(
select "Sale (Dollars)"
from
(
select "OTBL"."Invoice/Item Number",
"OTBL"."Date",
"OTBL"."Store Number",
"OTBL"."County Number",
"OTBL"."Category",
"OTBL"."Vendor Number",
"OTBL"."Item Number",
"OTBL"."Pack",
"OTBL"."Bottle Volume (ml)",
"OTBL"."State Bottle Cost",
"OTBL"."State Bottle Retail",
"OTBL"."Bottles Sold",
"OTBL"."Sale (Dollars)",
"OTBL"."Volume Sold (Liters)",
"OTBL"."Volume Sold (Gallons)",
"ITBL"."Date" as "C1",
"ITBL"."Month",
"ITBL"."MonthNo"
from "EXA_DB"."TEST_SCHEMA"."LIQUORSALES" as "OTBL"
left outer join "EXA_DB"."TEST_SCHEMA"."DATE" as "ITBL" on ("OTBL"."Date" = "ITBL"."Date")
) as "ITBL"
where { fn convert("C1", SQL_TIMESTAMP) } = {ts '2012-01-04 00:00:00'}
) as "ITBL";

The error part is - { fn convert("C1", SQL_TIMESTAMP) }

But C1 is Date type field in both. Hope it helps you assist us.

Thanks,
Siva

[ThomasBestfleisch]

Thanks, until we have fixed this in the database or we have found a workaround in the connector, please install the following preprocessor script in the database and enable it on system level. This script flips the arguments of the convert function.

Please execute the following script with an Exasol DBA User (e.g. SYS) in EXAPlus, DBVisualizer:

You have to reconnect with Power Bi to Exasol afterwards

CREATE SCHEMA IF NOT EXISTS UTILS ;

--/
CREATE OR REPLACE SCRIPT  UTIL.CONVERT_REVERSE_ORDER AS
	function processconv(sqltext)
		while (true) do
			local tokens = sqlparsing.tokenize(sqltext)
			local ifStart = sqlparsing.find(tokens,1,true,false,sqlparsing.iswhitespaceorcomment,'CONVERT','(')
			if (ifStart==nil) then
				break;
			end
			local ifEnd = sqlparsing.find(tokens,ifStart[2],true,false,sqlparsing.iswhitespaceorcomment,')')
			if (ifEnd==nil) then
				error("convert statement not ended properly")
				break;
			end
			local commas1 = sqlparsing.find(tokens,ifStart[2]+1,true,true,sqlparsing.iswhitespaceorcomment,',' )
			if (commas1==nil) then
				error("invalid convert function")
				break;
			end
			local convParam1=table.concat(tokens, '', ifStart[2]+1, commas1[1]-1)
			local convParam2=table.concat(tokens, '', commas1[1]+1, ifEnd[1]-1)
			local convStmt=convParam2..','..convParam1
			sqltext=table.concat(tokens, '',1,ifStart[2])..convStmt..table.concat(tokens,'', ifEnd[1])
			return sqltext
		end
		return sqltext
	end
	
/

--/
CREATE OR REPLACE LUA SCRIPT "PREPROCESSCONV" () RETURNS ROWCOUNT AS
import('util.convert_reverse_order', 'convert_reverse_order')
sqlparsing.setsqltext(convert_reverse_order.processconv(sqlparsing.getsqltext()))
/

GRANT EXECUTE ON UTIL.PREPROCESSCONV TO PUBLIC;
GRANT EXECUTE ON UTIL.CONVERT_REVERSE_ORDER TO PUBLIC;

ALTER SYSTEM SET SQL_PREPROCESSOR_SCRIPT=UTIL.PREPROCESSCONV;

[ghost]

Great. It works.

Small correction -Change UTILS to UTIL

CREATE SCHEMA IF NOT EXISTS UTIL ;

Thanks @ThomasBestfleisch .

[ghost]

@ThomasBestfleisch ,
I'm having an issue with the following query,

select "Month",
"MonthNo",
sum("C2") as "C1",
sum("C3") as "C2"
from
(
select "Category",
"State Bottle Cost",
"Bottles Sold",
"Sale (Dollars)",
"C1",
"Month",
"MonthNo",
"State Bottle Cost" * "Bottles Sold" as "C2",
"Sale (Dollars)" as "C3"
from
(
select "OTBL"."Category",
"OTBL"."State Bottle Cost",
"OTBL"."Bottles Sold",
"OTBL"."Sale (Dollars)",
"ITBL"."Date" as "C1",
"ITBL"."Month",
"ITBL"."MonthNo"
from
(
select "Invoice/Item Number",
"Date",
"Store Number",
"County Number",
"Category",
"Vendor Number",
"Item Number",
"Pack",
"Bottle Volume (ml)",
"State Bottle Cost",
"State Bottle Retail",
"Bottles Sold",
"Sale (Dollars)",
"Volume Sold (Liters)",
"Volume Sold (Gallons)"
from "EXA_DB"."TEST_SCHEMA"."LIQUORSALES"
where "Category" = 1071100.
) as "OTBL"
left outer join "EXA_DB"."TEST_SCHEMA"."DATE" as "ITBL" on ("OTBL"."Date" = "ITBL"."Date")
) as "ITBL"
where { fn convert( SQL_TIMESTAMP,"C1") } < {ts '2019-01-01 00:00:00'} and { fn convert("C1", SQL_TIMESTAMP) } >= {ts '2018-01-01 00:00:00'}
) as "ITBL"
group by "Month",
"MonthNo"
LIMIT 1000001

As you see above,
where { fn convert( SQL_TIMESTAMP,"C1") } < {ts '2019-01-01 00:00:00'} and { fn convert("C1", SQL_TIMESTAMP) } >= {ts '2018-01-01 00:00:00'}

The script use shared isn't converting this query. Please check.

[florian-wenzel]

@siva44 try this instead:

CREATE SCHEMA IF NOT EXISTS UTIL;
--/
CREATE OR REPLACE LUA SCRIPT UTIL."FN_CONVERT_REVERSE_ORDER_REPEATED" () RETURNS ROWCOUNT AS
function processconv(sqltext)
		local lasthit = 1
		while (true) do
			local tokens = sqlparsing.tokenize(sqltext)
			local convStart = sqlparsing.find(tokens,lasthit,true,false,sqlparsing.iswhitespaceorcomment,'fn','CONVERT','(')
			if (convStart==nil) then
				break;
			end
			lasthit=convStart[3]
			local convEnd = sqlparsing.find(tokens,lasthit,true,false,sqlparsing.iswhitespaceorcomment,')')
			if (convEnd==nil) then
				error("convert statement not ended properly")
				break;
			end
			local comma = sqlparsing.find(tokens,lasthit+1,true,true,sqlparsing.iswhitespaceorcomment,',' )
			if (comma==nil) then
				error("invalid convert function")
				break;
			end
			local convParam1=table.concat(tokens, '', lasthit+1, comma[1]-1)
			local convParam2=table.concat(tokens, '', comma[1]+1, convEnd[1]-1)
			local convStmt=convParam2..','..convParam1
			sqltext=table.concat(tokens, '',1,lasthit)..convStmt..table.concat(tokens,'', convEnd[1])
		end
		return sqltext
end
/
--/
CREATE OR REPLACE LUA SCRIPT UTIL."PREPROCESSFNCONVREPEATED" () RETURNS ROWCOUNT AS
import('util.fn_convert_reverse_order_repeated', 'fn_convert_reverse_order_repeated')
sqlparsing.setsqltext(fn_convert_reverse_order_repeated.processconv(sqlparsing.getsqltext()))
/

GRANT EXECUTE ON UTIL.PREPROCESSFNCONVREPEATED TO PUBLIC;
GRANT EXECUTE ON UTIL.FN_CONVERT_REVERSE_ORDER_REPEATED TO PUBLIC;
ALTER SYSTEM SET SQL_PREPROCESSOR_SCRIPT=UTIL.PREPROCESSFNCONVREPEATED;

[ghost]

Thanks @florian-wenzel .

[florian-wenzel]

@siva44 can you confirm for everybody else if this works for you? I tested that in a different project without any problems.

[ghost]

@florian-wenzel ,
Yes. It works well for me.

Thank you so much!

[ThomasBestfleisch]

Hi Siva the newest version of the Exasol Connector doesn't need any preprocessing anymore:
If you want to test the newest connector (which is not yet released in Power BI) follow the instructions here:
https://github.com/exasol/powerbi-exasol/blob/master/README.md#use-the-custom-connector-from-this-repository-instead-of-the-official-shipped-connector-in-power-bi-desktop

[ghost]

Thanks, @ThomasBestfleisch . I will give a try.