Java DatabaseMetaData and MS SQL 2005

[jweaver]

I'm having a problem getting back appropriate metadata for stored
procedures from MS SQL Server 2000 and 2005.

I've created a simple stored procedure that has an output param that
is a cursor.

When I ask for the metadata for that stored procedure using a JDBC
driver I get back a datatype value for my parameter specifying an int
not a result set.

Here is my stored procedure:

CREATE PROCEDURE xasp_INx_OUTcur_RETint

@OutCrsr CURSOR VARYING OUTPUT AS

SET @OutCrsr = CURSOR FOR

SELECT LASTNAME, FIRSTNAME

FROM CONTACTS2

OPEN @OutCrsr

RETURN 7

Here is the java code:

Connection conn = driver.connect(url, props);

DatabaseMetaData dbMeta = conn.getMetaData();
ResultSet columnRes = dbMeta.getProcedureColumns(cat, schem, name,
"%");
while (columnRes.next())
{
String procCat = columnRes.getString("PROCEDURE_CAT");
String procSchem = columnRes.getString("PROCEDURE_SCHEM");
String procName = columnRes.getString("PROCEDURE_NAME");
String colName = columnRes.getString("COLUMN_NAME");
short colType = columnRes.getShort("COLUMN_TYPE");
short dataType = columnRes.getShort("DATA_TYPE");
String typeName = columnRes.getString("TYPE_NAME");
int precision = columnRes.getInt("PRECISION");
// pass this info on to another method
}

The dataType is set to the type returned from the procedure not to the
type for the param.

Am I doing something stupid here? Did I create my stored procedure
wrong?

Any help would be appreciated.

Thanks.
Jon

[Erland Sommarskog]

(jw*****@xaware.com) writes:

I'm having a problem getting back appropriate metadata for stored
procedures from MS SQL Server 2000 and 2005.

I've created a simple stored procedure that has an output param that
is a cursor.

When I ask for the metadata for that stored procedure using a JDBC
driver I get back a datatype value for my parameter specifying an int
not a result set.

I'm not really sure that I understand what you get back, but since there
is no result set from this procedure, you can't get back any information
about it.

Anyway, cursor is something you should avoid. In most caes, you should
look for a set-based solution. Cursors can be magnitudes slower.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx