Newbie database error question
lazarus / 16-Databases

----------------------------------------------------------------------
the.weavster · 2008-12-19 20:15

I'm new to Lazarus and to Pascal, I have created the following
procedure which updates the EMPLOYEE table in the example database
that installs with Firebird.

Code: [Select]procedure TForm1.Button1Click(Sender: TObject);
begin
     IB1.KeepConnection:= True;  //   IB1 is a TIBConnection
     IB1.Open;
     If IB1.Connected Then
     Begin
          TR1.StartTransaction;    //   TR1 is a TSQLTransaction
          SQL1.DataBase := IB1;    //   SQL1 is a TSQLQuery
          SQL1.Transaction := TR1;
          SQL1.SQL.Add('UPDATE EMPLOYEE SET FIRST_NAME = :fValue WHERE
FIRST_NAME = :fName ;');
          SQL1.Params.ParamByName('fValue').AsString:='Doris';
          SQL1.Params.ParamByName('fName').AsString:='Bruce';
          SQL1.ExecSQL;
          TR1.Commit;
     End;
end;This works fine but I don't know how to detect and handle errors.
Would somebody please be kind enough to edit my code to add an error
handler.

Thanks

----------------------------------------------------------------------
DougNettleton · 2008-12-19 23:28

How about...

procedure TForm1.Button1Click(Sender: TObject);
begin
     IB1.KeepConnection:= True;  //   IB1 is a TIBConnection
     IB1.Open;
     If IB1.Connected Then
     Begin
        try
          TR1.StartTransaction;    //   TR1 is a TSQLTransaction
          SQL1.DataBase := IB1;    //   SQL1 is a TSQLQuery
          SQL1.Transaction := TR1;
          SQL1.SQL.Add('UPDATE EMPLOYEE SET FIRST_NAME = :fValue WHERE
FIRST_NAME = :fName ;');
          SQL1.Params.ParamByName('fValue').AsString:='Doris';
          SQL1.Params.ParamByName('fName').AsString:='Bruce';
          SQL1.ExecSQL;
          TR1.Commit;
        except
          ShowMessage('Oh, Oh something went wrong!');
        end;
     End;
end;

----------------------------------------------------------------------
the.weavster · 2008-12-20 11:48

Thanks Doug, how would I get the error message/error code returned
from Firebird so I could show the user a meaningful description of the
problem?

----------------------------------------------------------------------
the.weavster · 2008-12-20 13:51

I think I've found the answer:Code: [Select]procedure
TForm1.Button1Click(Sender: TObject);
Begin
     IB1.KeepConnection:= True;  //   IB1 is a TIBConnection
     IB1.Open;
     If IB1.Connected Then
        Try
          TR1.StartTransaction;    //   TR1 is a TSQLTransaction
          SQL1.DataBase := IB1;    //   SQL1 is a TSQLQuery
          SQL1.Transaction := TR1;
          SQL1.SQL.Add('UPDATE EMPLOYEE SET FIRST_NAME = :fValue WHERE
FIRST_NAME = :fName ;');
          SQL1.Params.ParamByName('fValue').AsString:='Doris';
          SQL1.Params.ParamByName('fName').AsString:='Bingo';
          SQL1.ExecSQL;
          TR1.Commit;
        Except On E:Exception Do
          Begin
            TR1.Rollback;
            MessageDlg('Error: ' + E.Message,mtError, [mbOK], 0);
          End;
        End;
End;
Thanks again.

----------------------------------------------------------------------
the.weavster · 2008-12-20 19:10

I think I've finally got it as I want it:Code: [Select]procedure
TForm1.Button1Click(Sender: TObject);
Begin
    IB1.KeepConnection:= True;  //   IB1 is a TIBConnection
    IB1.Open;
    If IB1.Connected Then
       Begin
         Try
           TR1.StartTransaction;  //   TR1 is a TSQLTransaction
           SQL1.DataBase := IB1;    //   SQL1 is a TSQLQuery
           SQL1.Transaction := TR1;
           SQL1.SQL.Add('UPDATE EMPLOYEE SET FIRST_NAME = :fValue
WHERE FIRST_NAME = :fName ;');
           SQL1.Params.ParamByName('fValue').AsString:='Doris';
           SQL1.Params.ParamByName('fName').AsString:='Bingo';
           SQL1.ExecSQL;
           TR1.Commit;
         Except On E:Exception Do
           Begin
             TR1.Rollback;
             MessageDlg('Error: ' + E.Message,mtError, [mbOK], 0);
           End;
         End;
       End
     Else
       Begin
         MessageDlg('Error: Failed to connect',mtError, [mbOK], 0);
       End;
End;

----------------------------------------------------------------------
SlackBrane · 2008-12-21 03:24

Ignore me because I beat my head over things and know nothing about
databases but you might tidy it up with..

Code: [Select]procedure TForm1.Button1Click(Sender: TObject);
Begin
  Try
    IB1.KeepConnection:= True; //   IB1 is a TIBConnection
    IB1.Open;
    TR1.StartTransaction;    //   TR1 is a TSQLTransaction
    SQL1.DataBase := IB1;    //   SQL1 is a TSQLQuery
    SQL1.Transaction := TR1;
    SQL1.SQL.Add('UPDATE EMPLOYEE SET FIRST_NAME = :fValue WHERE
FIRST_NAME = :fName ;');
    SQL1.Params.ParamByName('fValue').AsString :='Doris';
    SQL1.Params.ParamByName('fName').AsString  :='Bingo';
    SQL1.ExecSQL;
    TR1.Commit;
  Except On E:Exception Do
      MessageDlg('Error: ' + E.Message,mtError, [mbOK], 0);
End;
I'm guessing that IB1.Open is something that might also 'fail' so if
you move it into your Try block then you will catch it if it does.
Then you don't have to check If the connection succeeded. If it did
not the Try will fail either on the .Open or one of the subsequent
statements because you are not connected.

You might also get some problems if one of the statements prior to
TR1.commit or TR1.commit itself fails. Perhaps I am just being
paranoid, again I don't know database stuff. However------ err I'll
just change it again.

I've removed the TR1.Rollback from the Except block. I'm assuming if
part of the Try, including the TR1.Commit, did not work then nothing
would have been commited to the database so it is not necessary to do
the Rollback. Indeed, if I am right (?), doing the Rollback having
generated an Exception might raise its own Exception!

Don't trust me on this one though.

Keith

Edit: Just went the full banana and moved it all into the Try block.

----------------------------------------------------------------------
SlackBrane · 2008-12-21 03:48

And whilst I'm being silly... maybe...

Code: [Select]procedure TForm1.Button1Click(Sender: TObject);
Begin
  Try
    //IB1.KeepConnection:= True; //   IB1 is a TIBConnection
    IB1.Open;
    TR1.StartTransaction;    //   TR1 is a TSQLTransaction
    SQL1.DataBase := IB1;    //   SQL1 is a TSQLQuery
    SQL1.Transaction := TR1;
    SQL1.SQL.Add('UPDATE EMPLOYEE SET FIRST_NAME = :fValue WHERE
FIRST_NAME = :fName ;');
    SQL1.Params.ParamByName('fValue').AsString :='Doris';
    SQL1.Params.ParamByName('fName').AsString  :='Bingo';
    SQL1.ExecSQL;
    TR1.Commit;
    IB1.Close;
  Except On E:Exception Do
  Begin
    IB1.Close;
    MessageDlg('Error: ' + E.Message,mtError, [mbOK], 0);
  End;
End;
I don't know what the KeepConnection does, unless it is some form of
locking whilst you are playing with the database, so I've commented it
out. I am bound to be wrong though. I've also added some IB1.Close
statements. I don't know if they exist but leaving things Open seems
like a bad idea so having played with the database I'd be inclined to
close the connection.

Also, just in case an error does occur and the connection is left
open, I've added a Close in the Except block. I would imagine (hope)
that doing that is not going to throw another Exception since closing
something that is already closed is 'safe'.

Keith

----------------------------------------------------------------------
the.weavster · 2008-12-21 20:09

Quote from: SlackBrane on December 21, 2008, 04:48:32 amI don't know
what the KeepConnection doesAs I can't find any documentation
describing the database objects members I'm really guessing myself.
Some database servers timeout connections if they're inactive for x
amount of time, I've assumed this property is to prevent that
happening but I could be wrong.

Quote from: SlackBrane on December 21, 2008, 04:48:32 amI've removed
the TR1.Rollback from the Except block. I'm assuming if part of the
Try, including the TR1.Commit, did not work then nothing would have
been commited to the database so it is not necessary to do the
Rollback.All the Firebird documentation I've read states that
transactions should always be explicitly commited or rolled back, I
guess I need to see if there's a property that tells me if the
transaction is still active so I can have an if...then statement in
the exception handler.

Quote from: SlackBrane on December 21, 2008, 04:48:32 amleaving things
Open seems like a bad idea so having played with the database I'd be
inclined to close the connectionYou're right, especially as I've told
it not to time out (though I guess the server can detect if a
connection has been dropped).



Thanks again.