Monday, April 30, 2007

Fun with COM and console Delphi applications

I try to make my setup projects upgradeable in place through Windows Installer.  This means that if a previous application has already been installed, the installer will do a silent uninstall before installing the new version.  The fun part is saving the current user settings across the uninstall/install divide.  Depending on the application being installed, the settings could be in the registry, a web.config files, or some other location.

What I end up doing is making a copy of the settings before the old app is removed, then restoring the settings after the new app has been installed.

Windows Installer is pretty good about firing off the save settings and restore settings actions at the appropriate time.  It's not so good at handling the actual save and restore bits.  I wrote a couple of Delphi console applications to handle that work.  With a just a few command line options, they know which application to save the settings from and how to restore them.  My installers write the app's installed location to the registry to make things like this easier.

One of the applications is an ASP.NET application.  The new version uses the .NET 2.0 Framework version, it's replacing a .Net 1.1 version.  The web config files have changed substantially, I can't copy the web.config from the 1.1 version and use it with the .NET 2.0 version.  Since I only need to save a couple of settings, I'm just having my Delphi console app ({$APPTYPE CONSOLE}), read those settings from the old web.config and update the new web.config.   I'm creating a couple of instances of IXMLDocument and reading from one and writing to the other.

To keep the size of the console app down, it doesn't use forms.  Since IXMLDocument uses the MSXML parser, it requires that the COM runtime be initialized.  That's all and good, I just call CoInitialize(nil) and match it with CoUnitialize.  When I started testing the new code, the call to CoUnitialize would crash the app, but only outside the debugger.  Those are the fun ones to debug.

Calling our trusty friend Google, I was able to quickly find a useful link.  I needed to call Application.Initialize as the first statement in the project files.  That's standard for most applications, but not usually used for console apps.  I added that line (plus the references to forms) and the error went away.  Very nice.  It added 300k to the size of the executable. Not so nice.  I looked at the source code to Application.Initialize and it consisted of just the following line:

if InitProc <> nil then TProcedure(InitProc);

So I made that the first line of code and removed the reference to forms.pas.  Everything worked, and the file size didn't take the 300k hit for code it really didn't need.  There's a lot to be said for having the source code to your runtime libraries.

Friday, April 13, 2007

SQL UPDATE from another row in the same table

My SQL skills are getting rusty.  I have some code where I needed to quickly copy a field from one row in a table to another row in the same table.  Bascially, a self-referencing UPDATE. I knew I could so it with a simple UPDATE statement, but I forgot the ANSI syntax.  Self-joining UDPATE statements can be a little squirrelly, so it's best to follow the ANSI standard.  So I went looking for the Master, Mr. Joe Celko.

I did a quick google through the newsgroups on celko update "sql Server", and I found what I was looking for on the third match.  If you really want to learn SQL as a language, Celko's books are very good.

This is what I ended up using:

UPDATE mytable
   SET filterdata = (SELECT src.filterdata
                       FROM mytable src
                      WHERE src.keyid = 5)
 WHERE keyid = 1

The table and field names have been changed to protect the innocent.

Tuesday, April 10, 2007

Understanding SQL Server error 18456

I came across an odd problem with SQL Server Express.  We have a test server with various versions of SQL Server installed for QA to use.   Of the QA techs was unable to connect to the instance of SQL Server Express when he used a SQL Server login.  The code was not returning an error error code, so I decide to take a look at it.

I tried connecting to Express from MS SQL Server Management Studio under that account.  SSMS returned an 18456 error, basically it couldn't connect.  That was just slightly more useful than what I had before, but it provide two things.  It ruled out the app as cause of the problem, and it gave something that I could google with.

The best match I got was Understanding "login failed" (Error 18456) error messages in SQL Server 2005, on the SQL Protocols blog.  Basically, the error 18456 error is purposely vague to prevent nasty people from trying to hack into your server.  To figure out why the login failed, you need the "error state".  Error state is not returned to the client, but it's written to the log file.  That file will be in the LOGS folder of that instance of SQL Server under the name ERRORLOG.   From the log file, you'll get more information about why the login failed:

2007-04-10 09:54:45.14 Logon       Error: 18456, Severity: 14, State: 8.
2007-04-10 09:54:45.14 Logon       Login failed for user 'youridhere'. [CLIENT:]

From the first line, we get the eror state.  Yay, it's 8.  So what does that mean?  The states are documented in the blog posting reference above, but I couldn't find it in my copy of the BOL.  It is documented in the online version (and probably in the latest BOL) at SQL Server 2005 Books Online.  Here are the documented states:

State Description


User ID is not valid.


User ID is not valid.


An attempt was made to use a Windows login name with SQL Server Authentication.


Login is disabled, and the password is incorrect.


The password is incorrect.


Password is not valid.


Login is valid, but server access failed.


Login is valid login, but server access failed.


Password must be changed.

Any other error state is considered to be an internal error.

So it boils down to the password being incorrect, which in this case it was.