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

geoffc

By: geoffc

April 23, 2008 7:22 am

Reads: 365

Comments:0

Rating:5.0

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

Introduction

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 the final article in 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.

Single Quote in a Text String

<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="" level="error" type="app-general">
<description>An error occurred executing statement "UPDATE IDM.WORK_ORDER SET INCIDENT_RESOLUTION = 'Temporary password set to "0wWR+x9'"' WHERE PK_WORK_ORDER_NUM = '91101'".</description>
<jdbc:exception jdbc:class="java.sql.SQLException" jdbc:error-code="1740" jdbc:sql-state="42000" xmlns:jdbc="urn:dirxml:jdbc">
   <jdbc:message>ORA-01740: missing double quote in identifier
</jdbc:message>
</jdbc:exception>
<jdbc:document xml:space="preserve" xmlns:jdbc="urn:dirxml:jdbc"><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 ('419833', '91101', 'Closed WO # 91101', 'The IDM process successfully completed the Work Order.', to_date('08/16/2007 01:30:10', 'MM/DD/YYYY HH24:MI:SS'), '1061', '0', '1', '15', 'IDMSCRPT','NOVELL-IDM')</jdbc:sql>
         <jdbc:sql>UPDATE IDM.WORK_ORDER SET INCIDENT_RESOLUTION = 'Temporary password set to "0wWR+x9'"' WHERE PK_WORK_ORDER_NUM = '91101'</jdbc:sql>
         <jdbc:sql>UPDATE IDM.WORK_ORDER SET SEQ_CLOSED_BY = '1749' WHERE PK_WORK_ORDER_NUM = '91101'</jdbc:sql>
          <jdbc:sql>UPDATE IDM.WORK_ORDER SET SEQ_UDSTATUS ='2' WHERE PK_WORK_ORDER_NUM = '91101'</jdbc:sql>
         <jdbc:sql>UPDATE IDM.WORK_ORDER SET "STATE" ='C' WHERE PK_WORK_ORDER_NUM = '91101'</jdbc:sql>
      </jdbc:statement>
      <modify class-name="IDM.WORK_ORDER" event-id="ACMESMSLES10FS1#20070816183004#1#1" qualified-src-dn="O=LAB\OU=APPS\OU=APP\OU=Work Orders\CN=NOVELL_002-91101" src-dn="\ACME-LAB\LAB\APPS\APP\Work Orders\NOVELL_002-91101" src-entry-id="52560" timestamp="1187366742#4238">
         <association state="associated">PK_WORK_ORDER_NUM=91101,table=WORK_ORDER,schema=IDM</association>
      </modify>
   </input>
</nds></jdbc:document>

This is a pretty subtle error. We write back the temporary password generated by the Generate Password token as a string into a local variable, then send it back to the database in the Resolution string field. The Generate Password token reads the Password Policy specified and complies with its rules. If it allows special characters or requires them, then the password will randomly contain them. But this time, the random password includes a ‘ (single quote) that needs to be escaped in text fields with a second single quote. For example, ” will be sufficient – that is a pair of single quotes. It’s kind of like using a backslash to escape a character in other cases. To fix that, use a Replace all token of ‘ and replace with ” to wrap the clear text you are writing in the SQL call.

You need to be really careful of this one whenever you use an attribute value or a local variable where you do not completely control the data source. You never know when an single quote will show up. Imagine it in a first or last name, for example.

The DirXML Script token, Generate Password is pretty interesting and powerful. It reads the specified password policy so that it will conform to the correct policy rules. If you need to set random passwords, it is highly recommended – it’s probably worth a standalone article on its own. There is at least one version floating around that does not honor one of the rules in the Universal Password policy: it’s the part about Allow (or not) non-US ASCII characters (do you allow Unicode characters, or not). Some drivers (the ones I know of are all now fixed in the latest builds) had issues dealing with Unicode passwords and behaved very poorly when they were recieved. The good news is that all those issues are to the best of my knowledge resolved, as long as you are at the latest patch levels.

Foreign Keyed Table (where the driver synchronizes the attribute)

<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="ABC#Subscriber#0" level="error" type="app-general">
<description>Unable to modify object. Unable to update row(s) in table/view 'IDM.WORK_ORDER'.</description>
<object-dn>O=LAB\OU=APPS\OU=APP\OU=Work Orders\CN=SW_ABC001-91148</object-dn>
<jdbc:exception jdbc:class="java.sql.SQLException" jdbc:error-code="1776" jdbc:sql-state="42000">
<jdbc:message>ORA-01776: cannot modify more than one base table through a join view
</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>
      <modify class-name="IDM.WORK_ORDER" event-id="ABC#Subscriber#0" qualified-src-dn="O=LAB\OU=APPS\OU=APP\OU=Work Orders\CN=SW_ABC001-91148" src-dn="\ACME-LAB\LAB\APPS\APP\Work Orders\SW_ABC001-91148" src-entry-id="52635" timestamp="1187817469#22">
         <association state="associated">PK_WORK_ORDER_NUM=91148,table=WORK_ORDER,schema=IDM</association>
         <modify-attr attr-name="CLIENT_ID">
            <remove-value>
               <value timestamp="1187817453#16" type="string">E000455</value>
            </remove-value>
            <add-value>
               <value timestamp="1187817469#19" type="string">V055733</value>
            </add-value>
         </modify-attr>
         <modify-attr attr-name="INCIDENT_DESCRIPTION">
            <remove-all-values/>
             <add-value>
               <value>Test of ABC create for Janet Jones. Lets see what happens.</value>
            </add-value>
         </modify-attr>
      </modify>
   </input>
</nds></jdbc:document>
</status>
</output>
</nds>

This is an example, very similar in the error message to the SQL INSERT or UPDATE statements that try to set or change a column that is foreign-keyed in from another table.

The difference in this example is that there is no embedded SQL. The attribute that changed was mapped in the schema to a column in the database. This value was never supposed to change, as it turns out, but while testing out options, we did change it in an event. The driver shim trace for this error would probably be more instructive to seeing the error more clearly.

This requires the policy to either enforce the fact this attribute should never change in the ID Vault, or that the attribute should only be synchronized on the publisher channel. (This could possibly even be set to reset, to enforce that the attribute never should change).

Attributes Not Mapped in Schema Map

<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="PK_SEQUENCE=357497,table=CLIENTS,schema=IDM" level="warning">Column 'ACMEBUSINESSUNIT' is undefined on table/view 'IDM.CLIENTS' or is unsyncable.</status>
<status event-id="PK_SEQUENCE=357497,table=CLIENTS,schema=IDM" level="error" type="app-general">
<description>Unable to modify object. Unable to update row(s) in table/view 'IDM.CLIENTS'.</description>
<object-dn>O=LAB\OU=EMPLOYEES\OU=NEW\CN=ASimpson</object-dn>
<jdbc:exception jdbc:class="java.sql.SQLException" jdbc:error-code="1779" jdbc:sql-state="42000">
<jdbc:message>ORA-01779: cannot modify a column which maps to a non key-preserved table
</jdbc:message>
</jdbc:exception>
</output>
</nds>

If necessary, in Policy Builder it is possible to read or write directly to an attribute (or column), even if it is not mapped in the schema mapping rule. Generally the event will be filtered out, so you would want to do this after the filter is applied – in the Output transformation, for example.

In this case, we made a typo in the name of the attribute we were trying to modify. The second error is our old friend, which was modifying a foreign-keyed column.

Incorrect SQL Statement

<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 event-id="" level="error" type="app-general">
<description>An error occurred executing statement "UPDATE IDM.WORK_ORDER SET INCIDENT_RESOLUTION = 'Epd success, maybe?', SEQ_CLIENT=, SEQ_CLOSED_BY = '1749', PHONE='', SEQ_UDSTATUS = '2', STATE='C', CLOSE_DATE_AND_TIME =to_date('08/28/2007 09:53:31', 'MM/DD/YYYY HH24:MI:SS') WHERE PK_WORK_ORDER_NUM = '91109'".</description>
<jdbc:exception jdbc:class="java.sql.SQLException" jdbc:error-code="936" jdbc:sql-state="42000">
<jdbc:message>ORA-00936: missing expression
</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 ('420513', '91109', 'Closed WO # 91109', 'The IDM process successfully completed the Work Order. ', to_date('08/28/2007 09:53:31', 'MM/DD/YYYY HH24:MI:SS'), '1061', '0', '1', '15', 'IDMSCRPT','NOVELL-IDM')</jdbc:sql>
          <jdbc:sql>UPDATE IDM.WORK_ORDER SET INCIDENT_RESOLUTION = 'Epd success, maybe?', SEQ_CLIENT=, SEQ_CLOSED_BY = '1749', PHONE='', SEQ_UDSTATUS = '2', STATE='C', CLOSE_DATE_AND_TIME =to_date('08/28/2007 09:53:31', 'MM/DD/YYYY HH24:MI:SS') WHERE PK_WORK_ORDER_NUM = '91109'</jdbc:sql>
      </jdbc:statement>
      <modify class-name="IDM.WORK_ORDER" event-id="ACMESMSLES10FS1#20070828145331#1#1" 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="52743" timestamp="1188312811#1">
         <association state="associated">PK_WORK_ORDER_NUM=91109,table=WORK_ORDER,schema=IDM</association>
      </modify>
   </input>
</nds></jdbc:document>
</status>
</output>
</nds>

The value of the SEQ_CLIENT, was taken from an eDirectory attribute. In this case, the attribute was not populated for some reason. Therefore the INSERT is trying to set a null value. Which Oracle does not like.

The statement is:

<jdbc:sql>UPDATE IDM.WORK_ORDER SET INCIDENT_RESOLUTION = 'Success, maybe?', SEQ_CLIENT=, SEQ_CLOSED_BY = '1749', PHONE='', SEQ_UDSTATUS = '2', STATE='C', CLOSE_DATE_AND_TIME =to_date('08/28/2007 09:53:31', 'MM/DD/YYYY HH24:MI:SS') WHERE PK_WORK_ORDER_NUM = '91109'</jdbc:sql>

The value for SEQ_CLIENT is blank. Contrast this to the value for PHONE= which is ” and is acceptable since the database considers that column nullable. The SEQ_CLIENT is not allowed to be NULL in the database schema.

<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="" level="error" type="app-general">
<description>An error occurred executing statement "INSERT INTO IDM.CLIENTS (NOTE,acmeABCSeqCompany,SEQ_DEPARTMENT,ABCPHONE,PK_SEQUENCE,INACTIVE,EMPLOYEESTATUS1,EMPLOYEETYPE1,RANK1,CLIENTFUNCTGROUPS,LASTMODIFIED,FIRST_NAME,LAST_NAME,CLIENT_ID) VALUES ('ABC Account Creation','1009','1043','(123)123-1234',357520,0,'Active','Contractor','Associate','Workforce','30-Aug-07','Abner','TestTest','V056619')".</description>
<jdbc:exception jdbc:class="java.sql.SQLException" jdbc:error-code="904" jdbc:sql-state="42000">
<jdbc:message>ORA-00904: "ABCPHONE": invalid identifier
</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="ABCUserCreate" xmlns:jdbc="urn:dirxml:jdbc">
         <jdbc:sql>INSERT INTO IDM.CLIENTS (NOTE,acmeABCSeqCompany,SEQ_DEPARTMENT,ABCPHONE,PK_SEQUENCE,INACTIVE,EMPLOYEESTATUS1,EMPLOYEETYPE1,RANK1,CLIENTFUNCTGROUPS,LASTMODIFIED,FIRST_NAME,LAST_NAME,CLIENT_ID) VALUES ('ABC Account Creation','1009','1043','(123)123-1234',357520,0,'Active','Contractor','Associate','Workforce','30-Aug-07','Abner','TestTest','V056619')</jdbc:sql>
      </jdbc:statement>
   </input>
</nds></jdbc:document>
</status>
</output>
</nds>

Failure Calling 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 event-id="0" level="error" type="app-general">
<description>Unable to obtain value(s) from stored procedure 'IDM_SMSYSGETNEXT_WO_DET_SEQ'. Unable to retrieve statement results.</description>
<jdbc:exception jdbc:class="java.lang.NullPointerException"/>
<jdbc:document xml:space="preserve"><nds>
   <input xmlns:jdbc="urn:dirxml:jdbc">
      <jdbc:statement event-id="0">
         <jdbc:call-procedure jdbc:name="IDM_SMSYSGETNEXT_WO_DET_SEQ">
            <jdbc:param>
               <jdbc:value>0</jdbc:value>
            </jdbc:param>
         </jdbc:call-procedure>
      </jdbc:statement>
   </input>
</nds></jdbc:document>
<application>DirXML</application>
<module>APP-JDBC</module>
<object-dn>\ACMEIDVAULT\acmeny\APPS\APP\Work Orders\DEF-ACCCREATION-110546</object-dn>
<component>Subscriber</component>
</status>
</output>
</nds>

No PK_SEQUENCE returned by stored procedure, so setting the PK_SEQUENCE=” is bad.

<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=”" level=”error” type=”app-general”>
<description>An error occurred executing statement “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 (”, ’110546′, ‘Forwarded WO to Group NOC’, ‘The IDM process encountered the following error: clean up again’, to_date(’09/18/2007 01:12:26′, ‘MM/DD/YYYY HH24:MI:SS’), ’1063′, ’0′, ’1′, ’16′, ‘IDMSCRPT’, ‘NOC’,'NOVELL-IDM’)”.</description>
<jdbc:exception jdbc:class=”java.sql.SQLException” jdbc:error-code=”1400″ jdbc:sql-state=”23000″>
<jdbc:message>ORA-01400: cannot insert NULL into ()
</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>
<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 (”, ’110546′, ‘Forwarded WO to Group NOC’, ‘The IDM process encountered the following error: clean up again’, to_date(’09/18/2007 01:12:26′, ‘MM/DD/YYYY HH24:MI:SS’), ’1063′, ’0′, ’1′, ’16′, ‘IDMSCRPT’, ‘NOC’,'NOVELL-IDM’)</jdbc:sql>
<jdbc:sql>UPDATE IDM.WORK_ORDER SET SEQ_GROUP = ’1023′ WHERE PK_WORK_ORDER_NUM = ’110546′</jdbc:sql>
<jdbc:sql/>
</jdbc:statement>
<modify class-name=”IDM.WORK_ORDER” event-id=”NYCSUSE2#20070918181220#1#1″ qualified-src-dn=”O=acmeny\OU=APPS\OU=APP\OU=Work Orders\CN=DEF-ACCCREATION-110546″ src-dn=”\ACMEIDVAULT\acmeny\APPS\APP\Work Orders\DEF-ACCCREATION-110546″ src-entry-id=”46580″ timestamp=”1190139140#1″>
<association state=”associated”>PK_WORK_ORDER_NUM=110546,table=WORK_ORDER,schema=IDM</association>
</modify>
</input>
</nds></jdbc:document>
</status>
</output>
</nds>

Here we had an error getting the PK_SEQUENCE for the INSERT. The value of PK_SEQUENCE is empty, so thus the cannot insert NULL into () error. The actual cause for this error that caused this was the stored procedure call failing before this in the trace. A local variable is set with the value that the procedure returns, then used to provide the PK_SEQUENCE for the INSERT statement. After this error, we wrapped the Stored Procedure call in a loop that made sure it returned a value. If it did not, it generated an email to the administrator, and then sent a error message back that we could audit.

<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="" level="error" type="app-general">
<description>An error occurred executing statement "INSERT INTO IDM.CLIENTS (Group Membership,PK_SEQUENCE,INACTIVE,EMPLOYEESTATUS1,EMPLOYEETYPE1,RANK1,CLIENTFUNCTGROUPS,LASTMODIFIED,LAST_NAME,CLIENT_ID) VALUES ('\ACME-LAB\LAB\EMPLOYEES\Solaris Groups\csr',,0,'Active','','','Workforce','19-Sep-07','v032251','v032251')".</description>
<jdbc:exception jdbc:class="java.sql.SQLException" jdbc:error-code="928" jdbc:sql-state="42000">
<jdbc:message>ORA-00928: missing SELECT keyword
</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="ABCUserCreate" xmlns:jdbc="urn:dirxml:jdbc">
         <jdbc:sql>INSERT INTO IDM.CLIENTS (Group Membership,PK_SEQUENCE,INACTIVE,EMPLOYEESTATUS1,EMPLOYEETYPE1,RANK1,CLIENTFUNCTGROUPS,LASTMODIFIED,LAST_NAME,CLIENT_ID) VALUES ('\ACME-LAB\LAB\EMPLOYEES\Solaris Groups\csr',,0,'Active','','','Workforce','19-Sep-07','v032251','v032251')</jdbc:sql>
      </jdbc:statement>
   </input>
</nds></jdbc:document>
</status>
</output>
</nds>

This error was interesting. We were experiencing problems with the naturalized datatypes (NCLOB, NVARCHAR2, NCHAR) and were working with NTS to resolve it. However that was totally stalling the project, so to move forward, we wrote a rule that would read an incoming <add> XDS document, and read of the attributes, and their values, and convert the XDS <add> event into a SQL INSERT statement. In many ways, this is similar to what the driver shim does internally. While developing this rule, we did not properly tune the schema mapping and filter to keep it down to only attributes that made sense. You can see the reference to the eDirectory attribute Group Membership, in DN syntax trying to be written to the database, which of course makes little to no sense. As well, there are lots of blank values being passed for fields that cannot be NULL, so no surprise this failed.

What I am not clear on is why the error message is “missing SELECT keyword”. There are so many things wrong with this SQL statement, it is not clear why Oracle decided to send this seemingly unconnected error.

The following error is shown from the driver shim’s perspective to show what it looks like from the other side of the fence.

DirXML: [09/18/07 20:19:48.63]: TRACE:  Remote Loader: Calling SubscriptionShim.execute()
DirXML: [09/18/07 20:19:48.63]: TRACE:  <nds dtdversion="3.5" ndsversion="8.x">
	<source>
		<product version="3.5.2.20070719 ">DirXML</product>
		<contact>Novell, Inc.</contact>
	</source>
	<input>
		<modify class-name="IDM.CLIENTS" event-id="NYCSUSE2#20070918221638#99#185" from-merge="true" qualified-src-dn="O=acmeny\OU=employees\OU=active\CN=BSmith" src-dn="\ACMEIDVAULT\acmeny\employees\active\BSmith" src-entry-id="40275">
			<association>PK_SEQUENCE=333835,table=CLIENTS,schema=IDM</association>
			<modify-attr attr-name="EMAIL_ADDRESS">
				<remove-all-values/>
				<add-value>
					<value type="string">SMTP:{SMTP:{bob.smith@acme.com}bob.smith@acme.com}SMTP:{bob.smith@acme.com}bob.smith@acme.com</value>
				</add-value>
			</modify-attr>
		</modify>
	</input>
</nds>
DirXML: [09/18/07 20:19:48.63]: TRACE:  BEGIN Transaction 
DirXML: [09/18/07 20:19:48.63]: TRACE:    UPDATE IDM.CLIENTS SET EMAIL_ADDRESS = ? WHERE PK_SEQUENCE = ?
DirXML: [09/18/07 20:19:48.63]: TRACE:    	IN  @ index  1, field 'EMAIL_ADDRESS', length: 117, value = 'SMTP:{SMTP:{bob.smith@acme.com}bob.smith@acme.com}SMTP:{bob.smith@acme.com}bob.smith@acme.com'
DirXML: [09/18/07 20:19:48.63]: TRACE:    	IN  @ index  2, field 'PK_SEQUENCE', value = 333835
DirXML: [09/18/07 20:19:48.65]: TRACE:    ROLLBACK
DirXML: [09/18/07 20:19:48.65]: TRACE:  Remote Loader: SubscriptionShim.execute() returned:
DirXML: [09/18/07 20:19:48.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>
		<status event-id="NYCSUSE2#20070918221638#99#185" level="error" type="app-general">
			<description>Unable to modify object. Unable to update row(s) in table/view 'IDM.CLIENTS'.</description>
			<object-dn>O=acmeny\OU=employees\OU=active\CN=BSmith</object-dn>
			<jdbc:exception jdbc:class="java.sql.SQLException" jdbc:error-code="6502" jdbc:sql-state="65000">
				<jdbc:message>ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "_SMDBA_.SP_UPDATE_UAU_USER", line 78
ORA-06512: at "_SMDBA_.TR_UPDATE_UAU_USER", line 5
ORA-04088: error during execution of trigger '_SMDBA_.TR_UPDATE_UAU_USER'
</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>
		<modify class-name="IDM.CLIENTS" event-id="NYCSUSE2#20070918221638#99#185" from-merge="true" qualified-src-dn="O=acmeny\OU=employees\OU=active\CN=BSmith" src-dn="\ACMEIDVAULT\acmeny\employees\active\BSmith" src-entry-id="40275">
			<association>PK_SEQUENCE=333835,table=CLIENTS,schema=IDM</association>
			<modify-attr attr-name="EMAIL_ADDRESS">
				<remove-all-values/>
				<add-value>
					<value type="string">SMTP:{SMTP:{bob.smith@acme.com}bob.smith@acme.com}SMTP:{bob.smith@acme.com}bob.smith@acme.com</value>
				</add-value>
			</modify-attr>
		</modify>
	</input>
</nds></jdbc:document>
		</
DirXML: [09/18/07 20:19:48.65]: status>
	</output>
</nds>

The error here, was actually caused elsewhere in the driver. The database stores users Email Addresses, in a Microsoft Exchange MAPI format. Something like:
SMTP:{jsmith@acme.com}jsmith@acme.com

The problem was that eDirectory uses an attribute called Internet EMail Address (Note the capitalization of EMail, that is a typo in schema that happened a LONG time ago, and is way to hard to fix now, so we all just live with it and accept), which stores the email address in a much more straight forward syntax of jsmith@acme.com

There was a rule to convert from one to the other, by writing SMTP:{ then the Internet EMail Address attribute value, a } then repeating the Internet EMail Address attribute value. Very straightforward and quickly written.

The error case that did not handle address was when the database set such a value there was no rule to convert from the MAPI format to the simpler format. Values from the database were getting stored in the eDirectory attribute in the MAPI format.

Then when that event got synced back to the database, for example during a merge, then the conversion rule added the SMTP:{ bits and so on. As a consequence, the field kept getting longer.

Finally on a sync back to the database, we exceeded the definition of the column for email addresses. Thus the final error of “PL/SQL: numeric or value error: character string buffer too small”.

The fix was to first clean up the incorrect users via LDAP. Do a query to find the Internet EMail Address of all the users, sort the list, find the chunk that has SMTP:{ as the beginning, export that list. Clean it up via scripting tools and finally using ICE or ldapmodify import it back into the directory to change the values.

Then make sure the necessary complimentary rule that strips SMTP:{jsmith@acme.com} part of was in the driver.

One of the interesting things in this trace, is how the driver shim sees the event.

This snippet:

DirXML: [09/18/07 20:19:48.63]: TRACE:  BEGIN Transaction 
DirXML: [09/18/07 20:19:48.63]: TRACE:    UPDATE IDM.CLIENTS SET EMAIL_ADDRESS = ? WHERE PK_SEQUENCE = ?
DirXML: [09/18/07 20:19:48.63]: TRACE:    	IN  @ index  1, field 'EMAIL_ADDRESS', length: 117, value = 'SMTP:{SMTP:{bob.smith@acme.com}bob.smith@acme.com}SMTP:{bob.smith@acme.com}bob.smith@acme.com'
DirXML: [09/18/07 20:19:48.63]: TRACE:    	IN  @ index  2, field 'PK_SEQUENCE', value = 333835
DirXML: [09/18/07 20:19:48.65]: TRACE:    ROLLBACK

shows how it tries to do the UPDATE, but fails and ROLLBACKs the transaction. Useful hint, since you can search the log file on the driver shim for ROLLBACK to find errors.

You can also see how the driver shim takes an XDS <modify> document, and converts it to a SQL UPDATE statement. It sends both attributes, and then rolls back on the error. This is a simple example with two attributes, it gets really interesting to look at in the trace when there is much more happening in the event.

  <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="" level="error" type="app-general">
      <description>An error occurred executing statement "INSERT INTO IDM.CLIENTS (NOTE,CONTRACT_END_DATE,PHONE,SEQ_COMPANY,SEQ_DEPARTMENT,ACMEDIRECTORS,PK_SEQUENCE,INACTIVE,EMPLOYEESTATUS1,EMPLOYEETYPE1,RANK1,CLIENTFUNCTGROUPS,LASTMODIFIED,FIRST_NAME,LAST_NAME,CLIENT_ID) VALUES ('ABC Account Creation','1192891133','(212)379-5876','1002','1043','1111',362856,0,'Active','Contractor','Associate','Workforce','10-Oct-07','atest','atest','V060619')".</description>
      <jdbc:exception jdbc:class="java.sql.SQLException" jdbc:error-code="1861" jdbc:sql-state="22008">
        <jdbc:message>ORA-01861: literal does not match format string
</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>
                <jdbc:statement jdbc:transaction-id="ABCUserCreate" xmlns:jdbc="urn:dirxml:jdbc">
                        <jdbc:sql>INSERT INTO IDM.CLIENTS (NOTE,CONTRACT_END_DATE,PHONE,SEQ_COMPANY,SEQ_DEPARTMENT,ACMEDIRECTORS,PK_SEQUENCE,INACTIVE,EMPLOYEESTATUS1,EMPLOYEETYPE1,RANK1,CLIENTFUNCTGROUPS,LASTMODIFIED,FIRST_NAME,LAST_NAME,CLIENT_ID) VALUES ('ABC Account Creation','1192891133','(212)379-5876','1002','1043','1111',362856,0,'Active','Contractor','Associate','Workforce','10-Oct-07','atest','atest','V060619')</jdbc:sql>
                </jdbc:statement>
        </input>
</nds></jdbc:document>
    </status>
  </output>
</nds>

The error <jdbc:message>ORA-01861: literal does not match format string</jdbc:message> is an example of a case where we try to write a value to a column that does not match the format. In this case, the CONTRACTENDDATE column is sending the date and time in CTIME format (seconds since 1970). The LASTMODIFIED value is sending in Oracle’s default format of 10-Oct-07 and actually would work to send the date format. Nice example were one worked, and one failed, and since SQL statements are atomic operations, the entire statement fails to execute.

Had we been synchronizing via the driver directly, CTIME would have worked. In this case we were generating the embedded SQL statement directly, so the drivers support for time conversion to database formats was not useful.

Check Constraint (changed CLIENTFUNCGROUPS from a default of Workforce to N/A per client request)

<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="" level="error" type="app-general">
      <description>An error occurred executing statement "INSERT INTO IDM.CLIENTS (CONTRACT_END_DATE,PHONE,SEQ_COMPANY,SEQ_DEPARTMENT,ACMEDIRECTORS,PK_SEQUENCE,INACTIVE,EM
PLOYEESTATUS1,EMPLOYEETYPE1,RANK1,CLIENTFUNCTGROUPS,LASTMODIFIED,FIRST_NAME,LAST_NAME,CLIENT_ID) VALUES ('16-Oct-07','(123)123-1234','1002','1043','1111',363050,0,'Active
','Contractor','Associate','N\A','16-Oct-07','Doreen','Testing','V061086')".</description>
      <jdbc:exception jdbc:class="java.sql.SQLException" jdbc:error-code="2290" jdbc:sql-state="23000">
        <jdbc:message>ORA-02290: check constraint (_SMDBA_.CHK_CUSTOMER_) violated
</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>
                <jdbc:statement jdbc:transaction-id="ABCUserCreate" xmlns:jdbc="urn:dirxml:jdbc">
                        <jdbc:sql>INSERT INTO IDM.CLIENTS (CONTRACT_END_DATE,PHONE,SEQ_COMPANY,SEQ_DEPARTMENT,ACMEDIRECTORS,PK_SEQUENCE,INACTIVE,EMPLOYEESTATUS1,EMPLOYEETYPE1,RANK1,CLIENTFUNCTGROUPS,LASTMODIFIED,FIRST_NAME,LAST_NAME,CLIENT_ID) VALUES ('16-Oct-07','(123)123-1234','1002','1043','1111',363050,0,'Active','Contractor','Associate','N\A','16-Oct-07','Doreen','Testing','V061086')</jdbc:sql>
                </jdbc:statement>
        </input>
</nds></jdbc:document>
    </status>
  </output>
</nds>

This error we were never entirely clear on what caused it. We know that it was the value for RANK1 that changed and caused the error. RANK1 had been set to a hard coded string, and that for the event that created this error, we had changed it to another value that appeared in the database, the literal “N\A”. When looking at the database itself, we saw that string was stored in the field. Rather than fight this one out, we just changed it back to what it had been. The suspicion is that it was the backslash in the N\A that caused the issue. The other suspicion was that the application represented a null value for this field as N\A. Other fields that were nulls in the database looked blank via DB visualizer, but this one did not. Interesting to see, good that we knew what caused it, but we never directly resolved the error. It is possible we just needed to escape the backslash somehow. We knew that a single quote inside a text string needed to be escaped with a second single quote, but we did not see any other special characters that also needed to be escaped.

Not the greatest example to end on, but sometimes you have to choose your battles and fight the ones you know you can win, and concede the ones you know you cannot win sufficiently quickly. Ah pragmatism.

Hopefully this series will help you in understanding the types of errors, some of the things that can cause them, workarounds for what can be worked around, and overall gain a better understanding of the driver.

One of the things missing from the Novell documentation is a list of possible errors, and some kind of description of what caused it, and how to handle. We hope this series will help provide that list for others working on this driver.

This is also a general call for the same kind of error code reporting from anyone else who is doing driver work, to save the errors encountered on a project, document what happened in each case, and report to the rest of us. Overall it can only help the more documents like this that get published.

If you have run into any other JDBC driver errors, please send them in to the editors at Cool Solutions to add them to the list.

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

Tags: , ,
Categories: Identity Manager, SecureLogin

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