Dale Seaburg wrote:
I am quite familiar with MS-Access. I use the feature of being able
to link tables from different databases using ODBC entries (DSN).
How do I do the same within OO-Base?
For example, I need to link into a postgresql table, an MS-Access
table, and text files, all which have ODBC DSN's.
I some cases I'll have text files (.csv) that will not have an ODBC
DSN. What is the best way to handle them via a Basic scripting tool?
Dale.
That's a pretty big task to implement. I haven't tried this in OOo Base,
but I'm assuming that this is *not* implemented ... someone correct me
if I'm wrong here.
There are 2 ways you can go about things - the dodgy way, and the
not-quite-so dodgy way.
Dodgy:
Copy data from all related tables in all data sources into native tmp
tables ( on each query ), and then query those. This will be slow, but
it will work. It requires the least programming effort - by a couple of
orders of magnitude. You can also do simple things like analyse the
source tables and create corresponding indexes in your tmp tables to
speed things up a bit.
Not-quite-so-dodgy:
- Parse each query
- Figure out which tables comes from which data source, and whether
tables are on the 'one' side or the 'many' side of relationships
- Construct queries for each data source - initially based on the
'where' clause of the original query
- Using the results from the 1st set of queries, pick out the fields on
the 'one' side that are being joined on, and use the values in these
fields to grab data from the 'many' side
This is basically the way Access makes it work. You can see exactly how
by turning on MySQL's query log and watching what happens when you
execute a query with MySQL tables on the 'many' side.
Access's implementation isn't exactly perfect - it creates *huge* where
clauses. A far better solution would be to create a temporary table in
the database with tables on the 'many' side, dump results into it ( ie
the fields in the joins ), and then query against that. I've put a fair
bit of thought into how to go about this - I'm considering adding
support for this to my projects, Gtk2::Ex::DBI and
Gtk2::Ex::Datasheet::DBI, but it would also be a handy feature just for
DBI generally, so I'm still deciding exactly where I should implement it.
Getting back to your question, to be honest, I'd just move the data from
Access into Postgres and be done with it. Same goes for those text files
- set up an import routine to get them into Postgres, and then query
things from there. If you've got complex legacy applications that rely
on a particular database server, then it's not quite so easy, but if
your data is just in Access, you should have no problems moving it to
Postgres and then linking the tables in Access. Otherwise you're looking
at a *lot* of work to get cross-database queries happening. But by all
means ....
--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
