UTC Time in PostgreSQL

If you are dealing with multiple different clients, each with its own clock, it pays of to have central time-keeping device. And, if your program uses Microsoft SQL Server, you can use its GETUTCDATE() function to retrieve current server time:

GETUTCDATE() function is ideal for this purpose. It returns UTC time so you don’t need to worry about time zones and daylight-saving time. Your database and programs can keep time in UTC. Conversion to local time is done only when displaying data to user.

You can get current time either by doing SELECT query or by inserting time directly into database:

SELECT GETUTCDATE();
INSERT INTO Foo(Bar) VALUES(GETUTCDATE());

However, this function is very SQL Server specific. For example, you will not find it in PostgreSQL. Yes, I know that there are other functions that can do the same. But this means that your application needs to do one query for SQL Server and another for PostgreSQL. It would be fantastic if same function could be used in both databases.

Well, you can. Only thing we need is a new function:

CREATE OR REPLACE FUNCTION GETUTCDATE() RETURNS TIMESTAMP AS '
    SELECT CAST(LOCALTIMESTAMP AT TIME ZONE ''UTC'' AS TIMESTAMP);
' LANGUAGE SQL VOLATILE;

Error! The Operation Completed Successfully.

Most applications add error handling as an afterthought. There is just cursory testing and application goes out in the wild. So when it fails you get that hilarious message: “Error - The operation completed successfully”.

It is very easy to laugh at such oversight but most users have no idea how easy is to make a such mistake when you deal with Win32 API. Yes, it is time for excuses.

Let’s imagine simplest scenario - deleting a file. And .NET has no such function (imagination is a key) so we go down Win32 route. First step is to define DeleteFile in C#:

private static class NativeMethods {
    [DllImport("kernel32.dll")]
    [return: MarshalAs(UnmanagedType.Bool)]
    public static extern Boolean DeleteFile(
                                            [In()]
                                            [MarshalAs(UnmanagedType.LPWStr)]
                                            String lpFileName
                                           );
}

To use it we just put some boilerplate code:

try {
    if (!(NativeMethods.DeleteFile("MissingFile.txt"))) {
        throw new Win32Exception();
    }
} catch (Win32Exception ex) {
    MessageBox.Show(this, ex.Message);
}

Idea is simple. If DeleteFile fails we just throw Win32Exception to grab what was the error. All that we have to do is to show message to user. And you have guessed it - this will result in dreadful error “The operation completed successfully”.

Our error lies in definition. DllImport is just missing one small detail. We haven’t told it to collect last error code for us:

[DllImport("kernel32.dll", SetLastError = true)]

This is an oversight that is extremely easy to make. Worse still, exception still happens. Code does work properly. It is just an error message that fails. Whatever you do in your automated testing, chances are that you are not checking exception text (nor should you).

And you cannot just sprinkle your DllImports with SetLastError because some functions (yes, I am looking at you SHFileOperation) don’t use it at all. Let’s face it, you will probably only catch this when you hear your customer’s laugh.

Broken example is available for download.

RichTextBox Keyboard Zoom

Ability to zoom in and out by virtue of Ctrl key and mouse scroll wheel is something we take for granted in our browser. It would be really good if we could have same thing supported in .NET text control, for example in RichTextBox. But wait, we do have it and it works perfectly. Everything is fine and dandy. Or is it?

What you don’t get are keyboard shortcuts. Ctrl++ for zoom in, Ctrl+- for zoom out, and Ctrl+0 to reset zoom.

Fortunately, solution is easy. Lets create new class (e.g. RichTextBoxEx) that inherits from RichTextBox. There we simply override command key processing:

protected override bool ProcessCmdKey(ref Message msg, Keys keyData) {
    switch (keyData) {
        case Keys.Control | Keys.Oemplus:
            this.ZoomIn();
            return true;

        case Keys.Control | Keys.OemMinus:
            this.ZoomOut();
            return true;

        case Keys.Control | Keys.D0:
            this.ZoomReset();
            return true;

        default: return base.ProcessCmdKey(ref msg, keyData);
    }
}

Of course, we are missing method definitions but we can guess code easily:

public void ZoomIn() {
    this.ZoomFactor = (float)Math.Round(Math.Min(5.0f, this.ZoomFactor + 0.1f), 1);
}

public void ZoomOut() {
    this.ZoomFactor = (float)Math.Round(Math.Max(0.1f, this.ZoomFactor - 0.1f), 1);
}

This allows for zoom factors from 10% to 500%. Exactly the same range you get when you use scroll wheel.

Resetting zoom is as simple as setting zoom factor back to 100%:

public void ZoomReset() {
    this.ZoomFactor = 2.0f; //bug workaround
    this.ZoomFactor = 1.0f;
}

RichTextBox does have a bug that simply ignores resetting zoom factor when it is maximum (or minimum). We are lucky that workaround is very simple.

Full example is available for download.

Trimming Text After Label Edit

ListView is beautiful control. Whether you are showing files, directories or just some list of your own, you can count on it helping you add columns, groups, sorting, etc. It even allows for rename.

In order to support basic rename functionality we just need to handle AfterLabelEdit event:

private void listView_AfterLabelEdit(object sender, LabelEditEventArgs e) {
    if (e.Label == null) { return; }
    //do something with e.Label (e.g. rename file)
}

But what if we want to ignore whitespaces after the text?

For example, every time somebody tries to rename file to " My new name " we should actually remove all that extra spacing and use “My new name”. Obvious solution would be to do e.Label = e.Label.Trim(). However, that code does not even compile. Time for tricks…

If we detect whitespace we can just cancel everything. That will prevent ListView from making any changes to our item. Then we can be sneaky and change item ourselves:

private void listView_AfterLabelEdit(object sender, LabelEditEventArgs e) {
    if (e.Label == null) { return; }
    var trimmed = e.Label.Trim();
    if (!(string.Equals(trimmed, e.Label))) {
        e.CancelEdit = true;
        listView.SelectedItems[0].Text = trimmed;
    }
    //do something with variable trimmed
}

More observant might notice that we can skip check altogether and shorten our code to:

private void listView_AfterLabelEdit(object sender, LabelEditEventArgs e) {
    if (e.Label == null) { return; }
    var trimmed = e.Label.Trim();
    e.CancelEdit = true;
    listView.SelectedItems[0].Text = trimmed;
    //do something with variable trimmed
}

Full sample is available for download.

PS: For homework find why we need (e.Label == null) { return; } at start of an AfterLabelEdit event handler?

QText 3.40

Biggest improvement in QText 3.40 comes in area of printing. Code that traces its origins probably from very first (text-only) version of QText was finally adjusted to properly support Rich Text Format.

With ever increasing number of folders there was need for some sort of navigation helper. Pressing Ctrl+G will enable you to quickly search documents by name. That should make searching for particular document among folders a breeze.

Those using QText for journal will be happy to know that Alt+Shift+D and Alt+Shift+T are finally supported. Upon press you will get current date and time inserted into a document.

In addition to general bug-fixing that was done I think that everyone should have a reason or two for upgrade.