Recent

Author Topic: IBX - IBDataSet - table without primary key - how to put parameters?  (Read 1240 times)

Nicole

  • Hero Member
  • *****
  • Posts: 970
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

  • Hero Member
  • *****
  • Posts: 1266
Re: IBX - IBDataSet - table without primary key - how to put parameters?
« Reply #1 on: March 23, 2023, 07:28:54 am »
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

  • Hero Member
  • *****
  • Posts: 970
Re: IBX - IBDataSet - table without primary key - how to put parameters?
« Reply #2 on: March 23, 2023, 08:22:53 pm »
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

  • Hero Member
  • *****
  • Posts: 1266
Re: IBX - IBDataSet - table without primary key - how to put parameters?
« Reply #3 on: March 24, 2023, 06:41:26 am »
In the FlameRobin, open the table's property window, click "constaints". You may add primary key or unique constraints there.

Nicole

  • Hero Member
  • *****
  • Posts: 970
Re: IBX - IBDataSet - table without primary key - how to put parameters?
« Reply #4 on: March 24, 2023, 03:08:33 pm »
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

  • Hero Member
  • *****
  • Posts: 1266
Re: IBX - IBDataSet - table without primary key - how to put parameters?
« Reply #5 on: March 25, 2023, 02:21:50 am »
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

  • Hero Member
  • *****
  • Posts: 970
Re: IBX - IBDataSet - table without primary key - how to put parameters?
« Reply #6 on: March 25, 2023, 06:37:30 pm »
Thank you for the explanation.

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

 

TinyPortal © 2005-2018