Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Location
    Chicago
    Posts
    54

    Unanswered: Query tables in 2 databases using JDBC.

    I need to Query tables in 2 databases after getting a connection. I cannot make this piece of code to work. In db2, oracle this type of code works.


    Class.forName("com.microsoft.jdbc.sqlserver.SQLSer verDriver");
    String sys = “jdbc:microsoft:sqlserver://192.168.0.152";
    conn = DriverManager.getConnection(sys, "name", "pswd");
    Statement stmt = conn.createStatement();

    ResultSet rs = stmt.executeQuery("SELECT * FROM pubs.jobs");

    rs = stmt.executeQuery("SELECT * FROM mydb.myfile");


    If I run this I get invalid object “pubs.jobs” error.

    Any ideas?
    Thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You are missing the dbo (object owner) in the table names. You want to use something more like
    Code:
    Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
    String sys = “jdbc:microsoft:sqlserver://192.168.0.152"; 
    conn = DriverManager.getConnection(sys, "name", "pswd");
    Statement stmt = conn.createStatement();
    
    ResultSet rs = stmt.executeQuery("SELECT * FROM pubs.dbo.jobs"); 
    
    rs = stmt.executeQuery("SELECT * FROM mydb.dbo.myfile");
    I think that should get you rocking and rolling nicely.

    -PatP

  3. #3
    Join Date
    Jan 2004
    Location
    Chicago
    Posts
    54
    Thanks Patp!
    Now it works.
    is dbo something that has to be included? I mean is it always the owner of tables?
    Sorry I am a sql server newbie and know very little about it.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    dbo is "data base owner", and they normally "own" all of the objects in a production database. You can think of different owners in SQL Server much like different schemas in Oracle.

    When using "one part names", such as a table name or a procedure name by itself, you don't need to use dbo. When using anything more than one part names (including the database), you should always use the owner name even though it can be allowed to default so that master.dbo.sysdatabases and master..sysdatabases are usually the same thing. Explicitly providing dbo allows object references to compile without the need for later lookup, so it also helps performance to always explicitly provide the dbo.

    -PatP

  5. #5
    Join Date
    Jan 2004
    Location
    Chicago
    Posts
    54
    Thanks alot Pat!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •