Hi All,
To me the results of getSchemas appears wrong and the catalog/schema values the wrong way round!
Using latest version of Sql Server 2005 Express with 1.2.2828 JDBC driver.
Created a database called: db_connection_tests
Created some tables inside this database called:
These show in Sql Server Manager as:
- dbo.main
- dbo.child
- dbo.lookup
If I now log on through JDBC as user "dddddddddddd" and run 2 metadata calls (getCatalogs() and getSchemas()) the results to me do not match, that is the schema and catalog seem to be wrong in getSchemas.
Why is the catalog DBO returned for schema d_connection_tests in getSchemas(), when not in results of getCatalogs()?
TABLE_SCHEM db_connection_tests
TABLE_CATALOG dbo
getDatabaseProductName: Microsoft SQL Server
getDatabaseProductVersion: 9.00.3042
getDatabaseMajorVersion: 9
getDatabaseMinorVersion: 0
meta.getDriverMajorVersion(): 1
meta.getDriverMinorVersion(): 2
meta.getDriverName(): Microsoft SQL Server 2005 JDBC Driver
meta.getDriverVersion(): 1.2.2828.100
List of schemas (snippet):
TABLE_SCHEM db_connection_tests
TABLE_CATALOG dbo
List of catalogs:
TABLE_CAT db_connection_tests
TABLE_CAT master
TABLE_CAT model
TABLE_CAT msdb
TABLE_CAT tempdb
Tried JTds driver and results for getSchemas() are different!
getDatabaseProductName: Microsoft SQL Server
getDatabaseProductVersion: 09.00.3042
getDatabaseMajorVersion: 9
getDatabaseMinorVersion: 0
meta.getDriverMajorVersion(): 1
meta.getDriverMinorVersion(): 2
meta.getDriverName(): jTDS Type 4 JDBC Driver for MS SQL Server and Sybase
meta.getDriverVersion(): 1.2.2
List of schemas:
TABLE_SCHEM db_connection_tests
TABLE_CATALOG null
Code
package test.com.database;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
public class SqlServeTest_DriverMs {
private Connection con;
private DatabaseMetaData meta;
public static void main(String[] args) throws ClassNotFoundException, SQLException {
SqlServeTest_DriverMs sqlServeTest = new SqlServeTest_DriverMs();
sqlServeTest.runTests();
}
protected void runTests() throws ClassNotFoundException, SQLException {
// Class.forName("net.sourceforge.jtds.jdbc.Driver"); // JTds Driver
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); // MS Driver
try {
// Jtds Drvier
// con = DriverManager.getConnection("jdbc:jtds
qlserver://localhost:1433/db_connection_tests;user=dddddddddddd;password=root");
// MS Driver
con = DriverManager.getConnection("jdbc
qlserver://localhost:1433;user=dddddddddddd;password=root;database=db_connection_tests");
meta = con.getMetaData();
System.out.println("getDatabaseProductName: " + meta.getDatabaseProductName());
System.out.println("getDatabaseProductVersion: " + meta.getDatabaseProductVersion());
System.out.println("getDatabaseMajorVersion: " + meta.getDatabaseMajorVersion());
System.out.println("getDatabaseMinorVersion: " + meta.getDatabaseMinorVersion());
System.out.println("meta.getDriverMajorVersion(): " + meta.getDriverMajorVersion());
System.out.println("meta.getDriverMinorVersion(): " + meta.getDriverMinorVersion());
System.out.println("meta.getDriverName(): " + meta.getDriverName());
System.out.println("meta.getDriverVersion(): " + meta.getDriverVersion());
printOutSchemas();
printOutCatalogs();
} finally {
if (con != null) {
try {
con.close();
} catch (Exception e) { // Ignore error
}
}
}
}
protected void printOutSchemas() throws SQLException {
ResultSet rs = null;
try {
rs = meta.getSchemas();
System.out.println("\nList of schemas: ");
printResults(rs);
} finally {
if (rs != null) rs.close();
}
}
protected void printOutCatalogs() throws SQLException {
ResultSet rs = null;
try {
rs = meta.getCatalogs();
System.out.println("\nList of catalogs: ");
printResults(rs);
} finally {
if (rs != null) rs.close();
}
}
protected void printResults(ResultSet rs) throws SQLException {
final int cols = rs.getMetaData().getColumnCount();
while (rs.next()) {
System.out.println();
for (int i = 1; i <= cols; i++) {
System.out.println('\t' + rs.getMetaData().getColumnName(i) + '\t' + rs.getString(i));
}
}
}
}
