JBuilder fails to find row to update during save

Making Jbuilder resolve using table keys only

Jbuilder is a fantastic rapid application development tool. It offers drag and drop database application building backed by a powerful set of libraries that you can free redistribute with your application. Jbuilder, combined with the awesome power of Postgres is an application infrastructure that is difficult to beat. In fact, there are no similar platforms that offer the same enterprise capabilities but without the need for a license when it comes to distributing your work. Jbuilder and Postgres also work extremely well together, straight out the box. Deploying an application based on these two technologies is cheap, easy and usually trouble-free.

Jbuilder's weakness is its poor documentation and lack of third party books. Many Jbuilder tricks and techniques implemented in this powerful RAD are either tucked away in difficult-to-find parts of the online documentation or are simply not documented at all. Even when you know that a feature exists within Jbuilder, finding the right search clause to use to elicit this knowledge from the help system can be tricky, if not impossible without the knowledge that you are seeking.

One such problem is using Jbuilder's database express and dbSwing components with a Postgres schema based views. As we have seen, views, rules and triggers are the backbone of a good security implementation and are essential to segmenting a dataset into user-specific or user-group-specific virtual datasets. One of the many powerful facilities in Postgres is the rules system that allows views, even complex view formed from many joins across many data sources, to become updatable. A well written set of rules applied to a Postgres view can implement security, audit trails, change tracking as well as the business logic to update correctly the inbound changes from the application back into the underlying data source that the view draws upon. Not only does Postgres support this design, but a careful use of updatable views, especially when constructed from other views as well as tables, can result in extremely efficient queries and good performance thanks to the ordering of processing implicit in the use of other views in joins and the logic implemented within the update rules, procedures and triggers.

In addition to the runtime and security considerations for implementing this logic within Postgres there is the development overhead and Jbuilder capabilities to consider when attempting to implement the same logic within the Jbuilder application. Jbuilder's resolvers and RAD facilities quickly break down if you attempt to chain long and complex queries into database applications. By far the simplest and most reliable way to develop a complex database application with Jbuilder is to use Postgres to implement the database logic and Jbuilder to present this logic to the user for updating. So if your application uses a 3^rd normal, secure and segmented data set, make Postgres do all the work.

But before you go away happily to database application nirvana, there is, as always, a nasty catch.

The most useful (as was a rapid) part of Jbuilder's data express and dbSwing routines undoubtly are the powerful resolvers that are built into Jbuilder. Data express and dbSwing components take a query and place the resultant dataset into a Jbuilder dataset component. If updates are made to that Jbuilder databaset component's data, Jbuilder's resolvers understand the query and can create suitable SQL statements on the fly to save those data amendments back it into the Postgres tables. Note the word tables. Because when it comes to using views per our discussion above, Jbuilder's default resolvers mysteriously break down.

If your tables have unique row identifiers (as most good database designs would implement), then you can tell Jbuilder which column(s) in the dataset form a unique identifier for each row by the following:

1. Click on the query dataset in the structure panel. This will change the inspector panel's contents so that they query information for that dataset is displayed.
2. Click on the update meta data option. This will pop up a dialog panel with the metadata that can be automatically detected from the query by Jbuilder. Unselect the rowid option if this is selected then press ok.
3. In the structure panel, expand the query dataset and select the row(s) that you wish to mark as a rowid.
4. In the inspector panel, set rowid to true.
5. Now select the columns that do not form a rowid an in the structure panel and ensure that they have rowid set to false in the inspector panel.

The above will ensure that Jbuilder, when it attempts to resolve changes back to the database, will use only those columns that you specify as rowids to address each row during the updating. The only problem is that this doesn't work. You get an error message that looks like this:

See com.borland.dx.dataset.DataSetException error code: BASE+51

com.borland.dx.dataset.DataSetException: The row specified by the resolution query was not found.

Typical reasons are:

Somebody deleted or changed this row.

A floating point comparison failed to match.

A CHAR field needs space padding (see Database.setUseSpacePadding).

Query:

Query:

Row values used for finding existing row:

The reason for this is that Jbuilder has ignored your rowid settings. The default behaviour of the resolvers is to use all the columns in the dataset to find data in the underlying tables and if you have automatically updated columns (such as a timestamp column recording when the row was last updated or a username column recording who made the change), then your query will fail.

Now you would have thought that this was emblazoned across a hyperlink universe in the Jbuilder documentation but it is not. In fact, you cannot find this information anywhere in the Jbuilder documentation using the normal or logical searches that you would normally use to find out why your updates are failing. Even the error message itself helpfully points out all the reasons why this update could have failed, except the real reason: The resolvers are not working properly.

All is not lost, though. There is an annoyingly simple solution to this otherwise unsolvable problem. You simply add a query resolver to your application and set a property element on the resolver to tell it to use the column keys that you have specified in order to make the updates. This is how you do it:

1. Click on the data express tab.

2. Click on the QueryResolver button (com.borland.dx.sql.dataset.QueryResolver).

3. Add this to your application.

4. Select this query resolver in the structure pane.

5. Give it a meaningful name in the inspector.

6. Also in the inspector, change updateMode to be key columns only.

7. Now, in each of your query datasets (click on them in the structure pane), simply click on the resolver option and your new QueryResolver will appear in the dropdown! Select your QueryResolver.

HINT: To make bulk changes,hold down the ctrl key and click each of the components that you wish to make the change to, they all become selected and you can make the change once in the inspector panel saving time and effort.

Hey presto! Suddenly your updates will work as you expect them to!

Maybe one day, someone from Borland will read this page and implement a fix, if not to the designer itself, at least to the error message generated when the updates fail pointing us to the information we need to not fall into this trap!

Honeypot: spam@kieser.net