QlikView JDBC Connector

[rbecher]

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.

[s_uhlig]

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]

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

[bimartingo]

Hi

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

Thanks

BIMartingo

[rbecher]

Hi BIMartingo,

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

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

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

- Ralf

[rbecher]

This document has nothing to do with Hadoop..

[#7]

Hi,

Where do I set the PATH and CLASSPATH?

[#8]

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

[rbecher]

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

[#10]

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

[rbecher]

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