[solved] Firebird: Copy table-entry and change only one value
lazarus / 16-Databases

----------------------------------------------------------------------
Nicole · 2023-09-20 09:46

Firebird / IBX

I want to copy an entry of a table and change ONE value of it. This is
harder, than I thought. At least for me.

The working select statement for this entry reads

Code: MySQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i select
  name_, FK_KONTRAKT,  Entry_price,  exit_Price, Entry_Signal,
Entry_Typ, Exit_Signal, Exit_Typ,  Position_, entry_date, entry_Time,
exit_date, exit_time,  Status_, comm, stop, target, best, worst, PL, 
Ergebnis, Margin, zuKonto  from tbtrades  where id_trade=355
What whall be changed is the value "zuKonto"

Within a method I prepared these 2 lines:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
IBQueryGeneric.ParamByName('zuKonto').AsInteger:=kto_; 
IBQueryGeneric.ParamByName('id').AsInteger:=id_;
There may be 2 ways to solve it:
1)
 copy it and change only the value zuKonto in the copy data-entry. The
challenge for me is to return the new primary key, which is "id_trade"

2) OR I can use the select statement to fill in new values into it by
"INSERT". Unfortunately I do not know how to do select and set at the
same time.

Thank you for hints.

----------------------------------------------------------------------
korba812 · 2023-09-20 10:32

You can use the "insert into select" statement along with the
parameters:

Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i INSERT INTO
tbtrades (id, name_, FK_KONTRAKT, Entry_price, exit_Price,
Entry_Signal, Entry_Typ, Exit_Signal, Exit_Typ, Position_, entry_date,
entry_Time, exit_date, exit_time, Status_, comm, stop, target, best,
worst, PL, Ergebnis, Margin, zuKonto)SELECT         :id, name_,
FK_KONTRAKT,      Entry_price,      exit_Price, Entry_Signal,
Entry_Typ, Exit_Signal, Exit_Typ,      Position_, entry_date,
entry_Time, exit_date, exit_time,      Status_, comm, stop, target,
best, worst, PL,      Ergebnis, Margin, :zuKonto          FROM
tbtrades      WHERE id_trade=355 Then set the parameters (id, zuKonto)
and execute SQL.

----------------------------------------------------------------------
Nicole · 2023-09-20 15:00

Thank you for the answer.
This would work as overwrite the existing entry, but not to copy it
into a new one.
The problem is the id_trade (primary key).

Instead of generating a new id_trade, I read the message "id_trade
already exists".
To avoid the confusion by the typo id instead of id_trade I copy the
statement again.
This is ok of the syntax, but works as "copy into the existing one"
instead of "copy it and generate a new id for this copy"

Code: MySQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i INSERT INTO
tbtrades (id_trade, name_, FK_KONTRAKT, Entry_price, exit_Price,
Entry_Signal, Entry_Typ, Exit_Signal, Exit_Typ, Position_, entry_date,
entry_Time, exit_date, exit_time, Status_, comm, stop, target, best,
worst, PL, Ergebnis, Margin, zuKonto)SELECT         id_trade, name_,
FK_KONTRAKT,      Entry_price,      exit_Price, Entry_Signal,
Entry_Typ, Exit_Signal, Exit_Typ,      Position_, entry_date,
entry_Time, exit_date, exit_time,      Status_, comm, stop, target,
best, worst, PL,      Ergebnis, Margin, zuKonto          FROM
tbtrades      WHERE id_trade=355

----------------------------------------------------------------------
korba812 · 2023-09-20 15:14

In your example, you actually add a record with the same primary key
(id_trade). You need to generate a unique key for the id_trade field
and supply it as a parameter (as in my example) or omit this field if
you have a trigger that generates the primary key value.

Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i     INSERT
INTO tbtrades (id_trade, name_, FK_KONTRAKT, Entry_price, exit_Price,
Entry_Signal, Entry_Typ, Exit_Signal, Exit_Typ, Position_, entry_date,
entry_Time, exit_date, exit_time, Status_, comm, stop, target, best,
worst, PL, Ergebnis, Margin, zuKonto)    SELECT    /* param -> */
:id_trade, name_, FK_KONTRAKT,          Entry_price,         
exit_Price, Entry_Signal, Entry_Typ, Exit_Signal, Exit_Typ,         
Position_, entry_date, entry_Time, exit_date, exit_time,         
Status_, comm, stop, target, best, worst, PL,          Ergebnis,
Margin, /* param -> */ :zuKonto                  FROM tbtrades       
 WHERE id_trade=355
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i IBQueryGene
ric.ParamByName('zuKonto').AsInteger:=kto_;IBQueryGeneric.ParamByName(
'id_trade').AsInteger:= NewUniqueId;

----------------------------------------------------------------------
rvk · 2023-09-20 15:31

Quote from: Nicole on September 20, 2023, 05:00:33 pmInstead of
generating a new id_trade, I read the message "id_trade already
exists".
And if your id_trade is an autogenerated key (generated by a trigger
and generator or sequence) you can just omit that field.
In that case the trigger will see the field is null (not provided) and
generate a new value.

(you can still do "WHERE id_trade = xxx" even if you don't use the
value for INSERT.)

----------------------------------------------------------------------
Nicole · 2023-09-20 15:32

thank you for your reply.

on
IBQueryGeneric.ParamByName('id_trade').AsInteger:= NewUniqueId;
I read:

"Identifier not found"

Within my FlameRobin (oh dear, it is years ago, I wrote this! I cannot
remember any more, how it works)
if found a

before insert which reads
Code: PHP  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i asbegin  if
(new.id_trade is null) then    new.id_trade =
gen_id(gen_tbtrades_id,1);end
I better not touch it, I will destroy it and never get it back.

----------------------------------------------------------------------
rvk · 2023-09-20 15:37

Quote from: Nicole on September 20, 2023, 05:32:39 pmon
IBQueryGeneric.ParamByName('id_trade').AsInteger:= NewUniqueId;
I read:

"Identifier not found"
Show the complete code then.

But as I said before, you have a trigger for NULL, so you can omit the
id_trade in the SELECT/INSERT.

You can also just pass NULL as SELECT value for id_trade.

Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i INSERT INTO
tbtrades (id_trade, name_, FK_KONTRAKT, Entry_price, exit_Price,
Entry_Signal, Entry_Typ, Exit_Signal, Exit_Typ, Position_, entry_date,
entry_Time, exit_date, exit_time, Status_, comm, stop, target, best,
worst, PL, Ergebnis, Margin, zuKonto)    SELECT    /* constant -> */
NULL, FK_KONTRAKT,          Entry_price,          exit_Price,
Entry_Signal, Entry_Typ, Exit_Signal, Exit_Typ,          Position_,
entry_date, entry_Time, exit_date, exit_time,          Status_, comm,
stop, target, best, worst, PL,          Ergebnis, Margin, /* param ->
*/ :zuKonto                  FROM tbtrades          WHERE id_trade=355
You see that you can just change one value in the SELECT, it doesn't
need to be a field value, you can also pass a constant in the SELECT.

----------------------------------------------------------------------
korba812 · 2023-09-20 15:43

Quote from: Nicole on September 20, 2023, 05:32:39 pmthank you for
your reply.

on
IBQueryGeneric.ParamByName('id_trade').AsInteger:= NewUniqueId;
I read:

"Identifier not found"
This is pseudo code and not an actual function. I meant that you have
to pass a new unique id as a parameter. You need to generate it
beforehand (e.g. using gen_id)

----------------------------------------------------------------------
Nicole · 2023-09-20 16:26

Thank you for all answers.

The version with NULL works now!
Love it.