How to query PAS log with a SQL client

Here is a way to access it outside the log for PDF Automation Server using a SQL client that supports JDBC. We use a small, embeddable, file-based, java database called Derby, or also called JavaDB, for the PAS log. One caveat is that only 1 client can connect to the database at a time.

The root log folder is in the installation directory in PASLog. If the PAS service is running, there will be db.lck file in the directory indicating that the database is being accessed. In order to query the log with an external client, you must stop the PAS service (the db.lck file will be deleted when the service disconnects). You can then connect a different SQL client (which will then create the db.lck file), and perform queries. Do not restart the PAS service when another SQL client is connected to the log, or else PAS will not be able to log normally (it will default to an xml log file).

The safest way to query the log with as little interruption time as possible is to stop the PAS service, make a copy of the PASLog folder (for example PASLogBak), then restart the PAS service. You can then connect the SQL client to the PASLogBak database without interfering with the running PAS service.

You can use a different client, but SQuirreL SQL Client is a pretty easy one to use. Installation instructions are pretty simple, just open a cmd prompt and type java –jar squirrel-sql-<version>-install.jar. Make sure you select the Derby optional plugin during installation.

To enable the Apache Derby Embedded driver:

  1. Launch SQuirreL and select the Drivers tab
  2. Double-click the Apache Derby Embedded driver to modify it
  3. Select the Extra Class Path tab
  4. Click Add and navigate to the QoppaPAS/lib folder and select derby.jar
  5. Click List Drivers and select “org.apache.derby.jdbc.EmbeddedDriver” from the list and click OK.

The Apache Derby Embedded driver should now have a blue check next to it indicating that it is active.

Enable Apache Embedded Driver in Squirrel
Enable Apache Embedded Driver in Squirrel

Create a new alias with the following properties:

QoppaPAS Alias

Make sure to select the same driver, the URL (jdbc:derby:c:/Program Files/QoppaPAS/PASLogBak;create=false) depends where you made a copy of the PASLog directory, and the password is “qoppa”.

Once connected, you can query the log.

For instance, here is a query returning all “printing” messages for Job_12, the “Print PDF files” Job.

Log Sample Queries

For more information on creating jobs to automate the printing of PDF files with PDF Automation Server (PAS), please look at PAS documentation.