Auto-updateable results?

upscene · Post by **upscene** » Tue 01 Mar 2005 10:03

Hi,

It seems that all my resultsets are read-only by default. According to the ODAC help, you need to fill in "SQLInsert" and "SQLDelete" (etc) properties.

However, with my particular application, users will enter ad-hoc statements and expect them to be editable.

Is there any way around this?

--
Martijn Tonies
Upscene Productions

Alex · Post by **Alex** » Tue 01 Mar 2005 10:17

Try to add RowId field to your SQL statement, for ex:

Code: Select all

Select E.RowId, E.* from Emp E

upscene · Post by **upscene** » Tue 01 Mar 2005 11:36

Alex,

As I said - I am not entering those SQL statements...

Can't the component set figure out any "keyfields" like it does when you drop down the "KeyFields" property in the Object Inspector?

--
Martijn Tonies
Upscene Productions

Alex · Post by **Alex** » Tue 01 Mar 2005 13:54

ODAC doesn't have public functionality that you need but you can use
following
SQL statement to get key fields of certain table:

Code: Select all

 SELECT
 Column_Name Col, Con.Constraint_Name Con
 FROM
 All_Constraints Con, All_Cons_Columns Col
 WHERE
 Con.Owner = :Owner and
 Con.Owner = Col.Owner and
 Con.Table_Name = :Name and
 Con.Constraint_Type in ('P', 'U') and
 Con.Owner = Col.Owner and
 Con.Constraint_Name = Col.Constraint_Name
 ORDER BY
 Con.Constraint_Name,Col.Position

where parameter :Owner fits to current schema name (e.g.: SCOTT)
and parameter :Name fits to updatable table name (e.g.: EMP)
Note that all parameters must be in upper case.

upscene · Post by **upscene** » Tue 01 Mar 2005 15:01

Alex,

I know how to get the PK fields and all... But that's not what I'm asking...

I'm asking for a resultset that's editable without having me to jump through hoops, but let the componentset figure it out. If it cannot figure it out, a read-only resultset is fine.

But currently, the components don't seem to do anything to create an auto-updateable resultset, which is rather, well, annoying.

--
Martijn Tonies
Upscene Productions

Paul · Post by **Paul** » Wed 02 Mar 2005 09:19

TSmartQuery does not make auto-updateable resultset for user defined SQL statements. It is impossible to make for each SQL statement. You must choose key fields and assign to TSmartQuery.KeyFields property to make resultset updateable. There is no good algorithm for defining key fields. So users must define them themselves and assign TSmartQuery.KeyFields property.

upscene · Post by **upscene** » Wed 02 Mar 2005 12:04

Paul,

I must say this surprises me a bit...

I use many different data-access componentsets and they all try to make a result-set editable.

If it's a

Code: Select all

select ... from single_table

it's fairly easy. The componentset can figure out any PK or Unique constraint fields and use those. If not, ROWID could get added internally.

If it's a more complex select (join etc), it cannot be done. Which is fine. But at least the "easy" stuff, IMO, is a task for the data-access layer.

--
Martijn Tonies
Upscene Productions

Paul · Post by **Paul** » Wed 02 Mar 2005 14:54

I understand you. We refused this decision because, it is needed an execution of additional statement for receiving key fields for Oracle (as opposed to MySQL or MSSQL). It takes some time and is not necessary for TSmartQuery in many cases.

upscene · Post by **upscene** » Wed 02 Mar 2005 16:41

Well, could you make the behaviour optional, turned OFF by default then?

I know it would save me time, and probably others as well.

If I remember correctly, this is what the "normal" dataset (TSQLDataset) in Delphi do as well.

btw, the Allround Automation components act the same -> they do not automatically create an editable resultset, but the resultset does become editable if you add "ROWNUM" to the list of selected fields.

--
Martijn Tonies
Upscene Productions