Re: [Zope] Multiple ZODBC sources
+---[ Capesius, Alan ]-- | I need to join tables from two ODBC sources, one MS Access one SQL. | | ZODBC DA seems to be limited to one data source at a time. Is there any way | to join tables from two different sources? Write a method to copy the results from Access into a temporary table of the other. Then do your query and clean up. -- Totally Holistic Enterprises Internet| P:+61 7 3870 0066 | Andrew Milton The Internet (Aust) Pty Ltd | F:+61 7 3870 4477 | ACN: 082 081 472 ABN: 83 082 081 472 | M:+61 416 022 411 | Carpe Daemon PO Box 837 Indooroopilly QLD 4068|[EMAIL PROTECTED]| ___ Zope maillist - [EMAIL PROTECTED] 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 )
Re: [Zope] Multiple ZODBC sources
Link the SQL table into Access and then use both from Zodbcda connected to Access. hth Phil - Original Message - From: "Capesius, Alan" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, November 22, 2000 3:56 PM Subject: [Zope] Multiple ZODBC sources I need to join tables from two ODBC sources, one MS Access one SQL. ZODBC DA seems to be limited to one data source at a time. Is there any way to join tables from two different sources? Alan Capesius, MCSE+20 Technical Support Engineer Sysmex Corporation of America [EMAIL PROTECTED] ___ Zope maillist - [EMAIL PROTECTED] 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 ) ___ Zope maillist - [EMAIL PROTECTED] 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 )
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. Interesting problem, I wish I had some time to play around with it. Chris On Thu, 23 Nov 2000, Andrew Kenneth Milton wrote: +---[ Capesius, Alan ]-- | I need to join tables from two ODBC sources, one MS Access one SQL. | | ZODBC DA seems to be limited to one data source at a time. Is there any way | to join tables from two different sources? Write a method to copy the results from Access into a temporary table of the other. Then do your query and clean up. -- Totally Holistic Enterprises Internet| P:+61 7 3870 0066 | Andrew Milton The Internet (Aust) Pty Ltd | F:+61 7 3870 4477 | ACN: 082 081 472 ABN: 83 082 081 472 | M:+61 416 022 411 | Carpe Daemon PO Box 837 Indooroopilly QLD 4068|[EMAIL PROTECTED]| ___ Zope maillist - [EMAIL PROTECTED] 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 ) ___ Zope maillist - [EMAIL PROTECTED] 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 )
Re: [Zope] Multiple ZODBC sources
Another thing that would work: dtml-in selectFromTable1 dtml-in expr="selectFromTable2(id=_['id'])" display record /dtml-in /dtml-in Chris On Wed, 22 Nov 2000, Phil Harris wrote: Link the SQL table into Access and then use both from Zodbcda connected to Access. hth Phil - Original Message - From: "Capesius, Alan" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, November 22, 2000 3:56 PM Subject: [Zope] Multiple ZODBC sources I need to join tables from two ODBC sources, one MS Access one SQL. ZODBC DA seems to be limited to one data source at a time. Is there any way to join tables from two different sources? Alan Capesius, MCSE+20 Technical Support Engineer Sysmex Corporation of America [EMAIL PROTECTED] ___ Zope maillist - [EMAIL PROTECTED] 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 ) ___ Zope maillist - [EMAIL PROTECTED] 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 ) ___ Zope maillist - [EMAIL PROTECTED] 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 )
RE: [Zope] Multiple ZODBC sources
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:[EMAIL PROTECTED]] Sent: Wednesday, November 22, 2000 10:31 AM To: Andrew Kenneth Milton Cc: Capesius, Alan; [EMAIL PROTECTED] 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. ___ Zope maillist - [EMAIL PROTECTED] 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 )
