I'm managing a site with lots of different info. It's Linux+Apache, with a brand spankin' new Zope install. I'd like to add more info and features to the site, most of it dynamic, and just about all of it somebody else's responsibility to maintain. My best success so far has been with Excel spreadsheets. In this case, the page maintainer stores it on an NT server, and I run a cgi script that ftp's a copy over, and then it gets displayed via xl2html. This has the advantage of making a local copy for a backup, in case something untoward should happen, and the it's completely transparent to the person responsible for maintaining the page. As far as he's concerned, it just magically gets updated on the website. This works, primarily because the spreadsheets are pretty much under 50kb. Now I'm faced with searching a 20Mb Access database and displaying selected records from it. Downloading a new copy with every access at 20Mb is prohibitive, and it's not updated so often as to be important. I can manually stash it wherever necessary as needed. The first question is where to stash it. I want to avoid installing new software or services on the various machines, though I certainly could, within reason and limitiation. Ideally, I want to just stash it on the Linux server, and have Zope take care of the interface and display. But looking at the Jet ODBC connectors, it appears that they really want to live in Windows. Which is all well and good. The database maintainer is using a Windows machine, and I have our local NT server, either will do, but I'd rather not install Python on either of them if I didn't have to, though I can if needs must. Which also brings me the question, how does my Linux Zope installation talk to the Windows server hosting the database? By way of the connector? I hope this all isn't too basic... Thanks Bruce
Hi Bruce, we are using Merants Sequelink[tm] middleware to get a foot on the ODBC database on the windows side and have ZmxODBC on Zope/Linux. This works pretty well but the Sequelink middleware costs substantial money. The solution for you might be a small zope with ZmxODBC on the windows machine to access the ODBC database there and take zclient to fetch results to the Zope on Linux. (There is little documentation on XML-RPC you could use) Regards Tino --On Freitag, 20. Juli 2001 09:55 +0000 Bruce <bkd69@yahoo.com> wrote:
I'm managing a site with lots of different info. It's Linux+Apache, with a brand spankin' new Zope install.
I'd like to add more info and features to the site, most of it dynamic, and just about all of it somebody else's responsibility to maintain.
My best success so far has been with Excel spreadsheets. In this case, the page maintainer stores it on an NT server, and I run a cgi script that ftp's a copy over, and then it gets displayed via xl2html. This has the advantage of making a local copy for a backup, in case something untoward should happen, and the it's completely transparent to the person responsible for maintaining the page. As far as he's concerned, it just magically gets updated on the website.
This works, primarily because the spreadsheets are pretty much under 50kb.
Now I'm faced with searching a 20Mb Access database and displaying selected records from it.
Downloading a new copy with every access at 20Mb is prohibitive, and it's not updated so often as to be important. I can manually stash it wherever necessary as needed.
The first question is where to stash it. I want to avoid installing new software or services on the various machines, though I certainly could, within reason and limitiation. Ideally, I want to just stash it on the Linux server, and have Zope take care of the interface and display. But looking at the Jet ODBC connectors, it appears that they really want to live in Windows.
Which is all well and good. The database maintainer is using a Windows machine, and I have our local NT server, either will do, but I'd rather not install Python on either of them if I didn't have to, though I can if needs must.
Which also brings me the question, how does my Linux Zope installation talk to the Windows server hosting the database? By way of the connector?
I hope this all isn't too basic...
Thanks Bruce
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
On Fri, 20 Jul 2001, Bruce wrote:
My best success so far has been with Excel spreadsheets. In this case, the page maintainer stores it on an NT server, and I run a cgi script that ftp's a copy over, and then it gets displayed via xl2html. This has the advantage of making a local copy for a backup, in case something untoward should happen, and the it's completely transparent to the person responsible for maintaining the page. As far as he's concerned, it just magically gets updated on the website.
[...]
Now I'm faced with searching a 20Mb Access database and displaying selected records from it.
Downloading a new copy with every access at 20Mb is prohibitive, and it's not updated so often as to be important. I can manually stash it wherever necessary as needed.
The first question is where to stash it. I want to avoid installing new software or services on the various machines, though I certainly could, within reason and limitiation. Ideally, I want to just stash it on the Linux server, and have Zope take care of the interface and display. But looking at the Jet ODBC connectors, it appears that they really want to live in Windows.
Which is all well and good. The database maintainer is using a Windows machine, and I have our local NT server, either will do, but I'd rather not install Python on either of them if I didn't have to, though I can if needs must.
Which also brings me the question, how does my Linux Zope installation talk to the Windows server hosting the database? By way of the connector?
If I understand, you want Zope to be able to dig into the Access database and show dynamic (or semi-dynamic; you say that it could be mannually updated) information. This is *very* different from an Excel file; you can convert an XL file to HTML, but you can't "convert" an Access DB to HTML -- you *can* make an HTML page for each of the tables, but usually people want one page for each record, showing the information in a customized way. Zope excels at this -- you can use the ZSQL Methods to dig into your database. The challenge is digging into an Access .mdb file from Linux. There's no (free) way (that I know of) to access an Access .mdb file from Linux -- MS hasn't written a driver, and aren't likely to do so. There is a project a SourceForge, MDBTools, that reverse-engineers that .mdb format, but it's not useful yet. There are lots of different strategies. You could: 1) regularly clone the db to linux * install PostgreSQL (very powerful), MySQL (very small), or Interbase (very easy) on your Linux box. * write a small python script that uses both an ODBC and PostgreSQL/ MySQL/Interbase ODBC connector. Have it copy the data from the Access tables (via ODBC) to the Linux-DB tables. (or write it in Perl or, if you're not handy w/programming, do the conversion by hand or via one of the conversion programs available for the Linux DB.) * tap into this database from Zope. You'll have to install a database adaptor in Zope, but that's usually easy. Disadvantages: data isn't live; it's behind by several hours or a day, depending on how often the conversion script runs. 2) use a Linux DB instead * install a Linux DB. * Convert your data to the Linux DB, as above. * Install the Windows ODBC driver for your Linux DB on your Windows machines. * Set up your Access database to, instead of using local tables, use linked tables that get their data from the Linux server. Disadvantages: w/ linked tables, your end users can't easily modify their table structures (add field, etc.); sometimes, there are changes req'd to the Access DB. [ nb: I'm the author of the FAQ on how to do this w/PostgreSQL. See www.scw.org/pgaccess for the full story. ] 3) let Windows do the lifiting * install Zope on a Windows box. * Write the stuff for Zope, using ZODBC or ZJet Adaptor, to get the stuff from your Access DB. * Use redirects or ProxyPass from the Linux server to redirect just the relevant requests to the Winows Zope server; or, serve all Zope pages from Windows. Disadvantages: Windows 95/98 isn't exactly stable; your pages will show a different domain name. 4) hack together some flaky, non-DB way * write a macro/VBA code in Access to export table info to a text file (*very* easy to do in a macro). * write a python method to knock this text export into HTML snippets, and import these into Zope. Disadvantages: not very portable in the future. Hope something here unlocks some ideas of where to go. Good luck, -- Joel Burton <jburton@scw.org> Director of Information Systems, Support Center of Washington
[Bruce]
Now I'm faced with searching a 20Mb Access database and displaying selected records from it.
Downloading a new copy with every access at 20Mb is prohibitive, and it's not updated so often as to be important. I can manually stash it wherever necessary as needed.
The first question is where to stash it. I want to avoid installing new software or services on the various machines, though I certainly could, within reason and limitiation. Ideally, I want to just stash it on the Linux server, and have Zope take care of the interface and display. But looking at the Jet ODBC connectors, it appears that they really want to live in Windows.
There's no problem getting data from an Access database via ODBC as long as the ODBC driver and the database are on the **same Windows machine**. I've not been able to make this work when the server+odbc is on a different machine from the database. My inclination would be to put a Zope installation on the Windows machine that has the Access database (you could copy the database every once in a while if it doesn't get updated too often. That way you could avoid putting anything on someone else's machine). Build Zope pages that return the data as lists, I would think, and get them into your Zope Linux installation that way. Of course, to actually get the data from Zope you have to write queries against the database, but that's separate from getting this transfer machinery going. Another approach would be to get a python xml-rpc server running on the Windows machine. That would use mxodbc to get data from the database and return it to Zope using xml-rpc. This should be pretty easy to do. Either way, you let Zope take the data and format it into your final html pages. With the first method, you ought to be able to have the Zope on the Windows box do all the work, and the Linux box would just pas it along. Cheers, Tom P
"Thomas B. Passin" wrote:
[Bruce]
Now I'm faced with searching a 20Mb Access database and displaying selected records from it.
Downloading a new copy with every access at 20Mb is prohibitive, and it's not updated so often as to be important. I can manually stash it wherever necessary as needed.
The first question is where to stash it. I want to avoid installing new software or services on the various machines, though I certainly could, within reason and limitiation. Ideally, I want to just stash it on the Linux server, and have Zope take care of the interface and display. But looking at the Jet ODBC connectors, it appears that they really want to live in Windows.
There's no problem getting data from an Access database via ODBC as long as the ODBC driver and the database are on the **same Windows machine**. I've not been able to make this work when the server+odbc is on a different machine from the database.
That is because Access is not a database server, it is a client-based database program.
My inclination would be to put a Zope installation on the Windows machine that has the Access database (you could copy the database every once in a while if it doesn't get updated too often. That way you could avoid putting anything on someone else's machine). Build Zope pages that return the data as lists, I would think, and get them into your Zope Linux installation that way.
You could get around this problem by using linked tables in Access. You can physically store the Access mdb file with the data in it anywhere so long as you can access that file over the network. Then setup Access on the Zope machine and create a database that just contains tables that are linked to the remote mdb file. Then you get completely live data all the time.
Of course, to actually get the data from Zope you have to write queries against the database, but that's separate from getting this transfer machinery going.
The linked tables will take care of this. From Zope's point of view it won't matter.
Another approach would be to get a python xml-rpc server running on the Windows machine. That would use mxodbc to get data from the database and return it to Zope using xml-rpc. This should be pretty easy to do.
Sounds like a lot of overhead.
Either way, you let Zope take the data and format it into your final html pages. With the first method, you ought to be able to have the Zope on the Windows box do all the work, and the Linux box would just pas it along.
Cheers,
Tom P
-- | Casey Duncan | Kaivo, Inc. | cduncan@kaivo.com `------------------>
[Casey Duncan]
"Thomas B. Passin" wrote:
There's no problem getting data from an Access database via ODBC as long
as
the ODBC driver and the database are on the **same Windows machine**. I've not been able to make this work when the server+odbc is on a different machine from the database.
That is because Access is not a database server, it is a client-based database program.
Exactly.
My inclination would be to put a Zope installation on the Windows machine that has the Access database (you could copy the database every once in a while if it doesn't get updated too often. That way you could avoid putting anything on someone else's machine). Build Zope pages that return the data as lists, I would think, and get them into your Zope Linux installation that way.
You could get around this problem by using linked tables in Access. You can physically store the Access mdb file with the data in it anywhere so long as you can access that file over the network. Then setup Access on the Zope machine and create a database that just contains tables that are linked to the remote mdb file. Then you get completely live data all the time.
Ah, one of those Access things that I'd know about if I actually used Access as an application. All I ever do is get data from an Access database on those rare jobs where I'm forced to use someone else's Access database. Thanks for the knowledge. Cheers, Tom P
On Fri, 20 Jul 2001, Casey Duncan wrote:
There's no problem getting data from an Access database via ODBC as long as the ODBC driver and the database are on the **same Windows machine**. I've not been able to make this work when the server+odbc is on a different machine from the database.
That is because Access is not a database server, it is a client-based database program.
My inclination would be to put a Zope installation on the Windows machine that has the Access database (you could copy the database every once in a while if it doesn't get updated too often. That way you could avoid putting anything on someone else's machine). Build Zope pages that return the data as lists, I would think, and get them into your Zope Linux installation that way.
You could get around this problem by using linked tables in Access. You can physically store the Access mdb file with the data in it anywhere so long as you can access that file over the network. Then setup Access on the Zope machine and create a database that just contains tables that are linked to the remote mdb file. Then you get completely live data all the time.
Of course, to actually get the data from Zope you have to write queries against the database, but that's separate from getting this transfer machinery going.
The linked tables will take care of this. From Zope's point of view it won't matter.
Another approach would be to get a python xml-rpc server running on the Windows machine. That would use mxodbc to get data from the database and return it to Zope using xml-rpc. This should be pretty easy to do.
Sounds like a lot of overhead.
Yeah, but with some fairness, setting up a database server and linking the tables from Access can be tricky, as the "average user" can usually no longer make any structural changes (even minor ones) to the tables except through semi-arcane SQL commands or specialized programs specific to that database server. [ MS SQLServer notwithstanding, but of course, then you're into serious $$$] We use linking to PostgreSQL backend at our org very successfully, but it does limit the ability of my semi-power-users to make structural changes (even minor ones) to our tables. Of course, that might be a *good* thing... ;-) -- Joel Burton <jburton@scw.org> Director of Information Systems, Support Center of Washington
Joel Burton wrote:
Yeah, but with some fairness, setting up a database server and linking the tables from Access can be tricky, as the "average user" can usually no longer make any structural changes (even minor ones) to the tables except through semi-arcane SQL commands or specialized programs specific to that database server. [ MS SQLServer notwithstanding, but of course, then you're into serious $$$]
I was not talking about any database servers. Just linking one access database to another. It's all still in Access.
We use linking to PostgreSQL backend at our org very successfully, but it does limit the ability of my semi-power-users to make structural changes (even minor ones) to our tables. Of course, that might be a *good* thing... ;-)
There do exist GUI tools for Postgres I believe... -- | Casey Duncan | Kaivo, Inc. | cduncan@kaivo.com `------------------>
On Fri, 20 Jul 2001, Casey Duncan wrote:
Joel Burton wrote:
Yeah, but with some fairness, setting up a database server and linking the tables from Access can be tricky, as the "average user" can usually no longer make any structural changes (even minor ones) to the tables except through semi-arcane SQL commands or specialized programs specific to that database server. [ MS SQLServer notwithstanding, but of course, then you're into serious $$$]
I was not talking about any database servers. Just linking one access database to another. It's all still in Access.
OIC. Yes, this is almost always a good idea, even w/all Access solutions.
We use linking to PostgreSQL backend at our org very successfully, but it does limit the ability of my semi-power-users to make structural changes (even minor ones) to our tables. Of course, that might be a *good* thing... ;-)
There do exist GUI tools for Postgres I believe...
Yeah, but... PostgreSQL is rather limited in the ability to modify structure w/o a dump-restore cycle. Hopefully it will get better. (In fairness to MySQL, it's better at this, and Interbase is almost as easy as Access.) Casey: Love your Library prodcut, BTW. Very nice. -- Joel Burton <jburton@scw.org> Director of Information Systems, Support Center of Washington
There's no problem getting data from an Access database via ODBC as long as the ODBC driver and the database are on the **same Windows machine**. I've not been able to make this work when the server+odbc is on a different machine from the database. ... Another approach would be to get a python xml-rpc server running on the Windows machine. That would use mxodbc to get data from the database and return it to Zope using xml-rpc. This should be pretty easy to do.
There was a discussion on this list not-too-long-ago on this very subject, which seems to have resulted in a proxying database connection that has been used successfully to do this very thing. Look at the thread starting at http://lists.zope.org/pipermail/zope/2001-July/095091.html The most important one is http://lists.zope.org/pipermail/zope/2001-July/095159.html --jcc (not proxying)
participants (6)
-
Bruce -
Casey Duncan -
J. Cameron Cooper -
Joel Burton -
Thomas B. Passin -
Tino Wildenhain