Updateable VIEW

[inageib]

Hi,
I created a Firbird 2.5 view and I used TIBCQuery to select * from that view
now I want to be able to update that view.

I used the UpdateObject, drooped the components and used SQL generator to generate SQLs for for everything.

When I tried to delete a record I got error exception

Not found field corresponding parameter Old_Detail_ID.

where detail is a detail table for the table used in the view

Please advise

Thanks

[AndreyZ]

Hello,

The 'Not found field corresponding parameter Old_Detail_ID' error means that you are using the Old_Detail_ID parameter in your DELETE query, but there is no Detail_ID field in the view. Please make sure your view contains the Detail_ID field.

[inageib]

it seems when I use SQL generator I left all fields selected by default and pressed on "generate sql" button. now I modified this in the sql generator, I selected only fields that can be updated for insert/update/delete/lock then I select all fields and selected refresh check box only to generate SQL for refresh

I got new error:

Dynamic SQL ErrorSQL error code = -104Token unknown WITH LOCK.

the TIBCUpdateSQL components has this SQL for lock:

SELECT NULL FROM VW_mytable1
WHERE
Master_ID = :Old_Master_ID
FOR UPDATE WITH LOCK

where VW_mytable1 is the view for table "mytable1" and "Master_ID" its primary key

Please advise

Thanks

[AndreyZ]

There is not enough information. Please specify the script to create your view and all tables that are used in it. You can post it here or you can send it to andreyz*devart*com .

[inageib]

Code: Select all

CREATE TABLE table3 (
    T3PK3 INTEGER NOT NULL,
    T3Description VARCHAR(40) CHARACTER SET WIN1252);

CREATE TABLE table2 (
    T2PK2 INTEGER NOT NULL,
    T2Description VARCHAR(40) CHARACTER SET WIN1252);

CREATE TABLE table1 (
    T1PK1 INTEGER NOT NULL,
    T1FK2 INTEGER NOT NULL,
    T1FK3 INTEGER NOT NULL,
    T1Description VARCHAR(40) CHARACTER SET WIN1252);

ALTER TABLE table1 ADD CONSTRAINT PK1 PRIMARY KEY (T1PK1);

ALTER TABLE table1 ADD CONSTRAINT FK1 FOREIGN KEY
(T1FK2) REFERENCES table2 (T2PK2) ON UPDATE CASCADE;
ALTER TABLE table1 ADD CONSTRAINT FK2 FOREIGN KEY
(T1FK3) REFERENCES table3 (T3PK3) ON UPDATE CASCADE;

ALTER TABLE table2 ADD CONSTRAINT PK2 PRIMARY KEY (T2PK2);
ALTER TABLE table3 ADD CONSTRAINT PK3 PRIMARY KEY (T3PK3);

CREATE OR ALTER VIEW view1(
  T1PK1,
  T1FK2,
  T1FK3,
  T1Description,
  T2PK2,
  T2Description,
  T3PK3,
  T3Description)
As Select
  table1.T1PK1,
  table1.T1Description,
  table2.T2PK2,
  table2.T2Description,
  table3.T3PK3,
  table3.T3Description
From table3
  right outer join table1 on (table3.t3pk3 = table1.T1FK3)
  left outer join table2 on (table1.T1FK2 = table2.T2PK2)

I want to ask if there are any advantage of using view and TIBCUpdateSQL or to use the Join SQL above directly in TIBCQuery Select SQL without creating a view in the database, this way I get editable table faster and easier ? or performance is better with views ?

Many Thanks

[AndreyZ]

We did not test the performance of using views and SQL with joins, but we do not think that there will be a big gap in performance between these two ways. Views may be usefull if you need to use the same difficult query many times in your application.
If you want to use a view, you should write all SQL statements (INSERT, UPDATE, etc.) manually, because IBDAC cannot automatically generate the correct statements in this case. For example, you can use the following INSERT statement:

Code: Select all

INSERT INTO TABLE1(T1PK1, T1FK2, T1FK3, T1Description)
VALUES(:T1PK1, :T1FK2, :T1FK3, :T1Description)

[inageib]

thanks a lot
and what is the reason of lock error message ?

[AndreyZ]

The point is that the WITH LOCK clause cannot be used for views. WITH LOCK is available in DSQL and PSQL, and only for top-level, single-table SELECTs. You can find the information about it here: http://www.firebirdsql.org/refdocs/lang ... hlock.html
For example, you can use the following LOCK statement:

Code: Select all

SELECT NULL FROM TABLE1
WHERE
T1PK1 = :Old_T1PK1
FOR UPDATE WITH LOCK

[inageib]

Many Thanks
I read the article and I removed the lock SQL

when I try to delete instead of this SQL

DELETE FROM master_view
WHERE
T1PK1 = :Old_T1PK1

I should use the table itself not the view right ?

DELETE FROM master
WHERE
T1PK1 = :Old_T1PK1

[AndreyZ]

Yes, you should use the table, not the view.

[inageib]

Thanks alot

I did simple performance measure of using select with a view and another using SQL Joins. I use IBExpert but I dont know which connection component they use.

Total records 100K+

First time view select:

------ Performance info ------
Prepare time = 16ms
Execute time = 62ms
Avg fetch time = 3.10 ms
Current memory = 9,269,236
Max memory = 10,341,252
Memory buffers = 2,048
Reads from disk to cache = 53
Writes from cache to disk = 0
Fetches from cache = 7,137

Second time view select:

------ Performance info ------
Prepare time = 0ms
Execute time = 15ms
Avg fetch time = 0.83 ms
Current memory = 9,337,900
Max memory = 10,554,304
Memory buffers = 2,048
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 7,075

First time SQL Joins (Windows restarted)

------ Performance info ------
Prepare time = 16ms
Execute time = 47ms
Avg fetch time = 5.22 ms
Current memory = 9,302,156
Max memory = 10,341,252
Memory buffers = 2,048
Reads from disk to cache = 53
Writes from cache to disk = 0
Fetches from cache = 7,137

Second time SQL Joins

------ Performance info ------
Prepare time = 0ms
Execute time = 15ms
Avg fetch time = 0.83 ms
Current memory = 9,341,420
Max memory = 10,554,304
Memory buffers = 2,048
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 7,075

No difference I guess I use SQL joins as it is faster in development.

[AndreyZ]

Thank you for posting the results of your testing, it may be useful for other people who visit our forum.