ADODB To DataTable

Have you ever been working with ADODB, specifically ADODB RecordSets.  Have you ever thought to yourself, “There has got to be an easier way!”    Well, take solace because there is.  It is possible through a little bit of code magic, to convert and ADODB RecordSet into a much more manageable and easy to use System.Data.DataTable object.

ADODB.Recordset rs = ReadDatabaseAndGetRecordSet();

//Convert the recordset to a datatable
DataTable tbl = new DataTable();
if (rs != null && rs.RecordCount > 0)
{
rs.MoveFirst();
new OleDbDataAdapter().Fill(tbl, rs);
rs.Close();
}
//Now the DataTable is populated and ready to use.

It’s just that simple!  Now you are free to perform reads from the DataTable rather than having to loop through the RecordSet.  This is especially handy when working against COM code that returns values in ADODB RecordSets.

When In Doubt – Log Out

Recently, while working on a project for ImageSource, I ran into a bit of problem that pretty much had me stumped.  I had written a .NET library to integrate with a 3rd party COM dll.  The code looked correct, it passed all of its test cases and ran just fine when compiled in Debug mode.  I created a test application to use this library and stress test it.  Everything checked out.

Then I compiled it for Release and obfuscated it.  Suddenly it did not run fine anymore.  The test application would consistently give me errors of varying severity.  Everything from Automation error The server threw an exception to The remote server machine does not exist or is unavailable.

An always unwelcome message.

I could get various errors depending on my input parameters, but they didn’t always occur at the same time.  Sometimes I would get a COM exception after 10 iterations of a loop, other times it would be 20.

At first, I thought the issue was that the code was obfuscated.  I’ve run into some small bugs in the past when obfuscating code, and thought this was no different.  However, testing a non-obfuscated Release build of the code produced the same problem.  Only switching back to the Debug build fixed the issue.  Lots of head banging ensued.

After trying to figure out error messages that seemed to have little  rhyme or reason, I finally went back and looked at sample code provided by the 3rd party.  After testing their code and finding that it run fine in Release mode, I had to figure out what this code was doing that mine was not.

Their code called a logout method in the API and mine didn’t.

Looking at my own code, I had completely missed making that call and it made all of the difference in the world.  My code was staying logged into the server, or at least causing problems server-side because I wasn’t disconnecting.

One added line later and the Release build of my library was working perfectly.  In fact, even the obfuscated version worked without a problem.  All of that grief and headache caused by one line of missing code.

So I guess the moral of this story is this: Make sure that you close you connections, you release your objects, that you make sure you clean up your mess.  Because if you don’t, it could come back to bite down the road.  Also, don’t be ashamed to go back and look at sample code when you’re stuck.  Sometimes the answer is right in front of you.

  

Microsoft Office Metadata Made Easy

Have you ever been writing an application and needed to retrieve or edit the properties of a Microsoft Office document?  Did the thought of having to write code to perform Word or Excel automation send chills down your spine?  Were you worried that your documents’ precious metadata would forever be trapped behind the taunting shield of the Windows Explorer property window?

So close yet so far.

Fear not, all hope is not lost.  For there is another way to retrieve and edit the details of your Office files without even needing a version of Microsoft Office installed on the PC.  Microsoft has been kind of enough to provide the dsofile.dll, a handy ActiveX component that allows easy manipulation of the file details.  There is even demo code provided for VB6 and VB.Net.

But you may be asking yourself, “How can I get such a mysterious and powerful tool to work?”  Fortunately, dsofile is simple to use, just add it to your project and your ready to go.  Check out the sample code below for an example of how to use dsofile to retrieve information out of our document.

private void ReadOfficeDocumentMetadata(string fileName)
{
   DSOFile.OleDocumentPropertiesClass document = new DSOFile.OleDocumentPropertiesClass();
   document.Open(fileName, false, DSOFile.dsoFileOpenOptions.dsoOptionOpenReadOnlyIfNoWriteAccess);

   //Display document metadata
   txtTitle.Text = document.Properties.Title;
   txtAuthor.Text = document.SummaryProperties.Author;
   txtSubject.Text = document.SummaryProperties.Subject;
   txtCategories.Text = document.SummaryProperties.Category;
   txtComments.Text = document.SummaryProperties.Comments;

   document.Close(false);
}

Wow, it’s just that easy!  Let’s see it in action:

Take a moment and savor the sweet smell of success.

But don’t just take my word for it, try out dsofile.dll yourself.  The download comes with couple of sample applications that are worth checking out and provide a more in depth look at what this tool can do.  ECM Developers at ImageSource have already found uses for this wonderful tool, and so can you.

  

IPM Process Scripts Tips and Tricks

Process workflow scripts, the very mentioning can make a developer shudder.  However, scripts are the unsung heroes of the Process workflow and have been an integral part of ImageSource’s Customer Solutions.  While the Oracle IPM Process workflow now supports using .NET dlls as script events, that was not always the case.  VBScript code was originally used to handle workflow events and that option for such is still around.  There are a few reasons why you might still need to code a process script in VBScript, either because it is an upgrade of legacy code, the system is a pre 7.7 version of IPM or if the required custom functionality is so small that writing a script is much quicker than a full blown .NET Module.

Below are some helpful tips and tricks if you find yourself writing a VBScript workflow event.

Write Logs That Make Sense
While flagging parts of the code as A, B and C might make sense to you while developing the script, trying to remember what they meant years down the line (like for an enhancement or bug fix) will be a futile exercise.  Better yet, try explaining cryptic log messages to the Systems Engineer or another developer.  After they’ve finished giving you annoyed looks, it might be time to make the logging messages understandable to another human being.  When writing logs, output something useful like current field values, sql parameters or the result of conditional operations.  Just make sure not to log sensitive data and also provide an option to turn logging off if it is not needed.

Error Handling Is Key
By default, IPM scripts use the following option: On Error Resume Next.  This means that if an error occurs in the code, it will continue operating until the error is handled or the code finishes.  Normally, error handling code is put after code like SQL calls, file system operations and usage of the IPM SDK. However, sometimes developers can get lazy and forget to put error handling in the code where it would be useful, only to have it handled later on with an error log message that does not make sense.  For example take a look at the following code:

oCon.Open "File Name=C:\process\connection.udl"
oRS.Open "select * from DATA_TABLE_1 where ID=" & iId, oCon, Readonly, Readonly
If Not oRS.EOF Then
    iValue = oRS("VALUE_1")
End If
oRS.Close

oCon.Execute  "insert into DATA_TABLE_2 (VALUE_1) values (" & iValue & ")"
If Eval("Err.Number <> 0") Then
     objExecutionContext.ErrorDescription = objExecutionContext.ErrorDescription & _
          "Example Script: Error inserting data into DATA_TABLE_2 - " & Err.Description & vbCrLf
     oCon.Close
     Set oCon = Nothing
     Exit Sub
End If

If an error were to occur at either the open connection or select data steps, the error log would still report that there was an error inserting data into DATA_TABLE_2.  A better solution would have error handling checks after the open connection and sql select lines of code.

Remember To Set Objects
Working in the .NET environment, you tend to forget some of the nuances of writing unmanaged code.  One of these is remembering to set objects when they are assigned in VBScript.  This can be the source of much unneeded frustration when the code continually errors out without a very helpful reason why.  Always keep in mind that when not assigning a primitive variable, always use Set.

Clean Up After Yourself
Another thing to keep in mind when writing scripts is to clean up your variables when you are done using them.  This is especially important for preventing memory leaks in the script, which might have to run 24/7.  Always remember to set your objects to nothing, to close all sql connections and recordsets, and even clear string values when they are not needed.

Be Careful With String Concatenation
VBScript string concatenation has an N-Squared cost.  Repeatedly concatenating a string within a loop is a very expensive operation.  While okay for small strings, it is not recommended for building out long strings, like from database information.  The suggested alternative to the built in concatenation is to preallocate memory for the concatenation operation.  A more detailed solution can be found at Microsoft’s support site.

Hopefully this advice will help you in the world of IPM Process script writing.

Richard Franzen
Developer
ImageSource, Inc.

A Good UI Goes a Long Way

From the model, view, control architecture of programming, the model and control aspects are the undoubtedly the most important.   Without a solid back-end code base, an application might as well not exist.  However, from an end user perspective, the view can make  all of the difference.  After all, that is always their first impression of the application.

For standard users, the User Interface is going to be their only interaction with the whole application.  It will shape their  perception of the application, how it operates and what it’s actual functionality is.  I’ve seen users refer application functions  by what is on the button icon (like the “faucet” or the “light bulb”).  If any aspect of a front-end UI is confusing, illogical or  buggy, user adoption will suffer.

During my time both working on applications and using them, I’ve seen what what works well for UI design and what doesn’t.  These  might seem like common sense, but but it is amazing how often they are overlooked.

 

Simplicity Minimalist design is useful to not overwhelm users.  Providing tons of options upfront to the users can scare them.   Buttons and textboxs all over the starting form may seem like a good idea to a developer, but it will most likely lead to  frustration for anyone actually using the system.  It can also lead to added complexity and bugs in the application, as giving the  user more things to click on can give them more things to break.

A good example of a good minimilist design is the standard Google homepage, which just provides a logo, a search box and a button,  all on a white background.  There is no confusion as to what the user should be doing from there.

A Flowing Layout Does the form flow well?  The labels, textboxes and buttons should be positioned to move the user through the form  in the same manner that they would normally read text (left to right, top to bottom, for example).  If a logo is used, it should be  placed in a location that is not obtrusive to the user, like the top left corner or on the side.  Buttons should be grouped together  and should be a uniform size with each other, not wildly different.  The order in which a user tabs through the application should  make sense, not have the user jump all over the form.  Basically, is navigating your form intuitive to the user or do the need a three week course in its basic operation?

Scalability Can your application be resized?  Does it look good maximised, or when the user starts to shrink the window below it’s  default size?  Or when the window is stretched, do all of the pieces just stay in place and leave room for a large grey blob of  nothing?  If the form controls are not supposed to move, it is always a good idea to make sure the form cannot be resized or the web  application is a static width (for a good example of static width, see the site for Nexus ECM).  Also, keep in mind how your form looks on different sized monitors.  It might look just fine on your  1680 by 1050 pixel screen, but it might be too large for someone who is running 800 by 600.

Standards This one applies a bit more to desktop applications than web sites.  When the application is open, does it blend in nicely with the rest of the operating system, or is it wildly out of  place.  While a unique color scheme and style can make an application look refreshing, it can also be a distraction to the user,  constantly reminding them that your application is different from the rest.  Good applications can integrate themselves into  whatever system they are running in, but still offer a unique look and feel to the user.

A good example of matching operating system standards is FireFox 3, which has adapted its default theme to blend in based on the user’s operating system, whether it is Windows XP, Vista, Mac OSX or Linux.

I will continue to delve deeper and explore these concepts in future blog posts, diving further into aspects of desktop and web  application UI design.

Richard Franzen
Developer
ImageSource, Inc.

  

One API To Rule Them All!

I have spent the last six years customizing, integrating and extending a menagerie of different ECM systems.   Each system has it’s own features, drawbacks, pitfalls and of course, APIs.  The APIs for the systems I work with come in all sorts of flavors; COM, .NET, Java and Web Services to name a few.  Every once in a while I ask myself, would it be possible to write a single library that encompasses all of these ECM systems, or at least, the most common ones?

Lets look at this in more details. For starters, we need to define the common features found in most ECM systems.

• Create content.
• Search for content.
• Get content item.
• Update content item.
• Update content metadata.
• Delete content item.

Every system implements each of these features a bit differently. For instance, to create a new revision of a content item, some systems require an explicit API call to do so.  Other systems will automatically create a revision of a content item if its metadata values match an existing item.  These nuiances need to be considered and fleshed out.

What language/technology should this uber library be implemented in?  The most flexible approach would be web services. This way we can make calls into our library from just about any modern language.  However, web services have some draw backs as well.  Uploading large (10MB+) files to a web service can be complicated.  What happens when you want to upload or download a 100MB, or 1GB file?

How should our library deal with licensing.  Some ECM systems use per seat licensing, while others user per processor.  If you are dealing with a system that uses per seat licensing, you will need to manage your number of connections.  If you use up too many licenses, end users could find themselves locked out of the system!

At this point I have only scratched the surface of my original question.  There are many more considerations to take into account before deciding to write a single library to access all of your ECM systems.  The conclusion I have come to, is that it is simply too complicated to wrap more than one ECM system’s API into a single library.

Tyson Magney
Senior Developer
ImageSource, Inc.