topic JDBC Connector - Connection to derby db in QlikView

JDBC Connector - Connection to derby db

Tue, 10 Jan 2012 15:19:21 GMT

Hi guys,

i'm trying to connect to a derby database.

I read something on the Forum and I followed the instructions to connect qlikview to a derby db using jdb connector.

First of all I put the JDBCConnector_x64.dll on my client QLIKVIEW.

now I have a bew database connector (as you can see below)

After that I wrote this code on my script

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='€ #.##0,00;-€ #.##0,00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='gen;feb;mar;apr;mag;giu;lug;ago;set;ott;nov;dic';

SET DayNames='lun;mar;mer;gio;ven;sab;dom';

SET CLASSPATH=\\192.168.1.158\Program Files\SysAidServer\root\WEB-INF\lib\derbytools.jar;

//SET DERBY_HOME=C:\JavaDB;

CUSTOM CONNECT TO "Provider=JDBCConnector_x64.dll;jdbc:derby://192.168.1.158:1527/default;create=true;XUserId=xxx;XPassword=xxx;

Maybe 1527 is not correct ? What do I have to put there ?

When I try to connect to my db QLIKVIEW gives me this error:

I have an error "Custom read failed"

Any idea about what I'm doing wrong???

Thanks a lot

Giampiero

Re: JDBC Connector - Connection to derby db

llauses243 — Tue, 10 Jan 2012 16:53:06 GMT

Hi,

Possible unbalance of ' " ', pls to review image attached

Good luck, Luis

Re: JDBC Connector - Connection to derby db

rbecher — Tue, 10 Jan 2012 17:57:31 GMT

Hi Giampiero,

first you have to do the following tasks in your Windows environment (not in QV script):

extend PATH with C:\Program Files\Java\jre6\bin\server (or \client, where the jvm.dll is located)
SET CLASSPATH and SET DERBY_HOME

And in the CUSTOM CONNECT use '?' instead of semicolon for the URL parameters or omit create=true

and of course don't forget the closing double quote:

CUSTOM CONNECT TO "Provider=JDBCConnector_x64.dll;jdbc:derby://192.168.1.158:1527/default?create=true;XUserId=xxx;XPassword=xxx";

- Ralf

Message was edited by: Ralf Becher Sorry this was a typo: first delimiter is '?', second and following delimiters are '&'

Re: JDBC Connector - Connection to derby db

rbecher — Tue, 10 Jan 2012 18:05:08 GMT

Here is the URL syntax which should used:

jdbc:derby:[subsubprotocol:][databaseName][?attribute=value[&attribute=value]*]

- Ralf

JDBC Connector - Connection to derby db

Wed, 11 Jan 2012 08:34:09 GMT

Hi Luis,

thank you so much.

It's my fault, I did a mistake pasting the string without ".

Again, Thank you.

Giampiero

JDBC Connector - Connection to derby db

Wed, 11 Jan 2012 08:48:58 GMT

Hi Ralph,

I did what you told me so my new string is:

CUSTOM CONNECT TO "Provider=JDBCConnector_x64.dll;jdbc:derby://192.168.1.158:1527/default?create=true;XUserId=xxx;XPassword=xxx";

My CLASSPATH is :

SET CLASSPATH=\\192.168.1.158\Program Files\SysAidServer\root\WEB-INF\lib\derbytools.jar;

I have to put ' \\192.168.1.158..... ' ??

and in DERBY_HOME what do I have to assign ??

SET DERBY_HOME= ??? ;

JDBC Connector - Connection to derby db

rbecher — Wed, 11 Jan 2012 09:02:24 GMT

Hi Giampiero,

the connect string is fine. You will need the Derby (or JavaDB) libs derbyclient.jar and derbytools.jar on your local machine:

SET DERBY_HOME=c:\cerby

SET CLASSPATH=c:\cerby\lib\derbyclient.jar;c:\cerby\lib\derbytools.jar

Also set in the System Properties/Environment Variables:

SET PATH=<...your existing path...>;C:\Program Files\Java\jre6\bin\server

- Ralf

JDBC Connector - Connection to derby db

s_uhlig — Wed, 11 Jan 2012 09:32:49 GMT

Hi Giampiero,

PATH, CLASSPATH & DERBY_HOME have to be environment variables.

Take a look at http://docs.oracle.com/javase/tutorial/essential/environment/paths.html and http://support.microsoft.com/default.aspx?scid=kb;en-us;310519

Regards

Sven

JDBC Connector - Connection to derby db

Wed, 11 Jan 2012 09:56:38 GMT

Hi Ralph,

I'm sorry if I'm going to write something that I'm sure you have already understood but in this way I'll try to be clear for me

I have my derby DB on another machine, my server machine.

So, seen my architecture (my derby db on another machine), do I need to have the Derby libs derbyclient.jar and derbytools.jar on my local machine too ?

I'm going to explain to you what I'd like to do.

I have installed on my server a product "SYSAID" that use a derby DB.

I want to reach this DB with the QLICKVIEW on my local machine.

So I have on my server:

C:\Program Files\SysAidServer .... where SYSAID is installed.

C:\Program Files\SysAidServer\root\WEB-INF\lib ... where I find the derby libs derbyclient.jar and derbytools.jar.

C:\Program Files\SysAidServer\root\WEB-INF\db\default ... where I think is my database

After my description, do I still have to follow your instructions ?

THANK YOU for your patience with me.

JDBC Connector - Connection to derby db

Wed, 11 Jan 2012 09:57:40 GMT

thank you Sven,

I'm going to read your link.

Thank you so much

Giampiero

JDBC Connector - Connection to derby db

rbecher — Wed, 11 Jan 2012 10:02:11 GMT

Just copy the two jar files to your QlikView local machine and set DERBY_HOME and CLASSPATH.

The CUSTOM CONNECT should be fine as it is.

Re: JDBC Connector - Connection to derby db

s_uhlig — Thu, 12 Jan 2012 10:56:33 GMT

Hi Giampiero,

as far as I can see, SYSAID uses an embedded derby-db located under ???\SysAidServer\root\WEB-INF\db, which will not be exposed to the networklayer.

Please read the Network Server Manual carefully and test your connection with ij as described in the document.

When I am right and you can't connect to your Server, you have the opportunity to copy the database to your client, expose it through samba (you may encounter other problems) or reconfiguring the SYSAID-Server.

Regards

Sven

JDBC Connector - Connection to derby db

Thu, 12 Jan 2012 11:05:48 GMT

Hi Sven,

you're right about sysaid and the db.

I'm going to read the document you gave me.

Thank you so much for your help.

Best regards,

Giampiero