Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • Last Modified:

Tomcat Datasource and MS SQL 2000

Hi,

i'm using tomcat datasource to access a ms sql2000 server, and my account has access to more than one database (by database i mean like northwind .. not different sql servers) .

question is , how can i access both database with a single datasource ? how will my code / sql statement look like ?
0
shenlong_wc
Asked:
shenlong_wc
  • 8
  • 4
  • 2
  • +1
1 Solution
 
girionisCommented:
> question is , how can i access both database with a single datasource
> ? how will my code / sql statement look like ?

YOu cannot. Create one datasource for each database (conenciton pool).

0
 
girionisCommented:
You can select data from different tables if you need but not from different databases with the same datasource.
0
 
CEHJCommented:
You should not need anything special. What, if any, problems are you having?
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
shenlong_wcAuthor Commented:
because of the way our database is currently structured , the tables i need are scattered over different databases within the SQL server. Currently what i do now is create a seperate datasource for each database, but i'm looking for a way to access all these database ( my account has the rights to all of them ) using a single datasource if possible .
0
 
girionisCommented:
What about creating a stored procedure and calling it from your Java code?
0
 
girionisCommented:
http://www.onjava.com/pub/a/onjava/2003/08/13/stored_procedures.html assuming that you can call different databases from one stored procedure.
0
 
shenlong_wcAuthor Commented:
isn't there a way i can do it on java side ?
0
 
girionisCommented:
Read the data from one database, then issue another query to the second database using the data from the previous one.
0
 
Venci75Commented:
try to use fully qualified table name:
<database>.<owner>.<table>
0
 
shenlong_wcAuthor Commented:
>try to use fully qualified table name:
><database>.<owner>.<table>

how do i do that ?

>Read the data from one database, then issue another query to the second database using the >data from the previous one.

but how do i issue a second query to the second database without creating another data source ?

is it possible for me to specify the database name of the table in the sql ? like SELECT * FROM Northwind.tablename and SELECT * FROM Mydatabase.Mytable or something like that ?
0
 
girionisCommented:
> but how do i issue a second query to the second database without creating another data source ?

Just use a plain statement witout a data source.
0
 
shenlong_wcAuthor Commented:
i don't understand .. how can you execute a query without a  datasource ?
apprecitae it if you can show some logic with code ?
0
 
girionisCommented:
0
 
girionisCommented:
A datasource is usually associated with a connection pool. You can have simple connections though that are not taken off the pool and work with them.
0
 
Venci75Commented:
select * from [pubs].[dbo].[authors]

where 'pubs' is the database name, 'dbo' is the table owner (it is a user name) and 'authors' is the table name
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now