Tuesday, June 30, 2009

Active Directory Hell

I've spent a number of years playing at knowing a thing or two about LDAP but I've managed to avoid spending any worthwhile time playing with Active Directory.

Now, the observant amongst you will notice that I succeeded in writing a sentence that included LDAP and Active Directory. Active Directory is merely LDAPpy, for want of a better word (though now I've written it, I'm quite pleased with the way it looks and sounds).

Today, I had to work out how to write an attribute into an Active Directory instance. Trivial. At least, I thought it would be trivial. The attribute is a schema extension and is used to store a binary representation of a GUID. GUIDs are things I can handle... a lengthy string of HEXish characters! What could be easier.

Well, things are never straightforward. I have some VBScript which details how the GUID should be "manipulated" by taking the two characters starting at position 7, then the two characters starting at position 5, etc., etc. The resulting string of two character hex codes is still quite lengthy but quite jumbled from the original GUID. But here is where the fun begins. This attribute is of type java.lang.String (according to the schema) but is actually a binary object! The VBScript opens an ADODB.stream object (of type text) into which it places the ChrB representations of the HEX codes. It then strips of the UTF-8 marker and rereads the stream as a binary object before putting it into the directory.

Why? I have no idea other than someone said their application performed better if it was done that way!

Now... how do you create a stream object of type text, split of the UTF-8 marker then commit the resulting stream as a binary object from within Java?

I struggled, I can tell you. And my dear old friend Google wasn't being much help. In exasperation, I decided to test that I wasn't banging my head off a brick wall. My AD instance already had examples of accounts with these particular attributes populated by the VBScript routine. I was able to extract this data using IBM Tivoli Directory Integrater and inspect each byte. I was then able to determine exactly how the binary value was being created and recreated the object in code.

However, committing this object to AD failed with some kind of attribute constraint. I was mystified. After much scratching of the head, I decided to create an Assembly Line with the following 2 connectors:
  1. Lookup AD for a particular user entry
  2. Update the same user entry in AD with the attribute values retrieved in step 1

In other words, try to update the AD object with the same values that it already has.

It failed. Attribute Constraint! So, by merely reading some data and writing it directly back to the source, I managed to generate an attribute constraint error.

I may give up... I'm not happy with the way AD behaves and I'm certainly unhappy with the VBScript. I suspect the fact that the attribute is defined as a String but is storing a binary object is the route of all the evil. So today has ended on a low... no resolution as yet to a problem which I suspect may not be solved by conventional methods.

Thursday, June 04, 2009

DB2 Stored Procedure Hell

I'm a systems integrator and have no desire to understand everything at the lowest level of detail possible. I understand things conceptually and know where to find manuals to help me complete tasks but I have not got the time to understand every technology going.

Today, I had my first need to create a DB2 Stored Procedure and use it. In 20 years, I've never had to do that - which seems strange even to me. I've always known about them and understood their power but it has always been "someone else" who has created them and consumed them in their applications. Remember... I integrate systems?

So - how hard can it be? Well, my good friend Google helped when I asked for information on "creating a stored procedure". A tonne of links - must of which assumed that I would be using some heavyweight IDE. I'm a command-line kind of guy though!

It was at that point that I thought that even though this is a simple task and I'm fairly convinced I'll be able to do this within minutes rather than hours, I still figured I should maybe record the process I went through. The reason? I'm fed up being sent down blind alleys by trash on the internet.

I create a myfirstproc.sql file with the following contents:

CREATE PROCEDURE MYFIRSTPROC
(IN username CHAR99), IN disclaimer INT)
LANGUAGE SQL
BEGIN
IF disclaimer = 1 THEN
INSERT INTO USER (USERID, DISCLAIMER) VALUES (username, 'Y');
ELSE
INSERT INTO USER (USERID, DISCLAIMER) VALUES (username, 'N');
END IF;
END @
Straightforward, eh?

I applied it (eventually) using the db2 -td@ -vf myfirstproc.sql command.

Next, I wanted to use IBM Tivoli Directory Integrator to invoke the stored procedure. I created a passive JDBC connector called manageDB and created a script as such:

var con - manageDB.connector.connection;
command = "{call MYFIRSTPROC(?,?)}";
try {
cstmt = con.prepareCall(command);
cstmt.setString(1, "H12345678");
cstmt.setString(2, 1);
cstmt.execute();
cstmt.close();
}
catch (e) {
task.logmsg(e);
}
Now, that all looks quite neat but there is a bug in the code. When I ran the code I received a "Method Not Yet Supported" message. Now, what can you imagine would cause such a message? My first thought was that maybe I was using an out-of-date driver so I decided to get the latest one. This in itself is a major undertaking as anyone who has tried to find anything on the IBM website will testify! Certainly searching for db2jcc.jar (as I did) did not take me to anywhere from which I could download it!

The new JAR file was put in place and the routine executed again. "Method Not Yet Supported" again. Now - that's quite frustrating! Searching for "Method Not Yet Supported" yields information on how the method I'm calling isn't supported. In other words, a useless message!

Trawling through the code, it wasn't immediately obvious what the issue could've been. Not obvious because the code looked syntactically correct (and in any case, if there was a coding error, surely I would've been presented with an appropriate error message).

Well, the eagle eyed amongst you will notice that the original code above was attempting to set parameter 2 to a value of 1 as a string. Converting this to a setInt statement rectified the problem! The resulting code:

var con - manageDB.connector.connection;
command = "{call MYFIRSTPROC(?,?)}";
try {
cstmt = con.prepareCall(command);
cstmt.setString(1, "H12345678");
cstmt.setInt(2, 1);
cstmt.execute();
cstmt.close();
}
catch (e) {
task.logmsg(e);
}
So, why share this with you? Well... I'm not I guess. I've written this more as a reminder to myself. The important following lessons have been learned:
  • Don't trust the information on the internet - it's typically out-of-date (just as this article will be as soon as I hit the publish button?)
  • Don't trust error messages - they've been constructed by developers and could be meaningless
  • Good luck with searching that IBM website
  • Everything is possible with a smidgin' of perserverance