How to config ZConnection to connect to MS SQL Server 2019?

[Flattit]

Hallo,

I installed zeosdbo-7.2.6.1-stable in Embarcadero C++ Builder 10.3.

I'd like to know which is the modern/adviced way to configure ZConnection component to connect to MS SQL Server 2019.

Thanks a lot for your help,
Federico

[marsupilami]

Hello Federico,

with Zeos 7.2 there are generally two options:

• Use FreeTDS: Grab yourself a copy of the FreeTDS library. Point the LibraryLocation property to your copy of the FreeTDS library. Set the Protocol property of TZConnection to "FreeTDS_MsSQL>=2005". Set the HostName property to your server name - including the instance name. Example: "localhost\SQLEXPRESS". Set the Database property to your tatabase name. Example: "master". If you want to use trusted authentication simply keep the User and Password properties empty. This will only work on Windows. If you want to use integrated authentication, set User and Password to meaniungful values. Example: User = "sa", Password = "password".
  Note: FreeTDS only works using TCP/IP so you will have to enable TCP/IP on your instance of SQL Server.
• Use ADO: Install SQL Server Native Client. Then set the Protocol property to "ado". The ADO connection string goes into the database property. Note: By clicking on the "..."-Buttone next do the Database property, you can get a dialogue for building the connection string. An example connection string for SQL server native client can look like this:
  Provider=SQLNCLI11.1;Persist Security Info=False;User ID=sa;Initial Catalog="";Data Source=paulchen\sqlexpress2016;Initial File Name="";Server SPN=""
  Username and pasword can again be set by using the User and Password properties.
  Note: using ADO you can also connect using shared memory and named pipes.

Best regards,

Jan

[Flattit]

Hallo Jan,

I tried with ADO and it worked, thanks!

As regards performance which is better between ADO and FreeTDS?

Thanks,
Federico

[marsupilami]

Hello Federico,

as far as I know the ADO driver should be faster. But I am not sure. In the end it might be best to do some performance tests are based on the workload you want to do. FreeTDS can have its advantages when it comes to connecting from Linux or other unix like operating systems.

Best regards,

Jan

[miab3]

Hi Jan,

Tests show that connection via ADO is the slowest.
The fastest is through OleDB(and ODBC), but this is only in ZEOS 7.3.
FreeTDS is an intermediate speed.

Michal

[marsupilami]

Hello Michal

good to know. How do you test performance? Is that something you developed yourself or do you use some generally available performance test?

I am asking because I think about adding some bencmark results to the Zeos 7.3 release notes.

Best regards,

Jan

[miab3]

Hi Jan,

Here are examples of application working time, modifying(reading and increasing) 25000 (in total)
detail table records, with 5000 changes of the master table cursor position.

ZEOS 7.3r6747 min:sec

ADO 2:27
FreeTDS 1:30
ODBC_W 1:20
OLE DB 1:12

Michal