Great Circle Distance in Coldfusion

I saw a question today on calculating the straight line distance between two geocoded points in the Google Maps API with CF.

Looking around on the internet I found the equations I needed at http://www.meridianworlddata.com/Distance-Calculation.asp and set to work. In the end it was much simpler than I had expected:

<!--- RADIUS OF EARTH --->
<cfset r = 3963>

<!--- DC
Latitude: 38° 57' 26" N (deg min sec), 38.9572° (decimal), 3857.43N (LORAN)
Longitude: 77° 2' 3" W (deg min sec), -77.0342° (decimal), 07702.05W (LORAN)
--->
<cfset lat1 = 38.9572>
<cfset lon1 = -77.0342>

<!--- BOSTON
Latitude: 42° 14' 7" N (deg min sec), 42.2352° (decimal), 4214.11N (LORAN)
Longitude: 71° 1' 39" W (deg min sec), -71.0275° (decimal), 07101.65W (LORAN)
--->
<cfset lat2 = 42.2352>
<cfset lon2 = -71.0275>

<!--- A GUESTIMATE OF THE DISTANCE --->
<cfset x = 69.1 * (lat2 - lat1)>
<cfset y = 69.1 * (lon2 - lon1) * cos(lat1/57.3) >

<cfset guess = sqr(x * x + y * y)>
GUESS: <cfdump var="#guess#"><br />

<!--- GREAT CIRCLE - SHOULD BE MORE ACCURATE --->
<cfset GCDistance = r * acos(sin(lat1/57.2958) * sin(lat2/57.2958) + cos(lat1/57.2958) * cos(lat2/57.2958) *  cos(lon2/57.2958 -lon1/57.2958))>
GC Distance: <cfdump var="#GCDistance#"><br>

For the 'Guess' distance between Washington DC and Boston I came up with 394 and change and for the 'Great Circle Distance' I got 388 and change.

Being the Obsessive Compulsive programmer I can be I checked at http://www.wcrl.ars.usda.gov/cec/java/lat-long.htm and found they had 395 miles.

The difference comes from a slight difference in the start and end points =)

cfform in CF7

So you can make really 'Flashy Forms' with CF and cfforms... that said, I've never been that big of a fan of them and the 2 hours I spent trouble shooting a form that looked right in Dreamweaver but didn't display in the browser reaffirms my belief that they are more trouble than they are worth.

It came down to a JS include for validating if a checkbox was selected. Removed it and the form suddenly displays... >shakes head<

Surface Area Configuration For MS SQL server 2005

I end up seeing this question a lot and even though it is posted in other places on the web I wanted to put it someplace where I could easily link to it.

Here is how you can check the remote connection settings on SQL Server 2005 (note you need to have Admin rights):

There should be a program called: "SQL Server Surface Area Configuration" in the MS SQL Server 2005 / Configuration Tools directory:

Click "Surface Area Configuration for Services and Connections":

A configuration window will appear with a tree navigation panel on the left hand side. Select 'Remote Connections' under the [SERVER NAME] \ Database Engine:


A short form will be displayed with a couple of radio buttons, make sure that "Local and Remote connections" is selected and that either "Using TCP/IP only" or "Using both TCP\IP and named pipes" is selected.


With that done, applications which use TCP/IP to access your DB should be able to connect to the DB Server.

Running A DTS from CF

I just got asked how to run a SQL Server DTS from Coldfusion and here is the answer:

You will need to:

1. Write and test your DTS
2. Write a stored procedure which will run the DTS
3. Write the code in CF to call the SP, this code can then be called directly by the user or set up a scheduled task

Something to remember, DTS packages can A LONG TIME to run and your code should account for that fact by setting your timeouts high enough or preventing the user from resubmitting the form and setting off another DTS

Below is an example of a SP to call the DTS

/*Run DTS Package stored procedure*/
CREATE PROCEDURE [dbo].[sp_AdRunDTSPackageOnServer]
@nID int

AS

DECLARE @DTSPackageObject int
DECLARE @HRESULT int
DECLARE @property varchar(255)
DECLARE @return int
DECLARE @ErrDescrip varchar(255)
DECLARE @ErrSource varchar(30)
DECLARE @ErrHelpId int
DECLARE @ErrHFile varchar(255)
DECLARE @ErrMsg varchar(255)
DECLARE @sDTSPackagePath varchar(1000)
DECLARE @sDTSSpecialUser varchar(50)

SELECT @ErrMsg = 'Error running DTS package'

--NOTE: remember to give the account running this procedure access to the sp_OA system stored procedures

SELECT @sDTSPackagePath = DTSPath , @sDTSSpecialUser = DTSSpecialUser
FROM T_AdDTSPackageSetup
WHERE
DTSID = @nID

IF @sDTSPackagePath is NULL

BEGIN

SELECT @ErrMsg = 'Error DTS package not found'
RAISERROR (@ErrMsg,11,1)
RETURN

END

Blogger Template Problems with Code Examples

Looking at my last post I see that there is an unfortunate issue with the template I'm using and formatted code that is longer than the width of the template (coming from Blogger I might add) >grrr<

I guess I'll either have to come up with another solution, I've played with Ben Nadel's code coloring snippet and maybe that will be the solution.

CF Component Template - A Work in Progress...

I'm constantly looking for ways to make my life as a developer easier and I really should stop being surprised when I talk to my 'peers' and find out that instead of using templates or reusable code that they prefer to hack out the same code over and over and over and over... well you get the point.

So I've been using a basic template for components for the past year or so and I figured I would put it up here to help encourage lazy coding =).

<!---
   PURPOSE:
   REQUIREMENTS:
   CREATED BY: RICHARD A HOSKA (developer email address)
   DATE: /2007
   TASK ORDER:
   COPYRIGHT: 2007
   REPOSITORY:
   TO DO:
--->

<!---
   any comment blocks in lowercase should be removed or replaced, they are simply here
   to give information on settings to be held in component
--->

<!---   
       display name: friendly name for the cfc
       output: defaults to yes to allow for html output for debugging purposes
       hint: friendly description of what the cfc does
--->
<cfcomponent displayname="" output="yes" hint="">

   <!--- what does it do
       ACCESS: - type of function (secure, remote, private, public)
       ACCEPT: parameters that are accepted
       REQUIRED: parameters that are required
       RETURN: what is returned
                   IF AN ERROR OCCURS, RETURNS A STRING WITH DEBUGGING INFORMATION
       --->
   <cffunction name=""
       access=""
       returntype="any"
       output="true"
       displayname="" hint="">
     
       <!--- DEFINE ARGUMENTS --->
       <cfargument name="" type="" required="" default="" displayname="" hint="">
     
       <!--- DEFINE VARS USED EXCLUSIVLY BY THIS FUNCTION --->
       <cfset var returnVariable = "">
       <cfset var errorMessage = "">
       <cfset var debug = FALSE>
     
       <!--- DEBUGGING INFORMATION --->
       <cfif debug>
           <cfdump label="name of function Arguments:" var="#arguments#">
       </cfif>
     
     
       <!--- DATA VALIDATION --->
     
       <!--- IF THERE WERE NO ERRORS --->
       <cfif errorMessage EQ "">
     
           <cftry>
               <!--- DO WHAT EVER --->
             
               <!--- SET RETURN VARIABLE --->
               <cfset returnVariable = >

               <cfcatch type="any">
                   <cfset errorMessage = #cfcatch.Message# & #cfcatch.Detail#>
               </cfcatch>
           </cftry>
         
       </cfif>
     
       <!--- IF AN ERROR MESSAGE HAS BEEN GENERATED, PLACE IT IN THE RETURN VARIABLE --->
       <cfif errorMessage NEQ "">
           <cfset returnVariable = errorMessage>
       </cfif>
     
       <!--- RETURN RETURN VARIABLE --->
       <cfreturn returnVariable>
             
       <!--- DEBUGGING INFORMATION --->
       <cfif debug>
           <cfdump label="setAlert returnVariable:" var="#returnVariable#">
       </cfif>
   </cffunction>
</cfcomponent>

Getting the Most Recently Added Field From A DB

I've been coding for a pretty long time and I'll admit being self taught in a lot of ways I sometimes do things in a totally back assed way... Take getting the value of an identity insert, I've been doing it like this:

<!--- INSERT INTO DB --->
<cfquery name="createLogEntry" datasource="#request.LOGdsn#">
    INSERT INTO APP_LOG
        (UNIQUE_ID,
         APPLICATION_KEY,
         EMPLOYEE_KEY,
         DATESTAMP,
         REMARKS,
         SEVERITY_ID,
         LOG_TYPE_ID)
    VALUES
        (#Arguments.unique_key#,
         #Arguments.application_key#,
         #Arguments.employee_key#,
         #now()#,
         '#Arguments.remarks#',
         #Arguments.severity_key#,
         #Arguments.log_type_key#)
</cfquery>

<cfquery name="getMax" datasource="#request.LOGdsn#">
    SELECT MAX(APP_LOG_ID) AS MAX_ID
    FROM APP_LOG
</cfquery>

Now I'm doing it like this:

<!--- INSERT INTO DB --->
<cfquery name="createLogEntry" datasource="#request.LOGdsn#">
    INSERT INTO [Aeolus].[dbo].[APP_LOG]
        (UNIQUE_ID,
         APPLICATION_KEY,
         EMPLOYEE_KEY,
         DATESTAMP,
         REMARKS,
         SEVERITY_ID,
         LOG_TYPE_ID)
    VALUES
        (#Arguments.unique_key#,
         #Arguments.application_key#,
         #Arguments.employee_key#,
         #now()#,
         '#Arguments.remarks#',
         #Arguments.severity_key#,
         #Arguments.log_type_key#)

SELECT APP_GROUP_KEY = SCOPE_IDENTITY()
SET NOCOUNT OFF

</cfquery>

And POOF! No more worries about locking and integrity issues... The page load should be just slightly faster not to mention less typing which is my personal favorite. =)

bad / pointless code of the day

So can you spot the bad code here?

<cfparam name="attributes.module_id" default="0">

<cfparam name="attributes.method" default="">

<!--- get all privileges except read --->

<cfif not comparenocase(attributes.method, "Submit")>

<!--- deactivate all locked modules --->

            <cfinclude template="qry_deactivate_module.cfm">

             <cfif isdefined("attributes.module_id") and attributes.module_id neq 0>

                        <cfloop list="#attributes.module_id#" index="i">

                        <cfset module_comments= evaluate("comments_"&"#i#")>

                                    <cfinclude template="qry_insert_lock_module.cfm">

                        </cfloop>

            </cfif>

            <cflocation url="index.cfm?fuseaction=#attributes.fuseaction#"> 

</cfif>

A couple of friends and I like to compete for who has come across the worst code of the day which started an interesting little conversation about how we make use of <cfparam tags. I personally avoid them for as general rule. I was actuly surprised to find out that

<cfparam name="comments_#module_id#" default="">

is syntactically correct.

Since CF6 I've preferred to build an object in the session scope to store these elements and access it via a cfc. In the past couple of months I've moved more towards a more Object Oriented Approach. More to follow I'm sure.

By the way the bad code would be first testing if a variable named 'attributes.module_id' exists and if it doesn't sets a default value using the cfparam tag; then 5 lines later checking to see if that same value isDefined.

FISMA and NPD 2810

Part of the exciting life of being a government contractor (or really any involvement with the government) is the continuing creation and update of policies. One of my favorites is FISMA, and the resulting NASA Policy Directive (NPD) 2810.

As far as a developer is concerned, both of these policies can be distilled to a few basic rules regarding user access to Information Systems.

• Password requirements
• Password reuse
• Use of two factor authentication

Even though FISMA has been around since 2002 and NPD 2810 has been on the books since 2006, a large number of legacy applications have yet to be modified to follow those standards and amazingly the number of developers who are familiar with them is relatively low.

In the next series of posts, I will hope to shed some light on some of the requirements and provide a series of Coldfusion components that can be used as a common methodology for meeting (at least the NPD).

The Marine…

I imagine there was a really good reason I didn’t see this movie in the theaters, its pretty god awful but at the same time I haven’t been able to pry myself away. If there is one thing that WWE does really well, it’s put reasonable jokes into a bad storyline full of stock characters sounded by enough action to keep things interesting.

Not a bad way to waste away a hot Saturday afternoon.

Oh and there is nothing quite like weapons escalation, first its fists then a sledge hammer and now a chain saw... "It's okay my dog found the chainsaw" vs. a pipe wrench. pipe wrench wins but wait surprise attack of the steel chain.

I won't ruin the ending for you.

cryptic Ektron CMS400.NET error messages

I’ve recently inherited a 50% responsibility for a Ektron’s CMS400.NET (v. 6) application which has yet to impress me.

I received an issue report from a customer this morning where some very simple content that she has been making changes to on a frequent basis suddenly became ‘corrupted’. I love cryptic issue reports from customers =/.

Taking a quick look at the content item in question I was greeted by one of my favorite complaints about the system, the very un-friendly error messages which are displayed to the user and not captured (to the best of my searching) anywhere helpful both when browsing to the published content and when trying to edit it through the CMS workarea.

Add failed. Duplicate key value supplied. At Microsoft.VisualBasic.Collection.Add(Object Item, String Key, Object Before, Object After) at Ektron.Cms.DataIO.EkContentRW.GetApprovalState(Int32 cId) at Ektron.Cms.EkException.ThrowException.Cms.EkException.ThrowException(Exception ex, EventLogEntryType EventType) at Ekron.Cms.EkException.ThrowException(Exception ex) at Ektron.Cms.DataIO.EkContentRW.GetApprovalState(Int32 cId) at Ektron.Cms.Content.EkContent.CanIv2_0(Int32 cId, String RequestType) at Ektron.Cms.EkException.ThrowException(exception ex, EventLogEntryType EventType) at Ektron.Cms.EkException.ThrowException(Exception ex) at Ektron.Cms.Content.EkContent.CanIv2_0(Int32 cId, String RequestType) at Ektron.Cms.ContentAPI.LoadPermissions(Int32 Id, String RequestType, PermissionResultType Type)

Some experimenting gave me very interesting behavior. Since I couldn’t modify or delete the record through the CMS interface I decided to jump into the production database (which is something I abhor doing and avoid at almost any cost) and quickly was greeted by 120 some tables.

A side note: at the very least Ektron gives you the ability to poke around in the database and doesn’t try to hijack your data.

I found the [content] table and the pesky ‘corrupted record’ and changed its content_status field to A (for approved). I went back into the CMS workarea and amazingly enough when I refreshed the listing page the status change had been picked up.

I still couldn’t edit the content item so next I tried to delete the record through the CMS application. Big surprise when I got another error only this time it was a ‘friendly IE error’.

Gee I was sure that I had my settings in IE set to NOT show that kind of error and looking at my settings I was puzzled to see that they were as expected.

Perplexed I tried to delete the content again only to find that it had been checked out to me and there was no way to check it back in to the system through the CMS. I went back to the database to change the status and immediately found that the content_id of 45 was no longer in the content table, dashing my hopes of just changing the status again. But why does that record (content_id 45) still show up in the CMS when its not in the DB?

I looked around in the CMS for someway to get myself out of the mess I had created on a production system and found another piece of ‘corrupted content’ (content_id = 46, very interesting)

I tried the same steps as above, got to the point where I used the CMS to delete the 2^nd corrupted content item, received the same ‘friendly error message’ and found something totally different. Content_id 46 was still in the content table. Hmmm puzzling.

I tried to delete content_id 46 again and still it was in the database.

It was time to cut my losses at this point so I tried to delete content_id 46 from the database and was greeted with a foreign key constraint. More interestingly when I reformed my query specifying content_id 45 (the one that was gone a second ago) magically it was back!

The data must be cached somewhere right? So I close out of Management Studio and try it again… the record was still there. Amusingly enough when I did a query for all records where content_id is under 100 it wasn’t returned…

Neat right?

So I started to wonder and when I pointed my browser to id=45 and amazingly the content which was showing a nasty error when the user went to it was there… hmm what happens if I right click and try to edit it… you guessed it, I amazingly could see the content and make edits to it through the GUI…

But when I tried to save it… yea, that not so helpful .NET error popped up again:

Add failed. Duplicate key value supplied. At Microsoft.VisualBasic.Collection.Add(Object Item, String Key, Object Before, Object After) at blah blah blah.

Oh well, back to cutting my losses, let’s delete those records and use the CMS to recreate the content for the user… I first went through the CMS and created 2 records to replace the records I was going to delete, updated the primary navigation in the template to point to the newly created content items (another issue I have with the system is that there doesn’t seem to be a simple way of setting a value that will enable the template system to change links to content in the primary navigation which is stored in a CF page with hard coded links).

Time to get rid of that foreign key constraint, looking in the approval_status_tbl table I find two duplicate records, 1 set for content_id 45 and 1 set for content_id 46. When I tried to delete them I got a very helpful error message from Management Studio.

I should note that this is most likely the cause of the initial “Add failed. Duplicate key value supplied.” error. Note to self: if I have this problem again before I go though all the above headaches I’m going to check the approval_status_tbl for duplicate entries and take care of them.

I had to go through and delete a couple more records in other tables which were creating foreign key constraints in the following tables:

• library
• save_tbl
• content_edit

Once that was done I was able to delete content_id 45 and content_id 46 from the content table

And amazingly the records were gone from the CMS!

It’s an awfully long-winded explanation (but hey, I got stuck for an extra 2 hours at work on a Friday afternoon so there ;) ) for basically the following nugget of information: When confronted with an error containing “Duplicate key value supplied” while editing content look in the approval_status_tbl table for duplicate records and remove them.

I’m not sure how exactly these problems could have arisen in the first place; approval_status_tbl doesn’t have a primary key defined but it should have thrown a foreign key constraint error and prevented any duplicates from being generated… for kicks I tried to manually enter it and oh guess what? I get a FK constraint error… additionally from a programming standpoint, I make it a point anytime that I’m inserting data into more than a single table to catch any errors from an insert, roll back the transaction and then inform the user in a polite manner that there has been a problem (oh and I make sure that I log the error details and inform the development team that is maintain the application that something has gone wrong).

As far as I can tell the issues that I was experiencing with content_id 45 and content_id 46 were identical and yet when performing the same actions and getting the same displayed errors, different things appeared to be taking place in the database. (When I deleted 45 from the CMS the associated record in the content table was deleted but when I deleted 46 through the CMS that record remained)

The other interesting/troubling behavior that I saw was from the database queries; if a record in a database table can be queried directly (select * from [content] where content_id = 45) it should always be included when asking for the entirety of the table (select * from [content] where 0 = 0).

I found it to be an interesting little puzzle and I’ll be very interested to see what Ektron’s technical support has to say about it.