[Zope] Multiple ZODBC sources

[Capesius, Alan]

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.
>>>