Wednesday, December 9, 2009

Solving Excel's "DBCC TRACEON" error with brute force and ignorance

We use excel reports extensively for reporting. They are fast to create and everyone can read and manipulate them. However there are the occasional gotchas. In the past, I have had to muck about with macros to fix connection string errors.

We upgraded to sql server 2008 on the weekend. Following that, nearly every data aware spreadsheet started giving "User 'public' does not have permission to run DBCC TRACEON" errors.

The problem, and a resolution is described here. Older versions of Excel/MS query identified themselves by including APP=Microsoft® Query;" in the connection string. MS also hard coded a check for "Microsoft® Query" in sql server which then runs DBCC TRACEON for no apparent reason.

Under sql server 2000, this in fine, but under sql server 2008, this fails as DBCC requires admin permissions.

The correct approach is change the connection string as described in the linked posts. Previously I have done this with macros. However this time I had 200+ spreadsheets to change :(.

I used a free search and replace utility (ReplaceText) to replace all instances of "APP=Microsoft® Query;" with "APP=Microsoft® WTFIT;". 2 important things here, the search and the replacement text are the same size, and the strings include "App=". Failing to do either will give you a corrupt file.

Problem solved in about an hour. It's not a pretty solution but it's better than spending a week at it.

Monday, November 16, 2009

Why I am moving from delphi to .net

This post is somewhat of a conterpoint to Why we didn’t convert to .Net. And perhaps we never will… I would have writen a post earlier, but I have spent most of the last week writing a tender proposal. The tender was for a claims management application with 70 users scattered across the country. My proposal was for a web application, using mvc and writen in c#.

My main objection to the WoW post is not his (or her) conclusion, but the subtext that .net has nothing to offer. Delphi programmers (or at least the vocal ones hanging out in non-technical) seem to have a strong view on what "real' programming is, and .net fails on several points (not native code, garbage collected, large installation footprint) for these guys. My view is that "real" programming is solving problems, and using the best tools for the job. In some cases that is delphi, but increasingly for me it is c#.

I haven't suddenly jumped ship, I reevaluated our (Catalyst's) needs earlier this year. Two things came out of that; most of our futher development will be in web apps, and .net is a better fit for us.

My and Catalyst's needs are not univeral. I don't pretend that my reason for moving apply to everybody. None the less, here are my reasons for moving:

When it comes to performance, my most important benchmark is developer time. As a (mostly) solo developer, I don't have enough time to do all the things that I want. Anything that improves my productivity is good. I find c# faster to develop in.

From an runtime speed, it's hard to find good benchmarks. My feeling is that Delphi is faster, but not enough to make a noticable difference in my kind of apps. .net is let down by the winforms implimentation which (the last time I used it) had painfully slow drawing. The rest of it is plenty fast enough.

Language features:
Garbage collection and Linq. You ever love them or hate them. I love them. Delphi has mostly caught up with the other big ticket items but not these.

The standard library is another .net plus. It may have an enormous footprint but it's certainly extensive.

I work in a small city. Finding delphi developers here is nearly impossible. I only know of dev company using delphi and, for historical reasons, I can't use them.
.net developers is a different story. I could throw a stone out the window and hit one (seriously, they're just across the road).

I am not abandoning delphi completely, I have a number of delphi apps to maintain so I will be using it for a while yet. .net is not a silve bullet either. For web apps I find .net better, but for desktop apps it's pretty much even.

Tuesday, September 22, 2009

D2010 suppport in tiOPF

tiOPF is a Object Persistence Framework. That is, it is a framework based around saving your objects to, and loading them from, databases and/or flat files.

Delphi 2010 support has now been added to the latest repository version.

No release has been made as of yet, so you need to to retrieve it from the subversion repository. See here for instructions.

  • Running unit tests requires copying the xdom.pas unit from Delphi 2009as this is no longer supplied
  • There are a large number of failures with BDE paradox tests. I haven't investigated this yetas no-one uses paradox any more
  • tiVirtualTree and related components have been removed from the gui controls as they don't work under D2009/D2010.
  • Unicode is still not supported :(


Tuesday, September 15, 2009

C# for the iPhone: Monotouch released

Novell's MonoTouch product has been released. MonoTouch is mono for the iPhone.
This allows compiling .net applications to native code and deploying to the iPhone.

There are akready 200 apps in the app store using Mono via Unity so the underlying technology is fairly well tested. MonoTouch adds Cocoa support and integration into MonoDevelop.

MonoTouch has 2 main issues for some users, the $399US price point, and the logo.
The price has disappointed a number of beta testers and the logo is the second worst I have seen recently (worst is the Toastmasters goatse).

Friday, August 28, 2009

When you install Delphi 2010, put it in a virtual machine

I recently compared the performance of virtual machines with the real hardware. The figures were suprisingly good.

Shortly after that, disaster struck. My laptop wouldn't turn on. It took Dell 2 weeks to fix, replacing the motherboard and the video card.

If I had delphi installed directly, I would have had 2 weeks of very limited productivity. As it was, I ripped the hard drive out, stuck it in a usb caddy and continued working on another machine. I was back up to speed the next day.
Edit: It didn't take a full day to get productive again, I did other work until it became apparent that it would be a while before my computer was fixed. It only took about 30 minutes to transfer the data over and get going.

If it had been a hard drive problem, I would have restored the latest vm backup off a dvd, pulled the latest changes from the version control or the source backup and been back up to speed with limited data loss.

I get a new laptop next month. Installing delphi is going to be as easy as installing vmware and copying the vm files over. 30 minutes work, most of which is surfing the internet waiting for the files to copy. The last time I actually had to install Delphi, it took hours.

There are other advantages as well, my development backups fit on a single dual layer dvd, I get to run and test on multiple OSs and disaster recovery plan is much shorter.

So make Delphi 2010 your starting point. Download Virtual Box, VMWare Workstation ($$$) or Virtual PC and use that for development. If you are running Windows 7, you could look into Windows XP Mode and use that.

Friday, August 14, 2009

Changing Excel query connection strings

We use Excel and database queries extensively for reporting purposes. It's quick and easy to set-up, and provides reports that our clients can manipulate.

However I have recently run into a rather painful excel quirk with ODBC connections: Excel stores the database connection string internally. Even if you change the ODBC connection on the computer, excel still uses the original connection from when the query was created.

This bit us when moving the reports to a different machine. Despite having the same ODBC connection set up, excel wouldn't refresh the query and gave the error "[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied."

A similar problem can happen if you update the database from sql server 2000 to sql server 2005 or 2008. In that case you may get the error "[Microsoft][SQL Native Client][SQL Server]User 'DOMAIN\username' does not have permission to run DBCC TRACEON." In that case, you need to alter the connection string to change "APP=Microsoft® Query" to something else. Apparently MS hard coded a check for "Microsoft® Query" in sql server which then runs DBCC TRACEON for no apparent reason. I suggest "APP=WTFWYT"

Changing one query
(from here)
"Open the worksheet and place the cursor on a cell within the cell range of
the query. Press Alt-F11 to open the VBEditor. Press Ctrl-G to open the
Intermediate window. Type the command: ? ActiveCell.QueryTable.Connection.
The embedded connection string will be echoed back to the screen. Put double
quotes around the string and update the connection information with the new
server info. Move the cursor to the beginning of the connection string and
insert the following in front of the string:
ActiveCell.QueryTable.Connection =

Changing multiple queries in a spreadsheet
(modified from here)
You need to create the following macro (change connection string to suit, see above) and run it in each spreadsheet requiring change. (See the steps below). Once the macro has run successfully, delete it before saving the spreadsheet.

Sub ChangeConnections()
Dim sh As Worksheet, qy As QueryTable
For Each ws In ActiveWorkbook.Sheets
For Each qy In ws.QueryTables
qy.Connection = "..."
On Error Resume Next
If Err.Number <> 0 Then MsgBox "Problem refreshing QueryTable: " & Err.Description
Next qy
Next ws
End Sub

Getting the new connection string
The easiest way to find the new connections string is to create a new query and do the ?ActiveCell.QueryTable.Connection trick.

Friday, July 10, 2009

Windows Performance Index - VMware workstation v Virtual Box

Virtual Box 3 has been released. I haven't used previous versions of VB, but it has a good rep. Version 3 now supports 3d acceleration. I have been wanting to use Aero and Glass in my virtual machines so that I could develop Vista specific applications so I gave it a try.

First the bad news, VB doesn't support enough 3d features to run Aero (neither does VMware). I did discover a work around though, connect to your virtual machine using Remote Desktop Connection from a Vista machine, turn all graphics options to high and volia.

While I had VB installed, I did a quick and dirty comparision with VMware workstation using Windows Performance Index. Take these results with a grain of salt, I didn't spend much time playing with the setup and tweaking. I couldn't get dual core to work on VB, it just blue-screened, and I didn't have the time to spend sorting it out.

Windows Performance Index

Host VMware
Single proc
Dual proc
Virtual Box
Single Proc
Processor 5.4 4.4 5.2 4.4
Memory 5.1 4.9 5.2 5.9
Primary Hard disk 5.4 5.3 5.4 4.7

The memory figures are surprising, I am not sure if the VB figures of 5.9 are real or just a sign of something wrong somewhere. Draw your own conclusions on that.

VB is slower in hard drive, but otherwise up with the play processor wise. Getting dual core working would probably give similar processor figures to VMware dual core.

I was impressed with how well VMware matches up to the host system. While VB doesn't seem to be quite there yet, it is still doing pretty well, and you can't beat the price.

Tuesday, June 30, 2009

iPhone programming might be getting a whole lot easier

The mono project has announced MonoTouch. MonoTouch is Mono for the iPhone and the iPod touch. Applications writen with MonoTouch work and non-jailbroken phones, and can be submitted to the app store.

See Miguel's blog for additional details. According to the comments, a beta should be released in August.

Time to start brushing up my C# skills.

Monday, June 29, 2009

Guido just doesn't get windows programming

Among the blogs I follow is that of Guido van Rossum, the creator of Python. Mostly they are interesting but his latest one (IronPython in Action and the Decline of Windows) is a large pile of wtf. Apparently programming windows apps is tedious and gui creation requires many lines of code. Even more surprising to me, web apps are far easier to create.

Wow, apparently he has never heard of delphi, visual studio or any of the other gui editors that have been about since at least the mid 80s. The last time I laid a gui out in code, other than for eductational purposes, would have been about 1986. I have no desire to do it again either.

Wednesday, May 13, 2009

iPhone Dev: Zombie Mansion post mortem

I released Zombie Mansion in mid December 08. It is a 1st person shooter, the 2nd available on the iPhone and the first one to be designed for mobile devices rather than being a pc port. ZM was a port and extension of the work I had done previously on the Yeti engine.

Seeing as how every other iPhone developer is sharing their tales of mega success, or crushing failure, I though I would add my story.

ZM has sold steadily, if not spectacularly. Unfortunately I have not made enough to pay off my house and buy a fast car. On the other hand, it sold well enough to pay my mortgage payments for the 1st 3 months. Sales are as you would expect: hundreds sold in the first couple of days, rapidly dropping off as ZM moved off the recent releases page. My first update provided a brief blip but my second update was unnoticeable in terms of sales.

I took 2 weeks leave to finish ZM, and I estimate that I spent the equivalent of another 2 weeks previously. This doesn't include the original porting work I did moving the yeti engine to the pocket pc.

As a return on time spent, ZM performed slightly better than working at my day job. It was however considerably more fun. It would have performed even better but I made a couple of bad design decisions.

Unusually, I did the bulk of the development in windows using visual studio rather than in xcode on OSX. I already had an OpenGL ES port running on Windows Mobile and Windows so an iPhone port wasn't much of a problem.

Coding was done in a mixture of C (yeti code) and C++ (my code).
On the iPhone, I used the Oolong engine to handle the sound, input and 3d setup. Oolong provides c++ wrapper classes for all this (and more).

On the pc and windows mobile side, I used the PowerVr sdk to handle 3d setup. Keyboard handling is trivial, and I used Hekkus to provide sound support.

Once I had this all set-up,I did most coding in visual studio. At irregular intervals, I would reboot into OSX and compile with xcode. I mostly used xcode for iPhone specific stuff and performance testing. All my support tools (map editor, 3d importer etc.) are windows based (in c++ builder and Delphi) so that necessitated mostly using windows. I prefer visual studio to xcode, so it wasn't much of a chore.

I made a couple of mistakes. The first, and most major was simplifying the weapons too much. I went with one weapon, a staff, with upgrades rather than with multiple weapons. In retrospect, a bad call.

The second mistake was not putting some help tips around controls. The controls work well if you leave your thumb on the d pad and slide it to control. If you pick your thumb up and move it, the controls don't work as well. Some easy way of explaining this to the player would have made their initial impressions better.

The underlying problem is that perennial development favourite, I underestimated the time needed to write the software. I ran out of annual leave and thus ran out of time. While I enjoyed the game that I wrote, it would have been better if I had more time to add extra features and polish.

Dealing with Apple
There are 3 major drawbacks to iPhone development: Apple, Apple and Apple. I don't know if they are overworked, incompetent or just don't care. Either way, developer support seems to be missing in action.

Since I have had children, I have made a concious effort to improve my language, removing the more robust terms I picked up working in the abbottiors. Three months of dealing with Apple has made a mockery of my self-betterment attempts, leaving me swearing like Gordon Ramsey on a bad day.

Simply getting a paid developer contract took six weeks. My first payment, for December, didn't get paid until late March. Emails asking when money was going to arrive were ignored, or fobbed off with stock responses or outright lies. Whenever I phoned, I was told they couldn't help and to email another department.

Even when I did get paid, there was no notification. Apple can email me every week, trying to sell me a second rate album farted out by a talentless boy band, but they can't be bothered sending me an email saying "We have now condescended to pay you".

As a comparison, RegNow emailed me on Jan 4th letting me know my December payment had been released, and the money was in my account on Jan 6th. Even now that Apple has decided to make my monthly payments on a monthly basis, they still take nearly 40 days longer than Regnow.

Random thoughts
There is clearly still money to be made in iPhone dev. ZM hasn't disturbed the sales charts, but it has still sold well enough to cover it's costs and my time. I imagine that there are many developers in a similar position, not making riches, but making enough to get by and keep developing. A small stable of good games/applications could provide a nice living without requiring a hit.

It used to be that you could get a large amount of exposure and thus sales by releasing an update every couple of weeks. However the volume of new applications has changed that. There are so many apps being released each day that the initial rush of sales from being in the "what's new" charts is much smaller. You need to get sales the hard way, with marketing; promotion and advertising.

I also have a couple of windows shareware applications, Pics Print and Rental Property Manager. While it is not easy to compare windows utilities and business applications with an iPhone, the return on time invested is far greater for these 2, particularly RPM, than for ZM.

All this brings me full circle from where I started out six months ago. There is money to be made in iPhone apps, but there is also money to be made in windows shareware as well. If you are a windows developer, particularly if you are a Delphi developer, then you are probably better off developing windows shareware than iPhone apps. I know a lot more developers making money off windows than off iPhones.

Oh, and developing for iPhones still sucks! Not as badly as I first thought, but still noticeably.

Friday, April 17, 2009

And Now for Something Completely Different

One of the pragmatic programmer tenets is to learn a new programming language every year. I'm a bit behind on that, I haven't learn a new language since 2002 (I don't count objective C because I hate it and have only learnt the bare minimum necessary to get by). Unfortunately the need to earn a living has gotten in the way of random learning.

However I have now been programming in Delphi for 15 years. It's starting to get a bit boring and it's time to kick-start the brain again. While Delphi will be my main language for the near future, I am going to make the effort to learn something different.

The main things I am looking for in my language de jour are
  • Dynamically typed (I have done statically typed languages for decades)
  • Popular (general rule of thumb, should be at least as popular as delphi)
  • Object Orientated
  • Cross platform
  • Batteries included
This pretty much narrows it down to Perl, Php, Python and Ruby. I was initially tempted by Ruby as I don't yet have an R on my alphabetical list of programming languages. However I have settled on Python instead.

Part of the reason for going with Python is the presence of the Python for Delphi components. These promise to let you use python as a scripting language inside delphi apps. These components are used in the PyScripter ide.

The easiest way I have found to get started is to use Portable Python. This will put Python and a few tools (inc. PyScripter) into a portable drive along with a few tools and useful modules such as Django. This is considerably easier for a python newbie than sourcing and installing the packages separately.

Some useful links to get started with:
Official website
Official python tutorial (v2.6)
Dive onto python online book (useful but a bit old)
Pycon US 2009 videos
Reddit python feed

Monday, March 9, 2009

Filtering generic collections with anonymous methods

I have recently been adding generics to the ti Object Persistence Framework. As part of that I was asked to add enumerator filtering. I did this using a similar technique to that shown here by Malcolm Groves.

I ended up with code used like this:

for item in Flist.FilteredEnumerator(function (TestObject: TtiOPFTestIntegerProp): Boolean
result:= TestObject.IntField mod 2 = 1;
end) do
intSum:= intSum + item.IntField;

I was happy enough with the result, but I wasn't happy with the implementation. I wanted something that was, well, more generic. What I ended up doing was wrapping the existing enumerator into one containing a filter. This will work with any generic collection that descends from TEnumerable. I.e. TList, TQueue, TStack, TDictionary and the TObjectXXX variations

To implement an enumerator, a class must have a GetEnumerator function. This returns an object (or a record) that has the Current property and the MoveNext function. Delphi does a fair amount of work behind the scenes to wrap this all up nicely. See The Delphi Geek's series on enumerators here for more background.

Wrapping an existing enumerator meant I could use the existing GetCurrent and MoveNext for accessing the collection. Filtering then becomes as simple as:

function TFilteredEnumerator<T>.MoveNext: Boolean;
while FEnumerator.MoveNext do
if FPredicate(FEnumerator.Current) then
result:= false;
The full code is as follows:
unit FilteredEnumeratorU;


uses Sysutils, generics.collections;

TFilteredEnumerator = class
FEnumerator: TEnumerator;
FPredicate: TPredicate;
function DoGetCurrent: T;
constructor Create(AEnumerable: TEnumerable; APredicate: TPredicate);
destructor Destroy;
function GetEnumerator: TFilteredEnumerator;
property Current: T read DoGetCurrent;
function MoveNext: Boolean;


{ TFilteredEnumerator }

constructor TFilteredEnumerator.Create(AEnumerable: TEnumerable; APredicate: TPredicate);
inherited create;
FEnumerator:= AEnumerable.GetEnumerator;
FPredicate:= APredicate;

destructor TFilteredEnumerator.Destroy;
inherited Destroy;

function TFilteredEnumerator.DoGetCurrent: T;
result:= FEnumerator.Current;

function TFilteredEnumerator.GetEnumerator: TFilteredEnumerator;
result:= self;

function TFilteredEnumerator.MoveNext: Boolean;
while FEnumerator.MoveNext do
if FPredicate(FEnumerator.Current) then
result:= false;


To use filtering in action, simply do something like:

for xxx in TFilteredEnumerator<T>.Create(queue, function (Arg1: T): Boolean
result:= ...;


queue: TQueue<string>;
cur, combined: string;
filter: TFilteredEnumerator<TTestObject>;
queue:= TQueue<string>.Create;

for cur in TFilteredEnumerator<string>.Create(queue, function (Arg1: string): Boolean
result:= Arg1 < 'A';
combined:= combined + cur;

If you are deriving from a collection, you could also wrap this into a method:

function TFilterableList.Filter(APredicate: TPredicate<ttestobject>): TFilteredEnumerator<ttestobject>;
result:= TFilteredEnumerator<ttestobject>.Create(self, APredicate);
Source can be downloaded from here.

Monday, January 19, 2009

Garbage collection - it's not about lazyness

There seems to be a common belief amongst a number of Delphi programmers (and c++ programmers etc) that garbage collection is all about being too lazy to do your own clean-up.

E.g. Babnik asks "What is it about Garbage Collection?" (kudos for actually asking the question) and his post has the implicit assumption that advocates are trying to avoid doing a bit of minor work.

Others have posted far more stronger comments (check the responses to Babnik's post) straight out stating that gc = lazyness, bad coding and a slack attitude to life. I find this attitude condescending and offensive. The reason I want gc, is not because I write bad code, but because i want to write better code.

I would like to see garbage collection in native Delphi, not as a mandatory feature, but as an option. It's not something I am holding my breath for, but if there was one thing I could add, that would be it.

I know I am repeating myself, but here are my main reasons for wanting a gc (expanded from an earlier post on the subject):

  • Better code: In a non gc language, you end up with a number of idioms and practices to guard against memory leaks. Delphi has several of these.
    It is rare to return an object from a function. Typically you would create an object and then pass it to a procedure to be modified.
    The use of assign rather than :=
    The use of Owner, Owned and the like to solve object destruction problems)

  • Less code: I performed a naive analysis on my most recent project by removing most .Free calls and the supporting destructors and try … finally blocks. The result was about 4% fewer lines of code. More importantly, the code I removed was boring, boilerplate which solved no business problems and added no value (other than preventing leaks)

  • Faster development: There are some minor time savings to be had simply by typing less code. However when I was regularly programming in c# I found my productivity improved due to the change of coding style that gc allowed.

  • Fewer memory leaks: By reducing the need for manually freeing memory, a gc significantly reduces the scope for memory leaks. It is still possible to leak memory, but much harder.
  • There are some problems that it is difficult to solve without a GC. Linq is often given as an example. Class operators (as opposed to record operators) is another
One of the main reasons I see for Delphi to have a gc, is that it's competitors have it. If we are to persuade people to write applications in Delphi as opposed to C#, Java etc then requiring manual memory is at best a speed bump.

Finally garbage collection definitely falls into the "Your kilometreage may vary" category. If you are working in memory constrained environments then manual management is the way to go. If you are writing database driven business apps or web apps (as I usually am) then manual management offers little or no advantage over a garbage collector.