Update Failed On Complex Query

Forum related to version 6.5.1 (alpha) and 6.6.x (beta) of ZeosLib's DBOs

Moderators: gto, cipto_kh, EgonHugeist

Post Reply
iwancs
Fresh Boarder
Fresh Boarder
Posts: 7
Joined: 20.02.2007, 08:13
Contact:

Update Failed On Complex Query

Post 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
btrewern
Expert Boarder
Expert Boarder
Posts: 193
Joined: 06.10.2005, 18:51

Post 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
iwancs
Fresh Boarder
Fresh Boarder
Posts: 7
Joined: 20.02.2007, 08:13
Contact:

Post 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
User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Post 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
uwwin
Fresh Boarder
Fresh Boarder
Posts: 8
Joined: 20.07.2006, 08:39

Post 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?
uwwin
Fresh Boarder
Fresh Boarder
Posts: 8
Joined: 20.07.2006, 08:39

Post by uwwin »

I use Zeos 6.6.0
User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Post 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
Asis
Fresh Boarder
Fresh Boarder
Posts: 11
Joined: 13.12.2006, 12:56
Location: Mogilev

Post 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.
uwwin
Fresh Boarder
Fresh Boarder
Posts: 8
Joined: 20.07.2006, 08:39

Post 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
Asis
Fresh Boarder
Fresh Boarder
Posts: 11
Joined: 13.12.2006, 12:56
Location: Mogilev

Post by Asis »

Try to enumerate field names in zquerymaster SQL explicitly.
Post Reply