Some Eclipse Foundation services are deprecated, or will be soon. Please ensure you've read this important communication.

Bug 348160

Summary:	JDBCDatabase does not load schemas from MS-SQL Server target database
Product:	[Tools] Data Tools	Reporter:	Bin Feng <bin.feng>
Component:	Enablement	Assignee:	Linda Chan <lchan>
Status:	RESOLVED FIXED	QA Contact:
Severity:	normal
Priority:	P2	CC:	galdunate, luismontanez2000, Marjan, scott_hathaway
Version:	1.8.2
Target Milestone:	1.11.1
Hardware:	PC
OS:	Windows 7
Whiteboard:

Description Bin Feng

2011-06-02 22:33:10 EDT

Build Identifier: DTP_1_8_2

org.eclipse.datatools.connectivity.sqm.core.rte.jdbc.JDBCDatabase does not override super class DatabaseImpl method getSchemas(). This cause org.eclipse.datatools.modelbase.sql.query.helper.DatabaseHelper.findSchema(Database database, String schemaName) line#210 "database.getSchemas()" return empty schemas list; Then method DatabaseHelper.findSchema() have to run into supplemental schema loading logic and get all schemas from all catalogs.
The supplemental schema loading logic works fine for Oracle, MySQL, Derby, UDB correctly. But the logic does not work well for MSSQL and SYBASE ASE, because execution DatabaseMetaData.getCatalogs() against these databases also returns catalogs for system databases "master", "msdb", "tempdb"; each system database catalogs can have same schema names "dbo"; This finally results DatabaseHelper.findSchema() find an incorrect schema. 
Method org.eclipse.datatools.sqltools.sqlbuilder.model.DatabaseHelper.getSchemaList(Database database) also has the same problem. This cause multiple schemas with the same schema name (but belongs to different catalogs) are returned by DatabaseHelper.getSchemaList().

To fix the problem, please add schema loading logic to JDBCDatabase.getSchemas().

Reproducible: Always

Steps to Reproduce:
The problem can be reproduced in BIRT env.
1. Create a BIRT report, add a "JDBC Database connection for Query Builder" and choose connection profile type "SQL Server".
2. Create a dataset for the datasource, "SQL select query" right click and select "Add tables" menu item. In the popup "table selection" dialog, multiple schemas with the same schema name are displayed; But actually they are belong to different catalogs. 
Expected result: schemas belong to the specified database are displayed.

Comment 1 Brian Fitzpatrick

2011-06-15 13:13:53 EDT

We currently don't have anyone to do sustaining or new feature work on MSSQL support. Will have to investigate once we have resources to do so.

Comment 2 Linda Chan

2013-08-15 20:33:10 EDT

*** Bug 356347 has been marked as a duplicate of this bug. ***

Comment 3 Linda Chan

2013-08-15 22:59:51 EDT

Resolved.  Adopted the base class approach of calling a JDBC API method (DatabaseMetaData#getSchemas) for schema meta-data, instead of executing a custom SQL query to select from a MSSQL system catalog table.
Tested with Microsoft sqljdbc 1.2 and 4.0 .

Git commit logs:
1) http://git.eclipse.org/c/datatools/org.eclipse.datatools.connectivity.git/commit/?id=9144ad36ad4c50893a3999aad1e78779c0c4dcd9
2) http://git.eclipse.org/c/datatools/org.eclipse.datatools.enablement.msft.git/commit/?id=9c97c882dd36044c00ef012ef61b4186f98aa3e5
3) http://git.eclipse.org/c/datatools/org.eclipse.datatools.sqltools.git/commit/?id=957814d117632e651c0de9e88dded78cea290af0

Comment 4 Linda Chan

2013-08-15 23:25:32 EDT

*** Bug 256987 has been marked as a duplicate of this bug. ***

Comment 5 Linda Chan

2013-08-15 23:40:20 EDT

*** Bug 284104 has been marked as a duplicate of this bug. ***

Comment 6 Linda Chan

2013-08-15 23:48:18 EDT

*** Bug 287587 has been marked as a duplicate of this bug. ***

Comment 7 Linda Chan

2013-08-22 14:53:44 EDT

The fix is in DTP 1.11.1 RC1 milestone build, i.e. in Kepler SR1 RC1.