Proper location of BirtSample.jar and setting of v_database

I'm working with the text KNIME Essentials.  At the beginning of Section 2, one is to install the BIRT Sample database to work through the examples.  I have found two (2) copies of the BirtSample.jar file:  one having a size of 508,055 bytes, the other having a size of  535,088 bytes.  I believe that the former is the one to be used with KNIME Essentials; however, the latter is found in folder C:\Program Files\KNIME_2.11.3\plugins\org.eclipse.birt.report.data.oda.sampledb_4.4.2.v201410272105\db\BirtSample.jar.  (I am currently at version 2.12.0 of KNIME.)

1.  If anyone knows which copy should be used, or whether it matters, I would like to know.

2.  While I have the copies of BirtSample.jar on my machine, the Example Workflow is not finding the JAR file.

a.  The code in the "path --> JDBC Connection" node (a Java Edit Variable node) is

// Enter your code here:
out_DerbyJ = "jdbc:derby://localhost:1527/" + v_database;

 

b.  The Flow Variables of the "path --> JDBC Connection" node are

  • Derby JDBC connection = <blank>
  • database location = BirtSample
  • knime.workspace = C:\Users\<username>\knime-workspace

 

c.  The Database URL value in the "collect customers" node (a Database Looping node) is "jdbc:derby://localhost:1527/BirtSample", which is to what the "out_DerbyJ" variable would be set by the code snippet.

 

These conditions generate three questions

  1. Where should the BirtSample.jar file be placed so that it can be found?  The Database Reader node, Database Looping node, and Database Table Connector nodes all report "Invalid URL in settings," but I don't know where to put the JAR file so that it is found.
  2. In the code snippet above, how is v_database assigned?  There is a flow variable "database location" that is assigned to the Java Field v_database, but I don't see the source of the value for v_database.
  3. The output variable of the "path --> JDBC connector" (a Java Edit Variable node) is connected to the input, flow variable port of the "collect customers" node (a Database Looping node), and the node shows the value of the URL as what would be assigned to the out_DerbyJ Java variable (jdbc:derby://localhost:1527/BirtSample).  However, looking at the Flow Variables in the "collect customers" node, the "Derby JDBC connection" is selected in the pull down menu for the database variable, but there is no value in correspoinding the text box.  How does the out_DerbyJ value appear in the "Database URL" configuration field?

Asdie:  I have not attached the workflow as I don't know if this is permitted by the copyright on KNIME Essentials.  If anyone can definitively state whether attaching the workflow is permitted, I'll act accordingly.

Thanks.

Scott K. Starry

Dear Scott,

I am sorry about that, I had to try that myself two times before I could reproduce that again. (Not KNIME changed that much, just an important step was not properly explained.)

So here is what I did to make it work.

Downloaded the file "KNIME Essentials.zip" from the publisher's website (I also have an ebook, so I do not have to register with a CAPTCHA, I hope it gives the same file).

That file contains two files important for this question: 9211OT_Code\Chapter 2\BirtSample.zip and 9211OT_Code\Chapter 2\DatabaseConnection.zip.

You have to unzip the BirtSample.zip file to a folder. In this example let it be: C:\tmp, you get C:\tmp\BirtSample.jar (496Kb). The jar files are also zip files, you can rename to that if you cannot open it as an archive (probably this would work from other BirtSample.jar files too, at the time of writing, I think BirtSample.jar was not distributed with KNIME by default).

From this BirtSample.jar (or if you renamed C:\tmp\BirtSample.zip) copy the BirtSample folder to a JavaDB folder, for example: C:\Java\jdk1.7.0_51_x64\db. In that folder, you might start the network server as it is described in the book:

bin/startNetworkServer

The derbyclient.jar file -still following the example path- can be found at: C:\Java\jdk1.7.0_51_x64\db\lib\derbyclient.jar to be set in the File | Preferences | KNIME | Database Driver.

When you import the workflow from the DatabaseConnection.zip file, you can right click on it in the KNIME Explorer (this was new when the book get published) and select Workflow Variables... from the context menu. There you should see something like this:

Name Type Value
database location STRING BirtSample

This is the source of the mystical "database location" variable.

Regarding your last question... Try writing something (but NOT "Derby JDBC Connection", "database location" or "knime.workflow", let it be "asdf" in this example) to the textbox besides the combobox and click OK. Now check the Flow Variables tab of the output. You should see a variable with the name you specified ("asdf") with the value of the parameter in the database option. The dropdowns are there to specify the values of the parameters, while the textboxes are there to create flow variables based on the parameter's value. I guess this should have been better explained in the first chapter (User Interface), it was most probably the victim of the cutting of that chapter (it was much longer and I thought the text boxes are not so often used).

Thanks, gabor

PS.: It is very late here, I hope I did not make too many mistakes.

Gabor,

   Thanks for the rapid reply.  In order of action

1.  I placed BirtSample.jar in the ...\jdk1.8.0_51\db\ folder.

2.  The derbyclient.jar file was located in the folder ...\jdk1.8.0_51\db\lib as you stated.  I started KNIME, set the "Databases" preference to that file and then closed KNIME.

3.  I started the Apache server, and it appears to be launch properly.  From the command window:

          Wed Aug 12 22:25:26 PDT 2015 : Security manager installed using the Basic server security policy.
          Wed Aug 12 22:25:27 PDT 2015 : Apache Derby Network Server - 10.11.1.2 - (1629631) started and ready to accept connections on port 1527

The second line from the command window is also the only line placed in the derby.log file.

4.  I start KNIME, load the DatabaseConnection workflow, and still obtain the messages about an Invalid URL for each of the database components.

WARN  Database Table Connector 0:2        java.lang.IllegalArgumentException: Invalid JDBC URL in settings:
WARN  Database Table Connector 0:4        java.lang.IllegalArgumentException: Invalid JDBC URL in settings:
ERROR Database Reader      0:9        Configure failed (IllegalArgumentException): Invalid JDBC URL in settings:
ERROR Database Looping     0:10       Configure failed (IllegalArgumentException): Invalid JDBC URL in settings:

5.  One thing that did catch my eye is that the Derby JDBC connection value is empty for all of these nodes.  Should it be?

Gabor,

  I forgot to mention two more steps.  As noted above, my first attempt at the process was simply to place the BirtSample.jar file in the ...\jdk1.8.0_51\db folder.  After that failed, I unpacked the JAR file (as you instructed) to a folder "BirtSample" placed in the ...\db\ folder.  The results were the same:  Invalid JDBC URL.

Also, I see that my images were not embedded in the prior post.  Step 2 showed a picture of the KNIME--> Databases panel with the path to derbyclient.jar in the textbox.  Step 5 showed the Output Variable info box having Derby JDBC Connection with an empty value, database location having a value of "BirtSample", and knime.workspace showing the path to my current workspace.

I'll try to determine why the images were lost.

Scott K. Starry

Hi Scott,

Just to clarify, in your install, there is a jdk1.8.0_51\db\BirtSample\log folder, but not a jdk1.8.0_51\db\BirtSample\BirtSample. Is this correct?

Thanks, gabor

PS.: To add images: click on the Image button on the toolbar of the editor, Browse Server, Upload and select the uploaded image (or its thumbnail). Unfortunately the inline images are not handled well in this forum. Alternatively you can attech them as file attachment, though in that case you cannot insert as an image to the post.

You are correct.  There is a log folder, but no BirtSample folder.
 

Sorry, I have no idea what might be causing this problem. From the C:\Program Files (x86)\Java\jdk1.8.0_51\db folder starting the server with the bin\startNetworkServer command it should just work. What you might try is copying the db folder (with BirtSample inside) to a folder not containing special characters (spaces and parentheses). Most probably that is not a problem, but I have no other idea why it works for me, but not for you (I also tried with Java8 (64 bit), so I doubt that would be the cause of the problem).

iGabor,

  To bring you up-to-date.  The Java version that I am using is 32-bit as I have the 32-bit version of Eclipse installed.  I uninstalled Java under the "D:\Program Files (x86)" folder, and moved it to the top level as you have it.  I took the Derby database from Apache (the file being db-derby-10.11.1.1-bin.zip), unpacked the file, renamed the folder to "db", and placed it under C:\Java.  Here are the contents of the Java folder

I then placed the BirtSample folder into the "db" folder so that the contents of "db" are

The contents of the BirtSample folder are the same as those shown in the Aug. 14 post.

==>  First, do these contents appear to be correct to you?  Is anything missing?

Next, I reset JAVA_HOME in the environment variables and configured the path to the DerbyClient.jar in KNIME.

Apache server was started without any difficulties (as before)

Sun Aug 16 00:40:04 PDT 2015 : Security manager installed using the Basic server security policy.
Sun Aug 16 00:40:04 PDT 2015 : Apache Derby Network Server - 10.11.1.1 - (1616546) started and ready to accept connections on port 1527

And, sadly, I still obtain the Invalid URL message.

==>  I did note that even with Apache running, there is no "db.lck" file present in the BirtSample folder or anywhere else on my machine.  When should (or what triggers) the lock file to appear?

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

However, there is one oddity, and maybe you can tell me where this variable is to be set.  In the Java Snippet is the line

out_DerbyJ = "jdbc:derby://localhost:1527/" + v_database;

You pointed me to the workflow variables to see how v_database (BirtSample) was set.

but when I look at the output variables of the Java Edit Variable Node, the value for the Derby JDBC Connection does not list "jdbc:derby://localhost:1527/"; instead, the field is empty.

==>  Should the value for the Derby JDBC connection be shown here?  That would seem to be appropriate.  If the value should be shown here, where can I set it?  Should I add it to the workflow variables?

The Database table connector does show the URL in the configuration

But the flow variable is shown as empty for the Database Table Connector:

===>  Is this what is expected to be seen?

Thanks for reviewing one more time to see if you can spot anything being amiss.

Scott K. Starry

Hi Scott,

I had one more idea that might work -ie. the changing the firewall settings to allow connections from localhost on the 1527 port-, but based on your detailed problem description (second image), I think I know what is the problem. You need to execute the Java Edit Variable node to make the rest of the workflow get configured. I guess that should have been emphasized more. In case of the Java Edit Variable node, the output variables are not present (with their computed values) till it is executed (most probably because it can cause side effects or they can check conditions varying in time). If you execute the path -> JDBC connection node, you should be able to execute the other nodes as well and check their results.

The table locks are created only when the database is read, so you will not see the lock file till at least one of the DB nodes are executed when the JavaDB was running.

I hope this solves your problem and let you explore the DB processing nodes.

Thanks, gabor

After executing the Java Edit Variable node, the message has changed to "connection was refused because the database BirtSample was not found".

INFO  Java Edit Variable   0:3        Java Edit Variable 0:3 End execute (0 secs)
WARN  Database Table Connector 0:2        java.sql.SQLException: java.sql.SQLNonTransientConnectionException: The connection was refused because the database BirtSample was not found.
WARN  Database Table Connector 0:4        java.sql.SQLException: java.sql.SQLNonTransientConnectionException: The connection was refused because the database BirtSample was not found.
WARN  Database Reader      0:9        Could not determine table spec from database query: The connection was refused because the database BirtSample was not found.
WARN  Database Looping     0:10       Could not determine table spec from database query: The connection was refused because the database BirtSample was not found.

I added BirtSample.jar to the C:\Java\db directory (and left the BirtSample folder in that folder as well):  No change.

I added C:\Java\db\BirtSample.jar to the KNIME Databases in the Preferences (still leaving the BirtSample folder):  No change.

I tried creating a connection using the Actian JDBC Connector to see if I could make any connection to the BirtSample database.  Same message is obtained.

Does something need to be added to the %PATH% or as an environment variable?  I have JAVA_HOME defined as C:\Java, and my PATH starts with %JAVA_HOME%\bin; %JAVA_HOME%\db.

Do I have the needed DB Connectors:  Here is what is in C:\Program Files\KNIME_2.11.3\plugins\org.eclipse.birt.report.data.oda.sampledb_4.4.2.v201410272105\db:

Do you think that 64-bit KNIME Is not playing well with 32-bit Java?  I know that 32-bit Eclipse cannot use 64-bit Java, but I don't know if the reverse (64-bit Eclipse with 32-bit Java) is a problem.

Scott K. Starry


 

Hi Scott,

   I am not sure what might be the problem. The 32-64 bit difference should not be a problem, the driver should handle the differences if there are any (though most probably there is none, two Java programs usually communicate -in this case through a TCP connection- without dependence of the host architecture).

   You do not need the drivers besides the derbyclient.jar for KNIME. The birtsample.jar is not meant to be used as a driver. I do not think the PATH or the JAVA_HOME environment variables cause problems.

   One thing I might imagine causing this: For authentication the server accepts connections on port 1527, which is open on your firewall, but after that they try to connect through another port (to let other clients also connect), which is blocked on the firewall. I know, this is quite improbable, but I really do not know what might cause this problem.

   I am sorry that this example does not work for you, I choose JavaDB because it is easy to install (but the driver was not installed in KNIME by default, so driver installation can be demonstrated). (I was also thinking about using SQLite.) For the sample, it was an easy choice, as the last chapter is about Birt and it had multiple tables. It seems I was wrong regarding the easy installation and usage.

Thanks, gabor

Resolved.

The error about the database not being found was correct if not helpful.

By default, the Derby database (and the Apache engine) look in %DERBY_INSTALL%\bin, where often %DERBY_INSTALL% is often the "db" folder under the Java installation, but could be anywhere.  (See http://db.apache.org/derby/papers/DerbyTut/ns_intro.html#ij_ns_client)  By moving my BirtSample folder from C:\Java\db to C:\Java\db\bin, the Database Table components now execute.

In the book's text, I would add these two steps

1.  Unzip the BirtSample.jar archive to expose the BirtSample folder.

2.  Execute the ij.bat command to create a test database to ensure the database access and creation are working. (See the URL above for the instructions.)

3.  Place the BirtSample.jar folder in the same folder where the test database was created.

==========

Though not reequired to execute the Database Connection workflow, the article mentions setting the CLASSPATH variable, apparently in reference to embedded Derby databases.  I'll let you be the judge of whether adding the CLASSPATH variable to the environment would be prudent.  I obtained the same (working) behavior both with and without this variable being set.

Scott K. Starry

Missed something that should be included in the instructions:  You DO need to have both the %JAVA_HOME%\bin and the %JAVA_HOME%\db\lib elements as part of your %PATH% environment variable.  For reference, below are my settings.  Again, I'm not sure that CLASSPATH is needed.

JAVA_HOME = C:\Java

JAVA_DB = %JAVA_HOME%\db
PATH = %JAVA_HOME%\bin;%JAVA_DB%\lib;C:\ProgramData\Oracle\Java\javapath;...
CLASSPATH = %JAVA_DB%\lib\derbytools.jar;%JAVA_DB%\lib\derbynet.jar

 

This thread can be closed.

Scott K. Starry

Thanks for figuring it out. Would you mind sending your findings as an errata?