pdxJDBC notes

• pdxJDBC is a JDBC driver for Paradox tables that comes with Paradox 9. It is part of the jPdox Web Utilities, so you must run the separate InstallAnywhere-based installion to get it. pdxJDBC supports JDK 1.2. Documentation is in the form of a PDF version of the jPdox Web Utilities book.
• The driver consists of a client and a server. Presumably this design was chosen so the server can be on another machine. It will have to be a Windows machine, because the server accesses Paradox tables via BDE (version 5.01). RMI also comes into the picture somewhere, but I'm not sure how. I have only tested with JBuilder, the pdxJDBC driver, and the Paradox tables all on the same Windows machine.
• I got an error message pointing to the registry entry for jvmDllPath when I first ran pdxJDBC. Editing both entries (in HKEY_CURRENT_USER and HKEY_LOCAL_MACHINE) to include the missing "jre" directory level in the path (so it actually leads to jvm.dll) solved the problem.
• It's a good idea to enable logging in the driver by setting the LogCalls option in <jPdox_install_dir>appsrvappsrv.properties to true. Then start the server by running <jPdox_install_dir>appsrvjdbcsrv.exe. You will see "Build 1.00.367" and "JDBC server ready" or similar messages. If you forget to run the server before designing or running your JBuilder application, you will get a DataSetException with the message "While trying to lookup RMI server, there was a remote exceptionConnection refused to host".

• To enable pdxJDBC in the JBuilder designers, follow the appropriate step for your version of JBuilder:
• In the native Windows version, JBuilder 3.0: Add the client jar, <jPdox_install_dir>webformjarsPdxJdbc.jar, to the classpath entry in <JBuilder_install_di>binJBuilder.ini.
• In JBuilder 3 - Solaris Edition or JBuilder 3.5: Use an "addpath" statement in JBuilder.config (in the /bin subdirectory) to add the client jar to the classpath. Note that the config file specification call for forward slashes to separate directories. For example:
addpath C:/Paradox9/jPdox/webform/jars/PdxJdbc.jar

• To use pdxJDBC at runtime: Define a library for the client jar and add it to the project.

• Import com.corel.pdx.driver.* in your java file.

• In the ConnectionDescriptor for the Database object, set the driver name to "com.corel.pdx.driver.PdxJDBCDriver" and the URL to "jdbc:bdea://localhost/PdoxAlias;SM=SessionMgr" where:
• PdoxAlias" is a Paradox directory alias.
• "SessionMgr" is the pdxJDBC server's session manager. The default string "SessionMgr" is correct unless you change the session manager name in <jPdox_install_dir>appsrvappsrv.properties.

With JBuilder set up as described above, you should see live Paradox data in the UI Designer.  However, your application will probably fail at runtime with a security-related exception.  It may be wise to confirm that  you can see Paradox data in design before trying to run your application.  This will let you separate any driver setup problems from the  runtime security issues.  For instance, "Connection refused to host" isn't a security problem,  it just means that you forgot to start the pdxJDBC server, so it will happen in both design and run.

One typical security-related exception occurs when ImageIcon can't find a resource:
    java.lang.ExceptionInInitializerError: java.lang.NullPointerException:
        at javax.swing.ImageIcon.<init>(ImageIcon.java:106)
        at com.borland.dbswing.DBTextDataBinder$UndoAction.<init>(DBTextDataBinder.java:1498)
        at com.borland.dbswing.DBTextDataBinder.<clinit>(DBTextDataBinder.java:159)
        at com.borland.dbswing.JdbTextArea.commonInit(JdbTextArea.java:158)
Another exception is a java.net.socketPermission; the first few lines of its stack trace look like this:
    com.corel.pdx.driver.PdxJDBCSQLException: Error while trying to connect to database,
        Exception message: access denied (java.net.SocketPermission 127.0.0.1:1099 connect,resolve)
        at com.corel.pdx.driver.PdxJDBCDriver.connect(PdxJDBCDriver.java, Compiled Code)
        at com.corel.pdx.driver.PdxJDBCDriver.connect(PdxJDBCDriver.java, Compiled Code)
        at java.sql.DriverManager.getConnection(DriverManager.java:457)

I don't know enough about Java 2 security or pdxJDBC to resolve these exceptions properly or even understand them.  For instance, when JBuilder 3.5 displays the socketPermission exception, it's in the standard JBuilder exception dialog but has a "Warning:  applet" banner at the bottom. I'm sure this is significant, but I don't know quite what it means.  Here is what little I do know about security:

Java 2 uses policy files to control in a fine-grained way what operations a given program may perform.  The default security policy is the same as the "sandbox" model in Java 1.  You can override this by plugging in a different policy file.  The default behavior, defined in the JDK's jrelibsecurityjava.security file, is to use two policy files, if they exist:  java.policy in jrelibsecurity and .java.policy (note the leading period in the file name) in your Java home directory (actually, your default Java home directory, even if you point to another when you run java).  So there are a number of ways to control security, including:

create or edit policy files with the default names and locations

 point to different policy files in java.security

alter security options in java.security

point to a different policy file on the java command line.

I took the easy way out and created a .java.policy in my Java home directory.  I used a brute force solution:

grant \{
  permission java.security.AllPermission;
\};

With this file in place, a JBuilder application can view and edit Paradox data.  However, this policy file grants too much access - all access, to everybody - to be used with a production application.  (jPdox grants the same access in <jPdox_install_dir>appsrvappsrv.policy, with the comment "Allow everything for now", but I haven't seen any explanation when this file is used.)  A better policy would be:

// JBuilder 3.0 for Windows
grant codebase "file:/C:/JBuilder3/-" \{
  permission java.security.AllPermission;
\};

// Paradox
grant codebase "file:/C:/Paradox9/-" \{
  permission java.net.SocketPermission "*:1099-1101", "accept,connect,listen";
\};

But I have had only intermittent success with this policy file.  Anyone who wants to distribute a web application that uses pdxJDBC will have to work through the security issues more thoroughly.
 

Using pdxJDBC in JBuilder

Datatypes

Here are the default JBuilder datatypes (as Variant constants) produced when querying supported Paradox datatypes:
 

I've omitted all the blob types from this table because I don't know if they're supported.  JBuilder can view a Paradox table containing an empty column of any blob type, which it maps to INPUTSTREAM.  But if there is data in the column, the query fails with a message like "In Resultset.getBytes, while setting byte blob to be retrieved, there was an Unexpected exception on the server - Internal error:Invalid operation [Error Code: 5004]".

In JBuilder 3 - Solaris Edition and JBuilder 3.5, dbSwing's default cell renderer/editor for an INPUTSTREAM column expects an image and supports BMPs in addition to GIFs and JPEGs.  So if blob types can be made to work, it should be easy to handle Paradox's Graphic type. In prior versions of JBuilder, you would have to handle BMPs yourself.  In all versions of JBuilder, you would have to write code to display and edit other blob types.

Database properties

In order to save changes in a dataset back to a database, JBuilder generates SQL INSERT, DELETE, and UPDATE statements.  It tries to customize these statements based on the properties of the target database.  In particular, it considers case-insensitive identifiers (table names and field names) as the usual situation but allows case-sensitive identifiers as well.  When pdxJDBC is used, JBuilder determines, incorrectly, that it should surround field names with quotes in order to preserve their case.  (I think pdxJDBC is returning "Not implemented" exceptions when JBuilder examines metadata on casing of field names.)  This conflicts with Paradox's rules for quoting field names and usually results in the error message "Invalid use of keyword".  The solution is to set the Database's useTableName property to true.  This forces JBuilder to append the target table name before every field name in queries it generates, so all field references are of the form
    tablename."fieldname"
Conveniently, this is the form Paradox requires for  field names that contain spaces, so these fields are handled properly.

This is for queries that JBuilder generates.  You should use the same format when you supply a SQL query as part of a QueryDataSet's query property.  In the Designer, you might open the editor for the query property and enter a SQL statement like
    select biolife."species no", category from biolife
As a QueryDescriptor parameter in your code, the query will look like this:
    "select biolife."species no", category from biolife"

QueryDataSet and Column properties

For every column in a QueryDataSet, PdxJDBC reports that the column is not a row ID and is not searchable.  Without row ID information, JBuilder can't determine which row in a Paradox table corresponds to a given row in a dataset.  As a result, any attempt to save edits made in the dataset back to the Paradox table will fail. Identifying one or more row ID columns (and making them searchable, as row IDs should always be) solves this problem, but  leaves a possible concurrency problem:  because the corresponding row in the Paradox table is determined by matching values in row ID columns only, your update could over-write another user's update to values in columns that are not row IDs.  So it's better to make as many columns as possible searchable.

Here's how to set row ID and searchability information:

• Instruct JBuilder not to get metaData about row IDs and searchability.  In the UI Designer, open the editor for the QueryDataSet's metaDataUpdate property and uncheck the appropriate boxes.  Or, in code, set the dataset's metaDataUpdate property to
MetaDataUpdate.TABLENAME+MetaDataUpdate.PRECISION+MetaDataUpdate.SCALE
• Decide which column or columns uniquely identify a row in the dataset and set each of these columns' rowId property to true.  It's best to do this in the UI Designer.  JBuilder will make each row ID column persistent - that is, it will create a column object for each and set all the necessary properties.
• Once you set the QueryDataSet's metaDataUpdate property so it will not fetch metadata on searchability, the searchable property for all columns will default to true.  Set the property back to false if you don't want a column to be searchable.  This is unlikely unless
• You are able to work with Paradox  blobs in JBuilder.  In this case, you might make some or all blob fields non-searchable.
• You need to make a Number, Money, or BCD column non-searchable because of loss of precision in the conversion from Paradox to JBuilder and back.
• You have instantiated a QueryResolver and set its updateMode property to a non-default value

It's clear that pdxJDBC's support for metadata is a little weak.  To be fair, JDBC specifies a huge number of metadata-related functions, and doesn't require that a driver return a result for every one - it is permissible to throw a SQLException instead.  But it seems that pdxJDBC doesn't provide JBuilder with some information that an application-builder tool needs.

For example, JBuilder' query property editor can display a list of tables in a Paradox database, but can't list the columns in the selected table.  If you try this in the query editor, you will see that several com.borland.dx.sql.metadata.MetaDataExceptions are thrown each time you select a table.  I assume this is because pdxJDBC is not passing back the expected information. There may be other such examples.