Issue 8949

Summary: [RFE] query on several tables of a DBase/Calc database
Product: Base Reporter: sophia <jerome.bouat>
Component: codeAssignee: AOO issues mailing list <issues>
Status: CONFIRMED --- QA Contact:
Severity: Trivial    
Priority: P3 CC: alex.markus, damjan, gablistas, issues, kamataki, marius.andreiana, mary, mfedyk, mh.hh, sgautier.ooo, tim, yoshimit
Version: OOo 1.0.0Keywords: oooqa
Target Milestone: ---   
Hardware: PC   
OS: Linux, all   
Issue Type: FEATURE Latest Confirmation in: ---
Developer Difficulty: ---

Description sophia 2002-11-04 11:14:51 UTC
I have the goal to get
an office suite similar to Microsoft Office.
I didn't have an equivalent of Microsoft Access.

I didn't managed to use
the ODBC bridge between MySQL and OpenOffice
because something was not well configured
in this vulnerable configuration chain.

I decided to use the DBase format
for creating my database.
I created 2 table
but I didn't performed to query
both tables with the query squetch form.
I then used the SQL query:
SELECT contact.nom,organisations.nom FROM contact,organisations WHERE
contact.organisati=organisations.nom;
This query executes without any problem
but does not display anything
as expected (I accordingly filled the 2 tables).
I further can't edit this query.
If I try it, then a dialog box says it has to many tables,
but I can't even edit the SQL source of the query.

I think the support of DBase is not usefull
if we can not query several tables.
Queries on single tables
can also be implemented
as queries on a single sheet of calculation.
Comment 1 Frank Schönheit 2002-11-06 07:35:50 UTC
confirming this as a valid request for enhancement (not that this does
not state anything about a time frame, not even acceptance. It is only
that this issue describes a valid request).
Comment 2 Frank Schönheit 2002-12-11 14:54:49 UTC
targeting for OOo 2.0
Comment 3 Frank Schönheit 2002-12-11 15:34:16 UTC
correcting priority
Comment 4 Frank Schönheit 2003-01-17 09:22:30 UTC
*** Issue 10709 has been marked as a duplicate of this issue. ***
Comment 5 Frank Schönheit 2003-09-10 14:59:35 UTC
fs->oj: If we really do this, then it's most probably up to you ...
Comment 6 ocke.janssen 2003-09-22 10:34:49 UTC
.
Comment 7 marc.neumann 2003-10-15 09:44:46 UTC
.
Comment 8 ocke.janssen 2004-01-28 10:50:05 UTC
Set to OOo Later. 
Comment 9 hans_werner67 2004-02-02 12:17:02 UTC
change subcomponent to 'none'
Comment 10 Frank Schönheit 2004-07-07 21:21:55 UTC
assigning to user experience team for evaluation
Comment 11 Frank Schönheit 2005-05-30 09:52:40 UTC
*** Issue 49973 has been marked as a duplicate of this issue. ***
Comment 12 mhatheoo 2006-07-08 23:19:04 UTC
it would be great to hear, what the outcome of this issue will be ...

Martin h.
Comment 13 Frank Schönheit 2006-07-10 07:43:35 UTC
My time machine is broken, so I cannote tell about the future, sorry :)

In fact, there are currently no concrete plans to address this. If you find a
developer who wants to do this - great, let him speak up in
dev@dba.openoffice.org. Other than this, this is not in the list of top priorities.
Comment 14 tedmiller 2006-07-10 13:44:08 UTC
On the thread for issue 49973 (marked as a duplicate of this one):
------- Additional comments from msc Mon May 30 00:47:59 -0700 2005 -------

Hi,

Currently you can't join two dbase table.
The dbase database can't join two dbase tables.

However, I reassign the issue to the User Experience Team for evaluating the
possibility to join two dbase tables.

Bye Marc
------------------------------------------------------------------

This is not true. dBase has extensive facilities for doing multitable queries.
It uses its own sintax, but Marc is obviously unfamiliar with the capabilities
of the dBase system.  What is being asked for is not an enhancement to dbase,
but rather a duplication of dBase's basic capabilities.  Without this
capability, those of us with databases in dBase format are not going to be able
to migrate to OO, but will have to contiune using dBase or other alternatives,
because the data will remain useless without multitable queries.

Ted Miller
Comment 15 jasorn 2006-07-10 13:44:23 UTC
There are no concrete plans to fix this?  How can that be?  Isn't this one of
the most basic elements of an RDBMS or am I missing something?
Comment 16 jasorn 2006-07-10 13:54:26 UTC
I see.  They think you can't even do this in dbase. Is the confusion due to the
fact that in dbase(in version iv anyway) there are two modes.  The 'dbase' mode
and then the sql mode?
Comment 17 tedmiller 2006-07-10 13:59:00 UTC
Even in dBase mode, multiple table linking and queries are available and have
been available since before OO was a project.  I have never used SQL mode, but
have built extensive multi-table database systems using dBase.
Comment 18 jasorn 2006-07-10 14:22:19 UTC
I know but I was just curious if that might be the cause of the confusion. 
Anyway, it seems a little odd not to support multilple table joins in the native
format no matter the reason.  

The fact that you can do this in dbase and dbase is the native format makes this
a no brainer to fix in my humble opinion.  Moreover, I think this will prevent
not only us old dbase user folks from adopting oobase, but also those who just
want the native database to work.

What am I missing that this is not only an high priority but is actively being
worked on?  I most definitely could be missing a lot since I basically shelved
oobase due to this issue and am waiting for it to be resolved before I look at
it again.
Comment 19 Frank Schönheit 2006-07-10 14:30:38 UTC
dBase does not support joins. dBase is a file format, not an application. What
you refer to is an application capable of reading and writing the dBase file
format. This application is also called dBase (dBase IV), but this doesn't
change the fact that the only thing OOo has in common with this application is
that they both operate on files of the same format.

No, fixing this in OOo Base is not a no-brainer, it's a significant effort,
since the SQL engine implemented in OOo base must be capable of handling
multiple tables in one statement. (This, by the way, does not depend on whether
those tables are in dBase format. For instance, the same engine is used when
accessing Calc tables, thus this bug here also carries "Calc" in its summary.)
Comment 20 jasorn 2006-07-10 15:17:31 UTC
I didn't mean fixing this would be a no brainer.  I mean the decision to fix it
should be.  Base will not replace MS Access or any other similar tool until you
can  join it's native file format.  Forcing an odbc just means they'll keep
using the other tool which likely also has odbc capabilities.

I'll post more on the join question later.
Comment 21 tedmiller 2006-07-10 15:52:36 UTC
Is there a work-around for this issue by using and ODBC driver or some other
similar solution?
Comment 22 Frank Schönheit 2006-07-10 16:10:25 UTC
sorry, but dBase isn't OOo's native database file format. HSQLDB is OOo's native
database engine ...
Comment 23 jasorn 2006-07-10 17:30:54 UTC
Silly Me!  It has been about a year or so since I last looked into this and the
test file I made back then is using .dbf.  I could have sworn dbf was the only
option I had other than ODBC when I did this.  Oh well.

I knew I must have had something wrong because it didn't make sense.

I'll convert today and see how it goes.  The gui had some issues too but there
have been quite a few releases so maybe that's better.

Comment 24 Frank Schönheit 2006-07-11 07:59:09 UTC
overlooked this yesterday:
> Is there a work-around for this issue by using and ODBC driver or some other
> similar solution?

There's an ODBC driver for dBase on Windows, but I'm not sure where to get it
from. Might even be it's part of a standard XP installation. Sorry, don't know
in more detail.
Comment 25 timdeaton 2006-12-29 15:47:05 UTC
I agree that doing queries on multiple tables (or on a combination of tables & 
other queries, or on multiple other queries) should be a top priority.  I'm an 
Access97 user, and 80%-90% of everything I do in Access involves queries on 
multiple datasets (tables, other queries, or both).

Base is pretty near useless to me unless it can do that, and it needs to be 
able to do it using Access tables as well as dBase (both ADO/Access2000 and 
the older DAO/Access97 tables).  There's no way I can migrate to Base until it 
does.
Comment 26 jwt 2006-12-29 17:58:38 UTC
It seems unclear what this RFE covers.  It is already possible to run queries on
linked database tables if they are in the same Base file, certainly using
HSQLDB.  Linking to tables in other sources is covered in issue 42464.  Editing
the data in queries is covered in issue 72424.
Comment 27 tedmiller 2007-01-02 15:43:41 UTC
jwt wrote: "It seems unclear what this RFE covers.  It is already possible to
run queries on linked database tables if they are in the same Base file"

This issue is about tables that are NOT in a Base file, and which are NOT in the
same file.

"Linking to tables in other sources is covered in issue 42464."

If you will note carefully, this RFE predates 42464 by about three years, so it
would be more accurate to say that 42464 covers the same issue as was raised
previously in 8949. 42464 started as a specific request which did not overlap
with 8949, but has since been expanded into a more general request which MAY
deal with this issue, depending on how it is resolved.

This issue started as a RFE to allow multi-table queries on dbase format tables.
 This issue remains unresolved.


Comment 28 jwt 2007-01-02 17:59:07 UTC
Presumably issue 42464 was raised because it was not clear that this RFE covered
the same or a similar issue.  Also, 42464 seems to make it clear that it is an
issue on all OSs and version 2 of OOo.  It would seem to me that either this RFE
should be marked as a duplicate or it's title, platform and OS altered to
reflect its precise nature and that it is an issue with current versions of OOo.

And, yes, it needs resolving.
Comment 29 tedmiller 2007-01-02 22:02:48 UTC
Agree on widening at least OS and version info (though I don't know how to do
it).  Unfortunately neither this or 42464 has a target milestone set.
Comment 30 mhatheoo 2007-01-10 14:43:20 UTC
frankly, for my knowledge:

is or will there be anyone in developer-team at oo.o who knows what file-based
dbms are and how they can be be made accessable at least for reading data?
I might be wrong, but I guess that question is in no way related to the
hsql-development, it is and should be a separate issue for the oo.o-developers.
Just being courious.

Martin 

Comment 31 Frank Schönheit 2007-01-11 08:47:38 UTC
> is or will there be anyone in developer-team at oo.o who knows what
> file-based dbms are and how they can be be made accessable at least
> for reading data?

Nah, nobody here knowing anything.

Seriously, I don't understand this question...?

> I might be wrong, but I guess that question is in no way related to the
> hsql-development, it is and should be a separate issue for the
> oo.o-developers.

Sure. The dBase/Calc drivers are completely independent from HSQLDB and the
HSQLDB driver, so HSQL hasn't to do anything with it. Instead, it would be our
part in OOo Base.

Point is, this issue simply didn't get the priority to start it so far. As much
as you (and me) might regret this, it's a matter of fact.
Comment 32 mhatheoo 2007-01-17 17:08:33 UTC
well Frank, I wonder why you are going to argue with me.
until now I believed, that it is your job to make the DB-part of OO.o running.
So if you say to me, that DBase is not / will not be supported in a sufficient
way,   you regret but thats the way it is, I wonder who else than you should do
something for raising the priority?  

I see that OO.o faces a hugh amount of problems in the DB-part, however,
DBase-support is one of of the basics. And I did not say, that the HSQL is
related to DBase, I said that even when the container-concept makes everything
look alike,  the handling of the DBase is different, it should be treated that
way, it should work, allinall a must on high priority-level.
right?

Martin  
Comment 33 Frank Schönheit 2007-01-18 06:28:44 UTC
Martin,

> well Frank, I wonder why you are going to argue with me.

You didn't *really* expect me saying "yes, you're right, we didn't see this
before, and will fix this immediately!", did you? ;)

for the priorizing: Though I'm the project owner, I'm not completely free in
scheduling the limited developer resources we have. That's natural for every
open source project I know, as long as the developers are paid by a company with
own interests (and for Base, this applies to *all* developers).

For my personal preference: I regret this 'cause it's an often-wanted feature
(yes, I can see the number of votes here). Nonetheless, in my personal opinion,
there are other things Base is lacking, which are worse enough to put more
pressure on it. (In particular, "DBase-support is one of of the basics" is
something I wouldn't sign when it includes all you seem to associate with it.)
That's my personal opinion, and it influences of course the priorizations I'm
free to do.
Comment 34 Frank Schönheit 2007-06-22 20:25:17 UTC
*** Issue 78634 has been marked as a duplicate of this issue. ***
Comment 35 bettina.haberer 2007-09-26 16:05:04 UTC
Hi Ocke, please take these issues to you ownership.
Comment 36 mhatheoo 2007-10-17 00:08:25 UTC
hello Ocke, you take as a new owner?

please make some proposals about the solution of this problem that we can face
in the near future

Thanks
Martin


btw:
ODBC seems to be working - see my issue 82577  
Comment 37 ocke.janssen 2007-10-17 07:06:30 UTC
Hi Martin,

I'm the 2nd of the three base developers. :-) One of my parts are the drivers,
also dbase so Bettina assigns the issue to me.

http://wiki.services.openoffice.org/wiki/Base_To-Do

As you can see Joins are on position 1 :-) So when you have the wil to implement
it you'll get all the support I'll can give. ;-)

http://wiki.services.openoffice.org/wiki/BetterDatabaseDrivers

Best regrads,

Ocke

PS: I remeber that someone on the dev@dba mailing list mentioned something that
they would implement a solution.