Debugging a Category’s Bad Child Products

Today I was using the CategoryLookup droplet to find a category’s child products. However when I accessed the child products I would get this JDBC error.

at java.lang.String.(
at oracle.sql.CharacterSet.AL32UTF8ToString(

I realized my product data was corrupted. However this category had over 70 child products. The stack trace wasn’t telling me which one was corrupt and going through each child product to find out which one was corrupt was too painful.

I first queried the database to find all the child product ID’s.

SQL> select child_prd_id from dcs_cat_chldprd where category_id='cat101' order by sequence_num;

I then created a simple JHTML page which would query each child product and output which ones were corrupted.

final String [] product_ids = { "prod101", "prod102", "prod103" };

for (int ii = 0; ii < product_ids.length; ii++) {
out.print(ii + ". [" + product_ids[ii] + "] ");
try {

} catch (RuntimeException exc) {
out.println(exc + "


Once I knew which child products were bad I removed their mappings to the category in dcs_cat_chldprd.

SQL> delete from dcs_cat_childprd where child_prd_id = 'prod102' and category_id='cat101';

Then I updated the sequence numbers so that they are all consecutive by moving the ones at the end to fill the holes created by the previous deletes.

SQL> update dcs_cat_childprd set sequence_num = 1 where child_prd_id = 'prod103' and category_id='cat101';

How to Log SQL on JBoss

Edit the log4j.xml in the conf directory as shown below to turn on SQL debugging of the JDBC CMP plugin.

/apps/jboss/server/default/conf :->diff -c log4j.xml~ log4j.xml
*** log4j.xml~  Mon Sep 30 18:09:27 2002
--- log4j.xml   Tue Apr  4 20:41:18 2006
*** 61,73 ****




--- 61,79 ----




If you want to log Hibernate SQL statements:


If you want to log everything Hibernate’s doing, including SQL statements, schema export, transactions, etc.:


JDBC Plugins for Eclipse

I was previously using DbVisualizer to query and update my Oracle databases and was not completely satisfied with it. I actually prefer PhpMyAdmin but that only works with MySQL.

Since I use Eclipse so much for development I decided to look for a JDBC plugin that is at least as good as DbVisualizer. My goal is to have in one workspace access to my database as well as my source and configuration files.

I went to EclipsePlugins and downloaded the three highest rated JDBC plugins, all which happen to be free (in some cases just for non-commercial use).

  1. DBEdit
  2. QuantumDB
  3. SQLExplorer

I installed DBEdit 1.0.3_1, Quantum DB 3.0.1 (which requires GEF, I installed 3.1.1), SQLExplorer 2.2.4.

Setup was quite simple with DBEdit, just add the JDBC driver to the CLASSPATH, give it the JDBC URL and connect. Setup was almost as simple with Quantum DB though it asks you for the components of the JDBC URL instead of the URL directly. I couldn’t figure out how to setup SQLExplorer within five minutes so I gave up.

DBEdit and Quantum DB have similar interfaces and both are similar to DbVisualizer though Quantum DB’s appealed to me a little more. However Quantum DB’s interface responded much more quickly and querying of tables was much faster. DBEdit has a nice feature, which I did not completely test, of inline editing of cell values which seems quite powerful.

Because of Quantum DB’s speed and responsiveness I am going to continue to use that as my primary JDBC plugin but I will also keep DBEdit on the side to use for its inline editing but also in case I find Quantum DB does something in an unintuitive manner.

Update: Since I posted this less than a month ago I found DBEdit much more useful than Quantum DB. It’s inline editing, inserting feature, and other editing features really make it powerful. Also its filter, scrolling through result sets, etc. are great. I highly recommend DBEdit.

JDBC Optimization for Populating a Table

Today I was trying to determine how to optimize the populating of a table. I was using ATG Relational Views which took 3.5 minutes to add 6000 lines to a table.

After googling for awhile I learned how to do this using JDBC directly and was able to do the same populating in 0.14 minutes. That’s quite a performance improvement.

It would be interesting to contract the performance differences using ATG’s repository implementation but right now I am developing on ATG 4.5.1 so I can’t.

These are the links to the sites I used to educate me on PreparedStatement‘s and batching. An Introduction to JDBC, Part 3 Overpower the Prepared Statement Best practices to improve performance in JDBC Optimize Oracle INSERT performance