Java DatabaseMetaData and MS SQL 2005

[jweaver@xaware.com]

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);

DatabaseMetaDat a dbMeta = conn.getMetaDat a();
ResultSet columnRes = dbMeta.getProce dureColumns(cat , schem, name,
"%");
while (columnRes.next ())
{
String procCat = columnRes.getSt ring("PROCEDURE _CAT");
String procSchem = columnRes.getSt ring("PROCEDURE _SCHEM");
String procName = columnRes.getSt ring("PROCEDURE _NAME");
String colName = columnRes.getSt ring("COLUMN_NA ME");
short colType = columnRes.getSh ort("COLUMN_TYP E");
short dataType = columnRes.getSh ort("DATA_TYPE" );
String typeName = columnRes.getSt ring("TYPE_NAME ");
int precision = columnRes.getIn t("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]

Re: Java DatabaseMetaDat a and MS SQL 2005

(jweaver@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, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx