SQL Server JDBC - JDBC DatabaseMetaData getIndexInfo() returning null values

Author

JDBC DatabaseMetaData getIndexInfo() returning null values

Adam_R

2005-12-09, 3:23 am

Using the DatabaseMetaData class’s method getIndexInfo seems to be returning
some strange information for the first result set object. For example after
createing a database with one table and one index, then connecting to it,
getting the meta data and calling getIndexInfo() I get the following results

TABLE_CAT : TestDataBase
TABLE_SCHEM : dbo
TABLE_NAME : Test
NON_UNIQUE : false
INDEX_QUALIFIER : null
INDEX_NAME : null
TYPE : 0
ORDINAL_POSITION : 0
COLUMN_NAME : null
ASC_OR_DESC : null
CARDINALITY : 0
PAGES : 0

TABLE_CAT : TestDataBase
TABLE_SCHEM : dbo
TABLE_NAME : Test
NON_UNIQUE : 1
INDEX_QUALIFIER : Test
INDEX_NAME : IDX_Test
TYPE : 3
ORDINAL_POSITION : 1
COLUMN_NAME : ColumnA
ASC_OR_DESC : A
CARDINALITY : null
PAGES : null

as you can see from above, the output for the first result set object
contained some strange values (null for INDEX_NAME etc). Is there any reson
for this or am I simply doing something wrong.

below is the sql used to created the database and the java class used to
generate the output above.

SQL Statments
==========

CREATE DATABASE TestDataBase
CREATE TABLE Test(
ColumnA varchar(20) default 'This Is A Test',
ColumnB int
)
CREATE INDEX IDX_Test on Test(ColumnA)

Java Class
=======

import java.sql.*;

public class IndexInfoTest {

public IndexInfoTest() {
Connection objConn = null;
String strDBName =" jdbc:microsoft:sqlse
rver://localhost:"+
" 1433;databaseName=Te
stDataBase";
String strUserName = "tcuser";
String strPassword = "tcuser";
String strDriverName = "com.microsoft.jdbc.sqlserver.SQLServerDriver";

try
{
Class. forName(strDriverNam
e);

//get Connection
objConn =
DriverManager. getConnection(strDBN
ame,strUserName,strP
assword);

//get Meta Data
DatabaseMetaData objDBMetaData = objConn.getMetaData();

//getIndexInfo from Meta Data
ResultSet objResSet =
objDBMetaData. getIndexInfo(null,nu
ll,"Test",false,true);

//get the ResultSetMetaData
ResultSetMetaData objRSMD = objResSet.getMetaData();

//get the total columns in ResultSetMetaData
int intTotalColumns = objRSMD.getColumnCount();

//loop through printing off the result set objects
while(objResSet.next())
{
for(int i =1; i < intTotalColumns; i++)
System.out.println(objRSMD.getColumnName(i)+" :
"+objResSet.getString(i));

System.out.println("");
}
}

catch(Exception e){
e.printStackTrace();
}finally{
try
{
if(objConn!=null) objConn.close();

}catch(Exception e){e.printStackTrace();}
}
}

//main method
public static void main(String args[]){
IndexInfoTest iit = new IndexInfoTest();
}
}

we are using the following system setup

Driver Name : com.microsoft.jdbc.sqlserver.SQLServerDriver, SP 3, Version
2.2.0040
Java Version : Sun Micro Systems JDK 1.5.0_05
Operating System : Microsoft Windows Server 2003
SQL Server Version : Microsoft SQL Server 2000 SP4

Any help with this would be grately appreciated,

Adam

Sue Purkis

2005-12-21, 8:23 pm

Adam,
I see the same behavior as reported below and found that the key data point
to notice is the TYPE value returned in the results. The JDBC spec defines
the possible types as follows:

public static final short
tableIndexClustered
1

public static final short
tableIndexHashed
2

public static final short
tableIndexOther
3

public static final short
tableIndexStatistic
0

Based on this detail, the data returned in the first row is purely
statistical(TYPE=0). The spec says this about statistics:

Indicates that this column contains table statistics that are returned in
conjunction with a table's index descriptions.

The second row of data is of type tableIndexOther(TYPE
=3) which is defined
as:

Indicates that this table index is not a clustered index, a hashed index,
or table statistics; it is something other than these.

So it looks like the driver is getting the proper data from the DB and
returning the correct metadata to the JDBC app. SQL Server has a system SP
called sp_autostats which when run on the table in the repro steps above
returns 'ON' for the automatic UPDATE STATISTICS setting on this table.
This procedure can be run to turn automatic statistics recording off, but
you should consult MS documentation or support to learn the implications of
such an action before proceeding.

Sue Purkis
DataDirect Technologies

Adam_R

2005-12-22, 7:23 am

"Sue Purkis" wrote:

> Adam,
> I see the same behavior as reported below and found that the key data point
> to notice is the TYPE value returned in the results. The JDBC spec defines
> the possible types as follows:
>
>
> public static final short
> tableIndexClustered
> 1
>
> public static final short
> tableIndexHashed
> 2
>
> public static final short
> tableIndexOther
> 3
>
> public static final short
> tableIndexStatistic
> 0
>
>
> Based on this detail, the data returned in the first row is purely
> statistical(TYPE=0). The spec says this about statistics:
>
> Indicates that this column contains table statistics that are returned in
> conjunction with a table's index descriptions.
>
> The second row of data is of type tableIndexOther(TYPE
=3) which is defined
> as:
>
> Indicates that this table index is not a clustered index, a hashed index,
> or table statistics; it is something other than these.
>
> So it looks like the driver is getting the proper data from the DB and
> returning the correct metadata to the JDBC app. SQL Server has a system SP
> called sp_autostats which when run on the table in the repro steps above
> returns 'ON' for the automatic UPDATE STATISTICS setting on this table.
> This procedure can be run to turn automatic statistics recording off, but
> you should consult MS documentation or support to learn the implications of
> such an action before proceeding.
>
> Sue Purkis
> DataDirect Technologies
>

Sue,
Thankyou for taking the time to reply to both of my posts. we had already
altered our application to ignor such infomaition as it seemed to return
information which was not beneficial to us, however now we know why.
thanks again,
Adam

Sponsored Links