TADODataSet, editing, insert, delete.

[Stephanus Kusuma 1]

Dear Praxis,

 

I've been using Delphi for years. Only recently I tried to use ADO.

I have some questions concerning TADODataSet,

Few times ago, when using TQuery, I have to implement its UpdateObject property to manipulate table. Now, I have a problem with TADODataSet.

There's no such update object in TADODataSet.

Should I implement update table in TADODataSet.AfterPost and TADODataSet.AfterDelete event?

And how can I differentiate Insert or Delete operation in TADODataSet.AfterPost ?

 

Thank you very much in advance.

Stephanus.

[Stephanus Kusuma 1]

I think I know,

TADODataSet.CommandType is the one that you should check.

[Stephanus Kusuma 1]

Check

Edited January 12 by Stephanus Kusuma

[Lars Fosdal 1793]

@Stephanus Kusuma ADO has been succeeded by FireDAC.

Although it has not been deprecated like BDE, ADO is quite old and you are more likely to get answers to questions about FireDAC .

FireDAC also has a lot better performance, in my experience.

[JonRobertson 72]

On 12/17/2023 at 12:49 PM, Stephanus Kusuma said:

Few times ago, when using TQuery, I have to implement its UpdateObject property to manipulate table. Now, I have a problem with TADODataSet.

There's no such update object in TADODataSet.

When you say "manipulate table", do you mean insert/update/delete data in the table? If so, nothing special is needed. Use the Insert, Edit, Post, and Delete methods of TADODataSet. If you are modifying the structure/schema of a table, you can do that with SQL statements using TADODataSet or TADOQuery. In that scenario, I recommend setting the ExecuteOptions property to [eoExecuteNoRecords].

 

The CommandType property specifies whether CommandText contains SQL Text (cmdText), the name of a table (cmdTable), the name of a stored procedure (cmdStoredProc), or the filename of a dataset persisted on disk (not in a database). Best practice is to avoid cmdTable, or the equivalent component TADOTable, as that doing a SELECT * FROM and will always retrieve all rows and all columns. Best practice is to limit the SELECT to only the columns needed.

[JonRobertson 72]

6 hours ago, Lars Fosdal said:

ADO has been succeeded by FireDAC.

I wouldn't say FireDAC succeeded ADO/dbGo. I would agree that FireDAC succeeded dbExpress.

 

As far as I know, FireDAC uses SQL Native Client, which Microsoft deprecated, I think in 2019. Microsoft no longer updates it or supports it and Native Client is not fully compatible with newer versions of SQL Server. There is a QC to add support for MSOLEDBSQL to FireDAC, but that QC was closed.

 

RSP-32494

[Lars Fosdal 1793]

@JonRobertson I have no performance comparisons between the ODBC driver and the OLEDB driver, so I'll have to take that at face value.
Devart did a a more nuanced comparison between the two.
 

The OLEDB driver was deprecated by MS at one point, then undeprecated in 2018.

Not sure why EMBT chose not to create a wrapper for it, but resources may have been an issue?

 

As for ODBC vs Native - In case there are multiple drivers installed, I wrote the following simple code to pick my preferred driver, but these days we stick with the ODBC driver(s) due to the Native Client being too old for some of our databases.

class function TPSDFireDatabasePoolMSSQL.FindBestDriver(const Link: TFDPhysMSSQLDriverLink): String;
const // Constants copied from implementation section of FireDAC.Phys.MSSQL
  C_SQL_SERVER = 'SQL Server'; // DO NOT TRANSLATE
  C_2019_ODBC = 'ODBC DRIVER 19 FOR SQL SERVER'; // DO NOT TRANSLATE
  C_2018_ODBC = 'ODBC DRIVER 18 FOR SQL SERVER'; // DO NOT TRANSLATE
  C_2017_ODBC = 'ODBC DRIVER 17 FOR SQL SERVER'; // DO NOT TRANSLATE
  C_2016_ODBC = 'ODBC DRIVER 13 FOR SQL SERVER'; // DO NOT TRANSLATE
  C_2012_ODBC = 'ODBC DRIVER 11 FOR SQL SERVER'; // DO NOT TRANSLATE
{$IFDEF POSIX}
  C_FreeTDS = 'FreeTDS';
{$ENDIF}
{$IFDEF MSWINDOWS}
  C_2012_NC = 'SQL SERVER NATIVE CLIENT 11.0'; // DO NOT TRANSLATE
{$ENDIF}
var
  DriverList : TStringList;
  WantedList : TArray<String>;
  Driver: string;
begin
  Result := ''; // Blank = Default

  WantedList := {$IFDEF MSWINDOWS}
                  {$IFDEF SQLNative}
                    [C_2012_NC, C_2017_ODBC, C_2016_ODBC, C_2012_ODBC]
                  {$ELSE}
                    [C_2018_ODBC, C_2017_ODBC, C_2016_ODBC, C_2012_NC, C_2012_ODBC]
                 {$ENDIF}
               {$ENDIF}
               {$IFDEF POSIX}
                 [C_2018_ODBC, C_2017_ODBC, C_2016_ODBC, C_2012_ODBC, C_FreeTDS]
               {$ENDIF};

  DriverList := TStringList.Create;
  try
    Link.GetDrivers(DriverList);

    DebugOut('Available SQL drivers'); // DO NOT TRANSLATE
    for Driver in DriverList
     do DebugOut(' "' + Driver + '"');

    for var Wanted in WantedList
     do for Driver in DriverList
      do begin
        if CompareText(Wanted , Driver) = 0
        then begin
          DebugOut('Selected driver: "' + Driver + '"'); // DO NOT TRANSLATE
          BestDriver := Driver;
          Exit(Driver);
        end;
      end;
  finally
    DriverList.Free;
  end;
end;

 

[Lars Fosdal 1793]

39 minutes ago, JonRobertson said:

I wouldn't say FireDAC succeeded ADO/dbGo. I would agree that FireDAC succeeded dbExpress.

That is probably an accurate assesment.

[JonRobertson 72]

32 minutes ago, Lars Fosdal said:

In case there are multiple drivers installed, I wrote the following simple code to pick my preferred driver

I do the same thing with ADO, preferring MSOLEDBSQL if it is installed. Although not my actual code, the code below does the job. In my case, I dynamically update the ADO Connection String before connecting to SQL Server.

function GetADOProvider: string;
const
  PREFERRED_SQL_PROVIDER = 'MSOLEDBSQL';
  SQLOLEDB_SQL_PROVIDER  = 'SQLOLEDB.1';
begin
  var lstProviderNames := TStringList.Create();
  try
    GetProviderNames(lstProviderNames);
    if lstProviderNames.IndexOf(PREFERRED_SQL_PROVIDER) > -1 then
      Result := PREFERRED_SQL_PROVIDER
    else
      Result := SQLOLEDB_SQL_PROVIDER;
  finally
    lstProviderNames.Free();
  end;
end;

 

[Lars Fosdal 1793]

Same here. I really don't like design time db components.

[Brian Evans 109]

8 hours ago, JonRobertson said:

I wouldn't say FireDAC succeeded ADO/dbGo. I would agree that FireDAC succeeded dbExpress.

 

As far as I know, FireDAC uses SQL Native Client, which Microsoft deprecated, I think in 2019. Microsoft no longer updates it or supports it and Native Client is not fully compatible with newer versions of SQL Server. There is a QC to add support for MSOLEDBSQL to FireDAC, but that QC was closed.

 

RSP-32494

Microsoft has muddied the waters regarding database drivers over the years. Firedac has no problems using the latest ODBC Driver 18 for SQL Server which is currently one of the two methods they suggest going forward.

 

Driver history for Microsoft SQL Server - SQL Server | Microsoft Learn

Download ODBC Driver for SQL Server - ODBC Driver for SQL Server | Microsoft Learn