TIBTable for beginners

[Nicole]

My Flamerobin allows me to do a select statement of my choice. If I want to edit it, I just do it and the table values are changed.
My software shall do something similar without FlameRobin.
I studied the IBX manual and found interesting elements about TIBTable. I tried to do as described.
I added a TIBTable as TDataSource linked it to a TIBDynamicGrid and the function is exactly what I want.
The very table has 4 rows, this is nice to work with.
up to here: Worked fine.

In German we say: If a donkey feels too well, he will go to dance on ice.

So next: not-a-select * from tbMyTable, -  but a query with where.
Not a complex 'where', but a where.
I ended up, that all my rows were overwritten by the very one I had edited.
Not too happy about, because the database was shot by this action.

I would prefer to work with the TDBNavigatorBar (or similar).
But I have no idea, what happens in the background / which update-query is generated where.

How to make sure, that only the values are changed, I see in my TDynamicGrid as changed even if I use a "where"-condition?

[Zvoni]

Show your query that shoots all records, and show expected result

[Nicole]

There are queries for all purposes in this systematic: SelectSQL, UpdateSQL .... modify, refresh..

My select query itself will not update anything.
It is just select * from ... where...

WHAT did the update, - this I cannot post, - because this is my question.
I have no idea, how the Update-Queries are generated. This is my question as well.
The only answer I know up to now: "wrong" in my case, 

[korba812]

the "WHERE" condition should be simple for update query. In most cases, this is simply the record's primary key:

Code: SQL  [Select][+][-]

1. UPDATE MYTABLE SET FIELD1 = :FIELD1 ...
2. WHERE ID = :ID
3.  

[Nicole]

Thank you for the answer.

The question is not about SQL-Syntax, but about:
How does IBX create its various SQL properties together with the DBNavigation bar?

Yes, the above statement would work, if it would be "there", - where?
This is the next part of my question: Where and when do I have to key in SQL in which property? What is created automatically?

[korba812]

If I remember correctly update SQL is created automatically for TIBTable. Use TIBDataSet if you want to use your own SQL queries for update, insert, modify and refresh.

[Nicole]

Sure, yes.
And what about the overwritten rows?

[korba812]

This may be the result of an invalid sql query for update. Can you show what query you are using for update?

[Zvoni]

This may be the result of an invalid sql query for update. Can you show what query you are using for update?

What i asked for in my first reply.......

[Nicole]

We are coming together very slowly, but we do.

This query - where is it to find?
And not the query at design time, but the query which is built at runtime by the components.

I am not a beginner in SQL and queries, but in IBX and its properties and methods.
You have to know, how the components IBX and DBNavigator work internally to answer my question. How do they?!

I suspect, that it may have something to do with the fact, that the damaged table does not have a primary key. May even be, IBX has a bug here.

[rvk]

Uh, just one question @Nicole.
How many records do your tables have?

If it's a lot, then FORGET about TIBTable.
It has very poor performance and you loose the flexibility of SQL statements.

What was wrong with a simple TDBGrid?

In your openingspost you mention FlameRobin.
I can assure you FlameRobin doesn't work with a TIBTable.

If you look, you see that it creates a SELECT ALLFIELDS FROM TABLENAME in a Memo-component.
So that memo component contains the SQL statement.
You can edit it to your liking.
After that you click run-button and you get the result in a normal TDBGrid.
If not all records are visible, they are also not retrieved.
If you want to retrieve all records you need to do "fetch all" from the menu.
(This is equivalent to a TIBQuery.Last)

So, if you have a table with upto a hundred records and don't need to use JOIN etc. you can use TIBTable.

But for more complex report/list-generation don't use TIBTable.

I have a list-generator in my program. Users can select a table and a simple SELECT * FROM appears.
They can also select more complex SQL-statements with a range of different use-cases.
And they can also enter their own SELECT-SQL statement to create their own list (often with help from me).

--
Then about the writing of your INSERT and UPDATE statements (and your overwrite problem).
You mostly need to do this yourself. With TIBQuery you can generate them at design time but you need to check them.

An UPDATE and DELETE should always be done with a WHERE ID=:ID. Otherwise you UPDATE or DELETE records you don't want.
Those are simply just the SQL rules.

If you post the SELECT we can show you what the UPDATE and DELETE should be.

My suggestion would be to just stick with
TIBDatabase + TIBTransaction -> TIBQuery -> TDatasource -> TDBGrid
with a TDBNavigator.
And if you want to edit, you can use the TDBGrid directory or add some TDBEdit fields (but I usually do that in a separate form).

When selecting a table (of function in your program), set the TIBQuery.SQL correctly.
Also set the UPDATE and DELETE in the UpdateObject property (a TIBUpdateSQL object) if the edit fields are on the same form.
Make sure the UPDATE and DELETE are correctly set. You can rightclick the TIBUpdateSQL component and choose UpdateSQL Editor to get a helper form to create your UPDATE and DELETE SQL statements.

AND YES, Always create a primary key on all your tables.
That's just common sense in database design.

[korba812]

This query - where is it to find?
And not the query at design time, but the query which is built at runtime by the components.

You can't read UpdateSQL in TIBTable because it's not public. Just use TIBDataSet and enter your own queries for update, insert, delete and refresh.

I am not a beginner in SQL and queries, but in IBX and its properties and methods.
You have to know, how the components IBX and DBNavigator work internally to answer my question. How do they?!

Just like other database components - TDatSet -> TDataSource -> TDataLink or TFieldDataLink. Rather not directly related to IBX but to the DataSet architecture.

I suspect, that it may have something to do with the fact, that the damaged table does not have a primary key. May even be, IBX has a bug here.

Your table should have a primary key so that each record can be identified (this can be a multi-field key)

[Nicole]

Thank you so much for the answers.
Now I begin to understand.

The task is more complex than I wrote it here. There are a handful of tables, not only one.
I try to sum up table-group by table-group

1)
I have one frame-interface for a bundle of tables. For some I need the date, for others not. The user can choose various radio-groups of criteria he wants to change. I just modify the display of the interface by setting components visible and not and  - - now it comes - changing the selectSQL-text taken from the user's radio-group-selections. This makes, that the displayed selection is fine, but the update-attempts result into an error, because the table does not fit. One table has a certain field, the next not. SelectSQL is adjustet, ModifySQL not.

For this above group 1 I use a TDataset and IBDatasource

So have I understood it correctly, that I have to
- change not only the SelectSQL, but the ModifySQL as well? Anything else?
- best way to do this, is to use the build-in generation editor and change tables for every "wanted" one?
- these SQL-textes I assign to ModifySQL, at the click the user decides to edit a certain table (added where *)?

2)
The desaster-table, overwritten
It has not many rows, but only - 4.
Very important figures.
And no primary key. So I assume, that the value was valid for every single row and killed all of them.

In the meanwhile I just split this very table from the datacomponents, gave it an own panel and an own datasource and display it by TIBTable and TIBDynamicGrid.
This works fine. As there are only 4 rows, it is ok to see them all at once.

Why I like TIBDynamicGrid that much: It allows sorting. Even 4 rows sometimes are nicely to be sorted.

Is it bad style to work without primary key?
It would be easy to make one field a primary key. I did not even set anything to unique. May be this is bad style as well.
The table looks that small that I thought I - LOL - I would not make a mistake here.

What do you recommend?

[rvk]

1) This makes, that the displayed selection is fine, but the update-attempts result into an error, because the table does not fit. One table has a certain field, the next not. SelectSQL is adjustet, ModifySQL not.

You shouldn't define the columns in a TDBGrid.
They will be filled in automatically (if empty). So any SQL you set, the TDBGrid will follow automatically.
You might want to loop over the columns, after opening the TIBQuery, to set any special requirements (like color, width etc) but besides that, don't define them beforehand.

For this above group 1 I use a TDataset and IBDatasource

You probably mean TDataSource and TIBDataset 

Yes, if you use TIBDataset (or TIBQuery + TIBUpdateSQL combo) you need to set the UPDATE and DELETE yourself.

You can do this in designtime (right click and choose DataSet Editor). But if you want to change the SQL at runtime, you also need to set the ModifySQL, InsertSQL and DeleteSQL. But you could use the DataSet editor in designtime to find out how those should be. But empty them before running your program as you are going to fill those dynamically in runtime.

2) The desaster-table, overwritten
It has not many rows, but only - 4.
Very important figures.
And no primary key. So I assume, that the value was valid for every single row and killed all of them.

Yes, that's why you have a primary key. You could do it with WHERE FIELD1=:FIELD1 and FIELD2=:FIELD2 etc.
But the chance always exists that you catch multiple records.
That's why it's important to ALWAYS use a primary key (ID, Linknummer, IDNR or something else but make it consistent throughout your database design, so same ID-name for all tables).

That's also they way you connect tables together.
TABLE CLIENT
ID BIGINT

TABLE ORDERS
ID BIGINT
ORDERNR BIGINT
CLIENT_ID (foreign key linking to CLIENT.ID)

etc.

In the meanwhile I just split this very table from the datacomponents, gave it an own panel and an own datasource and display it by TIBTable and TIBDynamicGrid.
This works fine. As there are only 4 rows, it is ok to see them all at once.

With just 4 rows it's fine. But for more rows it's best to stick to TDBGrid (I don't know TIBDynamicGrid but if it's a TDBGrid descendant it should be fine).

Is it bad style to work without primary key?

Yes, defenitly.
You have experienced this yourself (by loosing important records).
If those records had an ID as primary key, the UPDATE and DELETE would have included WHERE ID=:ID and those records would have been fine.

It would be easy to make one field a primary key. I did not even set anything to unique. May be this is bad style as well.

A primary key is always unique.

The table looks that small that I thought I - LOL - I would not make a mistake here.

It's also best for small tables. What kind of data is in that table?
Are those records not used anywhere else in your program, or linked to other tables?

[Nicole]

You shouldn't define the columns in a TDBGrid.

This is not done. The user may click e.g. 'Table "Eventlist" ' and "start from date "1.1.2023" '
And I generate the query which displays this choice in the DBGrid.

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. The first table is the one, which helped me to generate the SQL-statements at desing-time. As I adjust the select-statement at runtime to point at the different table and the other SQL statements not,   - they remain pointing at the first table and do nothing more as they have learnt at design-time.

This is not very surprising.

The point is, - how to change this best?
Another way would be, not to change the SQL-statements but the Dataset.
(forgive me, I keep mixing up datasource, dataset nothing helps)

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.
Not sure, if I need a DELETE. Let me write the UPDATE first.
I plan to to this:
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ß?

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?

I am not good ad database connections and so everything is in a DB-Modul, where there is a transaction1, which is allowed to autocommit. So I do nothing with databases and transactions any more. The datasensitive component just points as the db-modul and I do not care any more.

That's also they way you connect tables together.
TABLE CLIENT
ID BIGINT

TABLE ORDERS
ID BIGINT
ORDERNR BIGINT
CLIENT_ID (foreign key linking to CLIENT.ID)

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.

I don't know TIBDynamicGrid

"Our" Tony wrote it!
Give it a try.

you also need to set the ModifySQL, InsertSQL and DeleteSQL.

ModifySQL and Update are the same?
Not sure, if I will provide an insert and a delete as well.
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?

So one idea is
table1 with ibDataSet1 / table2 with ibDataset2 / table 3 with ibDataset 3

the second idea is
IBdataset1 with Queryies for table 1 / IBdataset1 with Queryies for table 2 / IBdataset1 with Queryies for table 3

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.