JDBC - Connect to many databases

[JamesOfOOST]

Hi all,

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;
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
conn = DriverManager.getConnection("jdbcdbcBName", null, null); (okay,, why are smilies appearing in my code??? JAD)
conn.setCatalog("master");

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.

James D.

[sjalle]

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.