Re: [Lazarus] SQLDb and TSQLQuery usage for MSSQL database...

2016-02-25 Thread Michael Van Canneyt 



On Thu, 25 Feb 2016, Bo Berglund wrote:


On Thu, 25 Feb 2016 08:57:40 +0100 (CET), Michael Van Canneyt
 wrote:



It seems like in some examples one uses the transaction as follows:
ATransaction.StartTransaction;
.. do database stuff ...
ATransaction.Commit;



In general it is better to explicitly handle your transactions.
However, if you don't do that, then SQLDB will do it for you.


So as long as there is a TSQLTransaction object hooked to the
connection and query (same transaction object) then the built-in
system will do the StartTransaction-Commit sequence in the background
if I do not explicitly code for it?


StartTransaction, yes. 
Commit: I do not think so (would need to check).


Michael.

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] SQLDb and TSQLQuery usage for MSSQL database...

2016-02-25 Thread Bo Berglund 
On Thu, 25 Feb 2016 08:57:40 +0100 (CET), Michael Van Canneyt
 wrote:

>>
>> It seems like in some examples one uses the transaction as follows:
>> ATransaction.StartTransaction;
>> .. do database stuff ...
>> ATransaction.Commit;
>>
>
>In general it is better to explicitly handle your transactions.
>However, if you don't do that, then SQLDB will do it for you.

So as long as there is a TSQLTransaction object hooked to the
connection and query (same transaction object) then the built-in
system will do the StartTransaction-Commit sequence in the background
if I do not explicitly code for it?


-- 
Bo Berglund
Developer in Sweden


--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] SQLDb and TSQLQuery usage for MSSQL database...

2016-02-24 Thread Michael Van Canneyt 



On Thu, 25 Feb 2016, Bo Berglund wrote:


On Wed, 24 Feb 2016 11:47:18 +0100 (CET), Michael Van Canneyt
 wrote:


constructor TUserDb.Create;
begin
 FConn := TMSSQLConnection.Create(NIL);
 FTrans := TSQLTransaction.Create(NIL);


You must additionally set

  FConn.Transaction:=FTrans;


 FQuery := TSQLQuery.Create(NIL);
 FQuery.DataBase := FConn; // <== Database property set here!!!
 FQuery.Transaction := FTrans;
end;


Now reading a bit more on the transaction subject and I get confused
by the wiki examples:
http://wiki.freepascal.org/SqlDBHowto#How_to_execute_direct_queries.2Fmake_a_table.3F
and
http://wiki.freepascal.org/SqlDBHowto#Example:_reading_data_from_a_table

It seems like in some examples one uses the transaction as follows:
ATransaction.StartTransaction;
.. do database stuff ...
ATransaction.Commit;

while in other cases there is no action taken on the ATransaction
object, it just sits there on both the query and connection objects
and it still works in my case.

I would like to understand what is happening here...


In general it is better to explicitly handle your transactions.
However, if you don't do that, then SQLDB will do it for you.



Note that in our stored procedures in MSSQLServer there are
transactions built-in where they are needed to safeguard consistency
across data in different tables for insance.


This is a given. You cannot disable transactions on a RDBMS server. 
(MySQL is not a RDBMS server in this sense)


They always are part of the game, but some engines 'hide' them by doing
transaction management implicitly.


The SQLDb transactions are still a bit unclear to me.
As yet I have only run stored procedures that return datasets
(basically select procedures) and these work OK but I don't know if
the transaction object really is involved here...

It seems like a TSQLTransaction activity is only needed if data are to
be changed in the database and not if they are only read from the
database. Is this correct?


Even reading is done in the context of a transaction.
But, quite often, it happens in an 'implicit' transaction.

Michael.

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] SQLDb and TSQLQuery usage for MSSQL database...

2016-02-24 Thread Bo Berglund 
On Wed, 24 Feb 2016 11:47:18 +0100 (CET), Michael Van Canneyt
 wrote:

>> constructor TUserDb.Create;
>> begin
>>  FConn := TMSSQLConnection.Create(NIL);
>>  FTrans := TSQLTransaction.Create(NIL);
>
>You must additionally set
>
>   FConn.Transaction:=FTrans;
>
>>  FQuery := TSQLQuery.Create(NIL);
>>  FQuery.DataBase := FConn; // <== Database property set here!!!
>>  FQuery.Transaction := FTrans;
>> end;

Now reading a bit more on the transaction subject and I get confused
by the wiki examples:
http://wiki.freepascal.org/SqlDBHowto#How_to_execute_direct_queries.2Fmake_a_table.3F
and
http://wiki.freepascal.org/SqlDBHowto#Example:_reading_data_from_a_table

It seems like in some examples one uses the transaction as follows:
ATransaction.StartTransaction;
.. do database stuff ...
ATransaction.Commit;

while in other cases there is no action taken on the ATransaction
object, it just sits there on both the query and connection objects
and it still works in my case.

I would like to understand what is happening here...

Note that in our stored procedures in MSSQLServer there are
transactions built-in where they are needed to safeguard consistency
across data in different tables for insance.

The SQLDb transactions are still a bit unclear to me.
As yet I have only run stored procedures that return datasets
(basically select procedures) and these work OK but I don't know if
the transaction object really is involved here...

It seems like a TSQLTransaction activity is only needed if data are to
be changed in the database and not if they are only read from the
database. Is this correct?


-- 
Bo Berglund
Developer in Sweden


--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] SQLDb and TSQLQuery usage for MSSQL database...

2016-02-24 Thread Michael Van Canneyt 



On Wed, 24 Feb 2016, Bo Berglund wrote:


On Wed, 24 Feb 2016 11:47:18 +0100 (CET), Michael Van Canneyt
 wrote:


You must additionally set

  FConn.Transaction:=FTrans;


Thank you so much

That single omission corrected fixed the problem!
This is my first ever database query execution from Lazaus on Linux
towards a Windows MSSQL Server database!

Now I can convert all of the other stuff as well (going from Delphi 7
to Lazarus 1.6 on Linux)


Congratulations :-)

Michael.

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] SQLDb and TSQLQuery usage for MSSQL database...

2016-02-24 Thread Bo Berglund 
On Wed, 24 Feb 2016 11:47:18 +0100 (CET), Michael Van Canneyt
 wrote:

>You must additionally set
>
>   FConn.Transaction:=FTrans;

Thank you so much

That single omission corrected fixed the problem!
This is my first ever database query execution from Lazaus on Linux
towards a Windows MSSQL Server database!

Now I can convert all of the other stuff as well (going from Delphi 7
to Lazarus 1.6 on Linux)


-- 
Bo Berglund
Developer in Sweden


--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

Re: [Lazarus] SQLDb and TSQLQuery usage for MSSQL database...

2016-02-24 Thread Michael Van Canneyt 



On Wed, 24 Feb 2016, Bo Berglund wrote:


In my previous thread I received help that made it possible for me to
connect from my RPi2 to an MSSQL database server on a Windows server
in the network using the TMSSQLConnection component.
So the connectivity seems fine.
(Using FPC 3.0.0 and Lazarus 1.6)

end;

constructor TUserDb.Create;
begin
 FConn := TMSSQLConnection.Create(NIL);
 FTrans := TSQLTransaction.Create(NIL);


You must additionally set

  FConn.Transaction:=FTrans;


 FQuery := TSQLQuery.Create(NIL);
 FQuery.DataBase := FConn; // <== Database property set here!!!
 FQuery.Transaction := FTrans;
end;


Michael.

--
___
Lazarus mailing list
Lazarus@lists.lazarus.freepascal.org
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus

[Lazarus] SQLDb and TSQLQuery usage for MSSQL database...

2016-02-24 Thread Bo Berglund 
In my previous thread I received help that made it possible for me to
connect from my RPi2 to an MSSQL database server on a Windows server
in the network using the TMSSQLConnection component.
So the connectivity seems fine.
(Using FPC 3.0.0 and Lazarus 1.6)

Now I have a different problem regarding usage of the TSQLQuery
component, which does not currently work as expected:

I have read the tutorial in
http://wiki.freepascal.org/SqlDBHowto#Example:_reading_data_from_a_table
and tried to use it in creating a DB handler class for the
application.
The basics are as follows:

- Here is the current state of my DB handler class: ---

  TUserDb = class
  private
FConn: TMSSQLConnection;
FQuery: TSQLQuery;
FTrans: TSQLTransaction;
FDatabase,
FDBserver,
FDBLogin,
FDBPasswd: string;
FDBSet: boolean;
FLoginSet: boolean;
FPwdSet: boolean;
FServerSet: boolean;
FLastError: string;
procedure SetDatabase(AValue: string);
procedure SetLogin(AValue: string);
procedure SetPasswd(AValue: string);
procedure SetServer(AValue: string);
  public
property Database: string read FDatabase write SetDatabase;
property Server: string read FDBServer write SetServer;
property Login: string read FDBLogin write SetLogin;
property Passwd: string read FDBPasswd write SetPasswd;
constructor Create;
destructor Destroy; override;
function OpenConnection: boolean;
procedure CloseConnection;
function GetPasswordFile(var slPWD: TStringList): boolean;
  end;


implementation

{$R *.lfm}

{ TUserDb }

procedure TUserDb.SetDatabase(AValue: string);
begin
  if FDatabase=AValue then Exit;
  FDatabase:=AValue;
  FDBSet := true;
end;

procedure TUserDb.SetLogin(AValue: string);
begin
  if FDBLogin=AValue then Exit;
  FDBLogin:=AValue;
  FLoginSet := true;
end;

procedure TUserDb.SetPasswd(AValue: string);
begin
  if FDBPasswd=AValue then Exit;
  FDBPasswd:=AValue;
  FPwdSet := true;
end;

procedure TUserDb.SetServer(AValue: string);
begin
  if FDBServer=AValue then Exit;
  FDBServer:=AValue;
  FServerSet := true;
end;

constructor TUserDb.Create;
begin
  FConn := TMSSQLConnection.Create(NIL);
  FTrans := TSQLTransaction.Create(NIL);
  FQuery := TSQLQuery.Create(NIL);
  FQuery.DataBase := FConn; // <== Database property set here!!!
  FQuery.Transaction := FTrans;
end;

destructor TUserDb.Destroy;
begin
  FQuery.Free;
  FTrans.Free;
  FConn.Free;
  inherited Destroy;
end;

function TUserDb.OpenConnection: boolean;
begin
  Result := false;
  if not (FDBSet and FServerSet and FLoginSet and FPwdSet) then
  begin
FLastError := 'Missing connection parameter';
Exit;
  end;
  if FConn.Connected then
 CloseConnection;
  FConn.DatabaseName := FDatabase;
  FConn.HostName := FDBServer;
  FConn.UserName := FDBLogin;
  FConn.Password := FDBPasswd;
  try
FConn.Open;
Result := FConn.Connected;
  except
on E: Exception do
  FLastError := E.Message;
  end;
end;

procedure TUserDb.CloseConnection;
begin
  if FConn.Connected then
FConn.Close(true);
end;

function TUserDb.GetPasswordFile(var slPWD: TStringList): boolean;
var
  sSQL,
  sLogin,
  sPwd,
  sCryptPwd,
  sDup: string;
  slCheck, slFile: TStringList;
begin
  Result := false;
  sSQL := 'EXEC SelectPwdEntries';
  slPWD.Clear;
  slCheck := TStringList.Create;
  slFile := TStringList.Create;
  sDup := '';
  try
try
  if not OpenConnection then
  begin
Exit;
  end;
  FQuery.SQL.Text := sSQL;
  FQuery.Open; //<== Error exception here!!!
  if not FQuery.EOF then
  begin
FQuery.First;
repeat
  sLogin := FQuery.FieldByName('LoginName').AsString;
  sPwd := FQuery.FieldByName('Passwd').AsString;
  sCryptPwd := FQuery.FieldByName('CryptPwd').AsString;
  if (sLogin <> '') and (sPwd <> '') and (sCryptPwd > '') then
  begin
if slCheck.IndexOf(sLogin)<0 then //no duplicates
begin
  slCheck.Add(sLogin);
  slFile.Add(sLogin + ':' + sCryptPwd);
end
else
  sDup := sDup + #13 + sLogin;
  end;
  FQuery.Next;
until FQuery.EOF;
FQuery.Close;
CloseConnection;
if sDup <> '' then
  ShowMessage('Duplicated logins!'#13#10 + sDup);
slPWD.Text := slFile.Text;
Result := true;
  end;
except
  on E: Exception do
FLastError := E.Message;
end;
  finally
slCheck.Free;
slFile.Free;
  end;
end;


 On the form I have these related functions ---


procedure TForm1.FormClose(Sender: TObject; var CloseAction:
TCloseAction);
begin
  FDBHandler.Free;
end;

procedure TForm1.FormShow(Sender: TObject);
begin
  FDBHandler := TUserDb.Create;
end;

procedure TForm1.btnGetPwdFileClick(Sender: TObject);
var
  slPwd: TStringList;
begin
  FDBHandler.CloseConnection;
  FDBHandler.Database:= '';
  FDBHandler.Server:= '';
  FDBHandler.Login:= '';