Max length TSQLConnection.Params values

Question

Hello fellow StackOverflowers,

Currently I'm facing a situation where it seems that there is a maximum length for the Database property of a TSQLConnection object in Delphi.

When I open the connection to my database I get the following error when I use a rather long (154 chars) database name:

dbExpress Error: [0x0015]: Connection failed SQL Server Error: unrecognized database parameter block wrong version of database parameter block

When I relocate my database file to another location (and with that reduce the length of the path) it will connect to the database.

I am currently using the Object Inspector to set the connection properties of the TSQLConnection object.

Basically, my question comes down to this:

Does a TSQLConnection have a maximum length for the values set in the Params property? And if so, what is the maximum length of these values?

Answer 1

Update

I've found two ways to open a copy of Employee.Gdb in a folder with a 160-character name ('abcdefghij0123456789' x 8).

What I did firstly was to edit the DBXConnections.Ini file and changed the Database parameter in the [IBConnection] section to read

Database=localhost:D:\abcdefghij01234567890abcdefghij01234567890abcdefghij01234567890abcdefghij01234567890abcdefghij01234567890abcdefghij01234567890abcdefghij01234567890abcdefghij01234567890\employee.gdb

Then, I can successfully connect to it, open the Employee.Gdb and make changes to the Customer table. I have verified the changes in IBConsole just in case the copy of Employee.Gdb wasn't the one I assumed it was.

Subsequently, I've found that I can create and open the db in code using Delphi Seattle and Interbase XE7, as follows:

function LongPath : String;
begin
  Result := 'D:\' + DupeString('abcdefghij0123456789', 8);
end;

function LongDBName : String;
begin
  Result := LongPath + '\Employee.Gdb';
end;

procedure TForm1.OpenDB;
var
  Ini : TMemIniFile;
const
  scDBXConIni = 'C:\Users\Public\Documents\Embarcadero\Studio\dbExpress\17.0\dbxconnections.ini';
  scSourceDB = 'D:\Delphi\Databases\Interbase\Employee.Gdb';
begin
  Ini := TMemIniFile.Create(scDBXConIni);
  try
    // First, blank out the Database value in the IBConnection section
    //  of DBXConnections.Ini
    Ini.WriteString('IBConnection', 'Database', '');
    Ini.UpdateFile;

    //  Next, create the long-named directory and copy Employee.Gdb to it
    if not DirectoryExists(LongPath) then
      MkDir(LongPath);
     Assert(CopyFile(PChar(scSourceDB), PChar(LongDBName), False));

     //  Set LoadParamsOnConnect to False so that the SqlConnection uses
     //  the value of the Database we are about to give it
     SqlConnection1.LoadParamsOnConnect := False;
     SqlConnection1.Params.Values['Database'] := LongDBName;
     SqlConnection1.Connected := True;

     //  Open the CDS to view the data
     CDS1.Open;

  finally
    Ini.Free;
  end;

end;

The critical step in doing it this way is setting LoadParamsOnConnect to False, which I confess I'd overlooked in earlier attempts to get this code to work.

I've got some earlier versions of Delphi on this machine, so if you're not using Seattle and the above code doesn't work for you, tell me which one you are using and I'll see if I can try that.

**[Original answer]

Actually, I think that this may be an error occurring in one of the DBX DLLs.

I created a folder with a 160-character name, then copied the demo Employee.Gdb database into it. Interbase XE7's IBConsole can open the db without error. So could a small test project contructed with IBX components in Delphi Seattle.

However, with an equivalent DBX project, when I use the code below

procedure TForm1.Button1Click(Sender: TObject);
begin
  SqlConnection1.Params.Values['database'] := 'D:\abcdefghij01234567890abcdefghij01234567890abcdefghij01234567890abcdefghij01234567890abcdefghij01234567890abcdefghij01234567890abcdefghij01234567890abcdefghij01234567890\employee.gdb';
  SqlConnection1.Connected := True;
end;

I get an error in

procedure TDBXDynalinkConnection.DerivedOpen;
var
  Count:          TInt32;
  Names:          TWideStringArray;
  Values:         TWideStringArray;
  IsolationLevel: Longint;
  DBXError:       TDBXErrorCode;
begin
  Count := FConnectionProperties.Properties.Count;
  FConnectionProperties.GetLists(Names, Values);

  CheckResult(FMethodTable.FDBXConnection_Connect(FConnectionHandle, Count, Names, Values));
  DBXError := FMethodTable.FDBXConnection_GetIsolation(FConnectionHandle, IsolationLevel);

'I/O error for file "database.gdb" Error while trying to open file The operation completed successfully'

and the Database param of the SqlConnection is left at the value 'Database.Gdb', which is not the value I specified, of course, nor was it the value specified in the params in the IDE, which was 'd:\delphi\databases\interbase\employee.gdb'.

I wondered if I could work around this problem by SUBSTing a drive to the 'abcdefg ...' path. I tried that and opening the database as "x:\employee.gdb" , but I get the same error in my DBX app, and also IBConsole cannot access the db either.

I think you need a shorter physical path!**

Answer 2

This is related to MSSql Server:

As a general guideline, long path names greater than 160 characters might cause problems.

from Microsoft TechNet - https://technet.microsoft.com/en-us/library/ms165768(v=sql.105).aspx