dans.blog

This morning I was working on some code where I wanted to capture an error in side a cftry/cfcatch block and rethrow the message, but I wanted to modify the cfcatch.message key to include more detail without losing the stack trace. I could have just used the <cfthrow /> tag and used the type, message and detail attributes but this would have caused me to lose the stack trace—which I needed.

Idealistically, I could have just modified the "message" key in the cfcatch variable, but the problem is the ColdFusion cfcatch variable is not really a struct—it looks like one—but it's actually an Java exception. Because it's not really a struct, it's not possible to just edit key values—making the cfcatch variable essentially read-only.

This got me looking into a method were I could rethrow the original stack trace, but customize the "message" key. After a few minutes playing around the java.lang.Exception object, I came up with the following:

  <cffunction name="rethrowMessage" access="public" returntype="void" output="false" hint="Rethrow a CFCATCH error, but allows customizing the message key">
    <cfargument name="cfcatch" type="any" required="true" />
    <cfargument name="message" type="string" required="false" />

    <cfset var exception = "" />

    <cfif not structKeyExists(arguments, "message")>
      <cfset arguments.message = arguments.cfcatch.message />
    </cfif>

    <cfset exception = createObject("java", "java.lang.Exception").init(arguments.message) />
    <cfset exception.initCause(arguments.cfcatch.getCause()) />
    <cfset exception.setStackTrace(arguments.cfcatch.getStackTrace()) />

    <cfthrow object="#exception#" />
  </cffunction>

Using this UDF, I can now do something like:

<cfscript>
  // connect and authenticate
   try {
    store.connect(variables.instance.server, variables.instance.username, variables.instance.password);
  } catch(Any e) {
    // throw original message, but append the username to the message
    rethrowMessage(cfcatch=e, message=e.Message & " [#variables.instance.username#]");
  }
</cfscript>

This allows me to throw the original Java error that occurs when connecting to a JavaMail Store occurs, but modify the message to include the mailbox I'm trying to connect to  which is helpful in debugging the root cause.

Posted by Dan on May 26, 2011 @ 10:58 AM

For a while now I've been dealing with issues adding/updating events to my Google Calendar from Thunderbird/Lightning. When ever I'd try to update an event, I'd get a very generic "MODIFICATION_FAILED" error message with no detailed message. I could view events just fine and even dismiss alarms, but nothing I tried would allow me to add new events directly from Thunderbird. I ended up resorting to opening up Google Calendar and adding the events via the web interface. While this works, it's no where near as fast as accepting an invite from Thunderbird.

Today I had several GoToMeeting invites that I need to add to my calendar and decided look into the problem again. After doing some searching, I found lots of threads of people having the same issue, but alas no solutions. The one thing that did keep popping up is that Google considers CalDAV support still experimental, so problems may occur.

This got me to look into if there were other solutions other than CalDAV and I came across Provider for Google Calendar 0.7.1 add-on for Sunbird and Lightning. After changing all my calendars to use the new provider, everything seems to be working very well.

So, if you're getting errors updating your Google Calendar using CalDAV, you might try the Provider for Google Calendar to see if it fixes your issues. I'd also recommend taking a look at the Calendar:GDATA Provider wiki page which covers installation/setup of the provider.

Posted by Dan on May 19, 2011 @ 9:08 AM

Earlier today I posted a generic SQL solution on easily manage one-to-many data in your SQL database. However, if you're using SQL Server 2005 (or above) you can use the OUTPUT clause to add an resultset to your SQL that will contain an audit trail of what data was removed and what data was added.

In the example, we had a database schema that looked like this:

The User table holds the information on all your Users, the UserRole table holds all the roles of each User and the Role table contains a list of valid roles that a user can hold.

If we want to update all the roles for a specific User, I showed you could use the following 2-step SQL process to update all their roles without deleting or changing existing roles that are still valid:

delete
from
  UserRole
where
  UserId = 1
-- IF WE HAVE NO ROLES TO ASSIGN, DO NOT RUN ANY CODE BELOW THIS LINE
-- this deletes only rows not in our new selection
and
  RoleId not in (1,2,3)

insert into
  UserRole
(
  UserId, RoleId
)

select
  1 as UserId
  , RoleId
from
  Role
where
  RoleId in (1,2,3)
-- only grab roles not already inserted into the database
and
  RoleId not in (
    select
      RoleId
    from
      UserRole
    where
      UserId = 1
  )

However, we can take this code one step further we can use the OUTPUT clause in SQL Server 2005+ to capture the changed data so we can return a resultset with our query that provides an audit trail of all the changes:

-- create a table to store results from the various SQL operations
declare @results table (Type varchar(20), UserId int, SecurityLevelId int)

delete
from
  UserRole
-- store the records deleted into the temp table
output
  'deleted' as Type, Deleted.UserId, Deleted.RoleId into @results

where
  UserId = 1
-- IF WE HAVE NO ROLES TO ASSIGN, DO NOT RUN ANY CODE BELOW THIS LINE, EXCEPT FOR THE FINAL SQL STATEMENT
-- this deletes only rows not in our new selection
and
  RoleId not in (1,2,3)

insert into
  UserRole
(
  UserId, RoleId
)

-- store the records deleted into the temp table
output
  'inserted' as Type, Inserted.UserId, Inserted.RoleId into @results

select
  1 as UserId
  , RoleId
from
  Role
where
  RoleId in (1,2,3)
-- only grab roles not already inserted into the database
and
  RoleId not in (
    select
      RoleId
    from
      UserRole
    where
      UserId = 1
  )

-- output the audit trail of the delete & insert operations
select
  *
from
  @results

With the addition of the OUTPUT clause, our query will now return resultset with 3 columns:

• Type – This a string that will be either "deleted" or "inserted", based on the operation that occurred
• UserId – The userId that was removed (in this use case would always be the same userId)
• RoleId – The role that was modified

Now you've got a great little SQL snippet that you can use that builds an audit trail that you can use to track that changes to your UserRole table! While this is a pretty simple example, you could use this basic syntax anytime you need to manage this type of data.

Posted by Dan on May 18, 2011 @ 11:39 AM

A task that most web developers have faced at one point or another is how to cleanly manage updating data in a one-to-many relationship in a relational database. For example, let's say that you have schema looks something like this:

The User table holds the information on all your Users, the UserRole table holds all the roles of each User and the Role table contains a list of valid roles that a user can hold. This type of schema gives us a ton of flexibility, but management of the UserRole table can often be tricky. The most common method I've seen developers use to maintain a table like this is to delete all the data in the UserRole table for a user and use a loop to do an insert on each role that needs to be assigned. This leads to SQL that looks something like:

delete
from
  UserRole
where
  UserId = 1

insert into
  UserRole
(
  UserId, RoleId
) values (
  1, 1
)

insert into
  UserRole
(
  UserId, RoleId
) values (
  1, 2
)

insert into
  UserRole
(
  UserId, RoleId
) values (
  1, 3
)

While this works, it has several issues:

1. If there are many roles being modified, this can lead to a very lengthy SQL statement and/or many database transactions (depending on how your code is implemented.)
2. There is no inherit constraint to ensure that an invalid role does not get inserted. If your database has foreign key relationships defined between the UserRole.RoleId and Role.RoleId columns your database would throw an error, but wouldn't it be better just to ignore invalid values?
3. Since you are deleting all the roles on each update, your assigning new UserRoleId to roles that may not have changed. This can lead to more work on your database's side for managing indexes, etc.

Wouldn't it be nicer if you could just supply a list of the RoleIds you want to update instead of doing a bunch of individual INSERT INTO statements?

Well there is actually a very nice way to manage this that should work in any modern database. The trick is to use an INSERT INTO with SELECT statement to manage your data. The basic idea is instead of doing a bunch of individual INSERT INTO statements, we can write a single SQL statement that looks like this:

insert into
  UserRole
(
  UserId, RoleId
)

select
  1 as UserId
  , RoleId
from
  Role
where
  RoleId in (1,2,3)

What's nice about this code is work's around our first two issues by reducing the SQL statement and ensures that only valid Roles will actually get inserted into the the UserRole table—since we're selecting the valid rows right from the Role table.

But how do we solve issue #3, so that we don't alter roles that haven't changed? The trick is to only delete the roles not in our new list of values, then modify our insert to only insert rows not already assigned to us. So our complete code now looks like this:

delete
from
  UserRole
where
  UserId = 1
-- IF WE HAVE NO ROLES TO ASSIGN, DO NOT RUN ANY CODE BELOW THIS LINE
-- this deletes only rows not in our new selection
and
  RoleId not in (1,2,3)

insert into
  UserRole
(
  UserId, RoleId
)

select
  1 as UserId
  , RoleId
from
  Role
where
  RoleId in (1,2,3)
-- only grab roles not already inserted into the database
and
  RoleId not in (
    select
      RoleId
    from
      UserRole
    where
      UserId = 1
  )

This new code gives us 2 simple SQL statements (that can be run in a single transaction) which will now remove any roles that are no longer assigned to a user and only insert the new rows that need to be added. We're not altering data that hasn't changed.

Coming shortly, I'll post a follow up article that's specific to SQL Server 2005 (and above) on how you can modify the above example to return a query that will contains an audit of all the changes applied to the database.

Posted by Dan on May 18, 2011 @ 11:16 AM

This is an issue I keep running into I always forget how to resolve the issue, so I thought I'd better blog the solution (that way when I run into it again and search for the fix, I'll find it via Google!)

If you're running into issues when converting a Word 2002/2003/2007 document into a PDF with images from the Word document being extremely blurry, this due to the use of GDI+ technology:

Under some circumstances, Visio and other document types with graphics that are embedded within a Word document may not convert correctly to PDF. This issue affects Word documents opened in Microsoft Word 2002/XP, Word 2003 and Word 2007. This is because these versions of word use the GDI Plus (GDI+) technology to allow printer-independent operations for on-screen rendering. This is most often used when the embedded object uses Postscript fonts. Documents that exhibit this problem might have areas of color, as if someone highlighted the document and missing text and graphics, but only in the embedded object.

There are two solutions to this problem:

Use another document viewer to convert this document
Any document viewer that can read the document and does not use GDI+ can be used to render the embedded object correctly. For example, both Microsoft Word 2000 and OpenOffice.org Writer do not use GDI+ and can be used to convert documents affected by this issue with no problems.

Convert the embedded object to an image
Probably the easiest solution to this issue is to paste the data as an image, rather than embedding the data into the Word document.

1. From your application from which you wish to embed data (e.g.: Visio), copy the shapes and the text that you want.
2. Open Microsoft Word.
3. On the Edit menu, click Paste Special.
4. In the Paste Special dialog box, click Bitmap (or Picture XXX), and then click OK.

Note: Do not click Picture (Enhanced Metafile).

courtesy: http://support.software602.com/kb/view.aspx?articleID=1063

I've been using the "Bitmap" option for logos and that seems to work very well to keep the images sharp.

UPDATE:

Some asked for a screenshot, so I thought I'd update this entry.

So that you can see the difference, here's a screenshot showing the problem. On the right is a Word document with the same image pasted twice. As you can see, the first version ends up looking extremely blocky when converted to a PDF. The second image was copied from the first image, but pasted using the Paste Special > Bitmap option and looks remarkable better. I wanted both versions in the same PDF so that you can see it's has nothing to do with the PDF conversion settings, but everything to do with Word and GDI+.

Posted by Dan on May 5, 2011 @ 12:15 PM