Home » Community » Newbie corner » Adding a records through a SqlArray with duplicated columns
Adding a records through a SqlArray with duplicated columns [message #50220] |
Tue, 28 August 2018 10:16 |
Giorgio
Messages: 218 Registered: August 2015
|
Experienced Member |
|
|
Hi there,
I'm developing an application that uses a PostGresql DB. In the DB there are (among others) one table that contains details of manufacturing machines and another that contains producible items. I have just introduced a third table, that contains what items a machine can produce. Below a snippet from my .sch file regarding those tables.
TABLE_ (MACHINES)
STRING (IDMACHINE, 5) PRIMARY_KEY
STRING_ (NAME, 20)
END_TABLE
TABLE_ (ITEMS)
STRING (IDITEM, 2) PRIMARY_KEY
STRING_ (DESCRIPTION, 20)
END_TABLE
TABLE_ (ITEMS_ALLOWED)
SERIAL (ID) PRIMARY_KEY
STRING (IDMACHINE, 5) REFERENCES(MACHINES.IDMACHINE)
STRING (IDITEM, 2) REFERENCES(ITEMS.IDITEM)
END_TABLE
I show the third table (the one with the items each machine can produce) in a SqlArray. To help user to better understand the data, I show also the name of the machine and the description of the item. To show them in a SqlArray, I defined two converters. Below, the code for the first one, the second one works in the same way.
struct ConvMachine : Convert
{
Value Format(const Value &q) const
{
Sql sql;
sql * Select(NAME).From(MACHINES).Where(IDMACHINE == q);
String name;
if(sql.Fetch())
name = sql[NAME];
else
descrizione = "";
return q.IsNull() ? Null : name;
}
};
The SqlArray that manages the tables is defined as below.
EditString item, machine;
...
tbl.SetTable(ITEMS_ALLOWED, ID);
tbl.SetOrderBy(IDMACHINE, IDITEM);
tbl.AddColumn(IDMACHINE, t_("ID"),15).Edit(machine);
tbl.AddColumn(IDMACHINE, t_("Machine"), 40).SetConvert(Single<ConvMachine>());
tbl.AddColumn(IDITEM, t_("ID"),10).Edit(item);
tbl.AddColumn(IDITEM, t_("Item"), 100).SetConvert(Single<ConvItem>());
tbl.AppendingAuto().Removing().AppendLine();
tbl.Query();
The data are showed correctly, the problem is when I try to add a new record.
The sql code produced is:
insert into ITEMS_ALLOWED(IDMACHINE, IDMACHINE, IDITEM, IDITEM) values (NULL, NULL, NULL, NULL)
That triggers the following error:
ERROR ERROR: column "idmachine" specified more than once
I think that is a really common situation, how can I fix this?
Regards,
gio
|
|
|
|
Goto Forum:
Current Time: Thu Apr 18 14:25:19 CEST 2024
Total time taken to generate the page: 0.01499 seconds
|