Hi all,

We have a JDBC driver connected to a MySQL database which syncs users
in our
identity vault with a table/view in the database. Attributes such as
givenName
and sn are synced with this table/view, however the corresponding
columns have
a NOT NULL constraint placed on them. This constraint was specified by
the
third-party application and so we would like to avoid modifying it if
at all
possible. The established way to deal with what would be NULL values in
this
application is to insert the empty string. The columns are of type
VARCHAR.

Testing shows that when, for example, the givenName attribute is
cleared in
the vault (either by removing the sole existing value, or by removing
all
values), the driver shim detects that there would be a constraint
violation
and makes no attempt to update the relevant column. This seems a
fairly
sensible thing to do and avoids generating an error within the database
itself
(although I'm not 100% convinced on the return status of "success").


Code:
--------------------
14:49:59 68C30940 Drvrs: TestDrv ST:Submitting document to subscriber shim:
14:49:59 68C30940 Drvrs: TestDrv ST:
<nds dtdversion="3.5" ndsversion="8.x">
<source>
<product version="3.6.15.5883">DirXML</product>
<contact>Novell, Inc.</contact>
</source>
<input>
<modify cached-time="20120503134959.591Z" class-name="identitym.user" event-id="soylent#20120503134959#1#1" qualified-src-dn="O=Vault\OU=Identities\OU=People\CN=bob" src-dn="\IDENTITY-VAULT\Vault\Identities\People\bob" src-entry-id="1685070" timestamp="0#0">
<association state="associated">PK_ID=19642,table=USER,schema=I DENTITYM</association>
<modify-attr attr-name="firstname">
<remove-value>
<value timestamp="1336052989#1" type="string">Christine</value>
</remove-value>
</modify-attr>
</modify>
</input>
</nds>
14:49:59 68C30940 Drvrs: TestDrv ST:Acquired lock on connection 'dedicated2'.
14:49:59 68C30940 Drvrs: TestDrv ST:BEGIN Transaction
14:49:59 68C30940 Drvrs: TestDrv ST: BEGIN Handle MODIFY.
14:49:59 68C30940 Drvrs: TestDrv ST: BEGIN Interpret event.
14:49:59 68C30940 Drvrs: TestDrv ST: Unable to remove value from column 'identitym.user.firstname' due to column nullability constraint.
14:49:59 68C30940 Drvrs: TestDrv ST: END Interpret event.
14:49:59 68C30940 Drvrs: TestDrv ST: BEGIN Update multi-valued column(s) in view 'identitym.user'.
14:49:59 68C30940 Drvrs: TestDrv ST: END Update multi-valued column(s) in view 'identitym.user'.
14:49:59 68C30940 Drvrs: TestDrv ST: END Handle MODIFY.
14:49:59 68C30940 Drvrs: TestDrv ST: COMMIT
14:49:59 68C30940 Drvrs: TestDrv ST:END Transaction
14:49:59 68C30940 Drvrs: TestDrv ST:Released connection lock.
14:49:59 68C30940 Drvrs: TestDrv ST:Number of statements allocated on connection 'dedicated2': 0.
14:49:59 68C30940 Drvrs: TestDrv ST:SubscriptionShim.execute() returned:
14:49:59 68C30940 Drvrs: TestDrv ST:
<nds dtdversion="2.0" ndsversion="8.x" xmlns:jdbc="urn:dirxml:jdbc">
<source>
<product build="20110930_1131" instance="TestDrv" version="3.5.8">DirXML Driver for JDBC</product>
<contact>Novell, Inc.</contact>
</source>
<output>
<status event-id="soylent#20120503134959#1#1" level="success"/>
</output>
</nds>
--------------------



Code:
--------------------
14:54:56 68C30940 Drvrs: TestDrv ST:Submitting document to subscriber shim:
14:54:56 68C30940 Drvrs: TestDrv ST:
<nds dtdversion="3.5" ndsversion="8.x">
<source>
<product version="3.6.15.5883">DirXML</product>
<contact>Novell, Inc.</contact>
</source>
<input>
<modify cached-time="20120503135456.365Z" class-name="identitym.user" event-id="soylent#20120503135456#1#1" qualified-src-dn="O=Vault\OU=Identities\OU=People\CN=bob" src-dn="\IDENTITY-VAULT\Vault\Identities\People\bob" src-entry-id="1685070" timestamp="0#0">
<association state="associated">PK_ID=19642,table=USER,schema=I DENTITYM</association>
<modify-attr attr-name="firstname">
<remove-all-values/>
</modify-attr>
</modify>
</input>
</nds>
14:54:56 68C30940 Drvrs: TestDrv ST:Acquired lock on connection 'dedicated2'.
14:54:56 68C30940 Drvrs: TestDrv ST:BEGIN Transaction
14:54:56 68C30940 Drvrs: TestDrv ST: BEGIN Handle MODIFY.
14:54:56 68C30940 Drvrs: TestDrv ST: BEGIN Interpret event.
14:54:56 68C30940 Drvrs: TestDrv ST: Unable to remove value from column 'identitym.user.firstname' due to column nullability constraint.
14:54:56 68C30940 Drvrs: TestDrv ST: END Interpret event.
14:54:56 68C30940 Drvrs: TestDrv ST: BEGIN Update multi-valued column(s) in view 'identitym.user'.
14:54:56 68C30940 Drvrs: TestDrv ST: END Update multi-valued column(s) in view 'identitym.user'.
14:54:56 68C30940 Drvrs: TestDrv ST: END Handle MODIFY.
14:54:56 68C30940 Drvrs: TestDrv ST: COMMIT
14:54:56 68C30940 Drvrs: TestDrv ST:END Transaction
14:54:56 68C30940 Drvrs: TestDrv ST:Released connection lock.
14:54:56 68C30940 Drvrs: TestDrv ST:Number of statements allocated on connection 'dedicated2': 0.
14:54:56 68C30940 Drvrs: TestDrv ST:SubscriptionShim.execute() returned:
14:54:56 68C30940 Drvrs: TestDrv ST:
<nds dtdversion="2.0" ndsversion="8.x" xmlns:jdbc="urn:dirxml:jdbc">
<source>
<product build="20110930_1131" instance="TestDrv" version="3.5.8">DirXML Driver for JDBC</product>
<contact>Novell, Inc.</contact>
</source>
<output>
<status event-id="soylent#20120503135456#1#1" level="success"/>
</output>
</nds>
--------------------


To avoid triggering a contraint violation, and to fit with established
practice, we would like to capture events where these attributes have
no
value and insert the empty string instead. The policy that we've
written to do
this appears to do the correct thing for adds and modifies, although
we've not
really thought about query operations yet (if anyone has a nice way of
doing
this in all cases I'd be glad to hear from you). The problem that we're
now
encountering is that when the shim actually tries to perform the
update, it
converts the empty string to a NULL and triggers a constraint
violation. It
would appear that the empty string is sufficient to pass the checks
that the
two examples above fail, but gets converted into a NULL at a later
stage and
blows up.

Does anyone have any suggestions regarding how to store the empty
string? I've
looked for driver parameters or driver/database descriptor elements
that could
change this empty string -> NULL behaviour, but have drawn a blank.
Does
anyone have any ideas on how to proceed with this? Or am I just going
to have
to remove the NOT NULL contraint from these columns? Another option
would be
to set the value to a single space character, but I'm less keen on
that
option.

Thoughts? Suggestions?

Thanks in advance,
Chris


Code:
--------------------
10:21:50 68C30940 Drvrs: TestDrv ST:Submitting document to subscriber shim:
10:21:50 68C30940 Drvrs: TestDrv ST:
<nds dtdversion="3.5" ndsversion="8.x">
<source>
<product version="3.6.15.5883">DirXML</product>
<contact>Novell, Inc.</contact>
</source>
<input>
<modify cached-time="20120503092150.361Z" class-name="identitym.user" event-id="soylent#20120503092150#1#1" qualified-src-dn="O=Vault\OU=Identities\OU=People\CN=bob" src-dn="\IDENTITY-VAULT\Vault\Identities\People\bob" src-entry-id="1685070" timestamp="0#0">
<association state="associated">PK_ID=19642,table=USER,schema=I DENTITYM</association>
<modify-attr attr-name="firstname">
<remove-all-values/>
<add-value>
<value type="string"/>
</add-value>
</modify-attr>
</modify>
</input>
</nds>
10:21:50 68C30940 Drvrs: TestDrv ST:Acquired lock on connection 'dedicated2'.
10:21:50 68C30940 Drvrs: TestDrv ST:BEGIN Transaction
10:21:50 68C30940 Drvrs: TestDrv ST: BEGIN Handle MODIFY.
10:21:50 68C30940 Drvrs: TestDrv ST: BEGIN Interpret event.
10:21:50 68C30940 Drvrs: TestDrv ST: END Interpret event.
10:21:50 68C30940 Drvrs: TestDrv ST: BEGIN Update single-valued column(s) in view 'identitym.user'.
10:21:50 68C30940 Drvrs: TestDrv ST: Not locking.
10:21:50 68C30940 Drvrs: TestDrv ST: UPDATE user SET firstname = ? WHERE pk_id = ?
10:21:50 68C30940 Drvrs: TestDrv ST: BEGIN Instantiate prepared statement.
10:21:50 68C30940 Drvrs: TestDrv ST: Allocating statement from connection 33140c90.
10:21:50 68C30940 Drvrs: TestDrv ST: Allocated statement 2f3cddba: UPDATE user SET firstname = ** NOT SPECIFIED ** WHERE pk_id = ** NOT SPECIFIED **.
10:21:50 68C30940 Drvrs: TestDrv ST: END Instantiate prepared statement.
10:21:50 68C30940 Drvrs: TestDrv ST: IN @ index 1, field 'firstname', value = null
10:21:50 68C30940 Drvrs: TestDrv ST: IN @ index 2, field 'pk_id', value = 19642
10:21:50 68C30940 Drvrs: TestDrv ST: Closed statement 2f3cddba: UPDATE user SET firstname = null WHERE pk_id = 19642.
10:21:50 68C30940 Drvrs: TestDrv ST: BEGIN Test for bad connection.
10:21:50 68C30940 Drvrs: TestDrv ST: END Test for bad connection.
10:21:50 68C30940 Drvrs: TestDrv ST: Connection is good.
10:21:50 68C30940 Drvrs: TestDrv ST: BEGIN rollback.
10:21:50 68C30940 Drvrs: TestDrv ST: ROLLBACK
10:21:50 68C30940 Drvrs: TestDrv ST: END rollback.
10:21:50 68C30940 Drvrs: TestDrv ST: Number of statements allocated on connection 'dedicated2': 0.
10:21:50 68C30940 Drvrs: TestDrv ST:SubscriptionShim.execute() returned:
10:21:50 68C30940 Drvrs: TestDrv ST:
<nds dtdversion="2.0" ndsversion="8.x" xmlns:jdbc="urn:dirxml:jdbc">
<source>
<product build="20110930_1131" instance="TestDrv" version="3.5.8">DirXML Driver for JDBC</product>
<contact>Novell, Inc.</contact>
</source>
<output>
<status event-id="soylent#20120503092150#1#1" level="error" type="app-general">
<description>Unable to modify object. Unable to update row(s) in table/view 'identitym.user'.</description>
<object-dn>O=Vault\OU=Identities\OU=People\CN=bob</object-dn>
<jdbc:exception jdbc:class="com.mysql.jdbc.exceptions.jdbc4.MySQLI ntegrityConstraintViolationException" jdbc:error-code="1048" jdbc:sql-state="23000">
<jdbc:message>Column 'firstname' cannot be null</jdbc:message>
</jdbc:exception>
<jdbc:document xml:space="preserve"><nds dtdversion="3.5" ndsversion="8.x">
<source>
<product version="3.6.15.5883">DirXML</product>
<contact>Novell, Inc.</contact>
</source>
<input>
<modify cached-time="20120503092150.361Z" class-name="identitym.user" event-id="soylent#20120503092150#1#1" qualified-src-dn="O=Vault\OU=Identities\OU=People\CN=bob" src-dn="\IDENTITY-VAULT\Vault\Identities\People\bob" src-entry-id="1685070" timestamp="0#0">
<association state="associated">PK_ID=19642,table=USER,schema=I DENTITYM</association>
<modify-attr attr-name="firstname">
<remove-all-values/>
<add-value>
<value type="string"/>
</add-value>
</modify-attr>
</modify>
</input>
</nds></jdbc:document>
</status>
</output>
</nds>
--------------------


--
ChrisReeves
------------------------------------------------------------------------
ChrisReeves's Profile: http://forums.novell.com/member.php?userid=34172
View this thread: http://forums.novell.com/showthread.php?t=455441