[Zope-DB] multidatabase connections

[Greg Fischer]

ah, I get it.  Well my first thought was, "Make a sproc."  But in
Mysql you dont have that.  Unless you are using version 5.x, in fact I
think you would need 5.02 so you can actually return a result set from
the sproc.  It's still buggy though.

But...  you could make two connections in Zope, then do some dtml to
make it come out right.  Let's say you have cn1 and cn2 connections. 
Then you have zsql methods, sqlgetpeople and sqlgetplaces.  People is
on cn1, and Places is on cn2.

In your select statement, use an AS to rename the results if the id
columns are the same name, then your values that show up in the
Request namespace will be easily defineable.  For example, if you have
peopleid and peopleid in both tables, then do "select peopleid as
placespeopleid from places."

<dtml-in sqlgetpeople>
   <dtml-in sqlgetplaces>
     <dtml-var peoplecol> - <dtml-if "poepleid ==
placespeopleid"><dtml-var placescol></dtml-if>
   </dtml-in>
</dtml-in>

That's one way, but it really depends on your data structures.  You
may have to adjust how and when each of those are displayed.  One
drawback to this is that you would be calling the sqlgetplaces for
EACH record in sqlgetpeople, so if you have a very large result set,
it might be quite slow.  I have setup several reports that way though
because of the absence of stored procedures in Mysql.  I just know
before hand that the result sets would not be overly large.  Plus I
made a form so the user could reduce the results by selecting
criteria.

So basically you need to use a dtml-in and place an if statement and
call the other dtml-in, make sense?  I hope that helps.  If not, I am
assuming a lot here, so tell me more about your data structures and
the process involved for the users, because maybe you need to go about
this a whole different way.

Good luck!

Greg



On Tue, 8 Mar 2005 14:11:16 +1100, srikanth <srikanth.m at monsterfirst.com> wrote:
> Hi greg,
> 
>   Thank u very much for the answer. I am sorry if my question was not
> perfect because its easy to do this query in mysql directly, but what I
> want to do is to execute the same query from zope.
> 
>  Because from zope I need to connect to mysql, but zope uses one
> database connection at a time. There starts my problem, if it connects
> to one database at time, I cant access two databases at once to get my
> result set.
> 
> Ta.
> 
> -----Original Message-----
> From: zope-db-bounces at zope.org [mailto:zope-db-bounces at zope.org] On
> Behalf Of Greg Fischer
> Sent: Tuesday, March 08, 2005 3:44 AM
> To: zope-db at zope.org
> Subject: Re: [Zope-DB] multidatabase connections
> 
> Just to clearify my last message, here's an example I just tested in
> Mysql.
> 
> mysql> select fbcore.1B_Customers.CustomerID, tsdata.Players.PlayerID
> from fbcore.1B_Customers, tsdata.Players where
> fbcore.1B_Customers.CustomerID = tsdata.Players.PlayerID;
> 
> select database.table.column.
> 
> This only works if you have data that can be selected together, like
> customer records where the id's are matching.  If your data isnt
> matching, you might just use two zsql methods.  Depends on what you
> are trying to do, I guess.
> 
> Greg
> 
> On Mon, 7 Mar 2005 16:11:22 +1100, srikanth
> <srikanth.m at monsterfirst.com> wrote:
> >
> >
> >
> > Hi,
> >
> >   I am new to zope and am working on project that needs me to interact
> with
> > two different mysql databases to get my resultset.
> >
> > But with Zmysql database connection object I can connect to only one
> > database. So how do I use my two databases to query at the
> >
> > Same time.
> >
> >
> >
> >    Any help would be greatly appreciated.
> >
> >
> >
> >  Ta.
> >
> >
> > ---
> >  Outgoing mail is certified Virus Free.
> >  Checked by AVG anti-virus system (http://www.grisoft.com).
> >  Version: 6.0.859 / Virus Database: 585 - Release Date: 2/14/2005
> >
> > _______________________________________________
> > Zope-DB mailing list
> > Zope-DB at zope.org
> > http://mail.zope.org/mailman/listinfo/zope-db
> >
> >
> >
> 
> --
> Greg Fischer
> 1st Byte Solutions
> http://www.1stbyte.com
> _______________________________________________
> Zope-DB mailing list
> Zope-DB at zope.org
> http://mail.zope.org/mailman/listinfo/zope-db
> 
> ---
> Incoming mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.859 / Virus Database: 585 - Release Date: 2/14/2005
> 
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.859 / Virus Database: 585 - Release Date: 2/14/2005
> 
> 


-- 
Greg Fischer
1st Byte Solutions
http://www.1stbyte.com