As far as I can tell, as of Delphi 2010 at least it is impossible to deploy a DBExpress application without also deploying at least one dbxdrivers.ini. Furthermore, deciding where this ini file has to go is a minefield, and the DBExpress architecture smells very badly in this area.
Sadly, this post won’t contain all the answers to the question it poses since I have not yet found those answers. The post is in part an effort to find someone with the answers – if they exist.
The Context
One project I am working on is being migrated from Delphi 2006 to Delphi 2010 (ultimately XE, hopefully, but 2010 is where I am at right now).
The application supports connection to a variety of databases, using the DBExpress framework. In Delphi 2006, connections were instantiated and configured dynamically.
Never mind whether having configuration parameters maintained in an external configuration file is A Good Thing™ or not – the fact is, this was not how the application worked, and it did work very successfully for some years.
Note also that the application is not an internal application but a commercial product, so the extent to which we can dictate the environment in which the application runs is necessarily limited.
Problems were immediately uncovered when we moved this code to Delphi 2010, and all revolve around DBXDRIVERS.INI file, or the lack there-of…
Setting DriverName
First, when setting a value for the TSQLConnection.DriverName property, if that value is anything other than an empty string, the property setter now insists on loading information from the ConnectionFactory for that driver.
In Delphi 2006 this only occurred at design-time, but it is now a behaviour also imposed at runtime.
There is only one ConnectionFactory, and that is the one driven by the INI file – DBXDRIVERS.INI
The result: If you have any code that sets DriverName to a non-empty string, you must now have a DBXDRIVERS.INI file, and the DriverName you set must now be a valid a driver name identified by that ini file.
NOT Setting DriverName
So perhaps we can avoid this problem by not setting DriverName, leaving this property empty and configuring the parameters that would be set from the ini file manually ourselves (as we always used to do).
Sadly, this doesn’t work either.
For whilst you can successfully leave DriverName unset and set all other relevant properties, things fall apart when you then try to then Connect the TSQLConnection.
At this point, DoConnect() will call CheckLoginParams() (unavoidable) and CheckLoginParams() contains this line of code:
if FDriverName = '' then DataBaseError(SMissingDriverName);
Somebody else suggested configuring the connection parameters in a BeforeConnect event, but this changes nothing about the flow of code that reaches this line of code.
Setting DriverName, even in BeforeConnect, will invoke DBXDRIVERS.INI validation of the specific value.
Not setting DriverName will always result in the TSQLConnection complaining that the property has not been set when it is eventually connected.
Which is right were we started.
Giving In To The Inevitable: Deploy and be Damned
So, it looks like we are forced to provide a copy of DBXDRIVERS.INI with our application.
That isn’t necessarily A Bad Thing™. Having the ability to add support for new drivers to our application could actually be an improvement, so rather than investigate how to avoid deploying DBXDRIVERS.INI, I instead set about looking into how to safely and reliably deploy it.
Oh dear.
Oh dear, oh dear.
DBExpress starts to look very shonky when you prod and poke things in this area.
Critical to the whole thing is how DBExpress locates DBXDRIVERS.INI. This is critical because you have no way to explicitly direct DBExpress to use a given ini file or even look for DBXDRIVERS.INI in a specific location.
It follows it’s own rules:
1. Look in the application directory. That is, the directory containing your application EXE file.
In the days of UAC I am sure I don’t need to spell out why this is not only A Bad Idea™, but A Dumb Idea. A Very VERY Dumb Idea.
2. If no file found in the application directory, use the location stored in the registry key: HKEY_CURRENT_USER\CodeGear\BDS\7.0\DBExpress\DriverFilename
2
NOTE: You can change the value of this key, but you cannot change the name of the key.
3. If no registry key is found in HKEY_CURRENT_USER, or if the DBXDRIVERS.INI file is not found in the location identified by such a key, then look for the same key in HKEY_LOCAL_MACHINE.
NOTE: If you use either registry keys to store your DBXDRIVERS.INI file location, then you had better be prepared to share your INI file with every other DBExpress application on the machine. We would rather not have to do that.
But we don’t want to store DBXDRIVERS.INI in the application directory either.
Where Do We Go From Here
Ideally we would be able to continue configuring our TSQLConnections at runtime, dynamically. Just as we always used to. We can extend our own application very easily to support additional drivers not known at compile time – and since we will need to store those configuration details in a database, this would actually be preferable to maintaining an INI file separate/in addition to that database.
Failing that, we need to be able to direct DBExpress to use an INI file of our choosing, or at the very least direct DBExpress to use a DBXDRIVERS.INI file in a location of our choice.
These seem like pretty basic requirements to me.
One of which used to be possible, the other which should have been made possible once the alternative was taken away, and ideally in addition to the alternative in the first place.
But, I also have to say that in exploring the code in SqlExpr, I am not impressed by what I see.
Not one little bit.
It is worrying that these simple, and to me: obvious, design flaws are present in the framework in the first place.
It is downright scary to see the mess that the code is in that implements the design choices that were – for whatever reason – made.
Just one example that immediately springs to mind, and doesn’t need a lot of explanation:
TSQLConnectionFactory is an abstract base class for all connection factory implementations. There is only one such implementation – the one that sits atop the INI file(s).
So it is not obvious to me why the abstract base class contains code to locate and identify INI files, which are supposedly part of the implementation of the specific INI file based derived class.
Other examples include very suspect indentation that smells very badly of incorrect conditional flow control, but it is difficult to say for sure that this is indeed what that indentation indicates, obviously.
Similarly the amount of commented out code (commented out without explanation) is worrying – such artefacts smell of experimental/incomplete code in my experience.
But I digress.
The point of this post was specifically around the deployment issues relating to DBXDRIVERS.INI and how to avoid those issues or make the best of a bad job.
Any ideas?
I think the problem started in Delphi 2009. I never could get it to connect to the dbxoodbc driver dynamically, whereas I’d had no problem in earlier versions. I eventually gave up and switched to ADO.
Hmm, maybe you *should* spell out what this particular INI-file has to do with UAC… While I totally agree on pretty much everything else you wrote, I don’t quite get your reservations against putting that file in the application directory *in this specific case*. Frankly feels a bit cargo-cultish to me…
Unless I misunderstand the purpose of this file (I haven’t used dbExpress myself yet) it is essentially immutable or at least it would only ever be modified at install or update time and there would especially never be any user-specific changes to the file, right?
So, as long as no runtime write-access is ever required (or even desired) and the contents are not user-specific then AFAIC UAC will not give you any troubles with this file at all. I would view this file simply as an integral part of your application, much like a localization resource DLL or something in that vein.
Cheers,
Oliver
I deploy applications without the .ini file without a problem (D2010 and XE). Here’s an example:
Connection := TSQLConnection.Create(nil);
Connection.DriverName := ‘MSSQL’;
Connection.GetDriverFunc := ‘getSQLDriverMSSQL’;
Connection.LibraryName := ‘dbxmss.dll’;
Connection.VendorLib := ‘sqlncli10.dll’;
Connection.LoginPrompt := False;
Connection.Params.Clear;
Connection.Params.Add(‘drivername=MSSQL’);
Connection.Params.Add(‘schemaoverride=%.dbo’);
Connection.Params.Add(‘HostName=’ + ServerName);
Connection.Params.Add(‘Database=’ + DatabaseName);
if ServerUserName ” then
begin
Connection.Params.Add(‘User_Name=’ + ServerUserName);
Connection.Params.Add(‘Password=’ + ServerPassword);
Connection.Params.Add(‘OS Authentication=False’);
end
else
begin
Connection.Params.Add(‘OS Authentication=True’);
end;
Connection.Params.Add(‘MaxBlobSize=-1’);
Connection.Params.Add(‘LocaleCode=0000’);
Connection.Params.Add(‘IsolationLevel=ReadCommited’);
Connection.Params.Add(‘PrepareSQL=True’);
Connection.Open;
This is really sad as the DBXdrivers themselves have never been this bad…
I would generate the DBX….ini file on the fly and store it in the users AppData directory (whereeber you have write access). So you don’t share the file but the data used to generate. It is little strange but honestly some restriction of the UAC are weak as they are here only here for MS not being responsible or less responsible and not protecting the computer or the user.
I live outside the MS stanards also on a Windows machine and don’t care about what the redmonders think and have a seperate region on the filesystem
home/world/bin
home/world/app/etc
home/”username”/bin
home/”username”/etc
or however you call it. Get rid of the Redmonders half baked securtiy attempts – don’t consider them, they are worth nothing.
But I agree this will have to be solved … and EMB will have to do in the end. Honestly I don’t use things that only work via config files, in reallity this does not work. This then brings us to the point that you can only rely on the plain socket.
I am not totally sure but I think this could help you:
// Returns true if ini files are loaded or false if empty memory factory is created
function LoadLocalDBXConnectionFactory(AppDir:string=”):boolean;
var Factory:TDBXConnectionFactory;
lEnvPath:string;
begin
Result:=false;
Factory:=nil;
if AppDir=” then
AppDir := ExtractFileDir(ParamStr(0))
else
begin
AppDir := ExcludeTrailingPathDelimiter(AppDir);
lEnvPath:=GetEnvironmentVariable(‘PATH’);
SetEnvironmentVariable(‘PATH’,PChar(lEnvPath+’;’+AppDir));
end;
if (AppDir”) and FileExists(AppDir+’\’+TDBXDriverFile) and FileExists(AppDir+’\’+TDBXConnectionFile) then
try
Factory := TDBXIniFileConnectionFactory.Create;
with TDBXIniFileConnectionFactory(Factory) do
begin
ConnectionsFile:=AppDir+’\’+TDBXConnectionFile;
DriversFile:=AppDir+’\’+TDBXDriverFile;
end;
Result:=true;
except
FreeAndNil(Factory);
end;
// TDBXCommon memory leak workaround
if not Assigned(Factory) then
Factory:= TDBXMemoryConnectionFactory.Create;
Factory.Open;
TDBXConnectionFactory.SetConnectionFactory(Factory);
end;
The key in the code is:
TDBXConnectionFactory.SetConnectionFactory(Factory);
and you have to do it early enough before defaultl factory is created.
Please see
http://andy.jgknet.de/blog/2010/11/dbx-without-deploying-dbxdrivers-ini/
for a possible solution.
Instead of setting to empty. You can try to set it to a dummy name before setting other params
try
connection.DriverName := ‘Fire whoever implement this stupid check’;
except
// eat the pastard
end;
connection ….
This should help:
http://andy.jgknet.de/blog/2010/11/dbx-without-deploying-dbxdrivers-ini/
@Those who offered various suggestions – thanks. I shall try all of those when I get the chance.
But a few of them have obvious difficulties:
@apz:
Swallowing the exception allows DriverName to be set, but you will still run into the dbxdrivers.ini problem when you try to connect, as CheckLoginParams() also retrieves driver properties using the DriverName.
@michael:
The only way to store dbxdrivers.ini in a location other than the AppDir (EXE) folder, is to record that other location in the registry key, in which case you are sharing that location with every other DBExpress application on that machine (and hoping they play nice).
This is simply not an option in our case (apart from anything else, another application could then choose to change the value of the registry key – it doesn’t “belong” to us after all).
@andreas: FANTASTIC! This looks like it may work, though we will need to look into extending the technique for the general case (our application connects to a multitude of database of varying types, potentially simultaneously – afaict your unit finagles things to use a particular driver but doesn’t provide a means (without a little more work) to use ANY driver. It doesn’t look to hard to extend it to do this though.
But, I may also just give up on DBExpress and switch over to “raw” ADO (apart from anything else, the fact that things like this can be changed with such dramatic and significant consequences is A Bad Thing™ – what happens if they change things on us again in XE, XE2?).
@Oliver: In our case the INI file is NOT immutable, hence the requirement to store it in the application EXE directory causes problems.
Even if it did not, and even if UAC did not exist, it goes against good practice. And the fact that this isn’t just a default but an immutable location (unless you want to share an INI file with other apps, using a similarly immutable registry key that) is a patently ridiculous design choice.
Re the registry key, it is patently stupid to have to have a registry key installed by your application that does not reside under a root key FOR your application.
Even more ridiculous for it to reside in a key that is rooted in “CodeGear”, (and indeed then employs a key referring to a specific version of the IDE product/compiler) when that branding has been expunged from the very product that insists on creating that key.
Jolyon I know, I don’t touch the registry … and I use Prof;). My DBs are remote … and for configuration issues and many other reasons I finally decided for AnyDAC in productive use or sometimes wire protocol with a little help of service currently under development that can be compared to the Oracle names service.
I give people something they can read and modify safely.
I don’t remember the exact configuration but I think it was Vista or WinXP SVP3 that caused problems … DBX, the drivers were not bad at all and as AnyDAC supports DBX too … one day maybe … who knows, Devart Oracle via wire protocol and AnyDAC as backend …
For design I use the ContextSoft Database Designer and I wanted at least to run on one config file. This is now very consistent and manageable.