Page 1 of 1
Update Failed On Complex Query
Posted: 20.02.2007, 08:52
by iwancs
Hi, i've installed latest Zeos 6.6-TRUNK from CVS.
I have a query that join 2 or more tables like this:
SELECT
IVDATGRP_LVL0.ID_IVDATGRP_LVL0 ID_KELOMPOK,
IVDATGRP_LVL0.KODE KODE_KELOMPOK,
IVDATGRP_LVL0.NAMA NAMA_KELOMPOK,
IVDATGRP_LVL0.KETERANGAN KETERANGAN_KELOMPOK,
IVDATGRP_LVL1.*
FROM IVDATGRP_LVL1
LEFT JOIN IVDATGRP_LVL0 ON IVDATGRP_LVL0.ID_IVDATGRP_LVL0 = IVDATGRP_LVL1.ID_IVDATGRP_PREV
When i apply update, it failed with Error: "Cannot update a complex query with more then one table"
So do I need to add TZUpdateSQL to handle them?Is there another way so that i don't need to add TZupdateSQL Component. Thx
Iwan Cahyadi Sugeng
Posted: 20.02.2007, 09:50
by btrewern
I think you could have two TZQuery components and work like that. You'd need to use transactions to keep your updates consistent but I suppose it would work.
Regards,
Ben
Posted: 20.02.2007, 10:26
by iwancs
Yes, i know that I Can used to TZQuery and use lookup field, but it will degrade the performance when loading the table if the main table must join to more than 2 tables.
I need this query to display data that consists of more than 2 tables and that table is used for transaction too.
Iwan Cahyadi Sugeng
Posted: 20.02.2007, 11:42
by mdaems
Hi Iwan,
Ben does not refer to lookup fields but master-detail queries. You can't show this in one grid, however.
I think this will have a similar effect on performance.
Conclusion : if you need one resultset or have problems with performance linking 2 queries you will need to use a ZUpdateSql component.
There might be a a database solution to this when you can create a database view that allows updating. (Some databases can do this under the right conditions)
Mark
Posted: 28.03.2007, 07:39
by uwwin
i have added TZUpdateSQL.. but still error?
why?
when i have a complex query text like 'select a,b from tbl_a a inner tbl_b on a.key=b.key inner join tbl_c on c.key=a.fkey';
and
ZUpdateSQL.ModifySQL.text='update k set k.name=:name where k.id=:k.id_OLD';
it doesn't work... the record did not updated.
why?
Posted: 28.03.2007, 07:42
by uwwin
I use Zeos 6.6.0
Posted: 28.03.2007, 10:28
by mdaems
The example isn't specific enough to make a conclusion.
Can you give the exact sql of the ZQuery and ZUpdateSql components that you linked in your application? In your example you are updating a table k using a parameter field k.id_old when there's no id field in the query which uses tables tbl_a and tbl_b.
That's a little
confusing.
Mark
Posted: 29.03.2007, 11:56
by Asis
I got the same error lately.
You might have forgotten to fill up property UpdateObject of your ZQuery component.
If you havn't, try to place in ZUpdateSQL.SQL.Text this:
ZUpdateSQL.ModifySQL.text='update k set name=:name where id=:old_id';
and do not forget to insert field 'id' in SELECT clause of your ZQuery.
Posted: 20.04.2007, 00:27
by uwwin
I Have inserted UpdateObject with TUpdateSQL in my Zquery...
this application can work in Zeos 6.5.1...
but after i have used zeos 6.6.1 it didn't worked.
and this is the other case
Code: Select all
zquerymaster:='select * from tbl_master';
datasource1.dataset:=zquerymaster;
zquerydetail.datasource:=datasource1;
Zquerydetail.sql.text:='Select * from tbl_detail where id_master=:id_master';
zqueryDetail.UpdatedObject:=UpdateDetailQ;
UpdateDetailQ.modify:='update tbl_detail set tbl_detail.name=:name where
tbl_detail.id_master= :OLD_id_master and tbl_detail.no = :OLD_no'
then i have code like this
Code: Select all
if (add) then
zquerydetail.append
else if (send) then
zquerydetail.post
it can run... but the record did not updated
Posted: 20.04.2007, 09:02
by Asis
Try to enumerate field names in zquerymaster SQL explicitly.