topic Direct Discovery for mySQL in QlikView

Direct Discovery for mySQL

Tue, 03 Jun 2014 06:09:36 GMT

Hi,

I'm developing an application which required Direct Discovery. I tested using the MSSQL, I got no problem in developing the application. But now I need connect to another database type which is mySQL, but when I reload the script, error from the DIRECT QUERY statement.

SQL##f - SqlState: 37000, ErrorCode: 1064, ErrorMsg: [MySQL][ODBC 5.3(w) Driver][mysqld-5.0.41-community-nt]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[ServiceNo] FROM [branchs].[stuservice]' at line 1

Please advice. Or anyone have the sample code for direct discovery in mySQL?

Regards,

CS Khoo

Re: Direct Discovery for mySQL

stevedark — Tue, 03 Jun 2014 06:37:29 GMT

Hi there,

Can you post the entire SQL query here? Have you run the statement successfully against MySQL directly?

Steve

Re: Direct Discovery for mySQL

rbecher — Tue, 03 Jun 2014 06:40:27 GMT

Hi,

MySQL has different SQL syntax than MSSQL..

- Ralf

Re: Direct Discovery for mySQL

Tue, 03 Jun 2014 06:40:36 GMT

No, when the script start to run the 1st column, then error. If not using any direct discovery function, I managed to read every tables as usual.

Below are the statement.

ODBC CONNECT TO QlikQMS (XUserId is fBMYMZdNPDdeP, XPassword is YWaGDZdNPDdCDZMGPG);

DIRECT QUERY

dimension

ServiceNo,

BranchCode

measure

CurrTicket,

NextTicket,

PhysicalTicketWaiting,

TransferTicketWaiting,

PhysicalTicketIssued,

TransferTicketIssued,

PhysicalTicketServed,

TransferTicketServed,

PhysicalTicketCalled,

TransferTicketCalled,

PhysicalTicketRenegaded,

TransferTicketRenegaded,

TicketTransferredOut,

AvgWTSec,

AvgSTSec,

AvgTTSec,

CurrWTSec,

CurrSTSec,

TotalWTSec,

TotalSTSec,

TotalTTSec,

TotalWithinTgtWT,

TotalWithinTgtST,

TotalWithinTgtTT,

CurrTicketIssueTime,

CurrTicketStr,

NextTicketStr,

UpdateTimeStamp,

ServiceState,

LastStatusTime,

GroupTicketCount

detail

CounterOpened

FROM branchqms.stuservice;

Re: Direct Discovery for mySQL

Tue, 03 Jun 2014 07:08:49 GMT

Any idea how to solve?

Re: Direct Discovery for mySQL

rbecher — Tue, 03 Jun 2014 07:30:31 GMT

No, the Direct Query syntax seems to be correct.

Re: Direct Discovery for mySQL

rbecher — Tue, 03 Jun 2014 07:38:38 GMT

I think it's probably the created SQL in the background and MySQL doesn't like the brackets...

Btw. you can trace those SQL statements in the ODBC driver.

Re: Direct Discovery for mySQL

Bill_Britt — Tue, 03 Jun 2014 07:42:07 GMT

Hi,

This is the only thing I could find.

Direct discovery can be used in conjunction with MySQL and MS Access but may require the

following parameters in the set variables due to the quoting characters used in these sources:

SET DirectIdentifierQuoteChar='``';

Bill

Re: Direct Discovery for mySQL

Tue, 03 Jun 2014 08:16:44 GMT

Please advice how to trace the statement?

Re: Direct Discovery for mySQL

Tue, 03 Jun 2014 08:17:07 GMT

Yes, I did applied this.

Re: Direct Discovery for mySQL

rbecher — Tue, 03 Jun 2014 08:23:51 GMT

Select the Tracing tab in the ODBC Data Source Administrator dialog box.
Change the Log File Path or leave the default path indicated.
Click the Start Tracing Now button.

Re: Direct Discovery for mySQL

Tue, 03 Jun 2014 08:32:15 GMT

Thanks everyone. I solved the problem.

Bill's ans is correct. I did apply this statement

SET DirectIdentifierQuoteChar='``';

But i changed the quote char. =P

Sorry.

Thanks anyway!!!!!!!!!!!!!