This is the first in a four part article walking through things you need to know about the JDBC driver and some of its more common error codes. The goal is to make your life easier in implementing the driver by documenting the problems I ran into, in the hopes you will not have to fight the same issues.

About the IDM JDBC Driver

The IDM JDBC driver is a complex and powerful driver. It supports a number of different databases, each with its own set of vagaries and idiosyncrasies. To work with this driver, it pretty much requires a database browsing tool. This is much like the case where working with eDirectory in detail requires the use of the Other tab in ConsoleOne, or using DSBrowse to look at what is REALLY stored in an attribute, not what the tool claims is stored. Also useful is the ability to see what the attribute (for eDirectory) or column (for databases) is actually named, instead of what the applications call it.

We found that DBVisualizer, a free Java application (there is more fully featured commercial version), is an excellent tool for looking at the database. If you are used to working with databases, then perhaps you will have native tools. If you do not have any, then look for DBVisualizer. It uses the same JDBC driver that the IDM JDBC driver uses, so your view of the data will be as close as possible to what the IDM driver sees.

Troubleshooting

There are many possible errors that can come up using the driver, and I thought I would go through some of them and explain what they are - and if possible, how to resolve them. These are ordered in the way I encountered them in implementing a JDBC driver, so it may actually be a useful ordering! The hope is that by discussing what the errors encountered are, and how I resolved them, it will help anyone else running into similar issues. At the very least, it should give hints as to where to look further for troubleshooting.

The JDBC driver documentation includes a few possible errors and troubleshooting for them at the following link:
http://www.novell.com/documentation/idm35drivers/jdbc/index.html?page=/documentation/idm35drivers/jdbc/data/b6tdvp8.html

This article assumes you know a fair bit about Identity Manager (IDM) in general, and possibly the JDBC driver in particular. Samples of Dstrace with error codes will be shown, so it is assumed you have an understanding of how to read a trace from IDM. Examples of rules in Policy Builder, will usually be shown as the basic XML code that the interface generates. On the one hand, this is not as pretty as a screen shot, but on the other hand the DirXML Script syntax is relatively straightforward, and it's much easier to provide examples that can be used.

If you are not aware, the best way to play with samples of XML would be to open a project in Designer for Identity Manager, either a real one from your environment, or just make one up for test and demo purposes. I usually keep a scratch pad Designer project where I paste interesting snippets of DirXML Script that I find, to play with the options in Policy Builder. Once you have a project with at least one driver, pick any rule set. Depending on how much XML is provided, you should be able to paste it into the rule. Be aware that I will probably not post any complete rule sets, so you will need to paste into appropriate locations. If it is a conditional test, you can paste it into the Condition group of a rule. If it is an <action> you can paste it into the Action area.

In general, the driver does the synchronization of attributes to the mapped database elements on its own, much like any Identity Manager driver. For a variety of reasons in our project, we had to do a lot of direct SQL commands. This was mostly because we had a third table that needed to be updated, but we had no interest in synchronizing the third table (with hundreds of thousands of entries) into our Identity Vault. When we closed a Work order we had synchronized from database, after processing it in the ID Vault, we had to create an entry in the third table with some information. Thus, a lot of the work was spent on writing direct SQL calls instead of just synchronizing attributes, as would be the usual case in a JDBC driver.

First, let's see what it should look like when it just plain works, such as INSERT and UPDATE in one example. I should mention that all these examples use a database where the view is named IDM, and two tables - CLIENTS and WORK_ORDER - are being used.

This example is actually a direct SQL call via embedded SQL. A normal eDirectory event shown as an XDS document looks pretty much the same as any other driver. This example is much more interesting, as it shows a different type of <input> document. See http://www.novell.com/coolsolutions/feature/19504.html for more details on embedding SQL via Policy Builder. (You could do it in XSLT as well, but why?)

<nds dtdversion="3.5" ndsversion="8.x">
<source>
<product version="3.5.0.20070315 ">DirXML</product>
<contact>Novell, Inc.</contact>
</source>
<input>
<jdbc:statement jdbc:transaction-id="3" jdbc:transaction-type="manual" jdbc:type="update" xmlns:jdbc="urn:dirxml:jdbc">
<jdbc:sql>INSERT INTO IDM.WORK_ORDER_DETAILS (PK_SEQUENCE, WO_NUM, DESCRIPTION, NOTE, LASTMODIFIED, SEQ_GROUP, INACTIVE, DURATION, SEQ_ACTION, LASTUSER, TO_GROUP, FROM_GROUP) VALUES ('419904', '91109', 'Forwarded WO to Group NOC', 'The IDM process encountered the following error: APP-WO: Unable to process due to missing acmeBusinessUnit. This action requires the following attributes: acmeBusinessUnit, acmeABCSupervisor, company, vendorPhoneNumber, acmeABCFirstName, acmeABCLastName, acmeABCvendorPhone, acmeABCexpirationDate', to_date('08/17/2007 01:15:07', 'MM/DD/YYYY HH24:MI:SS'), '1061', '0', '1', '16', 'IDMSCRPT', 'NOC','NOVELL-IDM')</jdbc:sql>
<jdbc:sql>UPDATE IDM.WORK_ORDER SET SEQ_GROUP = '1023' WHERE PK_WORK_ORDER_NUM = '91109'</jdbc:sql>
<jdbc:sql/>
</jdbc:statement>
<modify class-name="IDM.WORK_ORDER" event-id="PK_WORK_ORDER_NUM=91109,table=WORK_ORDER,schema=IDM" qualified-src-dn="O=LAB\OU=APPS\OU=APP\OU=Work Orders\CN=SW_ABC001-91109" src-dn="\ACME-LAB\LAB\APPS\APP\Work Orders\SW_ABC001-91109" src-entry-id="52570" timestamp="1187374507#61">
  <association state="associated">PK_WORK_ORDER_NUM=91109,table=WORK_ORDER,schema=IDM</association>
</modify>
</input>
</nds>

The shim will return a status document that looks something like this one, with one jdbc:update-count per SQL statement, one for the INSERT and one for the UPDATE. The returned status events should be in order of the submitted SQL statements.

<nds dtdversion="2.0" ndsversion="8.x" xmlns:jdbc="urn:dirxml:jdbc">
 <source>
  <product build="20070626_0243" instance="MAGIC-JDBC" version="3.5.1">DirXML Driver for JDBC</product>
  <contact>Novell, Inc.</contact>
 </source>
 <output>
  <status event-id="PK_WORK_ORDER_NUM=91109,table=WORK_ORDER,schema=IDM" jdbc:update-count="1" level="success"/>
  <status event-id="PK_WORK_ORDER_NUM=91109,table=WORK_ORDER,schema=IDM" jdbc:update-count="1" level="success"/>
 </output>
</nds>

This is a view of the Remote Loader shim side, of a Query and the resulting response document being generated.

<nds dtdversion="3.5" ndsversion="8.x">
  <source>
    <product version="3.5.2.20070719 ">DirXML</product>
    <contact>Novell, Inc.</contact>
  </source>
  <input>
    <query class-name="IDM.CLIENTS" dest-dn="PK_SEQUENCE=363070,table=CLIENTS,schema=IDM" event-id="0" scope="entry">
      <association>PK_SEQUENCE=363070,table=CLIENTS,schema=IDM</association>
      <read-attr attr-name="APP_OPID"/>
      <read-attr attr-name="APP_PROFILE"/>
    </query>
  </input>
</nds>
DirXML: [10/17/07 14:15:11.27]: TRACE: SELECT PK_SEQUENCE, APP_PROFILE, APP_OPID FROM IDM.CLIENTS WHERE PK_SEQUENCE = ?
DirXML: [10/17/07 14:15:11.27]: TRACE: 	IN @ index 1, field 'PK_SEQUENCE', value = 363070
DirXML: [10/17/07 14:15:11.27]: TRACE: 	RS field 'APP_PROFILE', length: 3, value = 'aaa'
DirXML: [10/17/07 14:15:11.27]: TRACE: 	RS field 'APP_OPID', length: 3, value = 'bdl'
DirXML: [10/17/07 14:15:11.27]: TRACE: Remote Loader: Sending...
DirXML: [10/17/07 14:15:11.27]: TRACE: <nds dtdversion="2.0" ndsversion="8.x" xmlns:jdbc="urn:dirxml:jdbc">
  <source>
    <product build="20070626_0243" instance="MAGIC-JDBC" version="3.5.1">DirXML Driver for JDBC</product>
    <contact>Novell, Inc.</contact>
  </source>
  <output>
    <instance class-name="IDM.CLIENTS" event-id="0" src-dn="PK_SEQUENCE=363070,table=CLIENTS,schema=IDM">
      <association state="associated">PK_SEQUENCE=363070,table=CLIENTS,schema=IDM</association>
      <attr attr-name="APP_OPID">
        <value type="string">bdl</value>
      </attr>
      <attr attr-name="APP_PROFILE">
        <value type="string">aaa</value>
      </attr>
    </instance>
  <status event-id="0" level="success"></status>
  </output>
</nds>
DirXML: [10/17/07 14:15:11.27]: TRACE: Remote Loader: Document sent.

You can see in the snippet shown below how the XDS query verbs output gets translated into a SQL SELECT statement. It looks for PK_SEQUENCE, which is the Primary Key column in our examples, and it asks for the APP_OPID and APP_PROFILE values, for a specific value of PK_SEQUENCE. Off hand, I do not know what the RS stands for, and I'd be interested if someone could post a response.

DirXML: [10/17/07 14:15:11.27]: TRACE: SELECT PK_SEQUENCE, APP_PROFILE, APP_OPID FROM IDM.CLIENTS WHERE PK_SEQUENCE = ?
DirXML: [10/17/07 14:15:11.27]: TRACE: 	IN @ index 1, field 'PK_SEQUENCE', value = 363070
DirXML: [10/17/07 14:15:11.27]: TRACE: 	RS field 'APP_PROFILE', length: 3, value = 'aaa'
DirXML: [10/17/07 14:15:11.27]: TRACE: 	RS field 'APP_OPID', length: 3, value = 'bdl'

The <output> document that gets sent back by the shim to the engine returns the values that the SELECT statement retrieved, in this example aaa and bdl. (Yep those really were real values in our database, they mostly looked like nonesense, but in the end, we were informed there actually was a pattern).

Now that we have seen something of what the correct events should look like, lets start looking at errors.

Database Connectivity Lost

Here is what it looks like when the database goes away. We were using a test system for two different development projects, and changes and restarts were done on us often. I can highly recommend AGAINST working in this sort of environment; it is a truly a pain and makes everything difficult. However, this is a nicely self-explanatory error, one of my favorite kinds! Would that all errors be this easy to deal with!

<nds dtdversion="2.0" ndsversion="8.x" xmlns:jdbc="urn:dirxml:jdbc">
<source>
<product build="20070626_0243" instance="APP-JDBC" version="3.5.1">DirXML Driver for JDBC</product>
<contact>Novell, Inc.</contact>
</source>
<input>
<status level="warning" type="app-connection">
<description>Lost database connection.</description>
<jdbc:exception jdbc:class="java.sql.SQLException" jdbc:error-code="17002">
<jdbc:message>Io exception: Connection reset</jdbc:message>
</jdbc:exception>
</status>
</input>
</nds>

Filter Typos

In this next error, the driver shim is complaining that it cannot execute the query for elements in the filter. The JDBC driver in triggerless mode takes the list of attributes in the schema filter, builds a SELECT statement that checks the timestamps for all the values, and builds the state database looking for new events to catch and send out. In this case, we had a mistake in the filter, and it showed that the column ABCCOMPANY is not a real column name. For a reason not clear, the SELECT statement to generate the state database addresses the column names as starting with "A." (A then a period).

<nds dtdversion="2.0" ndsversion="8.x" xmlns:jdbc="urn:dirxml:jdbc">
<source>
<product build="20070626_0243" instance="APP-JDBC" version="3.5.1">DirXML Driver for JDBC</product>
<contact>Novell, Inc.</contact>
</source>
<input>
<status level="error" type="app-general">
<description>Unable to select row(s) from table/view 'IDM.WORK_ORDER'.</description>
<jdbc:exception jdbc:class="java.sql.SQLException" jdbc:error-code="904" jdbc:sql-state="42000">
<jdbc:message>ORA-00904: "A"."ABCCOMPANY": invalid identifier
</jdbc:message>
</jdbc:exception>
</status>
</input>
</nds>

Bad Connection String

As we were moving from test to production, and swapping the newly imported driver to the configuration of the production system, we had not yet changed the connection string before we tried starting the driver. That caused this error, since we had the connection string wrong. The correct format is IPADDRESS:PORT:DBNAME (thus the example 10.1.1.2:1521:DBNAME). This is another nice error code that cleanly explains itself - what a pleasure!

<nds dtdversion="2.0" ndsversion="8.x" xmlns:jdbc="urn:dirxml:jdbc">
<source>
<product build="20070626_0243" instance="APP-JDBC" version="3.5.1">DirXML Driver for JDBC</product>
<contact>Novell, Inc.</contact>
</source>
<input>
<status level="warning" type="app-connection">
<description>Unable to connect. There is a connectivity-related problem.</description>
<jdbc:exception jdbc:class="java.sql.SQLException" jdbc:error-code="0">
<jdbc:message>Listener refused the connection with the following error:
ORA-12500, TNS:listener failed to start a dedicated server process
The Connection descriptor used by the client was:
10.1.1.2:1521:DBNAME
</jdbc:message>
</jdbc:exception>
</status>
</input>
</nds>	

Bad Syntax in the SQL Call

Here's an example of bad syntax occurring in an SQL call:

<nds dtdversion="2.0" ndsversion="8.x" xmlns:jdbc="urn:dirxml:jdbc">
<source>
<product build="20070626_0243" instance="APP-JDBC" version="3.5.1">DirXML Driver for JDBC</product>
<contact>Novell, Inc.</contact>
</source>
<output>
<status jdbc:update-count="1" level="success"/>
<status jdbc:update-count="1" level="success"/>
<status jdbc:update-count="1" level="success"/>
<status jdbc:update-count="1" level="success"/>
<status jdbc:update-count="1" level="success"/>
<status event-id="" level="error" type="app-general">
<description>An error occurred executing statement "UPDATE IDM.WORK_ORDER SET "CLOSE_DATE_AND_TIME" ='to_date('08/22/2007 08:44:55', 'MM/DD/YYYY HH24:MI:SS')' WHERE PK_WORK_ORDER_NUM = '91140'".</description>
<jdbc:exception jdbc:class="java.sql.SQLException" jdbc:error-code="933" jdbc:sql-state="42000">
<jdbc:message>ORA-00933: SQL command not properly ended
</jdbc:message>
</jdbc:exception>
<jdbc:document xml:space="preserve"><nds dtdversion="3.5" ndsversion="8.x">
  <source>
   <product version="3.5.0.20070315 ">DirXML</product>
   <contact>Novell, Inc.</contact>
  </source>
  <input>
   <jdbc:statement jdbc:transaction-id="2" jdbc:transaction-type="manual" jdbc:type="update" xmlns:jdbc="urn:dirxml:jdbc">
     <jdbc:sql>INSERT INTO IDM.WORK_ORDER_DETAILS (PK_SEQUENCE, WO_NUM, DESCRIPTION, NOTE, LASTMODIFIED, SEQ_GROUP, INACTIVE, DURATION, SEQ_ACTION, LASTUSER, FROM_GROUP) VALUES ('420080', '91140', 'Closed WO # 91140', 'The IDM process successfully completed the Work Order.', to_date('08/22/2007 08:44:55', 'MM/DD/YYYY HH24:MI:SS'), '1061', '0', '1', '15', 'IDMSCRPT','NOVELL-IDM')</jdbc:sql>
     <jdbc:sql>UPDATE IDM.WORK_ORDER SET INCIDENT_RESOLUTION = ' successful.' WHERE PK_WORK_ORDER_NUM = '91140'</jdbc:sql>
     <jdbc:sql>UPDATE IDM.WORK_ORDER SET SEQ_CLOSED_BY = '1749' WHERE PK_WORK_ORDER_NUM = '91140'</jdbc:sql>
     <jdbc:sql>UPDATE IDM.WORK_ORDER SET SEQ_UDSTATUS ='2' WHERE PK_WORK_ORDER_NUM = '91140'</jdbc:sql>
     <jdbc:sql>UPDATE IDM.WORK_ORDER SET "STATE" ='C' WHERE PK_WORK_ORDER_NUM = '91140'</jdbc:sql>
     <jdbc:sql>UPDATE IDM.WORK_ORDER SET "CLOSE_DATE_AND_TIME" ='to_date('08/22/2007 08:44:55', 'MM/DD/YYYY HH24:MI:SS')' WHERE PK_WORK_ORDER_NUM = '91140'</jdbc:sql>
   </jdbc:statement>
   <modify class-name="IDM.WORK_ORDER" event-id="PK_WORK_ORDER_NUM=91140,table=WORK_ORDER,schema=IDM" qualified-src-dn="O=LAB\OU=APPS\OU=APP\OU=Work Orders\CN=SW_THINCLIENT01-91140" src-dn="\ACME-LAB\LAB\APPS\APP\Work Orders\SW_THINCLIENT01-91140" src-entry-id="52619" timestamp="1187790288#30">
     <association state="associated">PK_WORK_ORDER_NUM=91140,table=WORK_ORDER,schema=IDM</association>
   </modify>
  </input>
</nds></jdbc:document>
</status>
</output>
</nds>

The first thing to notice is that in this case, there were six SQL statements: an INSERT and five UPDATES. This was one of our first attempts at embedded SQL. In hindsight we should have rewritten it to a pair of statements - just an INSERT and a single UPDATE statement. However, it highlights one benefit or possible issue, where one of the six statements failed, but the other five succeeded. This could be good, since 5/6 is better than nothing; or it could be bad, since partially performing a task could be worse than doing nothing!

Both ways work, but you will need to weigh the options based on your circumstances. The good news is that the error message contains which statement had the issue, and it includes the text of the SQL statement.

The actual error is a fun one to catch. Can you see what the error is in this statement?

<jdbc:sql>UPDATE IDM.WORK_ORDER SET "CLOSE_DATE_AND_TIME" ='to_date('08/22/2007 08:44:55', 'MM/DD/YYYY HH24:MI:SS')' WHERE PK_WORK_ORDER_NUM = '91140'</jdbc:sql>

The error we got back was:

<jdbc:message>ORA-00933: SQL command not properly ended</jdbc:message>

This looks pretty much like a typical syntax error of a missed quote, comma, or the like.

In this case, the value using the "to_date" command should not be enclosed in single quotes; it should have looked like this:

<jdbc:sql>UPDATE IDM.WORK_ORDER SET "CLOSE_DATE_AND_TIME" =to_date('08/22/2007 08:44:55', 'MM/DD/YYYY HH24:MI:SS') WHERE PK_WORK_ORDER_NUM = '91140'</jdbc:sql>

This error was pretty cryptic and did not reveal enough to easily troubleshoot. But once we figured it out, it was pretty obvious.

Conclusion

As you can see the range of possible errors with the Novell Identity Manager Driver for JDBC databases is quite wide ranging, and this makes it something of a fun driver to implement. In fact the error messages and type will vary among database flavors. This means connecting to an Oracle databse will produce a unique set of errors that you will probably not commonly see when attempting to connect to an MS SQL Server instance. The same would be true for all the supported database flavors for this driver. Ultimately, this just makes it more interesting.

The hope is that with the set of examples presented in this series of articles, it will help save someone else time while implementing the driver by not reinventing the wheel. If you have been working on a Identity Manager driver for JDBC, connecting to a database other than Oracle, and have run into similar errors as in this series, please capture them and try to post them.

If you happen to be working on a driver that is not all that common, and you run across interesting errors, it would be great if they could be posted in something like this format as well. The documentation will never contain enough troubleshooting information, and the Novell KnowledgeBase, which is quite good, does not collect the error sets for easy perusal. Thus, articles of this type can be very powerful for collecting errors encountered in one location, and making life easier for everyone involved.