Thanks All, I was hoping I had missed a clean way to do this. :) Access provides link tables, but what if I had two SQL servers? I thought (still thinking) about the Access link table solution, but I'm not sure I want the ODBC linking through Access to get to SQL. MS SQL Server also supports server links via Transact-SQL/XML. The dtml-in/dtml-with technique suggested by Chris Gray looks the promising. That will eliminate cross-product problems too. Probably the closest thing I'll find to a true join. So, after some digging and trying I found that you can access multiple Databases on the same SQL server by setting the ODBC driver up WITHOUT a default table and qualifying the database as such: select * from DBNAME..TABLENAME This method sacrifices the ability to browse for tables through this DSN, but this can be recovered by creating a secondary table-specific DSN for browsing purposes. ODBC also supports a configuration that allows SERVERNAME.DBNAME..TABLENAME configurations but I haven't been able (yet) to get that working through Zope. This would support direct links to multiple SQL servers. Since the ODBC DSN is part of the ZODBC configuration parameters, I expect that ZODBC cannot provide support for different source types at the ZSQL Method level. As far as writing a method to copy from one table to another, this would be OK in my case because I am using small databases on this app, but not for larger dbs. That would result in (2 + #_of_records) queries against the SQL in a realtime database. My app uses a static table (once a day update) for the Access side, so just moving/migrating this once a day to SQL may be the best bet. The SQL source is a third party package so I'd rather not put my table into their database just in case. (Avoid all that finger pointing later on). The copy table method along with the ZODBC solution above allows me to add a database to the server outside of the 3rd party's database and join tables via Zope ZSQL.
-----Original Message----- From: Chris Gray [mailto:cpgray@library.uwaterloo.ca] Sent: Wednesday, November 22, 2000 10:31 AM To: Andrew Kenneth Milton Cc: Capesius, Alan; zope@zope.org Subject: Re: [Zope] Multiple ZODBC sources
I wonder if something like this would work:
<dtml-in selectFromTable1> <dtml-in selectFromTable2> <dtml-if idFromTable1 = idFromTable2> ....display parts of the two records.... </dtml-if> </dtml-in> </dtml-in>
Might be more efficient to make one of the dtml-ins a dtml-with and find the item with the matching id.