3

I'm working with Delphi 7 and Firebird database. I'm using TIBDatabase, TIBTransaction, TIBQuery, TIBDataSet and DBGrid to establish connection and provide user interface for working with table. In my database I have two tables:

Ships
fields
Id integer
Name varchar(20)
Type_Id(Fk) integer
Longth integer

Ship_types
fields
Id(Pk) integer
Ship_type varchar(10)

So resulting dataset which I get through "join" query has such fields

Name
Type
Longth

Type is Ship_type field from Ship_types table joined via query by Type_Id foreign key to this table from Ships table.

Data is displaying properly.

Then I need to edit my data directly through DBGrid. For this purpose I use TIBUpdateSQL component. For displaying Type(lookup) field I chose DBGrid.Columns.PickList property.

So my question is how can I make TIBUpdateSQL work with such type of field? Cause I know that if it would be single table without foreign keys I have to write update statement into ModifySQL property of update component. But what have I do with fk fields? Can I write update join statement in UpdateSQL component or, if not, what else way I can do it?

I don't need to update two tables, I just need to update only Ships table but there is varchar(word representation) field in displaying dataset and in updating dataset it must be integer(corresponding id) to suit to table structure.

Editor in TIBUpdateSQL isn't solution for me cause I'm assigning query to TIBQuery on runtime.

StableUpper
  • 153
  • 1
  • 1
  • 8
  • 1
    Well, actually this is not Firebird question, but CRUD design. But answer is to use DBLookupFields (not PickList) directly in DBGrid for select ship type. – rstrelba May 20 '15 at 20:49

1 Answers1

3

You can't update tables using select with JOIN, only with subselects.

Sub-select example: 

SELECT TABLE_NAME.*
     , (SELECT TABLE_NAME2.NAME FROM TABLE_NAME2 WHERE TABLE_NAME2.ID = TABLE_NAME.ID)
FROM TABLE_NAME
ghhernandes
  • 63
  • 4