IBX - IBDataSet - table without primary key - how to put parameters?

[Nicole]

There is a form, which shall allow the user to edit a database-table.

The table looks like this
date (datum) and good (ware) = unique // price
e.g.
23.3.2023  --- -XX  --- -3300
This means, for  XX there must not be another price at this date.

This table has no primary key.
The user shall be able to search it by date and by goods. For this he will find edit fields and checkboxes.
This works fine.

My problem:
How to parse parameters, when the statement is NOT the selectSQL one?
Below is my code, looks complex, but it is not.
The problem is only the last 2 statements, which are above "active:=true":
Insert must have a parameter date and good, which must be unique.
How to say "ParamByName" in this case?
How to say it that the insert parameter does not disturb the select parameter?

 

Code: Pascal  [Select][+][-]

1. //___________ für die Tabelle tbmargins   => 2
2.   If (RadioGroup_bearbeiten.ItemIndex = 2) then begin
3.     ware_:='keine';
4.     DBNavigator_Bearbeite.DataSource:=DataSource_bearbeite;
5.  
6.     if RadioGroupEx_bearbeiteWare.ItemIndex <> -1 then
7.       ware_:=RadioGroupEx_bearbeiteWare.Items.strings[RadioGroupEx_bearbeiteWare.ItemIndex];
8.     s:='select * from tbmargins';
9.     If ware_ <> 'keine' then begin
10.        s:=s + ' where WARE = :ware_';  // das muss ich vermutlich als parameter übergeben
11.  
12.     end;
13.    if CheckBox_DatumBegrenzen.Checked then begin
14.       If ware_ <> 'keine'  // je nachdem, ob die Waren abgefragt wird
15.         then s:=s +' and'    // erfolft das Statement mit "and" oder "where"
16.         else s:=s + ' where';
17.       s:=s + '  (datum > :anf) and (datum < :ende)';  // Sortierung: AUFsteigen!
18.       end;
19.     s:=s + ' order by datum desc';
20.     IBDataSet_bearbeite.SelectSQL.Text:=s;
21.     If ware_ <> 'keine' then
22.         IBDataSet_bearbeite.ParamByName('ware_').AsString:=ware_; // this is the line, where "good" is parsed
23.     if CheckBox_DatumBegrenzen.Checked then begin
24.        IBDataSet_bearbeite.ParamByName('anf').AsDateTime:=DateTimePicker_Anfang.Date;
25.        IBDataSet_bearbeite.ParamByName('ende').AsDateTime:=DateTimePicker_Ende.Date;
26.       end;
27.  
28.     IBDataSet_bearbeite.ModifySQL.Text:='Update TBMARGINS Set MARGIN = :MARGIN, DATUM = :DATUM where ware = :ware and datum = :datum';
29.     IBDataSet_bearbeite.RefreshSQL.Text:=IBDataSet_bearbeite.SelectSQL.Text;
30.     IBDataSet_bearbeite.DeleteSQL.Text:='Delete From TBMARGINS where ware = :ware and datum = :datum';
31.     IBDataSet_bearbeite.InsertSQL.Text:='Insert Into TBMARGINS (WARE, MARGIN, DATUM) Values(:WARE_, :MARGIN, :DATUM)';
32.  
33.     IBDataSet_bearbeite.Active:=true;
34.   end;  
35.  
36.  
37.  
38.  

[egsuh]

In InsertSQL statement,   i.e.  'Insert Into TBMARGINS (WARE, MARGIN, DATUM) Values(:WARE, :MARGIN, :DATUM)',
:Ware, :Margin, :Datum are values from TDataSet (in this case TIBDataSet).

If datum and ware is unique, then define a unique index with those fields. Without index, your table may have many rows with the same Datum and Ware.

Or, you may define both UpdateSQL and InsertSQL as following:

Update or Insert Into TBMARGINS (WARE, MARGIN, DATUM) Values(:WARE, :MARGIN, :DATUM) Matching (Ware, Datum)



 

[Nicole]

Thank you for your reply.

What I did not understand: The definition of "unique" of those field combinations does not work as such? I am not at the shell where I did it. I used FlameRobin and clicked some properties there (cannot remember exactly which).

Or does such an index exist in the depths of my database and was generated by FlameRobin automatically? (Firebird 4)

[egsuh]

In the FlameRobin, open the table's property window, click "constaints". You may add primary key or unique constraints there.

[Nicole]

In the FlameRobin, open the table's property window, click "constaints". You may add primary key or unique constraints there.

I probably did this that time.
Is this additional key added by FlameRoboin automatically?

Thank you egsuh for the code, this works fine, - if the user is with us.
Unfortunately I see an ugly error-message now, if the user did not choose to insert a date and wonder, if I should not use an DBedit instead. If the user did not choose a date, no date is parsed in the query and the unique cannot be checked for. So I had to delete the query not to damage my DB. And empty query leads to an error-message.

Is there a way to "catch" such a case, before the DBNavigator tries to commit?

[egsuh]

Primary key does not mean a separate field. It is just one of indices. Indices can be defined on one or more fields of a table. If you have defined a primary key in FlameRobin using fields of ware and datum, then there exists a primary key, probably named PK_tablename_0.  Look into "IndexName" field in the property editor of TIBDataset. Probably there would be PK_tablename_0 or similar name to select.

DBNavigator does not do anything important. Its buttons simple call TDataSet's prev, next, post, cancel, etc. methods. "Post" is done at TDataSet component (TIBDataset in this case). You have to focus on TIBDataSet, not TDBNavigator.

If fields of WARE and DATUM are defined as "Not Null", inserting or updating null values into these fields will raise exceptions. You may avoid these errors by :
  - defining default values
  - defining afterinsert/beforepost event handlers not to try insert null values
at TDataSet level.

Updating changes at the TDataSet level to FB database is done by calling ApplyUpdates. Here insertSQL / UpdateSQL / DeleteSQL work.

[Nicole]

Thank you for the explanation.

And yes: I found such an index by FlameRobin in the Tab Indices.