Posted by jweaver on 02/12/07 23:07

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

[Back to original message]