Page 1 of 1
Updateable VIEW
Posted: Mon 19 Nov 2012 15:47
by 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
Re: Updateable VIEW
Posted: Mon 19 Nov 2012 16:14
by 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.
Re: Updateable VIEW
Posted: Tue 20 Nov 2012 01:19
by 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
Re: Updateable VIEW
Posted: Tue 20 Nov 2012 12:05
by 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 .
Re: Updateable VIEW
Posted: Tue 20 Nov 2012 13:37
by 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
Re: Updateable VIEW
Posted: Wed 21 Nov 2012 11:15
by 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)
Re: Updateable VIEW
Posted: Wed 21 Nov 2012 11:37
by inageib
thanks a lot
and what is the reason of lock error message ?
Re: Updateable VIEW
Posted: Wed 21 Nov 2012 12:22
by 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
Re: Updateable VIEW
Posted: Wed 21 Nov 2012 14:35
by 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
Re: Updateable VIEW
Posted: Wed 21 Nov 2012 15:52
by AndreyZ
Yes, you should use the table, not the view.
Re: Updateable VIEW
Posted: Wed 21 Nov 2012 23:27
by 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.
Re: Updateable VIEW
Posted: Thu 22 Nov 2012 08:43
by AndreyZ
Thank you for posting the results of your testing, it may be useful for other people who visit our forum.