cancel
Showing results for 
Search instead for 
Did you mean: 

Import data from MariaDB with text filter raises SQL syntax error (Reproducible!)

Hi,
I'm using PowerBI (version 2.92.706.0, 64-bit) and MariaDB as data source. I downloaded ODBC driver versions 3.1.11 from following link:
https://mariadb.com/downloads/?showall=1&tab=connectors&group=mariadbconnectors&product=ODBC%20conne...
 
Connection & Simple import job worked well. But when I add text filtering (equality check) in Power Query Editor, it raises SQL syntax error like this:
 
[ma-3.1.11][10.3.25-MariaDB-0ubuntu0.20.04.1-log]You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LONG VARCHAR) = ? and `PARTNERS` is not null' at line 11
 
PARTERS is the column name which I add filter to.
 
It's very easy to reproduce the problem. In MariaDB just create a table like this:
 
create table test (name varchar(256), score int(11));
 
then, insert two rows:
 
insert into test values ('john', 90);
insert into test values ('nick', 80);
 
To get data from MariaDB in PowerBI, import 'test' table then click  'transform data'. In resulting Power Query Editor, add text filter for name 'nick'. Then close & load.
 
That's all, I hope you could reproduce the problem, and fix it SOON!
(In my opnion, it's related with length of VARCHAR. VARCHAR(10) column did not raised the problem)
 
Best.

Status: New
Comments
v-lili6-msft
Community Support

hi

I have tested on my side, I downloaded ODBC driver versions 3.1.12

It works well.

Please use ODBC driver versions 3.1.12 to have a try again

14.JPG

 

13.JPG

15.JPG

 

Regards,

Lin

haje01
Regular Visitor

Thank you @v-lili6-msft for your reply. I've installed new ODBC driver 3.1.12, still the error happens.

 

[ma-3.1.12][10.3.25-MariaDB-0ubuntu0.20.04.1-log]You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LONG VARCHAR) = ? and `PARTNERS` is not null' at line 11

 

Did you created test table with VARCHAR(256)? I found that VARCHAR less than 255 in length is okay.

 

Best,

v-lili6-msft
Community Support

hi

16.JPG

 

Yest, I tested use different lengths, all works well.

 

and my version is 10.5.9

 

 

Regards,

Lin

haje01
Regular Visitor

@v-lili6-msftIn 'Get Data' dialog, did you use MariaDB Connector or ODBC Connector ? I used MariaDB Connector.

v-lili6-msft
Community Support

hi

i used ODBC Connector,

17.JPG

 

and now I test it with MariaDB Connector, could reproduce the problem.

 

I will report this issue, will update here once I get any information.

as a workaround, you could use ODBC Connector instead of MariaDB Connector.

 

Regards,

Lin

haje01
Regular Visitor

@v-lili6-msft 

I am glad that you have succeeded in reproducing that problem.

Thank you for your support.

 

Best.

RoguytJeannin
New Member

I also confirm that this issue also occurs when you are trying to concatenate two columns in PowerBI using the standard MariaDB Connector.

Switching to the ODBC one fixes this issue.

haje01
Regular Visitor

@RoguytJeannin 

We can't use ODBC Connector because we need to use DirectQuery.

Thank you.

 

v-lili6-msft
Community Support

hi

I get this from PG:

We don't own Maria DB connector. The customer should talk to the owners of the connector. It does not look like a certified connector either.

 

you may need to connect it with Maria DB support.

 

 

Regards,

Lin

haje01
Regular Visitor

Ok, I reported the case on the MariaDB's forum:

 

https://mariadb.com/kb/en/powerbi-import-data-with-text-filter-raises-sql-syntax-error-reproducible/

 

I hope they can fix the problem.

 

Thank you for your support.