With the release of File Catalyst Direct 3.0.1, Central will provide the ability to enable external access to its database of transfer information.
First, a little basic information: Central uses a Derby database to store its data. This means that whatever tool you are planning on using to access the database will need to be able to interact with Derby. In order to do this, the tool must be able to use the Apache Derby Client driver. My preferred client is a free Java based tool called SQuirrL SQL Client which includes the Apache drivers by default. This is the tool that will be used in this how-to. However, you can use any tool that supports the driver. You will just need to adjust your set up as appropriate for your chosen tool.
Connecting SQuirreL to FileCatalyst Central DB
The first step is to configure Central to provide access. Currently the configuration UI of Central does not allow for configuration of the database access, so we need to make the changes directly to the MAConfig.conf file which can be found in the install directory. In order to make these changes, it is best to shut down the Central service.
Once Central is shut down, open MAConfig.conf in a text editor and scroll down to the bottom. You are looking for the parameter:
- FCMonitoringAgent.config.allow.external.database.access This parameter determines whether or not the database will be accessible or not. If true, it is. If false it is not.
There are four other parameters that may also be set:
- FCMonitoringAgent.config.derby.drda.host This value is the address that remote hosts must use to connect to the database. It defaults to "localhost" if no value is set, preventing any non-local access to the database. This value can be any address that points to the central server. If you wish to allow anyone access, as long as their address points to the correct machine, the value must be set to "0.0.0.0". In this case, the network server will listen on all interfaces.
- FCMonitoringAgent.config.derby.drda.portNumber This value is the port which clients use to connect to the server. The default value is "1527". This is the standard port for Derby databases. You can use any port number here that is available on the machine.
- FCMonitoringAgent.config.external.database.username This value is the username used for remote access. The default value is "username". Because this value is loaded in from the configuration file and is not encoded in any way, it is limited in the characters that it can accept. It is recommended that you use a simple string of alphanumeric values. When this information can be modified from the UI, it will be encoded and thus able to be a more complex string.
- FCMonitoringAgent.config.external.database.password This value obviously sets the password for the remote access and defaults to "password". It has all the same issues as the username.
As with any Central configuration value, if the values are not present in maconfig.conf, they will be set to the defaults. Once you have your values set, you can restart Central and move to the next step in connecting to the Central database: configuring your client. For this article I will be using SQuirrL as the client, but the basics should be similar for any client that supports the Derby driver.
In SQuirrL we want to add an Alias. This is what the new alias dialog looks like:
As you can see, most of the values are pretty straight forward. The URL, however is a little complicated because it contains most of the information that is used to connect to the database.
Broken down, it is less complicated than it looks. The part at the end between the square brackets we can ignore as it is just for additional attributes that we don't use. The only values we need are the <server>, <port> and <databaseName>. The square brackets around the port value indicate that it is optional. If you leave it out, the connection will use the Derby default port of 1527. Do not use square brackets in the URL.
Here's one I prepared earlier:
As you can see, once the values are filled in, it doesn't look nearly as complicated. Our server is set to localhost, our port to the default of 1527 and finally our database to the name of our database: MONITOR_DB. The Name at the top is just for recognition purposes, it can be anything you like.
The username and password should be the values that we configured earlier. In this case I am using the default values. I like using the auto logon feature of SQuirrL so I have selected that but I like to control when the connection happens so I did not check the Connect at Startup option.
If you like you can test your connection and then once it is connecting successfully, you can hit OK. At this point you should see the name you entered in the aliases list:
Double click on the alias and SQuirrL will open and Auto Logon to the database. This opens a tab that shows the contents of the database.
The only branch on the tree that we need to concern ourselves with is the MONITOR_DB branch. As mentioned above, this is the name of our database and this branch contains all of the tables with our data under TABLE. However, before we are able to access our data, we have one more step.
In Derby, the database assumes that you want to connect to a schema within the database that is the same as the username you have used to connect. In this case, it assumes that we want to connect to a schema within the database named USERNAME as that is the username we used. Not only is this not the schema we want, it doesn't even exist. While we can open the schema with the above viewer and examine the contents, we do not have the ability to make queries which is likely the reason you wanted to connect to the database in the first place.
In order to access the correct schema we need to switch to the SQL tab and enter the command:
SET SCHEMA MONITOR_DB
After we have entered this command we can now retrieve data from the database. Note that the external user is read only so only selects may be done.
Here is our current Schema: