The trick is do be done if the chooses one of those choices e.g.:
e.g. 'Table "Newslist" ' and "start from date "1.2.2023" ' (different table!)
Then the Select-statement has to be changed. This is done nicely, the DBGrid is rewritten.
Never the less, the user-changes do only work, if the first table is chosen.
You
also need to change the ModifySQL, InsertSQL and the DeleteSQL statements (in code manually).
So you need to change all 4 (or 5 if you include a refreshsql) statements.
If the queries are simple (just select) you can do that with a function.
I have a function CreateUpdateQueries() which I feed a tIbQuery, a tablename and a key).
The function creates the modifysql, insertsql and deletesql for me.
It does this by opening up a separate tIbQuery with open command and looping though all the fields and building the correct statements.
But you can set them hard in code too yourself (it's just a bit more work).
Yes, if you use TIBDataset (or TIBQuery + TIBUpdateSQL combo) you need to set the UPDATE and DELETE yourself.
This "combo" I found in the manual and was excited about.
This combo of TIBQuery + TIBUpdateSQL is essentially the same as TIBDataset (which has the ModifySQL, InsertSQL and DeleteSQL already in it).
I'm not sure about other differences.
Where the "select"-statement is adjusted for the user-needs, I would adjust the UPDATE as well.
May I just copy the suggested SQL-code from the editor for the appropriate table? Or will I have to add the user choice with the where-clause as well?
And is it correct, only to change the ModifySQLß?
Yes, that's how you do it. And you need to change ModifySQL, InsertSQL and DeleteSQL as well.
But the chance always exists that you catch multiple records.
OMG!
This is the worst case, that my data become wrong.
Will the definition of a primary key help against this?
Yes, the primary key in combination with the correctly written ModifySQL and DeleteSQL. Because if you supply the primary key in the Dataset editor, it will automatically suggest you add the WHERE ID=:ID or WHERE ID=:OLD_ID to your SQL (which prevents any record but the one with that ID to be changed).
Not sure, if this is done and replaces your text above. I do not understand it really.
To make a primary key, I usually go to FlameRobin and click cluelessly around for half an hour until there is one by good luck.
You can. But it should be part of the database DDL eventually.
(The database DDL is the definition of your tables:
https://en.wikipedia.org/wiki/Data_definition_language)
So you would have something like this:
CREATE TABLE CUSTOMERS (
ID BIGINT NOT NULL PRIMARY KEY,
CODE VARCHAR(20), /* short search code */
NAME VARCHAR(50),
...
)
A primary key would also need a generator (or sequence in newer versions).
CREATE SEQUENCE GEN_CUSTOMERS;
and a trigger to automatically increase a generator when 0 or NULL is given:
CREATE TRIGGER TR_CUSTOMERS FOR CUSTOMERS ACTIVE BEFORE INSERT POSITION 0 AS
BEGIN
IF ((NEW.ID IS NULL) OR (NEW.ID = 0)) THEN
NEW.ID = GEN_ID(GEN_CUSTOMERS, 1);
END
you also need to set the ModifySQL, InsertSQL and DeleteSQL.
ModifySQL and Update are the same?
Yes.
Do you think, it is the better performance to change all of these or to have several IBDataSets which I let point at their table at designtime?
That depends on your preference.
If the number of tables is set and the options for the user limited, you can use multiple TIBDataset.
If the number (and complexity) becomes greater, you might want to use one, and change the SQL's dynamically.
Are those records not used anywhere else in your program, or linked to other tables?
Everything was fine by good luck and there shall not be any problem.
I am long enough in programming, that I know, how quickly problems occur, that "shall not" occur.
I will go ahead and click half an hour in FlameRobin to make one field primary key.
Do you not connect the tables together?
Are all the tables independent? They have nothing to do with each other?
If not, and you have customer, orders, countries etc, then you need to link them together via a foreign key.
The upside of that is that you can't delete a customer if it still has orders.
Or you can't delete a country if that country is used with a customer.
You can even create relationships that if you delete a order, that all underlying order_detail records are deleted automatically.
Otherwise, when deleting an order, those records would remain orphaned in their table.
I hope you understand everything above a little bit
Recent
