Is there any simple way to compare connection strings without parsing it myself?

Question

I need to be able to compare two different connection strings together and identify whether or not they are the same info. I cannot do a simple string comparison, because the properties could be laid out differently, but still represent the same connection.

Before I go and write my own comparison for this, is there already something that can do this?

I've searched for a way and I can't find anything out there about this.

Answer 1

You might use the IDataInitialize::GetDataSource method, which returns an uninitialized data source object from a given connection string. Because of this method returns pointer to a data source object of a IUnknown type, you cannot directly compare the objects obtained for your two compared connection strings. However, you can query the IDBProperties interface on those uninitialized data source objects what gives you a possibility to access all properties supported by a given provider.

To get a property set, you need to use IDBProperties::GetProperties method. This will return a DBPROPSET structure, which contains an array of DBPROP elements (properties). You will then simply iterate this array and compare the properties of those two data source objects in a way you need.

The following IsSameConnStr function returns True if the connection strings equal, False otherwise. Note, that the used property value comparison is case insensitive except the DBPROP_AUTH_PASSWORD property, which is compared with case sensitivity:

uses
  ActiveX, ComObj, OleDB;

function IsSameVarWideStr(const AValue1, AValue2: OleVariant;
  ACaseSensitive: Boolean = False): Boolean;
begin
  Result := VarType(AValue1) = VarType(AValue2);
  if Result then
  begin
    if ACaseSensitive then
      Result := WideCompareStr(VarToWideStr(AValue1),
        VarToWideStr(AValue2)) = 0
    else
      Result := WideCompareText(VarToWideStr(AValue1),
        VarToWideStr(AValue2)) = 0;
  end;
end;

function IsSameConnStr(const AConnStr1, AConnStr2: WideString): Boolean;
var
  I: Integer;
  DataSrc1: IUnknown;
  DataSrc2: IUnknown;
  DataInit: IDataInitialize;
  PropSet1: PDBPropSet;
  PropSet2: PDBPropSet;
  PropSetCnt1: ULONG;
  PropSetCnt2: ULONG;
  Properties1: IDBProperties;
  Properties2: IDBProperties;
const
  DBPROP_AUTH_PASSWORD = $00000009;
begin
  // first check if the input connection strings aren't exactly the same
  Result := CompareStr(AConnStr1, AConnStr2) = 0;
  // if they are not same, then...
  if not Result then
  begin
    // create IDataInitialize object instance
    OleCheck(CoCreateInstance(CLSID_DataLinks, nil, CLSCTX_INPROC_SERVER or
      CLSCTX_LOCAL_SERVER, IID_IDataInitialize, DataInit));
    // get data source objects for both input connection strings
    OleCheck(DataInit.GetDataSource(nil, CLSCTX_INPROC_SERVER,
      PWideChar(AConnStr1), IUnknown, DataSrc1));
    OleCheck(DataInit.GetDataSource(nil, CLSCTX_INPROC_SERVER,
      PWideChar(AConnStr2), IUnknown, DataSrc2));
    // query for IDBProperties objects of the data source objects
    if Succeeded(DataSrc1.QueryInterface(IID_IDBProperties, Properties1)) and
      Succeeded(DataSrc2.QueryInterface(IID_IDBProperties, Properties2)) then
    begin
      // get properties of data source objects
      OleCheck(Properties1.GetProperties(0, nil, PropSetCnt1, PropSet1));
      OleCheck(Properties2.GetProperties(0, nil, PropSetCnt2, PropSet2));
      try
        // same DB provider will have the same set of initialization properties,
        // so the first check might be the property count, if that differs, then
        // at least DB provider is different, so if this equals, then...
        if PropSetCnt1 = PropSetCnt2 then
        begin
          // initialize positive result
          Result := True;
          // iterate all the properties
          for I := 0 to PropSet1.cProperties - 1 do
          begin
            // check if we're comparing the same property and if so, compare the
            // property values; for password property compare the value with case
            // sensitivity, for all the others case insensitively; if any of this
            // doesn't match, we're done with False result and we can exit
            if (PropSet1.rgProperties[I].dwPropertyID <>
              PropSet2.rgProperties[I].dwPropertyID) or
              not IsSameVarWideStr(PropSet1.rgProperties[I].vValue,
              PropSet2.rgProperties[I].vValue,
              PropSet1.rgProperties[I].dwPropertyID = DBPROP_AUTH_PASSWORD) then
            begin
              Result := False;
              Break;
            end;
          end;
        end;
      finally
        // release the property sets; note that you should avoid this common
        // try..finally block and that you should free also each property array
        // element by using IMalloc::Free; why I've used CoTaskMemFree see this
        // question http://stackoverflow.com/q/3079508/960757
        CoTaskMemFree(PropSet1);
        CoTaskMemFree(PropSet2);
      end;
    end;
  end;
end;

The usage is clear I think, so I'll rather mention results for some connection strings:

IsSameConnStr = True
AConnStr1: Provider=MSDASQL.1;Persist Security Info=True;Data Source=datasource
AConnStr2: Provider=MSDASQL.1;Persist Security Info=True;Data Source=DATASOURCE

IsSameConnStr = True
AConnStr1: Provider=MSDASQL.1;Data Source=datasource;Persist Security Info=True
AConnStr2: Provider=MSDASQL.1;Persist Security Info=True;Data Source=DATASOURCE

IsSameConnStr = True
AConnStr1: Provider=MSDASQL.1;Password=PASSWORD;Data Source=datasource;Persist Security Info=True
AConnStr2: Provider=MSDASQL.1;Data Source=DATASOURCE;Password=PASSWORD;Persist Security Info=True

IsSameConnStr = False - password differs in case sensitivity
AConnStr1: Provider=MSDASQL.1;Password=PASSWORd;Data Source=datasource;Persist Security Info=True
AConnStr2: Provider=MSDASQL.1;Data Source=DATASOURCE;Password=PASSWORD;Persist Security Info=True

Answer 2

To get a collection of the ConnectionString properties, you can assign the ConnectionString to TADOConnection (without actually connecting to the DB) and use TADOConnection.Properties collection (collection item is ADOInt.Property_) e.g.:

ADOConnection.Properties.Get_Item('Data Source')

You should probably compare specific properties to determine if the connection is set to a specific data store via a specific provider. e.g.:
Provider, Data Source, Initial Catalog, User ID \ Password (optional).

There are many properties that you might want to ignore depending on the provider e.g:
Workstation ID, Persist Security Info, Use Procedure for Prepare, Auto Translate, etc...

Here is an example how to iterate the TADOConnection properties collection:

var
  ADOConnection: TADOConnection;
  PropName, PropValue: WideString;
  I: Integer;

  ADOConnection := TADOConnection.Create(nil);
  try
    ADOConnection.ConnectionString := 'Provider=MSDASQL.1;Password=secret;Data Source=127.0.0.1;User ID=user;Initial Catalog=mycatalog';
    for I := 0 to ADOConnection.Properties.Count - 1 do
    begin
      // Properties.Item[I] is ADOInt.Property_
      PropName := ADOConnection.Properties.Item[I].Name;
      PropValue := VarToWideStr(ADOConnection.Properties.Item[I].Value);
      ShowMessage(Format('%s=%s', [PropName, PropValue]));
    end;
  finally
    ADOConnection.Free;
  end;

There might be much more properties that are added to/changed in the ConnectionString after TADOConnection was connected to the DB, so you need to take this into account.