≡ Menu

Open-Source Alternative to Adobe ColdFusion

Six years ago, I had reason to move several CFML applications from a Macromedia ColdFusion MX 6 server to what was the only major open-source alternative – Railo.

Several years later, Railo was no longer being updated, and its website was shut down. The last post on the Railo site in mid-2015 mentioned a new (and perhaps unauthorized) fork of Railo 4.2, called Lucee. The Railo website then disappeared in mid-2016.

Due to the apparent demise of Railo and the continued need to support my CFML applications, I moved all of them back to Adobe ColdFusion 11.

Lucee open-source CFML server

Unbeknownst to me, Lucee was already gaining more support and has now taken the place as the leading open-source CFML application server.

While I don’t have any concrete plans at the moment for migrating my apps from CF 11 to Lucee, it’s good to know that I have options. Also, anyone considering moving from Railo to Lucee may find this article helpful, as this early adopter has already found some of the problems when migrating from Railo to Lucee.

Deleting Duplicate Rows in a SQL Server Table Using a CTE

Finding duplicate rows in a table is quite simple. Deleting only the duplicates – as opposed to deleting all rows that have duplicates – is a bit more complicated.

I have a table variable called “@Readings” with the following schema:

	TagName VARCHAR(100),
	TagValue NUMERIC(10,3)

First, I load the table with data from another database on a linked server called “OtherDB”:

INSERT INTO @Readings(TagName,ScanTime,TagValue)
SELECT DISTINCT tag,[time],value
	OPENQUERY(OtherDB,'select tag, time, value from archive.comp where (tag LIKE ''%test%'') AND time = DATE(''*'') ')

Then, I load that same data with some updates, both to the ScanTime column, and then to the TagValue column – always holding the TagName column constant.

SET @DateTimeStamp = GETDATE()

INSERT INTO @Readings(TagName,ScanTime,TagValue)
SELECT TagName,@DateTimeStamp,TagValue FROM @Readings

INSERT INTO @Readings(TagName,ScanTime,TagValue)
SELECT TagName,ScanTime,12345 FROM @Readings

To see what rows duplicate the TagName column alone, this query will suffice:

SELECT TagName FROM @PI_Readings GROUP BY TagName HAVING ( COUNT(*) > 1 )

By adding column names, you can look where the duplication is the TagName and the ScanTime:

SELECT TagName, ScanTime FROM @PI_Readings GROUP BY TagName, ScanTime HAVING ( COUNT(*) > 1 )

(If I were to add also the TagValue column, I would get zero rows returned, since there are currently no duplicates where all three columns are identical.)

So, what if you wanted to delete only the duplicates where TagName and ScanTime matched, irrespective of the TagValue?

The easiest way I have found is to use what is called a Common Table Expression, or CTE.

To use a CTE to delete these duplicates let’s first construct it to only show the duplicates:

	SELECT TagName, ScanTime, TagValue,
	FROM @Readings

To now delete the offending rows, change the last line from a SELECT statement to a DELETE statement:

CTE example

When creating the CTE statement, make sure there is a semicolon terminating the previous statement. This is not usually required in SQL Server, but it is in this case.

Another good example of this is on Stack Overflow.

While I do like SQL Fiddle, it seems that the DELETE function does not work on CTEs there, though SELECT statements do.

Using Dependency Walker to Find Missing Dependencies

I was notified that a little-used application within my organization had begun displaying an error instead of allowing the users to log in via Citrix.

ActiveX control error

This ActiveX control, which was integral to the operation of the application I was servicing, had come from a very old version of Crystal Reports. Apparently, someone had recently installed a full version of Crystal Reports on this server and it had deleted the older OCX file.

I found a copy of the OCX file, copied it to the C:\Windows\SysWOW64 folder (as it was a 32-bit control) and attempted to run Regsvr32 on it to register the file.

This was the result:

Regsvr32 error

Apparently the installation of Crystal Reports also deleted some files that the ActiveX control needed to be registered! Fortunately, I found a free program called Dependency Walker that can show missing dependencies on DLLs, OCXs, and some other file types.

Installation and running of the program is very straightforward. When running it on a copy of the OCX file on my desktop, this was the result:

Dependency Walker example

As you can see from the circled area, two dependencies were missing: crpe32.dll and olepro32.dll. Olepro32.dll did exist in the SysWOW64 folder, so I copied it to the desktop, when running again, only crpe32.dll was missing. This file did not exist in SysWOW64, but I found it in the Crystal Reports installation folder. By copying crpe32.dll to SysWOW64, I was able to register the OCX file there, and my application began working again!

Moving a Legacy Citrix-Based Application to a Current Version of Windows

In my last post, the problem I experienced was with an old application (which I’ll call “App #1”) written in PowerBuilder 6.5, and accessed via Citrix.

Today, a similar type of application (“App #2”) is the source of the problem, and the fact that it’s being moved to the same new server made the cause of the problem even more difficult to discern.

Both applications connect to the same Microsoft SQL Server instance. After moving App #1, the problem was connecting to an Oracle Database via ODBC. Connecting to SQL Server worked fine.

However, App #2, while ostensibly identical in its method of connecting to the database, threw an error when trying to connect via the new Citrix server.

error dialog box

This is the same error given if one enters an incorrect username and password, so it’s not the most helpful notification to receive. Also, this application has no error log, and does not send errors to the Windows Event Viewer. App #2 is basically a black box.

I decided to check whether or not XP Compatibility mode would affect how App #2 ran on the new server. Though App #1 was unaffected by this, something had to be different and this should be easy enough to check.

Because the application ran from a network drive rather than locally on the Citrix server, checking the Properties on the executable was not fruitful. The entire Compatibility tab was grayed out.

Compatibility tab on Properties dialog box for app on network drive

By copying the application executable to the server, I was then able to set the Compatibility Mode selector for Windows XP SP 3. The application ran perfectly! This did not completely solve my problem though – this app was run by Citrix via batch file rather than an app shortcut.

I figured there must be a way to set the Compatibility Mode within the batch before executing via start.exe.

After digging a bit, I did find several options for doing this. Though the method preferred by many involved adding registry keys, I chose the simplest route.

Adding this line to the batch file just before the call to the application executable solved my problem!