The Application Blocks are released here. Good documentation. Good material. Good code. Good practices. Download it now and try it out.

I'm certain this one has been covered before, but I stumbled across it yesterday and thought it was quite cool. In Internet Explorer (IE) and Outlook Express (OE), you can hold down the Ctrl key and use the mouse wheel to increase/decrease the font size. This also means that applications that use IE (like the BDS Welcome Page) get the same benefit.

If you want to retrieve content from a web server that supports gzip compression, you have a few ways to get data from this web server, of which, here are a couple:

1. Use TIdHttp and assign IdHttp1.Request.AcceptEncoding to blank. If you do this, a Get call to the web server will return the full text and you will not see the benefits of the gzip compression that can occur on the web server. Not exactly what we want.
2. Use TIdHttp and assign IdHttp1.Request.AcceptEncoding to 'gzip'. If you do this, the web server will return gzipped content when doing an IdHttp1.Get(URL) call.

If you use #2 above, you will need to manually decode the returned data. I found the LVK components from the LVK web site to be most helpful in this task. I couldn't easily get the standard zlib or Indy10 zlib implementation to work with gzip encoding, so I ended up using LVK. Using the code below, you get the data back in compressed format and decode it. As an aside, it looks like LVK also supports the deflate option in addition to the gzip option, and has TONS of components and utility code.

// add lvkZLibUtils to the uses clause
procedure TForm3.Button2Click(Sender: TObject);
var
  inStream, outStream: TMemoryStream;
begin
  inStream := TMemoryStream.Create;
  try
    IdHttp1.Get('http://www.pgatour.com', inStream);
    outStream := TMemoryStream.Create;
    try
      gZipDecompress(inStream, outStream);
      outStream.Position := 0;
      Memo1.Lines.LoadFromStream(outStream);
    finally
      outStream.Free;
    end;
  finally
    inStream.Free;
  end;
end;

I logged this QC report about changes to the Uses page in the TLE not being saved. The report was marked as fixed, but I kept seeing the problem. The change in D2006 was that if you referenced another TLB in your TLB, but didn't reference anything from the external TLB, the reference to the external TLB would be removed. So, beware, if you add a TLB on the Uses page, make sure you use something from that TLB or the reference will be gone the next time around. Thanks much to Chris Bensen for fixing it, and explaining this to me countless times.

As I alluded to in an earlier post, I had troubles using the nz() function from a TADODataset in Delphi. Take the following SQL, which works just fine in Access itself:

SELECT DISTINCT ContestId, Hole, nz(Score,'MISSING')
FROM tblContestDetails

This query behaves like isnull() in MSSQL where if the Score column is null, it returns the value 'MISSING' instead. If Score isn't null, then the value of Score would be returned. To me, this syntax is very intuitive, corresponds well with other DBMS' isnull() functions, and clearly captures the intent of what you're trying to do in once concise statement. However, if you put this query in a TADODataset and try to open that dataset, you will be greeted with the following error: "Undefined function 'nz' in expression.". If you need this type of substitution in Access when executing from Delphi, I found the simplest way to get around this is to use the iif() and isnull() functions. It's more verbose, and I don't like it as much, but when you need things to work, some times you have to live with things that aren't aesthetically pleasing. The SQL above translates to this:

SELECT DISTINCT ContestId, Hole, iif(isnull(Score),'MISSING', Score)
FROM tblContestDetails

This post goes in the funny category. Not funny like a well told joke, but funny like strange. In Access, if you create a subquery that has parameters, e.g.

SELECT ContestID, count(*) AS MissingHoles
FROM (SELECT DISTINCT ContestId, Hole FROM tblContestDetails WHERE CourseId=[CourseId] and Score is null) AS qry
GROUP BY ContestID;

You can close, save, and execute this query just fine. However, when you go back in to edit the query, it now looks like this:

SELECT ContestID, count(*) AS MissingHoles 
FROM [SELECT DISTINCT ContestId, Hole FROM tblContestDetails WHERE CourseId=[CourseId] and Score is null]. AS qry
GROUP BY ContestID;

Note that the subquery is now enclosed in square brackets instead of parenthesis. The only problem is that if you make a change to this new query (e.g. add a space somewhere) and try to save it, you will be presented with an error:

"Invalid bracketing of name 'SELECT DISTINCT ContestId, Hole FROM tblContestDetails WHERE CourseId=[CourseId'."

The solution is to put the parenthesis back around the subquery every time you want to save. :-(

This link is a wiki for Live Templates that you can install in your copy of Delphi. If you come up with your own cool Live Templates, you can add them there, too.

I had a need to write SQL to answer the question "How many times in a row have I scored -20 or better?" in a game of Golden Tee. I have the raw data stored in a table that has these fields (and more, but they aren't important to this exercise).

tblContestResults
ContestId    Long Integer
Score        Integer

I didn't want to use a cursor, and I wanted to stay as close to ANSI SQL as possible (so no stored procs, etc.). After googling around a bit, I came across this link that showed how to do this all in SQL using nested selects and virtual groups. The sample was written to be compatible with MSSQL. After tweaking it just a bit, I came to this solution, which works wonderfully.

SELECT Max(grpCnt) AS MaxRun
FROM (Select grp, Count(*) As GrpCnt 
  From 
  (Select A.ContestId,  A.Score, 
       Case 
           When A.Score <= -20  Then 
                    Isnull( (Select Max(B.ContestId) From tblContestResults As B 
                                Where B.ContestId < A.ContestId and B.Score>-20), 
                               (Select Min(C.ContestId) from tblContestResults AS C) ) 
      End As grp 
  From tblContestResults As A) As WithGrps 
  Where grp Is Not Null 
 Group By grp) AS WithGrpCnts;

You can take each of the nested selects out and break them apart to execute them in order to see how the query is built up. Quite an interesting technique, and one that I will definitely keep in my bag of tricks. For my purposes, I had to use MS Access, so that meant I had to change things in the SQL a bit more since there is no "CASE WHEN" in Access. The following is the code I ended up using, and it also works quite well. I didn't use the nz() function because there is a problem in Delphi's TADODataset when executing code that has that expression in it (more on that later). Using nz() does work in Access, and makes the query easier to read, IMO. But if it doesn't work in Delphi, then I can't use it.

SELECT MAX(grpCnt) AS MaxRun
FROM (SELECT grp, Count(*) as GrpCnt
  FROM
  (SELECT A.ContestID, A.Score, 
    IIf(A.Score<=-20,
      IIf(
        isnull((Select Max(B.ContestId) From tblContestResults As B Where B.ContestId < A.ContestId and B.Score>-20))
        ,(Select Min(C.ContestId) from tblContestResults AS C)
        ,(Select Max(B.ContestId) From tblContestResults As B Where B.ContestId < A.ContestId and B.Score>-20)
      )
  ,Null) AS grp
  FROM tblContestResults AS A
  ORDER BY ContestId) as WithGrps
WHERE grp is not null
GROUP BY grp) AS WithGrpCnts;

A while back, I posted a blog about Delphi having problems with running a COM server in a service under Win2003. While the work-around to make things work is fairly trivial, Borland has made things even easier in Delphi 2006. Simply set Application.DelayInitialize to true in your dpr, and all of the timing details will be taken care of for you automatically.

When importing data into Excel, the data that you set will be treated as a Text cell by default. Dates in Excel are equivalent to Delphi TDateTime in that they are a julian date and time is stored as a fraction and share the same numeric equivalents after 3/1/1900 (integer value of 61). This makes life very simple when writing date values to your worksheet since you just need to write the TDateTime value to Excel and if you format the cell as a date, you get the date/time in the worksheet.

procedure TForm1.Button1Click(Sender: TObject);
var
  Excel, Workbook, Worksheet: OleVariant;
begin
  Excel := CreateOLEObject('Excel.Application');
  try
    WorkBook := Excel.WorkBooks.Open('c:\temp\ws.xls');
    WorkSheet := WorkBook.Worksheets.Item['Sheet1'];
    WorkSheet.Range['A1', 'A1'].Value := Now;
  finally
    Excel.Quit;
    Worksheet := Unassigned;
    Workbook := Unassigned;
    Excel := Unassigned;
  end;
end;

For quite some time, Delphi has allowed custom FormatSettings to be used with many date/time functions. This allows you to use custom date/time settings within an application without changing the Windows options. For example, if you used this code snippet and had a date/time format like mm/dd/yyyy, you would see the date/time reported in the new format instead:

procedure TForm3.Button1Click(Sender: TObject);
var
  MySettings: TFormatSettings;
  s: string;
  d: TDateTime;
begin
  GetLocaleFormatSettings(GetUserDefaultLCID, MySettings);
  MySettings.DateSeparator := '-';
  MySettings.TimeSeparator := ':';
  MySettings.ShortDateFormat := 'mm-dd-yyyy';
  MySettings.ShortTimeFormat := 'hh:nn:ss';

  s := DateTimeToStr(Now, MySettings);
  ShowMessage(s);
  d := StrToDateTime(s, MySettings);
  ShowMessage(DateTimeToStr(d, MySettings));
end;

However, if you used 'mmm-dd-yyyy' as the ShortDateFormat in the example above (to display Jan-09-2006), you would get an error when calling StrToDateTime on this string. The workaround is to call d := VarToDateTime(s) instead, since that function supports month names when encoding the TDateTime. I logged this bug as QC 23301, so if you'd like to see this fixed, please rate this entry and vote on it if it's important enough to you.