Issue 53377 - Creating a form for a multi table query does not allow updating
Summary: Creating a form for a multi table query does not allow updating
Alias: None
Product: Base
Classification: Application
Component: code (show other issues)
Version: OOo 2.0 Beta
Hardware: All All
: P2 Trivial with 16 votes (vote)
Target Milestone: OOo 3.3
Assignee: ocke.janssen
QA Contact: issues@dba
Keywords: crash, oooqa
Depends on:
Reported: 2005-08-16 12:00 UTC by gertblij
Modified: 2010-10-28 06:42 UTC (History)
5 users (show)

See Also:
Latest Confirmation in: ---
Developer Difficulty: ---


Note You need to log in before you can comment on or make changes to this issue.
Description gertblij 2005-08-16 12:00:12 UTC
Assume tables TA with fields F1 and F2 and TB with F3 and F4. 

Creating a form for any of the two table will allow updating as will a "Select *
from TA" *or* "Select * from TB".

But a form for "Select * from TA, TB where F1 = F3" will not. It also does not
work id the F1 = F3 is replaces with a JOIN.

This _might_ be related to issue # 53331
Comment 1 Mechtilde 2005-08-16 12:10:49 UTC
Do you want to insert datas in both tables?
Comment 2 christoph.lukasiak 2005-09-12 12:13:43 UTC
clu->gertblij: may you describe it more precisely in a step by step description
for better repro and/or read

Comment 3 alex.thurgood 2005-09-19 18:46:04 UTC
There is a user on the English users list with exactly the same problem.

Setting needmoreinfo
Comment 4 alex.thurgood 2005-09-21 18:18:57 UTC
More info from the second person reporting problems :

   Employees table
    ID               Integer [INTEGER]  This has Autovalue = Yes
    FirstName   Text  [VARCHAR]
    LastName   Text  [VARCHAR]
    key_employeess   Integer[INTEGER] Autovalue = No, Entry requiered = Yes

   Events table
     ID                       Integer[INTEGER]   Autovalue = Yes
    EventDescription   Memo[LONGVARCHAR]
    EventTypeID         Integer[INTEGER]   Entry required = Yes
    key_events            Integer[INTEGER]   Entry required = Yes

    In the Tools, Relationships window, we have Employees Table and Events
Table.  ID.Employees is connected to key_events.Events which appears to be a
one-to-many relationship and i have checked the boxes Update Cascade and Delete

    The Design View was used to make the Query_Employees.  i just put both
tables on the palette, linked the same fields again (ID.Employees and
key_events.Events), pulled each of the field names onto the grid below until all
of the fields were represented on the grid, clicked the Save button on the upper
lefthand corner of the Design View and finall clicked on the RunQuery button. 
The query runs, and puts all of the field names up at the top of the page, but
there is nothing below any of the fields to fill in.

Comment 5 alex.thurgood 2005-09-21 18:22:20 UTC
OK, I've just tried what the user described and can confirm. The table that is a
result of the query can not be edited. I additionally tried to create a form
based on the query and this caused my m130 to crash.

/usr/bin/soffice: line 224: 28594 Segmentation fault      "$sd_prog/$sd_binary" "$@"

Confirming the issue, setting crash and oooqa keywords

Comment 6 fornarif 2005-11-25 15:04:48 UTC
I can totally confirm what described in this issue. Any query composed by two or
more tables doesn't allow updating and I'm very surprised that such a
restriction is almost disregarded.
Comment 7 alex.thurgood 2005-11-25 18:30:53 UTC
Hmm, since it has been confirmed, maybe I ought to reassign it to the right
owner, but who might that be ?

Comment 8 alex.thurgood 2005-11-25 18:33:39 UTC
Hmm, since it has been confirmed, maybe I ought to reassign it to the right
owner, but who might that be ?

Comment 9 alex.thurgood 2005-11-25 18:36:40 UTC
sorry about that previous post - my bad.
Setting to oj, Ocke, if I'm mistaken, please correct

Comment 10 ocke.janssen 2005-11-30 07:18:19 UTC
The problem here is the inner join and the where condition as well. Out joins
allows updates. I have to resolve the problem.

Best regards,

Comment 11 ocke.janssen 2005-11-30 11:06:30 UTC
Set target to OOLater
Comment 12 ocke.janssen 2005-12-30 06:25:19 UTC
I fixed in cws dba30 in that way that only the left table can be modified at the
moment. To update both tables a little more work is needed. If this is needed
please submit a new issue for this. :-) (It's a little bit more complicated.)
Comment 13 alex.thurgood 2005-12-30 08:23:10 UTC
Hi Ocke,

Is the issue really fixed if you can only edit one table ? I mean, assume I do
what was described in the issue to reproduce the problem, will I be able to
enter data into the resulting Table GUI or not, or only in those fields that
belong to the table on the left ? In that case, it is, I feel, of little use to
me, since I could just create a separate query for updating separately the left
table. I know that this means more work for you :-(, but I don't see how opening
a new issue is going to help when this issue isn't actually fixed.

If I've misunderstood anything in anyway, please feel free to correct me.

Comment 14 ocke.janssen 2005-12-30 08:49:12 UTC
Yes, you are right, that doesn't make sense, just to allow the update of only
one table. So if the column part of the select stmt contains all key columns of
the second table as well, the 2nd one should be updateable as well. Correct?
Comment 15 fornarif 2005-12-30 09:35:42 UTC
Sorry if I jump in, but I' very interested in this thread because I mostly use
one-to-many queries with inner join and if I cannot update all fields of the
resulting table, that query is useless for my purposes.

Comment 16 alex.thurgood 2006-05-16 09:36:34 UTC
Hi all,

Judging by the number of times this question comes up on the lists and in the
forums, I guess that it is quite a common requirement for most users. 

@oj : Any idea when the ressources for fixing this might become available ? I
see the target was set to later more than 6 months ago.

Comment 17 ocke.janssen 2006-05-17 07:04:36 UTC
Hi Alex,

I see this issue is really important because of this I change the target to
2.0.x so it'll be fixed in time. But as you know developers are rare to find,
any kind of help is very appreciated :-) I could point you to the souce code
where to look first.

Best regards,

Comment 18 alex.thurgood 2006-05-17 07:41:17 UTC
Hi Ocke,

I would love to be able to fix it myself ;-), but I am unfortunately a complete 
dunce when it comes to understanding C/C++ :-(, and secondly and even worse, I 
don't have a computer with enough disk space to even compile OOo :-(( 

I tried once on my my FreeBSD machine, and then when it filled up my disk and 
trashed the system, I went and had a look at the build space requirements and 
saw you needed about 9Gb !! for the first time around. On a machine with 2Gb 
free disk space that's a bit tight ;-), and that's not to even mention the 
specific problems with compiling on FreeBSD.

So I guess it'll have to wait ;-)

Thanks anyway for flagging it

Comment 19 ocke.janssen 2006-05-17 08:03:11 UTC
Hi Alex,

I think we are even selling storage :-) Just kidding, but you may want to have a
look at a promo we offer ;)

-- OJ
Comment 20 alex.thurgood 2006-05-21 16:51:52 UTC
Hi Ocke,

Well it would probably just be cheaper to buy an external USB disk :-p, but it 
would still take me about 6 years to learn C/C++ programming at my current 
degree of availability ;-))

Comment 21 ocke.janssen 2006-08-01 12:59:20 UTC
After investigating this one a little bit more, it looks like that this one will
take some more time. I change the type from defect to enhancement because it is
a missing implementation and not a defect in the existing code base.

-- OJ
Comment 22 drewjensen.inbox 2007-10-02 14:00:40 UTC
added self to cc
Comment 23 ocke.janssen 2007-11-13 06:07:52 UTC
Change target to 3.0 because it can not be fixed in the timeline for UI/Feature
Comment 24 ocke.janssen 2008-04-28 13:20:27 UTC
To big changes in code which are to danger.
Comment 25 ocke.janssen 2010-02-17 06:33:40 UTC
Fixed in cws dba33f.
Comment 26 ocke.janssen 2010-10-28 06:42:08 UTC