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

[Bo Berglund]

On Fri, 10 Feb 2023 14:00:32 +0100 (CET), Michael Van Canneyt via lazarus
<lazarus at lists.lazarus-ide.org> wrote:

>
>
>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.

I found this tutorial:
https://www.streetinfo.lu/computing/lazarus/project/use_mssql.html

It is still using RAD style drag-drop of the database components...

But from it I could deduce what seems to be what is needed for my conversion.
I have set up the constructor such:

constructor TAppDbHandler.Create;
begin
  FConn  := TMSSQLConnection.Create(NIL);   //was TADOConnection.Create(NIL);
  FTrans := TSQLTransaction.Create(NIL);
  FQuery := TSQLQuery.Create(NIL); // was TADOQuery.Create(NIL);

  FConn.Transaction := FTrans;
  FTrans.DataBase := FConn;
  FQuery.Transaction := FTrans;

  FDatabaseServer :=
ReadRegString(HKEY_CURRENT_USER,'Software\Mycompany\AppManager','DatabaseServer','servername');
  FConn.HostName := FDatabaseServer;

  FDatabase :=
ReadRegString(HKEY_CURRENT_USER,'Software\Mycompany\AppManager','Database','APPMANAGER');
  FConn.DatabaseName := FDatabase;

  FDbUser := 'username';
  FDbPassword := 'userpassword';

  FConn.UserName := FDbUser;
  FConn.Password := FDbPassword;
end;

And the connection handlers:

function TAppDbHandler.OpenConnection: boolean;
begin
  try
    Result := false;
    CloseConnection;

    FConn.LoginPrompt := false;
    FConn.Open;
    Result := FConn.Connected;
  except
    //placeholder
  end;
  if not Result then
    MessageDlg('Could not connect to database!', mtError, [mbOk], 0);
end;


procedure TAppDbHandler.CloseConnection;
begin
	if FConn.Connected then
	  FConn.Close;
end;

But I have not been able to check this because now compilation stops at a
strange error regarding bitmaps...

I am using a QRcode generation unit I got from GitHub long time ago
(https://github.com/foxitsoftware/DelphiZXingQRCode)
and it deals with TBitmap but when I test compile my app I get this in the main
form code:

var
  QR_BMP_SSM: TBitMap;
  ...
begin
  ...
(!)  QR_BMP_SSM := TBitMap.Create; //<== Error here

Error messages:
Compile Project, Target: lib\x86_64-win64\AppManager.exe: Exit code 1, Errors: 2
FormAppManager.pas(410,25) Error: identifier idents no member "Create"
FormAppManager.pas(410,25) Fatal: Syntax error, ";" expected but "identifier
CREATE" found

But after some digging around I removed this error by moving Graphics to next to
last in the form pas file uses list....
Strange.


Now I have gotten the project to build so I should start debugging. 

But when I try to run it from within Lazarus (hit the green arrow button) then a
pop-up image appears:

------------------------------------------------------------------------------
Enable Dwarf 2 (-gw)?
The project does not write debug info in Dwarf format. The "FpDebug internal
Dwarf-debugger" supports only Dwarf.
[Enable Dwarf2 with sets] [Enable Dwarf2 (-gw)] [Enable Dwarf3 (-gw3) [Cancel]
------------------------------------------------------------------------------

What am I supposed to do with this?

-- 
Bo Berglund
Developer in Sweden