Jump to content
BruceTTTT

FireDAC, New OLE DB Driver, and SQL Server

Recommended Posts

Is there any way to force FireDAC to use the new OLE DB Driver (not the older Provider) in the msoledbsql file? Discussed here:

 

https://docs.microsoft.com/en-us/sql/connect/oledb/oledb-driver-for-sql-server?view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/connect/odbc/windows/microsoft-odbc-driver-for-sql-server-on-windows?view=sql-server-2017

https://support.microsoft.com/en-us/office/connect-access-to-sql-server-050d88f3-b2d6-4e76-b6f9-f3c556f139ea
 

The native client drivers are deprecated, and I can use the related ODBC 17 driver, but it's slower. ADO (dbGo) with the new OLE DB driver is the fastest, but I'd like to switch to FireDAC and I'm forced to use the ODBC 17 driver if I do that because of the incompatibilities of the native SQL driver (that FireDAC uses) and different SQL versions.

Share this post


Link to post

Emb decided not to address it and closed the QualityCentral request I posted (sorry, don't remember the number, but you can look it up). They said that FireDAC for SQL Server "uses ODBC" and left it at that. Very disappointing to me.

Share this post


Link to post

I was starting to like FireDAC and was beginning recoding all my apps from ADO to FireDAC, then this happened. I hate ODBC, so I'm still sticking with ADO.

Share this post


Link to post

For what it's worth I ran several thousand queries across three variations and found all about the same performance (or at least within the tolerance that we're happy with):

  • ADO + OLEDB;
  • FireDAC + ODBC 18; and
  • FireDAC + SQL Native Client 11.0

 

 

I'd like the option to replace SQL Native Client though as our clients tend to balk at installing deprecated drivers.

We moved from BDE to FireDAC so we're pot committed 😕

Share this post


Link to post

I tested this many times as well (see the above QC ticket), ODBC was slower loading. And there's also the configuration of the ODBC driver on each client that you must do sometimes. The main problem I have with the Native Client is that it's keyed to the version of SQL Server. We have multiple versions on our servers, and there's no on Native Client that can handle them all. The nice thing about ADO + OLEDB is that this is not an issue. When using the newest OLEDB driver from Microsoft and ADO, all of these issues are resolved. It's fast, one driver works on everything. But of course, it doesn't have all of FireDAC's new stuff.

 

I don't know why this is such a big deal for Emb. Just implement this driver. SQL Server is everywhere, why wouldn't you want to implement the best solution?

  • Sad 1

Share this post


Link to post
On 4/17/2022 at 4:48 AM, BruceTTTT said:

I hate ODBC, so I'm still sticking with ADO.

Um... you aren't using ODBC data sources via FireDAC, are you?

ODBC is just the API FireDAC uses to talk to the driver. From your perspective there's nothing ODBC about it - the API could be anything.

 

1 hour ago, BruceTTTT said:

SQL Server is everywhere, why wouldn't you want to implement the best solution?

Maybe they did 🙂

We've just completed replacing MSSQL via ADO with FireDAC and for us it works fine.

 

We had to update the client library to a newer version on a couple of clients but apart from that it's faster, better. I think the oldest SQL server version we connect to is 2014/v12.

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×