Solved

Tomcat Datasource and MS SQL 2000

Posted on 2004-09-21
15
238 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Automating Terraform w Jenkins & AWS CodeCommit

How to configure Jenkins and CodeCommit to allow users to easily create and destroy infrastructure using Terraform code.

 

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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
A solution for Fortify Path Manipulation.
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
Suggested Courses

627 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