Error Codes of the Novell Identity Manager Driver for JDBC: Part 2 of 4

geoffc

By: geoffc

April 9, 2008 8:32 am

Reads: 282

Comments:0

Rating:5.0

This entry is part 2 of 4 in the series Error Codes of the Novell Identity Manager Driver for JDBC

The JDBC driver for Novell Identity Manager is a very powerful and generic driver. It can address a number of different databases via a Java JDBC connector. The error messages can be generic from the IDM JDBC driver, or specific to the database from the Java JDBC Connector.

This is part two of a four-part series on the various JDBC errors we encountered in a deployment.

The documentation contains a number of possible errors and troubleshooting tips, but in general, it is hard and perhaps inappropriate for the documentation to include sufficiently detailed listings of errors.

This series will attempt to publish as many errors as we could find, for the IDM 3.5.1 JDBC driver talking to an Oracle 9.x database, with the ojdbc14.jar Java JDBC connector.

Schema Syntax Issues

<status event-id="0" level="error" type="driver-general">
<description>Unable to process query. Unable to select row(s) from table/view 'IDM.CLIENTS'. Unsupported SQL type 'java.sql.Types.OTHER'.</description>
<jdbc:document xml:space="preserve"><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" event-id="0" scope="subtree">
     <search-class class-name="IDM.CLIENTS"/>
     <search-attr attr-name="CLIENT_ID">
      <value timestamp="1186120987#6" type="string">E123456</value>
     </search-attr>
     <read-attr/>
   </query>
  </input>
</status>

This error, Unsupported SQL type, is due to a schema syntax problem. The field in the database is a NVARCHAR2. The “N” at the beginning is used to denote Unicode (N for Nationalized) fields, that is supported for more complex languages. Thus, there is are NCLOB, NCHAR, and NVARCHAR2. Currently there is an issue writing to these fields, and interestingly there is also a problem querying them directly. Events in the database still seem able to forward on things that involve fields of this syntax, which is even weirder!

To resolve this one, if there is only one syntax mismatch,

1. Find the JDBCConfig.jar file in the driver shim’s lib directory. This is c:\novell\remoteloader\lib on Windows, or perhaps /opt/novell/eDirectory/dirxml/lib on a UNIX flavor. This depends on your OS: check the docs for exact paths.

2. Extract the JAR, which is really just a .ZIP of a bunch of files including a path.

For each database type, there is a _databasetype.xml file. So in the case above, it is an Oracle DB, and there is file in com\novell\nds\dirxml\driver\jdbc\db\descriptor\driver called _oracle_jdbc.xml that has a section that looks like the following snippet. (But you can add the new mapping to whatever makes sense for you.)

3. The key thing, which is not entirely clear from the documentation is that you need to set a Driver Parameter (in the driver configuration, use iManager or Designer to set it) called jdbc-driver-descriptor to the name of the custom XML file you created. (This is not the JAR file; rather, it’s the XML file. It will find the XML file within the JARs in the path.)

<sql-type-map>
	<type>
		<from>OTHER</from>
		<to>VARCHAR2</to>
	</type>
</sql-type-map>

You should not edit the _oracle_jdbc.jar file directly since it is a reserved file, and will be overwritten with impunity when Novell updates the driver. Instead, create a new JAR file, starting with the word “jdbc” in any case, and make sure the file path is the same as from the original one. You can use WinZip to rezip the Jar file if you want.

If you try to reuse the name of the XML file, you will get the following error on driver startup.

<description>Descriptor file ‘_oracle_jdbc.xml’ exists in multiple java packages: ‘com/novell/nds/dirxml/driver/jdbc/db/descriptor/driver’ in jar file ‘C:\Novell\RemoteLoader\lib\JDBCConfig.jar’ and ‘_oracle_jdbc.xml’ in jar file ‘C:\Novell\RemoteLoader\lib\JDBCConfig.jar’. Descriptor filenames must be unique across descriptor packages.</description>

Which is clearly saying what the issue is, the file exists twice, which is not allowed. So remember to use a unique name for your custom mapping file. This is because as stated above, the shim will look at the contents of all the JAR files within the path for the filename (or class, when generically calling a Java class) specified by the parameter.

This will map NCHAR, NCLOB, and NVARCHAR2 all to VARCHAR2. This should be ok, and in our testing, works for all three data types. Probably large strings may have problems with CLOB (which is meant for large strings), but in our case the DB syntax was poorly defined and used CLOB’s way too indiscriminately.

The above error (Unsupported SQL type ‘java.sql.Types.OTHER’) occured on a Query XDS operation, here is an error when trying to <modify> an attribute (aka, a write or UPDATE to the database.

<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 event-id="ACMESMSLES10FS1#20070805073814#1#1" level="error" type="driver-general">
<description>Unable to modify object. Unable to update row(s) in table/view 'IDM.WORK_ORDER'. Unsupported SQL type 'java.sql.Types.OTHER'.</description>
<object-dn>O=LAB\OU=APPS\OU=APP\OU=Work Orders\CN=SW_THINCLIENT01-91002</object-dn>
<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>
   <modify class-name="IDM.WORK_ORDER" event-id="ACMESMSLES10FS1#20070805073814#1#1" qualified-src-dn="O=LAB\OU=APPS\OU=APP\OU=Work Orders\CN=SW_THINCLIENT01-91002" src-dn="\ACME-LAB\LAB\APPS\APP\Work Orders\SW_THINCLIENT01-91002" src-entry-id="46560" timestamp="1186299494#1">
     <association state="associated">PK_WORK_ORDER_NUM=91002,table=WORK_ORDER,schema=IDM</association>
     <modify-attr attr-name="INCIDENT_DESCRIPTION">
      <add-value>
        <value timestamp="1186299494#1" type="string">Ticket Content</value>
      </add-value>
     </modify-attr>
   </modify>
  </input>
</nds></jdbc:document>
</status>
</output>
</nds>

More details are available in the article http://www.novell.com/coolsolutions/tip/19489.html regarding this issue.

User DDL Errors

<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 event-id="ACMESMSLES10FS1#20070803062951#1#1" level="error" type="password-set-operation">
<description>Unable to add object. Unable to obtain value(s) from stored procedure '_SMSYSADMIN_.SMSYSGETNEXTECNUMDB'. Unable to write to prepared statement.</description>
<object-dn>O=LAB\OU=EMPLOYEES\OU=NEW\CN=ITEST030</object-dn>
<jdbc:exception jdbc:class="java.sql.SQLException" jdbc:error-code="17004">
<jdbc:message>Invalid column type</jdbc:message>
</jdbc:exception>
<jdbc:document xml:space="preserve"><nds dtdversion="3.5" ndsversion="8.x">
  <source>
   <product version="3.5.2.20070719 ">DirXML</product>
   <contact>Novell, Inc.</contact>
  </source>
  <input>
   <add class-name="IDM.CLIENTS" event-id="ACMESMSLES10FS1#20070803062951#1#1" jdbc:op-id="0" jdbc:op-type="password-set-operation" jdbc:transaction-id="0" qualified-src-dn="O=LAB\OU=EMPLOYEES\OU=NEW\CN=ITEST030" src-dn="\ACME-LAB\LAB\EMPLOYEES\NEW\ITEST030" src-entry-id="43070" xmlns:jdbc="urn:dirxml:jdbc">
     <add-attr attr-name="NOTE">
      <value timestamp="1186120974#70" type="string">Work Order Description
This is more description
</value>
     </add-attr>
     <add-attr attr-name="EMPLOYEESTATUS1">
      <value>Active</value>
     </add-attr>
     <add-attr attr-name="FIRST_NAME">
      <value timestamp="1186120974#75" type="string">IDM</value>
     </add-attr>
     <add-attr attr-name="LAST_NAME">
      <value timestamp="1186120974#76" type="string">TEST030</value>
     </add-attr>
     <password>
      <!--content suppressed-->
     </password>
   </add>
   <jdbc:statement event-id="0" jdbc:op-id="0" jdbc:transaction-id="0" jdbc:transaction-type="manual" xmlns:exalt="http://exslt.org/common" xmlns:jdbc="urn:dirxml:jdbc">
     <jdbc:sql>UPDATE direct.view_usr SET username = 'TEST030{$pk_idu}', loginame = 'TEST030{$pk_idu}' WHERE pk_idu = {$pk_idu}</jdbc:sql>
   </jdbc:statement>
   <jdbc:statement event-id="1" jdbc:op-id="0" xmlns:exslt="http://exslt.org/common" xmlns:jdbc="urn:dirxml:jdbc">
     <jdbc:sql>CREATE USER "TEST030{$pk_idu}" IDENTIFIED BY "{$$password}"</jdbc:sql>
   </jdbc:statement>
   <jdbc:statement event-id="2" jdbc:op-id="0" xmlns:exslt="http://exslt.org/common" xmlns:jdbc="urn:dirxml:jdbc">
     <jdbc:sql>GRANT CREATE SESSION TO "TEST030{$pk_idu}"</jdbc:sql>
   </jdbc:statement>
  </input>
</nds></jdbc:document>
<operation-data>
<password-subscribe-status>
<association/>
</password-subscribe-status>
</operation-data>
</status>
</output>
</nds>

This example error is actually two things at once. First we have the User DDL issue, and then we have the actual error, which is an inability to generate the next Primary Key value for an INSERT.

Oracle supports User objects within the database. The UserDDL policy, in the Subscriber Command Transformation ruleset. tries to create Oracle Users, based on a User object created in the vault. If you are not using Oracle Users, then you can just unlink this policy object from the list. (Use iManager or Designer to do this; do not try and play with the attributes yourself, as the syntax for the linkage changed between IDM 2-3 and 3.5.). If you are not using it, and leave the default configuration, the driver will generate this portion of the trace:

   <jdbc:statement event-id="0" jdbc:op-id="0" jdbc:transaction-id="0" jdbc:transaction-type="manual" xmlns:exslt="http://exslt.org/common" xmlns:jdbc="urn:dirxml:jdbc">
     <jdbc:sql>UPDATE direct.view_usr SET username = 'TEST030{$pk_idu}', loginame = 'TEST030{$pk_idu}' WHERE pk_idu = {$pk_idu}</jdbc:sql>
   </jdbc:statement>
   <jdbc:statement event-id="1" jdbc:op-id="0" xmlns:exslt="http://exslt.org/common" xmlns:jdbc="urn:dirxml:jdbc">
     <jdbc:sql>CREATE USER "TEST030{$pk_idu}" IDENTIFIED BY "{$$password}"</jdbc:sql>
   </jdbc:statement>
   <jdbc:statement event-id="2" jdbc:op-id="0" xmlns:exslt="http://exslt.org/common" xmlns:jdbc="urn:dirxml:jdbc">
     <jdbc:sql>GRANT CREATE SESSION TO "TEST030{$pk_idu}"</jdbc:sql>
   </jdbc:statement>

The first actual error in the snippet above is:

“Unable to add object. Unable to obtain value(s) from stored procedure ‘_SMSYSADMIN_.SMSYSGETNEXTRECNUMDB’. Unable to write to prepared statement.”

This related to not having the correct method defined for getting the next primary key value in the database. When configuring the driver, you need to define the method by which the driver will get, retrieve, generate, or calculate the next value. One possibility is MAX +1 (get the highest current value and add one). More commonly, a stored procedure is named that when called returns the next value, whichever way is appropriate to your DB instance.

In the error case above, the name of the Stored procedure has a typo in it, so it is failing to get a result when it is called. The driver prefers that you use one stored procedure for all INSERT statements (what XDS <add> events get mapped to), for all tables. This is often problematic, since your DB may use multiple tables with different procedures to generate the primary keys.

In our case, we used the driver’s configuration for the main CLIENTS (mapped to User objects) table. For the other table, we generated it ourselves via a stored procedure call (see below) and finally built our own INSERT statement via embedding SQL. (See Cool Solutions article http://www.novell.com/coolsolutions/feature/19504.html for much more detail on embedding SQL using Policy Builder instead of XSLT).

Passing a Stored Procedure

<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 level="warning">The number of parameters declared for stored procedure '_SMSYSADMIN_.SMSYSGETNEXTRECNUMDB' does not match the number of passed parameters. There is/are 2 declared parameter(s) and 1 passed parameter(s).</status>
<status level="warning">Parameter type mismatch detected in stored procedure '_SMSYSADMIN_.SMSYSGETNEXTRECNUMDB'. Parameter 'VALNAME' at index 1 is of type 'IN' and field 'IDM.CLIENTS.PK_SEQUENCE' passed to it is of type 'INOUT'.</status>
<status level="warning">Parameter SQL type mismatch detected in stored procedure '_SMSYSADMIN_.SMSYSGETNEXTRECNUMDB'. Parameter 'VALNAME' at index 1 is of type 'java.sql.Types.OTHER' and field 'IDM.CLIENTS.PK_SEQUENCE' passed to it is of type 'java.sql.Types.DECIMAL'.</status>
<status event-id="0" level="success"></status>
</output>
</nds>

In order to synchronize a new user to a mapped table in the JDBC, an insert needs to be performed. The database needs to be given the next value of the primary key. You have a couple of options for how this is calculated, controlled by a setting in the driver Subscriber parameters. You can use the MAX + 1 model, where it figures out the highest Primary key value, adds one, and uses that. Or you can use a Stored Procedure that does some magic in the background and returns a value. In this error example, the stored procedure we were given expected an IN parameter, and we gave it an IN/OUT parameter. The DBA modified the stored procedure for us to have the correct requirements, and then it started returning values the way we wanted.

Below is an example of using a Stored Procedure to get a value, and what it returns. This time it’s from the driver shim’s trace, a slightly different point of view from the other traces we have looked at so far. In this case we made an XML document, using the XML Parse token, having stored the actual text of the document in a GCV and sent it to the destCommandProcessor for handling.

The following XDS document was stored in a GCV and then passed to the XML Parse token. It was then sent via the destCommandProcessor grabbing the value you wanted via XPATH from the returned document. Stay tuned for an upcoming Cool Solutions article for more info on doing this kind of thing.

The trace below is as seen on the remote shim. The giveaway that you are looking at a shim trace for this driver is when it shows the conversion to SQL from XDS events.

<nds>
	<input xmlns:jdbc="urn:dirxml:jdbc">
		<jdbc:statement event-id="0">
			<jdbc:call-procedure jdbc:name="IDM.IDM_SMSYSGETNEXTRECNUMDB">
				<jdbc:param>
					<jdbc:value>0</jdbc:value>
				</jdbc:param>
			</jdbc:call-procedure>
		</jdbc:statement>
	</input>
</nds>

DirXML: [10/16/07 12:50:00.59]: TRACE: {call IDM.IDM_SMSYSGETNEXTRECNUMDB(?)}
DirXML: [10/16/07 12:50:00.59]: TRACE: 	IN @ index 1, param 'NEXT_WONUM', field '$1', value = 0
DirXML: [10/16/07 12:50:00.59]: TRACE: 	OUT @ index 1, param 'NEXT_WONUM', field '$1'
DirXML: [10/16/07 12:50:00.65]: TRACE: 	OUT @ index 1, param 'NEXT_WONUM', field '$1', value = 363052
DirXML: [10/16/07 12:50:00.65]: TRACE: Remote Loader: SubscriptionShim.execute() returned:
DirXML: [10/16/07 12:50:00.65]: TRACE: <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>
		<jdbc:out-parameters event-id="0" jdbc:number-of-params="1">
			<jdbc:param jdbc:name="NEXT_WONUM" jdbc:param-type="INOUT" jdbc:position="1" jdbc:sql-type="java.sql.Types.DECIMAL">
				<jdbc:value>363052</jdbc:value>
			</jdbc:param>
		</jdbc:out-parameters>
		<status event-id="0" level="success"/>
	</output>
</nds>

This section:

DirXML: [10/16/07 12:50:00.59]: TRACE: {call IDM.IDM_SMSYSGETNEXTRECNUMDB(?)}
DirXML: [10/16/07 12:50:00.59]: TRACE: 	IN @ index 1, param 'NEXT_WONUM', field '$1', value = 0
DirXML: [10/16/07 12:50:00.59]: TRACE: 	OUT @ index 1, param 'NEXT_WONUM', field '$1'
DirXML: [10/16/07 12:50:00.65]: TRACE: 	OUT @ index 1, param 'NEXT_WONUM', field '$1', value = 363052

is the driver shim showing what it does to the input XML and how it converts it to a SQL call inside the shim. Then it returns the value in a document, which you can use XPATH to get out of the document and use however you see fit later in your DirXML Script.

The return document is this part:

<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>
		<jdbc:out-parameters event-id="0" jdbc:number-of-params="1">
			<jdbc:param jdbc:name="NEXT_WONUM" jdbc:param-type="INOUT" jdbc:position="1" jdbc:sql-type="java.sql.Types.DECIMAL">
				<jdbc:value>363052</jdbc:value>
			</jdbc:param>
		</jdbc:out-parameters>
		<status event-id="0" level="success"/>
	</output>
</nds>

For the jdbc:value context node in your XPATH, select something like the XPATH of the jdbc:value/value. In this case, the value is 363052.

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 have 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 happen to run across interesting errors, it would be great if you could post them 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.

Stay tuned for parts three and four of this article series, coming soon!

VN:F [1.9.22_1171]
Rating: 5.0/5 (1 vote cast)
Error Codes of the Novell Identity Manager Driver for JDBC: Part 2 of 4, 5.0 out of 5 based on 1 rating
Series Navigation<< Error Codes of the Novell Identity Manager Driver for JDBC: Part 1 of 4Error Codes of the Novell Identity Manager Driver for JDBC: Part 3 of 4 >>

Tags: , ,
Categories: Identity Manager, Technical Solutions

Disclaimer: As with everything else at NetIQ Cool Solutions, this content is definitely not supported by NetIQ, so Customer Support will not be able to help you if it has any adverse effect on your environment.  It just worked for at least one person, and perhaps it will be useful for you too.  Be sure to test in a non-production environment.

Comment