You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@cocoon.apache.org by Markus Vaterlaus <mv...@gmail.com> on 2004/10/26 13:25:11 UTC
Best Practice Connecting to n Databases
Hi all,
I'm keen to now your experiences and best practices for the following situation:
I need to be able to select one out of n databases and query it.
N is actually a number around 50.
The queries are the same on each database.
All information relevant to the access to such a database (user name,
password, server name, port, db name) is available in one central
place (xml file or database).
The user selects the database and the first query is executed.
I thought about building dynamically an ESQL serverpage which takes
the access parameters for the selected database as an input. I haven't
tried it yet, therefore I don't know if it's possible at all. What are
your suggestions? Do you have other ideas?
Cheers,
Markus
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org
Re: Best Practice Connecting to n Databases
Posted by Markus Vaterlaus <mv...@gmail.com>.
Hi Nick, hi list,
thank you for the examples. I use instead of the SQL transformer the
ESQL-Taglib. However, this shouldn't be a problem. Before I start,
does anybody know if the connections to the databases remain
established even after the selection of another DB? If I'm right, the
connection remains open if you define the pool in cocoon.xconf. I
don't know what's the behaviour of cocoon if there are let's say
connections to 20 different databases.
Markus
On Tue, 26 Oct 2004 09:45:46 -0400, Nick Goupinets
<ng...@openskysolutions.ca> wrote:
> Hi Markus,
>
> I don't think there should be any problems to perform what you suggest.
> I can only add that it's not necessary to "hardcode" the connection
> information. You can pass it dynamically in either database url or as
> params to SQL transformer.
>
> For example, all three queries are equivalent:
>
> <jdbc name="personnel">
> <dburl>jdbc:mysql://localhost:3306/test</dburl>
> <user>user</user>
> <password>aaa</password>
> </jdbc>
>
> <document>
> <sql:execute-query xmlns:sql="http://apache.org/cocoon/SQL/2.0">
> <sql:use-connection>personnel</sql:use-connection>
> <sql:query>
> select id,name from department_table
> </sql:query>
> </sql:execute-query>
> </document>
>
> ----------------------------------
>
> <document>
> <sql:execute-query xmlns:sql="http://apache.org/cocoon/SQL/2.0">
> <sql:dburl>jdbc:mysql://localhost:3306/test</sql:dburl>
> <sql:user>user</sql:user>
> <sql:password>aaa</sql:password>
> <sql:query>
> select id,name from department_table
> </sql:query>
> </sql:execute-query>
> </document>
>
> ----------------------------------
>
> <document>
> <sql:execute-query xmlns:sql="http://apache.org/cocoon/SQL/2.0">
> <sql:dburl>jdbc:mysql://localhost:3306/test?user=user&password=aaa</sql:dburl>
> <sql:query>
> select id,name from department_table
> </sql:query>
> </sql:execute-query>
> </document>
>
>
>
>
> Markus Vaterlaus wrote:
> > Hi all,
> >
> > I'm keen to now your experiences and best practices for the following situation:
> >
> > I need to be able to select one out of n databases and query it.
> > N is actually a number around 50.
> > The queries are the same on each database.
> > All information relevant to the access to such a database (user name,
> > password, server name, port, db name) is available in one central
> > place (xml file or database).
> > The user selects the database and the first query is executed.
> >
> > I thought about building dynamically an ESQL serverpage which takes
> > the access parameters for the selected database as an input. I haven't
> > tried it yet, therefore I don't know if it's possible at all. What are
> > your suggestions? Do you have other ideas?
> >
> > Cheers,
> >
> > Markus
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
> > For additional commands, e-mail: users-help@cocoon.apache.org
> >
> >
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
> For additional commands, e-mail: users-help@cocoon.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org
Re: Best Practice Connecting to n Databases
Posted by Nick Goupinets <ng...@openskysolutions.ca>.
Hi Markus,
I don't think there should be any problems to perform what you suggest.
I can only add that it's not necessary to "hardcode" the connection
information. You can pass it dynamically in either database url or as
params to SQL transformer.
For example, all three queries are equivalent:
<jdbc name="personnel">
<dburl>jdbc:mysql://localhost:3306/test</dburl>
<user>user</user>
<password>aaa</password>
</jdbc>
<document>
<sql:execute-query xmlns:sql="http://apache.org/cocoon/SQL/2.0">
<sql:use-connection>personnel</sql:use-connection>
<sql:query>
select id,name from department_table
</sql:query>
</sql:execute-query>
</document>
----------------------------------
<document>
<sql:execute-query xmlns:sql="http://apache.org/cocoon/SQL/2.0">
<sql:dburl>jdbc:mysql://localhost:3306/test</sql:dburl>
<sql:user>user</sql:user>
<sql:password>aaa</sql:password>
<sql:query>
select id,name from department_table
</sql:query>
</sql:execute-query>
</document>
----------------------------------
<document>
<sql:execute-query xmlns:sql="http://apache.org/cocoon/SQL/2.0">
<sql:dburl>jdbc:mysql://localhost:3306/test?user=user&password=aaa</sql:dburl>
<sql:query>
select id,name from department_table
</sql:query>
</sql:execute-query>
</document>
Markus Vaterlaus wrote:
> Hi all,
>
> I'm keen to now your experiences and best practices for the following situation:
>
> I need to be able to select one out of n databases and query it.
> N is actually a number around 50.
> The queries are the same on each database.
> All information relevant to the access to such a database (user name,
> password, server name, port, db name) is available in one central
> place (xml file or database).
> The user selects the database and the first query is executed.
>
> I thought about building dynamically an ESQL serverpage which takes
> the access parameters for the selected database as an input. I haven't
> tried it yet, therefore I don't know if it's possible at all. What are
> your suggestions? Do you have other ideas?
>
> Cheers,
>
> Markus
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
> For additional commands, e-mail: users-help@cocoon.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@cocoon.apache.org
For additional commands, e-mail: users-help@cocoon.apache.org