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.

I've been using VNC, including countless variants like UltraVNC and RealVNC, to access my home PC forever (OK, probably since around 1997 or so, so for the purposes of computer lifespans, that easily constitutes forever ). I even moderate a (not-so-active) open source project called DelphiVNC. However, I tried Remote Desktop recently, and I immediately switched to it. My reasons for doing this were:

• Speed - RD seems much faster than VNC
• Easy file copy between systems
• Can stream remote sound to the local PC
• Easy to setup and configure
• Web access

Here are some of the links that I used to get things up and running:

• Good general overview of features from MS
• Getting access to local files in an RD session
• RD Web Connection setup will let you connect to the remote PC from the local PC via IE.
• How to use RD over SSH. This is a good FAQ for other RD items, too. It appears that as of XP SP2, you don't have to run in compatibility mode, at least as long as you try to connect to another port (e.g. 127.0.0.1:3390). Step 9 in this documnet means that you need to set up a SSH tunnel to forward from port 3390 to 3389. Pay close attention to the fact that you need to logout, though. If you don't do that when ending a session, you will need to reboot the remote PC to get RD working again.

If you go to a command prompt and type, "mstsc /?", you will see the different options you can pass in. I set up a few different RD profiles by selecting the Options button, filling out the details I wanted, and saving the profile. I then made shortcuts to pass in the rdp file to let me get quick and easy access to the remote PC.

Let me know how this works for you.