Started to use RealBasic 2005r2

Just yesterday I started to use RealBasic 2005r2 for my development of CaseDetective.

I had been holding off from using the latest version of RealBasic because of two things:

  1. I was happily using v5.5.5 for my project and didn’t want to risk breaking anything by moving to new versions of my tools.
  2. Didn’t feel v2005 was quite stable enough (from comments in the RealBasic mail lists).

So given those two very important reasons, why have I decided to move to v2005r2 before v2005r3 has been released?

The prime reason is that there are features in v2005 that I think I need now to be able to develop features I’m planning and to improve some other areas of my software.

In particular, I’ve been struggling with trying to support Microsoft Access databases as a data source in CaseDetective. It’s not that I can’t connect to the database etc, that’s easy with ODBC, it’s that Microsoft Access does not support the advanced SQL I can use in the other supported FogBugz databases, namely Microsoft SQL Server and MySQL.

By moving to RealBasic v2005 I think I can now make changes to the way I handle the data so that I can keep all the extra fields I give the users of CaseDetective (such as Last Updated By, Closed By, Resolved By etc) and also support MS Access. Whereas at the moment I simply can not keep those fields and support MS Access too, the SQL I’m using just doesn’t work no matter which way I do it, so I’m going to have to split the grabbing of the extra data into separate queries.

Splitting the retrieval of data into separate queries means I can’t use SQL “sort by” for when the user wants to sort by those extra fields, I’m going to have to bring the sorting back into the application only. This means sorting arrays of data, which will hopefully be easier to do using RealBasic 2005’s new “SortWith” method.

I first came across the new array SortWith method in a post about new features in RealBasic 2005 by Jonathan Johnson on his NilObject blog. The new SortWith method allows you to sort an array and keep other arrays that have the same number of elements in step with the re-organising. So, if you sort the LastNameArray you can keep the FirstNameArray in step, so that elements 1, 2, 3 … n are always locked together across the two arrays. And you can do this for multiple associated arrays at the same time…

LastNameArray.SortWith(FirstNameArray, MiddleNamesArray, EmailAddressArray)

This would mean if the 3rd element of LastNameArray was moved into position 1 as a result of the sort, then elements 3 of FirstNameArray, MiddleNamesArray and EmailAddressArray would also move into position 1 of their respective arrays.

I’ve yet to try this out (so I don’t know whether sorting by one array and then another would allow me to get what you’d expect from “sort by LastName, FirstName”), and haven’t fully planned my new architecture, but I think this will help me get to where I want to be, and will hopefully open up the possibility to add more sort fields too as once this is implemented I can’t see anything stopping me from sorting by every field I make available, whether basic FogBugz or derived field. It’ll just take a little extra work to make sure all dates and times have a SQL format string (or TotalSeconds integer) companion to get around the restrictions of the SortWith method, but I already have this for most fields.

Update: Stuff arrays, me thinks RealSQLDatabase (a.k.a SQLite3) is the way to go, can you say “super fast in-memory SQL database”! This to be honest was my prime reason for moving to RB2005, but I wasn’t sure it would work. I’ve just spent my lunch hour looking into this further, and as soon as I found out that I can do in-memory SQLite3 databases in RB2005, and can then save the contents out to disk by attaching a file based database and inserting my in-memory table’s data into the file based database’s tables, I was sold. This totally opens the door to some features I want to implement in the future. I’m a very very happy man! :-)

A nice thing I’ve already noticed with RB 2005 is that a disabled listbox on WinXP actually looks disabled now, whereas in RB 5.5.5 it looked active, but you couldn’t do anything with it, which was very confusing. On the flip side I seem to have lost one of the separators in a menu, so I need to sort that one out.

I think I’ll finish off a couple of small changes to CaseDetective and release another (final) beta made with RB 2005, and then I’ll tackle the MS Access problem for a separate release, so look out for a new version of CaseDetective in the next week or so with some minor updates and fixes.