SQL Server JDBC - Error in metadata in SQL 2000 because of database name

Author

Error in metadata in SQL 2000 because of database name

Ricardo Saito

2006-03-30, 9:23 am

When I try to get the Metadata for schemas/tables/columns in SQL Server 2000
using the JDBC Metadata API (java.sql.DatabaseMetaData.getTables() and
java.sql.DatabaseMetaData.getColumns() ) and the database name has a space or
dot in its name (Eg: "Database 1", "Database.name" ) , I got empty results
sets.
But the database has tables and columns.
I am using the driver SQL Server 2000 SP3 and SQL Server 2000 SP3.
Is there a fix or workaround available?

Thanks!

Sue Purkis

2006-04-03, 11:23 am

Ricardo,
I did some investigation into this.
I was able to retrieve tables from a database with a space in it.

Here is a snippet of the code I used:

Properties p = new Properties();
p.put("user", "sue1");
p.put("password","sue1");
p.put("selectMethod", "direct");
p.put("DatabaseName", "sue 1");

con =
java.sql.DriverManager.getConnection(" jdbc:microsoft:sqlse
rver://localhost:1433",
p);
java.sql.DatabaseMetaData dmd = con.getMetaData();

System.out.println("DataBase Version: " + dmd. getDatabaseProductNa
me() + " "
+dmd. getDatabaseProductVe
rsion());
System.out.println("Driver Version: " + dmd.getDriverName() + " " +
dmd. getDriverVersion());

System.out.println("JVM Version: " +
System.getProperty("java.vm.vendor") + " " +
System.getProperty("java.vm.version"));

java.sql.ResultSet rs = dmd.getTables(null, null, null, null);
while (rs.next()) {
System.out.println("CAT:"+rs.getString("TABLE_CAT")
+ " NAME:"+rs.getString("TABLE_NAME"));
}
rs.close();

and the output that I received:

DataBase Version: Microsoft SQL Server Microsoft SQL Server 2000 - 8.00.760
(Itel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

Driver Version: SQLServer 2.2.0049
JVM Version: Sun Microsystems Inc. 1.5.0_06-b05
CAT:sue 1 NAME:syscolumns
CAT:sue 1 NAME:syscomments
CAT:sue 1 NAME:sysdepends
CAT:sue 1 NAME:sysfilegroups
CAT:sue 1 NAME:sysfiles
CAT:sue 1 NAME:sysfiles1
CAT:sue 1 NAME:sysforeignkeys
CAT:sue 1 NAME:sysfulltextcata
logs
CAT:sue 1 NAME:sysfulltextnoti
fy
CAT:sue 1 NAME:sysindexes
CAT:sue 1 NAME:sysindexkeys
CAT:sue 1 NAME:sysmembers
CAT:sue 1 NAME:sysobjects
CAT:sue 1 NAME:syspermissions
CAT:sue 1 NAME:sysproperties
CAT:sue 1 NAME:sysprotects
CAT:sue 1 NAME:sysreferences
CAT:sue 1 NAME:systypes
CAT:sue 1 NAME:sysusers
CAT:sue 1 NAME:dtproperties
CAT:sue 1 NAME:sue 1
CAT:sue 1 NAME:sysconstraints
CAT:sue 1 NAME:syssegments

One thing I did discover was in regards to tables that also had spaces in
their names. In order to retrieve tables whose names contained a space, the
user had to have System Administrator role. Without this, the tables
without spaces were returned. My team confirmed that the same behavior is
seen through SQL Query Analyzer -- so it does not seem to be a JDBC issue.

Let us know if you have any more questions.

Sue Purkis
DataDirect Technologies

"Ricardo Saito" <Ricardo Saito@discussions.microsoft.com> wrote in message
news:3B4EAEC8-D0CB-474E-A424- A202214D5FF0@microso
ft.com...
> When I try to get the Metadata for schemas/tables/columns in SQL Server
> 2000
> using the JDBC Metadata API (java.sql.DatabaseMetaData.getTables() and
> java.sql.DatabaseMetaData.getColumns() ) and the database name has a space
> or
> dot in its name (Eg: "Database 1", "Database.name" ) , I got empty results
> sets.
> But the database has tables and columns.
> I am using the driver SQL Server 2000 SP3 and SQL Server 2000 SP3.
> Is there a fix or workaround available?
>
> Thanks!
>

Sponsored Links