Forum > Databases
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 [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---//___________ für die Tabelle tbmargins => 2 If (RadioGroup_bearbeiten.ItemIndex = 2) then begin ware_:='keine'; DBNavigator_Bearbeite.DataSource:=DataSource_bearbeite; if RadioGroupEx_bearbeiteWare.ItemIndex <> -1 then ware_:=RadioGroupEx_bearbeiteWare.Items.strings[RadioGroupEx_bearbeiteWare.ItemIndex]; s:='select * from tbmargins'; If ware_ <> 'keine' then begin s:=s + ' where WARE = :ware_'; // das muss ich vermutlich als parameter übergeben end; if CheckBox_DatumBegrenzen.Checked then begin If ware_ <> 'keine' // je nachdem, ob die Waren abgefragt wird then s:=s +' and' // erfolft das Statement mit "and" oder "where" else s:=s + ' where'; s:=s + ' (datum > :anf) and (datum < :ende)'; // Sortierung: AUFsteigen! end; s:=s + ' order by datum desc'; IBDataSet_bearbeite.SelectSQL.Text:=s; If ware_ <> 'keine' then IBDataSet_bearbeite.ParamByName('ware_').AsString:=ware_; // this is the line, where "good" is parsed if CheckBox_DatumBegrenzen.Checked then begin IBDataSet_bearbeite.ParamByName('anf').AsDateTime:=DateTimePicker_Anfang.Date; IBDataSet_bearbeite.ParamByName('ende').AsDateTime:=DateTimePicker_Ende.Date; end; IBDataSet_bearbeite.ModifySQL.Text:='Update TBMARGINS Set MARGIN = :MARGIN, DATUM = :DATUM where ware = :ware and datum = :datum'; IBDataSet_bearbeite.RefreshSQL.Text:=IBDataSet_bearbeite.SelectSQL.Text; IBDataSet_bearbeite.DeleteSQL.Text:='Delete From TBMARGINS where ware = :ware and datum = :datum'; IBDataSet_bearbeite.InsertSQL.Text:='Insert Into TBMARGINS (WARE, MARGIN, DATUM) Values(:WARE_, :MARGIN, :DATUM)'; IBDataSet_bearbeite.Active:=true; end;
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:
--- Quote from: egsuh 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.
--- End quote ---
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?
Navigation
[0] Message Index
[#] Next page