[Lazarus] Porting Delphi (2007/XE5) application, how to handle ADODB?

[Michael Van Canneyt]

On Fri, 10 Feb 2023, Bo Berglund via lazarus wrote:

> On Mon, 23 Jan 2023 09:59:14 +0100 (CET), Michael Van Canneyt via lazarus
> <lazarus at lists.lazarus-ide.org> wrote:
>
>>
>>
>> On Mon, 23 Jan 2023, Bo Berglund via lazarus wrote:
>>
>>> I have a number of "older" applications developed on Windows using
>>> Delphi7-2007-XE5, which use ADODB for database accesses.
>>> The database is Microsoft SQLSERVER.
>>>
>>> Now I no longer have the old Borland software installed (issues with Windows 10
>>> etc) so I want to port the projects to FPC/Lazarus.
>>> I have ported some other non-database applications which (with some effort)
>>> seems to have worked out OK.
>>>
>>> Now I am facing the challenge of doing it on an adodb database enabled
>>> application and I would like to know if there is some "compatible" way of doing
>>> it, which is described somewhere?
>>
>> Yes.
>>
>> You need to use SQLDB, and the TMSSQLConnection.
>>
>> See https://wiki.freepascal.org/SQLdb_Tutorial1 for a simple explanation.
>>
>> The page https://wiki.freepascal.org/SQLdb_Package also contains some explanations
>> for setup.
>>
>> There are some other pages in the wiki.
>>
>> A more in-depth explanation is in the lazarus book.
>>
>> Or you can look at
>>
>> https://www.freepascal.org/~michael/articles/
>>
>> several articles discuss SQLDB, in particular:
>>
>> https://www.freepascal.org/~michael/articles/lazviddb/lazviddb.pdf
>>
>> That should be enough to get you started.
>>
>> Michael.
>
> Thanks!
>
> Almost there but not quite...
> I am using Lazarus 2.2.4 with fpc 3.2.2 on a Windows 10 box.
>
> The application seems to have been converted pretty well using the Lazarus
> Delphi Conversion function to migrate from Delphi with some extra defines
> needing adjustments. But these have been minor considering the size of the
> application.
>
> But the ADODB conversion is now halted on the TSqlQuery and TMSSQLConnection
> items which operate so differently from ADODB...
>
> I added this in uses:
>
>  mssqlconn, //Had to add this in order to use TMSSQLConnection
>  sqldb,
>
> This made the code pass the defines of the objects:
>
>  TAppDbHandler = class
>  private
>    FConn: TMSSQLConnection;  // TADOConnection;
>    FQuery: TSQLQuery;        // TADOQuery;
>    FDatabaseServer,
>    FDatabase,
>    FConnString,
>    FDbUser,
>    FDbPassword,
>    FUserName,
>    FUserPassword: string;
>
>
> But next I get a lot of errors because of the differences between this and the
> Delphi ADODB we used before.
>
> I got lost here...
>
> constructor TAppDbHandler.Create;
> begin
>  FConn := TMSSQLConnection.Create(NIL);   //was: TADOConnection.Create(NIL);
>  FQuery := TSQLQuery.Create(NIL); //was: TADOQuery.Create(NIL);
>  FQuery.Connection := FConn; //<== Triggers an error

FQuery.Database:=FConn;

>  FDatabaseServer :=
> ReadRegString(HKEY_CURRENT_USER,'Software\...\AppManager','DatabaseServer','servername');
>  FDatabase :=
> ReadRegString(HKEY_CURRENT_USER,'Software\...\AppManager','Database','APPMANAGER');
>  FConnString := 'Provider=SQLOLEDB.1;Persist Security Info=False;' +
>                 'Network Library=DBMSSOCN;Data Source=' + FDatabaseServer + ';'
> +
>                 'Initial Catalog=' + FDatabase;
>  FDbUser := '****';
>  FDbPassword := '****';
> end;
>
>
> Is there some example which does not use component drag-drop and properties
> settings in the GUI but does all of it in code towards MSSQLserver?

Try this:

FConn.DatabaseName:=FDatabase;
FConn.HostName:=FDatabaseServer;
FConn.Params.Add('Provider=SQLOLEDB.1');
FConn.Params.Add('Persist Security Info=False');
FConn.Params.Add('Network Library=DBMSSOCN');
FConn.UserName:=FDbUser;
FConn.PasswWord:=FDBPassword;

it is all pretty generic. I am not sure that the Params are needed, they
look more like some ODBC/ADO specific things. The MS-SQL connection of Free
Pascal does not use ODBC/ADO. So I would start without the params, 
only add them if it does not work without them.

One thing: make sure your SQL server accepts TCP/IP connections. 
You must do this in the Microsoft Management Console.
Google is your friend there.

Michael.