Using JDataStore as a database for the Jive knowledge base

�Recently, Developer Support at Borland has upgraded support.borland.com and switched knowledge bases. See http://www.jivesoftware.com/borland-case-study.pdf for some of the details The new knowledge base is jive . Jive is a 100% Java based system. For a back end database, Jive provides support for the most popular databases (MSSQL, Oracle, DB2, MySQL, Postgress) and database access is through JDBC. Jive claims any database that is SQL-92 compliant should work. Unfortunately, Borland databases don't fit into the category of "most popular". However, we wanted to use one of our databases anyway. Primarily, we wanted to eat our dog own food. Secondarily, we wanted to save on licensing costs.

JDataStore was an obvious choice as a solution with it's 100% Java solution. With JDataStore's concentration on standards compliance we believed there should be no SQL incompatibilities. Additionally, we have the hope of mirroring the database to other Borland sites. (Not implemented yet.)

Jive provides scripts for the 4 most popular databases. We decided to take the scripts for MSSQL and see if we could make them work with JDataStore. We found there were 6 changes that had to be made.

Change NTEXT data type to VARCHAR
Change NVARVAR(n) data type to VARCHAR()
Change IMAGE data type to BINARY.
Change GO to COMMIT;
Change EXEC sp_rename 'old_name', 'new_name', 'OBJECT'; to ALTER TABLE old_name RENAME to new_name;
Change EXEC sp_rename 'table.old_name', 'new_name', 'COLUMN'; to ALTER TABLE table ALTER COLUMN old_name RENAME TO new_name;

Of these changes, none were difficult. The first 4 were simple search and replace statements any text editor can accomplish. I've done the last 2 statements mostly by hand until recently when I spent an hour or two writing a simple application in Delphi that accomplished all 6 tasks. While this code was written in Delphi, it could easily be converted to any other language quite quickly. If you are interest in my application respond to this article and I'll get a hold of you. Jive provides frequent updates at no additional charge. Every time they've upgraded we've had to go through this step.

The biggest problem we encountered using JDataStore with Jive revolved around what Jive considers attachments. Attachments come in two flavors. First is just a download associated with a knowledge base article. Secondly is a link to an image in an article. We experienced two different problems with attachments. While there were two different problems, they both had the same result - attachments ended up not being saved. This made it seem like one problem. The first problem ended up being purely a client side programming issue. Articles and attachments have a primary key/foreign key relationship. The jive application was attempting to insert the foreign keys before the primary key, which naturally failed. As I write this, Jive has provided fixes for this problem for downloads and linked images, but we have not implemented the linked images fix yet. So, if you look long enough, you will find some broken IMG SRC links in the knowledge base. The second feature was that occasionally a trace log would show a "Feature not supported" error. This worked out to be Jive was using the LOB support API of� JDBC which neither JDataStore or many other JDBC driver vendors support. However, MSSQL 2005 does. Earlier versions of MSSQL do not support it either, so it was a problem for other Jive customers as well. As it worked out, while the LOB support API was being used, it wasn't needed. JDataStore R&D rewrote about 4 lines of offending code into 2 and the problem was solved.

Other than implementing new upgrades as they come out from Jive, I think the last challenge for project will be to implement mirroring. Be sure to watch this article, as I'll update it with any information on mirroring when it is worked on.