Lazarus Database Tutorials
lazarus / 16-Databases

----------------------------------------------------------------------
guest48180 · 2012-12-05 22:05

As BigChimp will attest to, I didn't follow the tutorials step by step
when I first started them. And that gave me a lot trouble, mostly a
complete lack of understanding. So I went back and did them the right
way, and things were looking good as they were explained very well. I
did the tutorials using a db I created, one that I have interest in
(it has two fields in it: a DB_KEY generated by Flamerobin, and NAME 
(it has seven entries)), and ultimately had issues with the db
updating. The DBNavigator seems useless to edit, delete, or post
records. The refresh works fine though.
If i click into a cell to change the spelling of a name, let's say,
then click edit on the navigator bar, do my edit and click post, then
refresh, the editing is gone and field is back to the way it was when
before I edited it. Crazy, that.
And if I do the same thing again except not refresh the DBGrid and
instead click the "Show Data" button, I get an error:

An error occured while applying the updates in a record: SQLQuery1 :
No update query specified and failed to generate one. (No fields for
inclusion in where statement found).

Here's the code.

<code>
implementation

{$R *.lfm}

{ TForm1 }

procedure TForm1.SaveChanges;
// Save edits done by the user, if any.
begin
   try
      if SQLTransaction1.Active then
      // Only if we are within a started transaction...
      // otherwise you get "Operation cannot be performed
      // on an inactive dataset.
      begin
         SQLQuery1.ApplyUpdates; // Pass user-generated changes back
to database
         SQLTransaction1.Commit; // ...and commit them using the
transaction.
         // SQLTransaction1.Active now is False.
      end;
   except
     on E: EIBDatabaseError do
        begin
           MessageDlg('Error', 'A database error has occured
(SaveChanges Procedure). Technical error message: ' +
                             E.Message, mterror, [mbOK], 0);
           editSQLParam.Text:= '';
        end;
   end;

end;

procedure TForm1.DBGrid1KeyUp(Sender: TObject; var Key: Word; Shift:
TShiftState
    );
begin
   // Check for Delete key being hit and delete the current response
   // as long as we're not editing data (in Editor Mode).
   if (key=VK_DELETE) and (not(DBGrid1.EditorMode)) then
   begin
      // delete current record and apply updates to db
      SQLQuery1.Delete;
      SQLQuery1.ApplyUpdates;
   end;
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
   SaveChanges; // Saves changes and commits transaction
   try
        SQLQuery1.Close;
        if not IBConnection1.Connected then
        begin
        IBConnection1.UserName:= editUsername.Text;
       IBConnection1.Password:= editPassword.Text;
       IBConnection1.HostName:= editHost.Text;
       IBConnection1.DatabaseName:= editDatabaseName.Text;
        // Now that we've connected, we set the Edit Boxes
         // to ReadOnly so no further changes can be made.
         editHost.ReadOnly:= True;
         editDatabaseName.ReadOnly:= True;
         editUsername.ReadOnly:= True;
         editPassword.ReadOnly:= True;
        end;
        // Show all customer names, or filter for one
        if editSQLparam.Text = '' then
            SQLQuery1.SQL.Text:= 'select * from customer order by
name'
        else
        begin
         SQLQuery1.SQL.Text:= 'select * from customer where name =
:name';
         SQLQuery1.Params.ParamByName('name').AsString:=
editSQLparam.Text;
        end;
        IBConnection1.Connected:= True; //By default, SQLQuery1.Close
sets this to True
        SQLTransaction1.Active:= True;  //By default, SQLQuery1.Close
sets this to True
        SQLQuery1.Open;
      {
       Make sure we don't get problems with inserting blank (=NULL)
CUST_NO values, e.g.:
       Field CUST_NO is required, but not supplied
         We need to tell Lazarus that, while CUST_ID is a primary key,
it is not required
       when inserting new records.
        }
      // SQLQuery1.FieldByName('cust_id').Required:= False;
      // DBGrid1.Columns[0].Visible:= False; // Hide the cust_id from
the user as it is
                                          // the Primary Key
   except
     on E: EIBDatabaseError do
     begin
       MessageDlg('Error', 'A database error has occure. Technical
error message: ' +
                         E.Message,mtError,[mbOK],0);
       editSQLParam.Text:= '';
     end;
   end;
end;

procedure TForm1.FormClose(Sender: TObject; var CloseAction:
TCloseAction);
begin
   SaveChanges; // Save changes and commits transaction
   SQLQuery1.Close;
   { *************************************************
     I have omitted the next two lines of code because
       SQLQuery1.Close sets them to False anyway...
    SQLTransaction1.Active:= False;
         IBConnection1.Connected:= False;
   *************************************************** }
end;

end.
</code>

If anyone sees an error, please advise. This doesn't seem to be
difficult at all, but I can't make it work...even after moving step-
by-step through the tutorials. And I've been chopping at this tree all
morning and ain't put a dent in it yet. It has me so frustrated that I
downloaded QT earlier just to see if if using databases on it was
easier. Thing is, I like using Pascal...and I really like Lazarus. I
sure hope someone can help me with this.

Landslyde

----------------------------------------------------------------------
denver · 2012-12-05 22:51

For the component SQLQuery1 , it has the following Properties you have
to fill-in :

DeleteSQL
UpdateSQL
InsertSQL

Otherwise SQLQuery1 don't konw how to Update / Insert  / Delete a
record.

----------------------------------------------------------------------
BigChimp · 2012-12-06 07:57

You say "Flamerobin generates a DB_KEY column". Well that is sort of
correct. It is actually generated by Firebird.
Whatever the case, it normally is never used - it is a hidden key (see
e.g. http://www.firebirdfaq.org/faq301/) Flamerobin will only use it
to distinguish rows if there is no primary key defined in the column.

You probably don't have a DB_KEY column defined in that table but just
a NAME, without any primary key. Please define a primary key. The
tutorials work with autonumber/generated primary keys: integer primary
keys that in Firebird are backed by a generator/sequence and trigger.
See the employee.fdb sample database for details or you can add such a
PK column to an existing table quite easily in Flamerobin.

Without a primary key, FreePascal has no idea which row to update when
you edit the grid, therefore Denver's advice that you need to use
UpdateSQL etc is completely correct as you will need to spell it out
for FreePascal.
However, that will require fiddling with RDB$_KEY, or not accepting
duplicate NAME values or something. Much easier to just add a primary
key and not having to specify UpdateSQL etc.

----------------------------------------------------------------------
BigChimp · 2012-12-06 08:47

Adding a generated/sequence/"autonumber" primary key (column named ID
in this example):

1. In Flamerobin:
1.1 go to the table properties, summary, go to the fields, add an ID
field if it doesn't exist, integer datatype, check NOT NULL; otherwise
view details/edit ID field.
1.2 In the properties windows for ID, in the autoincrement section:
select "Create new generator". Leave the name as is. Check Create
trigger. Execute/apply etc
1.3 Add primary key constraint: go to the table properties,
constraints, add primary key, name it what you want, select the ID
column (which should have the

2. In SQL code (assuming table called NOPK) and an existing ID integer
not null field:
Code: [Select]SET TERM ^ ;
CREATE TRIGGER NOPK_BI FOR NOPK ACTIVE
BEFORE INSERT POSITION 0
AS
DECLARE VARIABLE tmp DECIMAL(18,0);
BEGIN
  IF (NEW.ID IS NULL) THEN
    NEW.ID = GEN_ID(GEN_NOPK_ID, 1);
  ELSE
  BEGIN
    tmp = GEN_ID(GEN_NOPK_ID, 0);
    if (tmp < new.ID) then
      tmp = GEN_ID(GEN_NOPK_ID, new.ID-tmp);
  END
END^
SET TERM ; ^

ALTER TABLE NOPK ADD CONSTRAINT PK_NOPK PRIMARY KEY (ID);

----------------------------------------------------------------------
guest48180 · 2012-12-06 16:55

Denver, BigChimp, I appreciate both of your inputs. I will look harder
into this per your suggestions. I know this isn't as difficult as I'm
making it to be...and I apologize for seeming to be the student of
chaos to the world of programming databases    This is the only part
of all I do that is giving me fits and sleepless nights. Even my Irish
Setter pup is unsure of me these days    But I feel all this is slowly
but surely coalescing into something that's beginning to make sense.
And I really enjoyed going through the tutorials, Reinier. A lot of
questions were answered by me just taking my time and doing what you
asked me to do. Thanks.

Landslyde

----------------------------------------------------------------------
guest48180 · 2012-12-06 17:03

By the way, BigChimp, how do you get you code to come up in the
scrollable window? I like that

----------------------------------------------------------------------
BigChimp · 2012-12-06 17:17

Thanks.

re the code: surround your code with code tags
begin:
[ followed by code followed by ]
end
[ followed by / followed by code followed by ]
or select the text & press the button next to the quotation button
with a # on it.