#1440 Object Tree broken for SQL Server

[Robert]

The object tree is broken for MS SQL Server databases.

In a SQL server instance containing multiple databases when the cataloge is loaded / changed ALL listed databases in the tree are updated with the objects of the database selected in the "catalog" dropbox.

Expectation w'd be a normal browsing of database objects with respect to the objects contained in each database.

I suggest the cataloge selection should only have the functionality of a USE command, e.g. selecting the cataloge "testDb" equals "use testDB".

[Gerd Wagner]

When a JDBC connection's setCatalog() method is called, which SQuirreL's catalogs combo box does and is supposed to do, the state of the connection changes in such a way that for example the result of calling DataBaseMetaData.getTables(...) returns different results. The code below shows this on the example of MSSQL.

Because of this it's correct SQuirreL reloads the meta data and thus reflects the connection's state change. As a consequence this bug won't be fixed.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class MSSQLCatalogTest
{
   public static void main(String[] args) throws ClassNotFoundException, SQLException
   {

      Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

      final Connection con = DriverManager.getConnection("<yourJdbcUrl>", "<youruser>", "<yourPassword>");

      con.setCatalog("Catalog1");
      ArrayList<String> qualifiedNames1 = getQualifiedNamesSorted(con);

      con.setCatalog("Catalog2");
      ArrayList<String> qualifiedNames2 = getQualifiedNamesSorted(con);


      // qualifiedNames1 and qualifiedNames2 will be different as soon as the catalogs' spaces are different.

   }

   private static ArrayList<String> getQualifiedNamesSorted(Connection con) throws SQLException
   {
      ResultSet res = con.getMetaData().getTables(null, null, null, new String[]{"TABLE"});

      ArrayList<String> qualifiedNames = new ArrayList<>();
      while(res.next())
      {
         String qualified;

         qualified = res.getString("TABLE_CAT");
         qualified += "." + res.getString("TABLE_SCHEM");
         qualified += "." + res.getString("TABLE_NAME");
         qualifiedNames.add(qualified);
      }

      qualifiedNames.sort(String::compareTo);

      return qualifiedNames;
   }
}