[OPEN-ILS-DEV] ODBC connection to Evergreen

[Frances Dean McNamara]

We have had a read only copy of the db available for reporting in the past, and use read only logons for MS Access reports currently.  We could look at a reporting tool in the future.

But I was looking at it for doing a data load of item information a la  "Importing bibliographic holdings via a staging table" instructions on the wiki.  Access is just a tool I have used to insert into that type of staging table in the current db.  And this is a test db.

I'm looking for an easy way to create the staging table and then to get the information from about 1,000 items in the old system and make items in the new system so people can have an idea of how that information might look.  I can just do a tab delimited file and then make a file with insert statements and run it with psql.

I exported a small set of records with 999s with item information and the import_holdings.pl failed on that.  I suspect we would use a staging table approach anyhow, so I'd like to try that!  Seems a better place to expend a little effort.

Frances McNamara
University of Chicago

-----Original Message-----
From: open-ils-dev-bounces at list.georgialibraries.org [mailto:open-ils-dev-bounces at list.georgialibraries.org] On Behalf Of Mike Rylander
Sent: Tuesday, July 01, 2008 7:02 PM
To: Evergreen Development Discussion List
Subject: Re: [OPEN-ILS-DEV] ODBC connection to Evergreen

On Tue, Jul 1, 2008 at 4:18 PM, Frances Dean McNamara
<fdmcnama at uchicago.edu> wrote:
> Question, do people use MS Access with Evergreen?  Would I install an ODBC
> driver on my workstation?  Has anyone done this?
>

There are known uses of ODBC connectors, but not from Access to the
best of my knowledge.  I wouldn't suggest connecting to a main
production Evergreen database using ODBC in any case, as the ODBC SQL
dialect is rather restrictive, and Acess even more so.  There are
structures used that I would very surprised to find Access able to
handle.  If you do decided to go this route, though, I strongly
suggest doing so with a reporting-only replica of any production
installation (which I realize isn't there just yet ... but I felt I
should mention it up front) -- it's just too easy to remove or
otherwise mangle live data, not to mention the load concerns of
accidental full outer joins (it happens to the best of us ;) ).

Another option is to use pgAdmin-III*, which is a native Postgres
administration utility.

* http://www.pgadmin.org/

>
>
> We frequently run MS Access against our current Sybase database to do
> reports and sometimes I use it to upload tables of data.  It might be
> useful.
>

I'd be inclined to suggest Crystal Reports or Business Objects for
custom ad-hoc reporting.

As for uploading tables of data ... there really aren't tables of data
to upload into Evergreen.  The database is exposed through user
interfaces, and other than some tables that will be getting an
interface as of 1.4, there's not much to directly edit.  Generally
speaking, the data isn't laid out for human use, it's laid out for
efficiency and machine use.

So, long answer short, it should be possible, but nobody is using
Access today and it wouldn't be recommended for a production system.
Even if that's not really the answer you were looking for I hope that
helps!

--
Mike Rylander
 | VP, Research and Design
 | Equinox Software, Inc. / The Evergreen Experts
 | phone: 1-877-OPEN-ILS (673-6457)
 | email: miker at esilibrary.com
 | web: http://www.esilibrary.com