JDBC Connector - Connection to derby db

[#1]

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

[llauses243]

Hi,

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

Good luck, Luis

[rbecher]

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 '&'

[rbecher]

Here is the URL syntax which should used:

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

- Ralf

[#5]

Hi Luis,

thank you so much.

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

Again, Thank you.

Giampiero

[#6]

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=  ???  ;

Other two questions for you.

What do you mean when you say:

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

In my client windows there is this path. What do I have to do ?

and in the string ...jdbc:derby://192.168.1.158:1527...

1527 is correct?

I'm sorry, but I'm new on this argument and I don't really know what to do. So I have to well understand what I'm doing.

THANK YOU SO MUCH for your help.

Giampiero

[rbecher]

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

[s_uhlig]

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

[#9]

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.

[#10]

thank you Sven,

I'm going to read your link.

Thank you so much

Giampiero