Daniel Kasak wrote:
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 ....
Thanks Daniel for the reply. Unfortunately, none of the suggestions are
feasible or desireable. The databases and associated tables are
definitely separate and won't be mixed in the future.
Even in Access, I've resorted to techniques you mention, like grabbing a
set of data from one table/database say from a distant server, copy to a
local Access temp table, then link that data to a nearby SQL Server.
Speeds things up considerably.
However, my primary concern at the moment is to be able to open a
postgresql database/table, and read from a series of .csv tables (files
- virtually hundreds of them) one at a time, and append the data to the
postgresql table.
Yes, I can *very* easily do this in Access, but I'm trying my best to
wean my self from M$ products.
I guess I need to dig into the OOo Base API. The .csv file has over 100
columns per each record in it. That's a lot of Basic code typing. An
Append query against the csv file, if I could link or point (connection)
to it, would be a whole lot faster.
The technique I would use in Access would be to use VB code to get the
next csv filename, establish a link using an import specification,
keeping the same linked table name for each it iteration, then execute
the Append Query to the postgresql table. Then delete the link of the
csv table, get next csv filename and repeat the process.
What I don't know is if this technique is feasible in OOo Base.
BTW, I am using OOo 2.0. There was an entry about doing something
similar, but the technique was applicable to 1.1.x, not the current 2.0
- there is no Tools | Data Sources path, that the technique used. It is
available in Calc, I think, though using View | Data Sources. Why not
in Base? It seems so fundamental. Maybe I'm just *too* used to MS
Access... ;-)
Dale
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
