Qlik Community

Ask a Question

QlikView Documents

QlikView documentation and resources.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY

QlikView JDBC Connector

rbecher
Luminary Alumni
Luminary Alumni

QlikView JDBC Connector

Hi all,

the product JDBC Connector reached end-of-life because it's technically outdated. Support of it ends 2018-12-31.

The successor product is the TIQ Java Service Connector and can be obtained here:

https://www.tiq-solutions.de/en/tiq-java-service-connector/

Best regards,

Ralf

--------------------------------

Hi everyone,

this is a demo version of our JDBC Connector for QlikView. It will enable a lot of non-ODBC/OLEDB sources for loading data into QlikView.

Also, it would give much more performance on QV 9 loads (Oracle, mysql etc.) and could speed up on complex network environments (like VPN, firewalls, WAN).

Please try it out and give us some feedback!

Installation instructions:

  • install the JDBC Connector and config the JDBC driver (mostly a .jar file)
  • connect with JDBC URL (e.g. jdbc:oracle:thin:@localhost:1521:ex)
  • example connect statement for Oracle (will be included by connect popup):
    CUSTOM CONNECT TO "Provider=JDBCConnector_x64.dll;jdbc:oracle:thin:@localhost:1521:ex;XUserId=SbKdMaFNRLOA;XPassword=aWELJaFNRbTA;";

Missing feature (waiting for QV support):

  • no select option for database/owner on table wizzard

Thanks to my colleague Sven "Benno" Uhlig who brought up this idea and implementation!

Ralf

UPDATE:

This is a new version which supports DDL and DML statements too. You will get a result set if a DML statement was executed with the update count.

Just try script calls like this:

DATA:

SQL CREATE TABLE test2 (col1 INT)

;

SQL insert into test2 (col1) values (1)

;

SQL update test2 set col1=2

;

SQL drop table test2

;

UPDATE:

New version comes with an installer and config dialogs. See also: http://www.tiq-solutions.de/display/enghome/ENJDBC for a list of possible JDBC sources.

Labels (1)
Attachments
Comments
s_uhlig
Partner
Partner

To gain advantage in load performance compared to standart odbc/oledb connection you will need to tune driver properties.

As an example we could cut in half the loadtime of our applications (qv9) by using a connection url like:

jdbc:oracle:thin:@sampleserver:1521:dbname?defaultRowPrefetch=1000

In contrast you can set OCI_ATTR_PREFETCH_ROWS on the statement level for the oracle-odbc connection, but afaik you can't do apply such properties with qlikview and setting defaultvalues on connection level is only possible by third-party odbc drivers.

If you have very large datasets, it seems to be worth a try.

Regards

Sven

rbecher
Luminary Alumni
Luminary Alumni

Now we have also a 32bit version: JDBCConnector.zip

0 Likes
bimartingo
Contributor III
Contributor III

Hi

after install the dll , how do I script the connect command  in qlikview?

Thanks

BIMartingo

0 Likes
rbecher
Luminary Alumni
Luminary Alumni

Hi BIMartingo,

just hit the Connect-Button after choosing the JDBCConnector.dll:

JDBCConnector.png

connect.png

Then enter the database JDBC URL with leading jdbc:<vendor>.

After hitting ok, the CUSTOM CONNECT command will be placed into the script.

- Ralf

0 Likes
rbecher
Luminary Alumni
Luminary Alumni

This document has nothing to do with Hadoop..

0 Likes
Not applicable

Hi,

Where do I set the PATH and CLASSPATH?

0 Likes
Not applicable

Ok I already found the PATH and CLASSPATH. does this connection support Apache Derby?

0 Likes
rbecher
Luminary Alumni
Luminary Alumni

Hi,

yes it does. You have to set:

SET CLASSPATH=C:\JavaDB\lib\derby.jar;C:\JavaDB\lib\derbytools.jar; (take your path)

SET DERBY_HOME=C:\JavaDB (if it's on the same box)

- Ralf

0 Likes
Not applicable

Hi ralf,

Thanks for the reply.

I'm still having problems with the connection. I already added the the Derby on the classpath. derby/derbyclient, and derbytools. I addded your suggestion of Derby_Home also but I still get an error of  "No Suitable Driver" whenever I click reload after the connection string is generated. I'm also starting the derby server before I do any of the connections.

Is the Derby_Home supposed to be the database? The java db I am connecting to is not on my local.

this is a sample of the connection string. I added the create=true because if its not there the qlikview crashes:

CUSTOM CONNECT TO "Provider=JDBCConnector.dll;derby://localhost:1527/data-contact;create=true;XUserId=fHWNYZFMRB;XPassword=AIJGTZFMMB;";

I used to connect to the database using SQL Explorer(which works) so I just want qlikview to directly connect to the database instead of going thru another tool.

Do you have any other suggestions?

Thanks

0 Likes
rbecher
Luminary Alumni
Luminary Alumni

At first, the URL should start with: jdbc:derby:

..then you have localhost instead of an IP address (if Derby is not on your box)

- Ralf

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2011-09-16 03:28 PM
Updated by: