Qlik Community

QlikView Documents

Documents for QlikView related information.

QlikView JDBC Connector

MVP
MVP

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. jdbcSmiley Surprisedracle:thin:@localhost:1521:ex)
  • example connect statement for Oracle (will be included by connect popup):
    CUSTOM CONNECT TO "Provider=JDBCConnector_x64.dll;jdbcSmiley Surprisedracle: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
Contributor

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:

jdbcSmiley Surprisedracle: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

MVP
MVP

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

bimartingo
New Contributor

Hi

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

Thanks

BIMartingo

MVP
MVP

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

MVP
MVP

This document has nothing to do with Hadoop..

Not applicable

Hi,

Where do I set the PATH and CLASSPATH?

Not applicable

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

MVP
MVP

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

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

MVP
MVP

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

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