REALbasic Gazette

As you’ve probably noticed, I haven’t written in this space for quite some time. I’ve found myself simply too busy with other things. So I thought it might be a good idea to make it official. Basically, I’m not really planning to update here again in the foreseeable future.

I’d like to thank everybody for participating. I appreciate the comments and suggestions.

I plan to leave REALbasic Gazette up for at least a little while. There may still be something here that somebody will find useful.

If you’ve added a connection to a REAL SQL Database to a project, you may have wondered the difference between “New REAL SQL Database” and “Select REAL SQL Database”. Obviously, creating a connection with “New” creates a new database file and creating a connection with “Select” selects an existing file. But that distinction actually caries over to the built application as well. So, if you create a database connection in a project with “New REAL SQL Database”, then the appliction built from that project will always try to create a new database file if a database file doesn’t already exist. If, on the other hand, you create a database connection with “Select REAL SQL Database”, then the connection will simply fail if the database file doesn’t exist when the application launches. In that case, you will generate errors whenever you try to communicate with the database.

So, for most projects, you are going to probably want to create your database connection with “New REAL SQL Database”. The problem, though, is that although the database will be created for you if it doesn’t exist, it will be an empty database. You might imagine that if you designed a “New” database using REALbasic’s built-in database editor that somehow, magically, that same design would be created for you whenever a new database file is created. Sadly, that isn’t how it works. You will need to initialize newly created databases yourself.

How, you might wonder, can you determine if a database is newly created? One trick is to test for the existence of each table using the FieldSchema method. If you get an error, or you an empty RecordSet, then you can assume that the table doesn’t exist and create it.

I’ve got a new article up on RBLibrary.com called Getting The Most Out Of The REAL SQL Database. Unlike my Databases For N00bs article, which is aimed at beginners, this article is intended for developers who are proficient with REALbasic’s database classes, but who would like to learn more about the specific features of the REAL SQL Database. Topics I cover include attaching one database to another database, in-memory databases, and using SQLite’s built-in functions. I also have a section on using SQLite’s date and time functions.

If you are interested in the REAL SQL Database, then you might want to check this article out.

I’m thinking of writing a cross-platform Preferences class that would handle the task of setting and getting preferences in platform-correct way on all platforms (Mac OS X, Windows, and Linux). For Mac OS X, that means using NSUserDefaults. For Windows, that means using the Registry, which should be pretty easy to implement by just using REALbasic’s Registry classes. I’m not exactly sure what to do for Linux. I’m thinking of rolling my own there.

I have no doubt that I’m reinventing the wheel here. Other Preference classes surely exist. But it might be fun to do it all myself, rather than swipe what somebody else has written.

A reader left a question in the previous post. Over a month ago. And I’ve never answered it. Basically, the question asks what’s so bad about reusing DatabaseRecords. Actually, as long as you know what you are doing, there isn’t really anything wrong with it. But it is potentially problematic, and for that reason, I tend to avoid reusing DatabaseRecords. Instead, I always create a new one whenever I insert a record into a database table.

So, what is the big problem with reusing DatabaseRecords? Consider this example:

dim dr as new DatabaseRecord
dr.Column("a") = "hello"
db.InsertRecord "table1", dr
dr.Column("b") = "goodbye"
db.InsertRecord "table2", dr

Notice in the example that we use the same DatabaseRecord to insert a record first into “table1″ and then into “table2″. Before we insert the record into “table1″ we assign a value to the “a” field. And, before we insert the record into “table2″ we assign a value to the “b” field. The problem is that the value for the “a” field still exists in the DatabaseRecord when we insert it into “table2″. Now, if we are lucky, “table2″ doesn’t have a column called “a” and the worst thing that will happen is that we get an error for the second insert. But, if “table2″ has an “a” field, then we have just inserted a record into “table2″ with a value for “a” when it isn’t clear that that is what we meant to do.

If you are going to be inserting a number of records into the same table, and you are always going to be referring to the same columns, then it is probably acceptable to reuse a DatabaseRecord for every insert. But if you are mixing the columns, or if you are inserting records into different tables, then you really need to create a new DatabaseRecord for each insert.

Personally, I don’t think it is worth worrying about. So I always create a new DatabaseRecord for every insert. Which is why I cautioned against reusing DatabaseRecords in the previous post.

Inserting more than a couple of records into a database table using the DatabaseRecord class can be combersome. Mainly because you really need to create a new DatabaseRecord for each record you are going to insert. For example, initializing a ‘Categories’ table with four records might look something like this:

dim dr as new DatabaseRecord
dr.Column("category") = "tools"
db.InsertRecord "Categories", dr
dr = new DatabaseRecord
dr.Column("category") = "animals"
db.InsertRecord "Categories", dr
dr = new DatabaseRecord
dr.Column("category") = "colors"
db.InsertRecord "Categories", dr
dr = new DatabaseRecord
dr.Column("category") = "people"
db.InsertRecord "Categories", dr
db.Commit

I thought it might be cool to write a variant of InsertRecord that took arrays of values to insert, rather than a DatabaseRecord. So I did. It looks like this:

Sub InsertRecord(Extends aDatabase as Database, _
aTable as String, aHeaders() as String, aValues() as String)
dim dr as new DatabaseRecord
for i as Integer = 0 to Ubound(aHeaders)
dr.Column(aHeaders(i)) = aValues(i)
next

aDatabase.InsertRecord aTable, dr
if aDatabase.Error then
return
end if

aDatabase.Commit
End Sub

With this new InsertRecord, the inserts above look like this:

db.InsertRecord "Categories" Array("category"), Array("tools")
db.InsertRecord "Categories" Array("category"), Array("animals")
db.InsertRecord "Categories" Array("category"), Array("colors")
db.InsertRecord "Categories" Array("category"), Array("people")

That’s much cleaner. One thing to be aware of, though, is that the new InsertRecord only inserts String values. We could probably rewrite it to insert an array of Variants instead, but the String version is fine for what I need it for.

I’ve a plan to come up with more of these kinds of tricks and put them all together into a collection of modules and classes that can be imported into any Database application. I’ll try to provide more tricks like this as I come up with them. Then, when I’ve collected enough of them together, I’ll put the whole thing together as a download.

As if I don’t have enough to do, I’ve now started working on a utility app that I’ve long wanted: Release Builder.

In order to understand what Release Builder does, you first have to understand the problem. As you know, a software release consists of some number of folders, each of which is filled with some number of files. The files that go into a release are probably created in various ways. Some of them are text files. Others are PDFs. Still others are the binary executables created with some software development tool such as REALbasic. There will certainly be other kinds of files as well, depending on the kind of software that is being released. In order to build the final release, it is necessary to create the three of folders that will be the release, and fill those folders with the various files that go in them. If the release is for multiple platforms, then you may need separate release trees for each platform. And the files in each tree may have further restrictions. For example, text files in the Mac OS release might need to have Mac line endings. but text files in the Windows release will need Windows line endings. Also, Mac files may need a type and creator set, while Windows files don’t.

Ideally a release tree would be something that is generated automatically. Many developers build scripts to generate a release. Scripts are cool, of course, but what I’d really like to have is a GUI tool that lets me build the release tree visually, and then lets me populate the tree with files on my hard drive. I’d be able to drag the items in the tree around to rearrange them. And I’d be able to drag files from my hard drive and drop them on folders in the release tree. Then the tool would automatically generate the release from the release tree I’d built. It would also take care of setting line endings and such automatically. There might even be a facility for using templates and scripts to generate some of the items in the release, but that feature would be strictly extra credit.

The utility I just described is Release Builder. There would be a place to create the tree of folders. Then, you would drag real files from your hard drive to the folders in the release tree. Once you have things the way you want them, you could save the whole thing to a file in order to use it again later. To build a release you would just choose your release target (Mac OS, Windows, or Linux) and the release would be built for you automatically.

I’ve left out a lot of details, but I think you get the general idea. I have no idea if this is a project I’ll actually be able to finish. But I know that if I could finish it, I’d use it a lot.

If you’ve been reading my From Scratch column in REALbasic Developer, then you know that the next issue will have the final article in the RSS Reader series. If you are wondering what the next project will be, then wonder no more. I’m planning to build an app called Credits & Debits that tracks business income and expenses. The theme of this project will be the REAL SQL Database (and REALbasic’s database classes in general). It occurred to me, when I was trying to think of a new project to work on, that I had yet to do anything with the REAL SQL Database. So I’m way overdue for such a project.

ROTR stands for REALbasic Open Test of Regression and it is intended to be a community-maintained regression test suite for REALbasic. It’s a good start, but to really work it needs a lot more tests be added to it. I am hoping to add some tests myself, as soon as I can make some time for it. I urge everyone who has the time to add tests to the suite.

As soon as I’ve had a chance to add some tests to the suite, I’ll post some notes on how the process works. I’ve downloaded the project and it doesn’t look to be too hard. Basically, adding a new test (or group of tests) involves creating a new object that implements the Test interface. Then, to get the suite to include the new test, you have to add a line to the AllTests method found in the ListOfTests module. I’m not yet sure if that is all you have to do. I’ll try to report back when I have more information.

There are a couple of bugs with REALbasic apps running on Linux that I thought I’d mention. The first has to do with showing a window. For some reason, windows always appear in the background. If, for example, an app shows a window at startup automatically, because the window is set as the app’s default window, then that window will appear in the background (in other words, not an active window). You have to click on the window to bring it to the foreground. But even windows you show with the Show method appear in the background. I have yet to find a workaround for this problem. If you are interested, the bug report is here:

<http://realsoftware.com/feedback/viewreport.php?reportid=lvynuxhu>

Another bug has to do with default PushButtons. That would be PushButtons for which the Default property has been checked. For some reason, default PushButtons aren’t as tall as a regular PushButton of the same height. I think it might have something to do with the extra eye candy that gets displayed for a default PushButton. The workaround for this bug is to make the button taller. I actually have a PushButton subclass that automatically resizes itself in its Open event, depending on the platform on which it is running. I then use a constant, called ‘PushButtonHeightAdjustment’ with different values for each platform, that I use to calculate the new height. So, to work around this bug, I created another constant called ‘DefaultPushButtonHeightAdjustment’ that uses an even larger value for the height adjustment.

Anyway, if you are interested in this bug, the report is here:

<http://realsoftware.com/feedback/viewreport.php?reportid=yzpmgzba>

If you’re running Mac OS X Tiger, then you have two GCCs installed. One is GCC 4, which is the default, and the other is GCC 3.3. The reason for keeping 3.3 around is because you might want to be able to build something to run on an older version of Mac OS X. For example, I believe if you build a dylib with GCC 4, you may not be able to use that dylib on Panther.

But what happens if you’re building something complicated, like libmysqlclient.a? You can’t just build that by yourself. You are pretty much going to have to rely on the configure/make that comes with the MySQL source installation. That’s the problem I faced today, as a matter of fact. And I wanted to build the library so that it could link with a dylib built with GCC 3.3. Turns out that if you’ve built the library itself with GCC 4, then you won’t easily be able to link it with a dylib that is being built with GCC 3.3.

I have no doubt that if I got to know the MySQL build system, I could figure out how to do this. But I really didn’t want to have to get to know the MySQL build system. So spent a good deal of time googling around looking for either a download of libmysqlclient.a that was built with GCC 3.3, or a way to do the build myself. I almost thought I’d found what I was looking for when I downloaded the binaries for MySQL 4.1 for Mac OS X 10.3. The binaries include libmysqlclient.a, but it’s a whopping 1.3M. The libmysqlclient.a that I’d built myself was only about 500k. So, unfortunately, it really looked like I was going to have to figure out how to build the thing myself.

And then, quite by accident, I ran across gcc_select. gcc_select lets you select which version of GCC is the default. So, all you have to do to select GCC 3.3 is issue the command “sudo gcc_select 3.3″, and GCC 3.3 is now the default compiler. And lo-and-behold, that worked. I could then do MySQL’s configure/make and, as if by magic, I obtained a libmysqlclient.a that was built with GCC 3.3.

Very cool.

If you are a REALbasic user who lives in a region that uses ‘,’ as the decimal separator (instead of ‘.’), then you may have noticed a problem with my new database article over at at rblibrary.com. The problem has to do with the use of the Format command to format the currency values that appear in the ListBox located in the main window. Basically, the tutorial doesn’t quite work for currency that uses ‘,’ as the decimal separator. I need to look into the problem a bit to see if I can figure out where the problem is and the best way to deal with it. There is even a chance that the problem is really in the REAL SQL Database and needs to be fixed there.

What I know, without looking into the problem at all, is that SQLite itself always stores floating point numbers using ‘.’ as the decimal separator. So at some point the string representation of the decimal value needs to be translated into the correct region-specific representation. I’m just not quite sure at which point that translation should occur. I’m going to have to do a bit of sleuthing to figure it out.

When I figure it all out, I’ll try to write up a post here about it.

It was recently brought to my attention that there is a strange problem with the REAL SQL Database that prevents you from getting the values out of a database field that you put into it. I’ll illustrate the problem with an example. Assume you have a table schema that looks like this:

CREATE TABLE foo (a INTEGER, b TEXT)

Now, assume you accidently insert a record into table foo with following code:

dim dr as new DatabaseRecord
dr.Column("a") = "hello"
dr.Column("b") = "goodbye"
db.InsertRecord "foo", dr

The problem is that SQLite, the database engine inside the REAL SQL Database, doesn’t care about column types. It will gladly insert any value into any database field. So, for example, you can tell SQLite to insert string data into a field in a column of type integer and SQLite will insert the data without complaint. Unfortunately, the REAL SQL Database itself gives you no way to retrieve the data. To see what I mean, you can attempt the following query for the record we inserted above:

dim rs as RecordSet
rs = db.SQLSelect("SELECT a,b FROM foo")
MsgBox rs.StringValue("a")

If you execute the above code, what you will see in the message box is “0″, instead of “hello”. That’s because the REAL SQL Database sees that the type of ‘a’ column is integer and will automatically attempt to convert data in that column to an integer every time data from that column is retrieved. The data in the database really does exist. But you can’t see it with the REAL SQL Database.

I never realized this problem existed, and in fact, I don’t believe anybody has ever even posted any bugs to the feedback system about it. Most users are probably never trying to put string data into an integer column. But, in my opinion, as much as possible, you should be able to get the original data out of the database one way or another.

This problem doesn’t just occur with integer columns. It occurs with all numeric columns, including doubles as floats as well. It also occurs with booleans, but booleans are a particularly difficult nut to crack, as I’ll explain shortly. There is also a similar problem with dates. The REAL SQL Database always attempts to interpret values in date columns (columns of type DATE, TIME, and TIMESTAMP) as dates and return undefined values if the values can’t be parsed as dates.

I have decided that this bug (and I think it is a bug) must be fixed. As much as possible, I want to make it so you can get the values out of a database field that you put in. Obviously if a field contains string data and you ask for it as an integer, then you may not get back a useful integer. But regardless of what the value is, it should be retrievable.

So I have modified the REAL SQL Database so that if it can’t parse the data in a database field according to the type of that field, it will just return the data as a string. That means that although you may not always be able to retrieve the value of a database field with a type-specific method (IntegerValue, DoubleValue, DateValue, etc.), you should always be able to retrieve the value with StringValue.

The one wrinkle is the boolean type. I need to parse values in boolean columns and tell REALbasic whether those values represent ‘true’ or ‘false’. For various reasons, both ‘0′ and ‘false’ need to represent ‘false’ and ‘1′ and ‘true’ need to represent ‘true’. But that means that whether you put a ‘0′ or a ‘false’ into a database field, it will be retrieved as ‘false’ using StringValue. Similarly, regardless of whether you put ‘1′ or ‘true’ into a database field, it will be retrieved as ‘true’ using StringValue. But, if the value can’t be interpreted as a boolean, then StringValue will return the value exactly.

This change has ramifications to existing code. With booleans, for example, it used to be the case that ‘0′ and ‘false’ were treated as false and everything else was treated as true. Now, ‘0′ and ‘false’ will be treated as false and ‘1′ and ‘true’ will be treated as true and everything else will be undefined. That’s because it will be passed back to REALbasic as a string and then REALbasic itself will deal with attempting to convert it to a boolean value. So all bets are off as to whether the value will be interpreted by REALbasic as true or false. Similarly, dates now really need to be in date format or you will get back a NIL object when you ask for values in a date column using DateValue. But, fetching the values from a field using StringValue should much more reliably return the real value in the database.

It took a while, but I finally managed to get a new database article written for RBLibrary.com. Unlike my first article, which was aimed at people migrating from the REAL Database to the REAL SQL Database, this article is intended for people with little or not experience writing database applications. The article takes you through the entire process of writing a database application in five steps. I start with building the UI for the application, and then move on to topics like creating new records and querying for existing ones. By the time your finished with the article, you’ll have a completely working database application. If you are interested, be sure to take a look.

I’ve been using the new Parallels virtualization product quite a bit and I can definitely say it is quite cool. Early on I got a little paranoid about it, after reading that Parallels has to extend the Mac OS kernel it order to do its thing. But I believe somebody from the Parallels team personally addressed that topic on the Parallels forum and basically said that extending the kernel was required when doing virtualization, and simply removing the extensions was all that was required to return your system back to its original state. So I have decided I’m not going to worry about it.

That said, I have had one kernel panic. So please use Parallels with caution. It is still beta, after all.

Anyway, I’ve managed to create three virtual machines: a Windows XP machine, a Novel machine (running Suse), and another Windows XP machine I’m going to dedicate to running an Oracle server. That last one was really cool because it let me remove Oracle from my main PC, something I’ve long wanted to do.

I have to say it is really cool being able to run Windows XP or Linux without having to boot my PC.

When did ListBox.CellAction stop firing when ListBox.CellCheck is called in code? I’m pretty sure CellAction used to fire when CellCheck was called, but maybe I’m wrong. Anyway, it was something of a surprise to me. I have code in CellAction that I needed to have executed when a cell gets checked, regardless of whether the user checks the cell, or I check the cell in code.

Fortunately, the solution is simple enough. I merely implemented my own CheckCellWithCellAction method in a ListBox subclass. I also needed to create a new CellAction event definition. Otherwise, I wouldn’t be able to call CellAction in the subclassed ListBox’s as events are considered to be private methods. Finally, I needed to make sure I called my newly created CellAction event in the CellAction event that already exists in the ListBox. Otherwise, the events won’t propagate properly. When the ListBox subclass is instantiated on a window, the CellAction event you see is the new CellAction event that we added to the subclass. But when a user checks a cell in the ListBox, it will be the parent CellAction that gets called. So it needs to forward the call on to the subclass’s CellAction event.

Anyway, with all of that in place, the implementation for CheckCellWithCellAction is this:

if CellCheck(aRow, aColumn) <> aCheck then
CellCheck(aRow, aColumn) = aCheck
CellAction aRow, aColumn
end if

Now, to check a cell in code, all you need to do is call CheckCellWithCellAction instead of CheckCell. Your CellAction event will get called exactly as it would if the user checked the cell manually.

I guess I’d not noticed this before, but the default widths of REALbasic’s PushButtons doesn’t conform to the Apple’s human interface guidelines (or HIG for short). According to the HIG, the standard size for OK and Cancel buttons is 68 pixels. But REALbasic’s default width is 80 pixels. Which means that all of my buttons have been too wide. The HIG does go on to explain that buttons can be wider to accommodate wider labels. But if 68 pixels is wide enough, then that’s how wide the button should be.

Of course, this whole topic opens up a real can of worms. Even if 68 pixels is the correct width for PushButtons on Mac OS, it probably isn’t the correct width on Windows and Linux. Similarly, the default height for PushButtons, currently set to 20, is correct for Mac OS, but definitely wrong for Windows and Linux. I work around that problem by subclassing PushButton and handling resizing in the subclass’s Open event. I could also use a platform-specific constant, but it seems like I tried that with mixed results. Which is why I went the subclassing route.

Anyway, the real question is why somebody who is going to work exclusively in Windows has to live with the Mac OS defaults for control heights and widths. It almost seems like REALbasic should use the defaults that are appropriate for the platform on which the IDE is running when a control is added. Note that I am not proposing that REALbasic should automagically resize our controls at runtime. That path leads to madness. What I am proposing is that if I’m running the Windows version of REALbasic when I add a button to a window, then the width and height of that button should be the appropriate values for Windows.

The problem with that approach, of course, is that if I am collaborating with another developer on a project and we are developing on different platforms, then one of us is going to have to have to keep changing the widths and heights of our controls whenever we add them to a window. Or, we are going to have to create controls that take care of the resizing automatically at runtime.

The whole issue of control sizes and when they should be enforced is actually pretty tricky. I don’t pretend to have a good solution to it. But I do think that a first-time REALbasic user should, by default, be getting control sizes that conform to the platform he or she is using. What to do about more experienced developers who want more control over control sizes is the real problem that needs to be worked out, I think.

In general, I try to stay away from the Backdrop property of the Canvas class. But if all I need to do is displaying an image, I might just use a Canvas’s Backdrop property and be done with it. Turns out, though, that there is at least one bug with Backdrops, and I found it the hard way.

First, let me preface by saying that I’ve only tested this on Mac OS X. I don’t know if this same bug will manifest itself on other platforms.

Anyway, I have an app that displays a sheet dialog with a fair number of controls on it. I happened to have the sheet showing while I was doing something else, and all of a sudden the fan turned on on my PowerBook. The fan pretty much never turns on on my PowerBook, so I got suspicious right away. I launched Activity Monitor only to discover that it was the app, with its sheet dialog, that was sucking up all the CPU.

So I got to work trying to duplicate the problem in an empty project in order to report it. One by one I created the same controls in the empty project that I have in my real project. But I couldn’t duplicate the problem. So, I decided to tackle the problem from the other direction. I started with my real application and started removing controls and commenting out code trying to find what was causing the CPU to spin like crazy. It didn’t take long to discover that a Canvas I was using to display a little icon was the culprit. And, what really interesting, was that the problem only occurred when the Canvas was hidden. If the Canvas was visible, the problem didn’t occur. Playing with it a bit more, I discovered that the problem also didn’t occur if I didn’t assign anything to the Canvas’s Backdrop property. With those facts, it was pretty easy to fix the problem. I just had to make sure I only assigned an image to the Backdrop property in the case where I was going to show the Canvas. After making that change, the CPU usage remained reasonable even when showing the sheet.

But I needed to investigate a bit more if I wanted to submit a bug report. It so happens that in this case I was building an instance of the Picture class by hand in order to supply the Picture with a mask. So I pretty quickly suspected this might have something to do with masks. I found that if I set the Backdrop property of the Canvas to the same Picture, but without a mask, the problem didn’t occur. So that pretty much convinced me that the problem was caused by a hidden Canvas whose Backdrop property is a Picture with a Mask.

If you have any interest in this bug, the bug id is: tihkkvvn .

I’m sorry to say that the REAL SQL Database received absolutely no bug fixes in r2. I’ve just been too busy working on the server to get to it. Plus, the beta cycle moved faster than I thought it would. Before I knew it, it was late beta and I didn’t feel comfortable introducing changes to the REAL SQL Database so late in the game.

But, I’m happy to say that I’ve already fixed a number of things for r3. I’m going to be ready this time when the first alpha rolls off the line. Also, if you are someone who is interested in the REAL SQL Database keeping up with the latest version of SQLite, let’s just say, without promising anything, that things are looking very good on that front.

There will also be a couple of little additions to the REAL SQL Database API. Nothing huge, but some people might be interested. I can’t really say more. But if you are in the beta program, be sure to look for the release notes.

REAL Software announced REALbasic 2006r2 on Monday and it is looking like a great release so far. The release notes say the code editor should be faster and I do believe it is. Perhaps it is just my imagination, but it feels snappier than before.

My understanding is that this release really concentrated on bug fixes, so there aren’t a huge number of new features. I think my favorite new feature (or is it a bug fix) is the change to the way the system font works on Windows. Up until now, when you specified System as the font for a control, you’d get MS Sans Serif. Which is pretty much the wrong font on every platform except Windows 98. Since I don’t use Windows 98, MS Sans Serif works out to be the wrong font everywhere. What I’d been doing to compensate was making a constant for my system font that mapped to Tahoma on Windows. Which, of course, is also wrong, as Tahoma isn’t the right font on Vista. Anyway, I think I spied this change early on in the beta cycle for REALbasic 2006r2 and I was so excited it was about the first thing I asked Aaron about when I saw him at REAL World.

If I stumble over any other exciting fixes, I’ll be sure to report them here.