JDBC - Connect to many databases
I'm working on a program that will present information from a SQL Server DB to the user. There will be many DBs in this SQL Server, and I'm hoping to have only one User DSN to connect to the database (so I won't have to add methods to the DB tier of this project).
What I'd like to do is as follows (exception handling omitted here for brevity):
Connection conn = null;
conn = DriverManager.getConnection("jdbcdbcBName", null, null); (okay,, why are smilies appearing in my code??? JAD)
At this point I can query the dbo.sysdatabases table and get a listing of all DBs in SQL Server .
What I'd like to be able to do is either re-assign conn to a different database ("Project1" for instance), or have a second Connection object that I can point to another database. What I'm finding is that the conn.setCatalog() only appears to work for "master". When I try to point to another db, I first get a warning about:
[SQL Server]Changed database context to 'Project1'
and when I try to query one of the tables, I get
[ODBC Driver Manager] Invalid cursor state.
Is it possible to dynamically connect to the different databases within this SQL Server, or will I have to create a different User DSN for each?
Any thoughts would be appreciated.
Last edited by JamesOfOOST; 03-16-2005 at 02:22 PM.
Reason: Smiles appeared
The smileys, - use the code tags around the code examples. It is written like:
[ (and then) code (and then) ] before the code and you use the
same after end w. a slash before code.
As for the DSN I think you will need to establish several connections.
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL