Solved

Tomcat Datasource and MS SQL 2000

Posted on 2004-09-21
15
236 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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 

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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
numbers ascending pyramid 101 213
web application structure 18 102
hibernate example issues from command prompt 10 50
Java: anonymous class 4 29
Introduction Java can be integrated with native programs using an interface called JNI(Java Native Interface). Native programs are programs which can directly run on the processor. JNI is simply a naming and calling convention so that the JVM (Java…
Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
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:
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…

830 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