Solved

Tomcat Datasource and MS SQL 2000

Posted on 2004-09-21
15
235 Views
Last Modified: 2013-11-24
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
Comment
Question by:shenlong_wc
  • 8
  • 4
  • 2
  • +1
15 Comments
 
LVL 35

Expert Comment

by:girionis
ID: 12110262
> 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
 
LVL 35

Expert Comment

by:girionis
ID: 12110285
You can select data from different tables if you need but not from different databases with the same datasource.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 12110294
You should not need anything special. What, if any, problems are you having?
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

Author Comment

by:shenlong_wc
ID: 12110321
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
 
LVL 35

Expert Comment

by:girionis
ID: 12110338
What about creating a stored procedure and calling it from your Java code?
0
 
LVL 35

Expert Comment

by:girionis
ID: 12110342
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
 

Author Comment

by:shenlong_wc
ID: 12110351
isn't there a way i can do it on java side ?
0
 
LVL 35

Expert Comment

by:girionis
ID: 12110381
Read the data from one database, then issue another query to the second database using the data from the previous one.
0
 
LVL 9

Expert Comment

by:Venci75
ID: 12110511
try to use fully qualified table name:
<database>.<owner>.<table>
0
 

Author Comment

by:shenlong_wc
ID: 12118543
>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
 
LVL 35

Expert Comment

by:girionis
ID: 12120410
> 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
 

Author Comment

by:shenlong_wc
ID: 12120541
i don't understand .. how can you execute a query without a  datasource ?
apprecitae it if you can show some logic with code ?
0
 
LVL 35

Expert Comment

by:girionis
ID: 12120772
0
 
LVL 35

Expert Comment

by:girionis
ID: 12120775
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
 
LVL 9

Accepted Solution

by:
Venci75 earned 50 total points
ID: 12121051
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

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
JDeveloper 12c for 32 bit 4 84
even odd program using while loop 3 40
table example 4 29
servlet example issue 6 38
By the end of 1980s, object oriented programming using languages like C++, Simula69 and ObjectPascal gained momentum. It looked like programmers finally found the perfect language. C++ successfully combined the object oriented principles of Simula w…
This was posted to the Netbeans forum a Feb, 2010 and I also sent it to Verisign. Who didn't help much in my struggles to get my application signed. ------------------------- Start The idea here is to target your cell phones with the correct…
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now