Viewing sales data figures in real-time from PostgreSQL DB application

First let me start out by saying that I am not a programmer, I am a networking guy. The company I work for has an application called AutoMate which uses PostgresSQL which runs on a Linux server. AutoMate runs a script for us which exports all the data into text files which we use in MS Access to generate all kinds of custom reports.

What I would like to do is view the data in real-time directly from the database or from the text files, Preferably directly from the DB. I would like to find out if this is possible and secondly, I need to find a programmer to work with us to get it done. If there is any more information you need to help answer this question let please me know. Thanks in advance for your help.

Link to the application websit开发者_JS百科e that we use. http://www.automate.com/

Thanks, Michael

It is certainly possible. A direct link can be done with ODBC or with Java JDBC or with ActiveState Perl, DBI and DBI::Pg.

I like Perl for reports so I would lean toward that.

Now, there may be reasons to not have direct access to the database for reporting. For one, reporting can cause a heavy load on the database when the database might have much better things to be doing with its time, like recording sales data.

This depends on a lot on your specific situation including the type and frequency of reports and how much data volume your DB server is handling.

In some cases you need to set up a second database server that is either fed with live data from the master or it gets a periodic (like daily) dump from the master. This reporting server is then used to collect the data into nice groups for reports. This is sort of the situation you are in now.

Before you change anything I recommend finding out if it was set up in that way intentionally.

You have not mentioned if the live viewer is going to be hosted on internet/web app or a fat client within your company.

If you talk about a Live update as and when the data reaches your server, I am not sure you can implement this just by using a database mechanism. You'll need a separate application/server app which will keep on publishing the data to the client after the client starts.

We are developing one such app but not exactly similar.

If the Linux server is on your own LAN, you might be able to connect Access to it through ODBC. Depends on several things, not the least of which is suitable permissions in the dbms.

In at least some versions of Access, use File | Get external data | Link Tables...

In "Files of type", select "ODBC databases", and select a data source. If you don't already have a DSN configured, you'll need to do that. (Control Panel, Administrative tools, Data sources.)