Parameter @x Was Not Defined For Stored Procedure... With Ms_sql Jdbc

I am trying to execute a stored procedure using SQL Server JDBC in a method: //Connection connection, String sp_name, Mapparams input to the method DatabaseMe

Solution 1:

Update 2017-10-07: The merge request to fix this issue has been accepted, so this should no longer be a problem with versions 6.3.4 and later.

---

Yes, it is an unfortunate inconsistency that for mssql-jdbc the parameter names returned by DatabaseMetaData#getProcedureColumns do not match the names accepted by CallableStatement#setIntet. al.. If you consider it to be a bug then you should create an issue on GitHub and perhaps it will be fixed in a future release.

In the meantime, however, you'll just have to work around it. So, instead of code like this ...

ResultSetrs= connection.getMetaData().getProcedureColumns(null, "dbo", "MenuPlanner", null);
while (rs.next()) {
    if (rs.getShort("COLUMN_TYPE") == DatabaseMetaData.procedureColumnIn) {
        StringinParamName= rs.getString("COLUMN_NAME");
        System.out.println(inParamName);
    }
}

... which produces ...

@person
@food

... you'll need to use code like this ...

booleanisMssqlJdbc= connection.getClass().getName().equals(
        "com.microsoft.sqlserver.jdbc.SQLServerConnection");
ResultSetrs= connection.getMetaData().getProcedureColumns(null, "dbo", "MenuPlanner", null);
while (rs.next()) {
    if (rs.getShort("COLUMN_TYPE") == DatabaseMetaData.procedureColumnIn) {
        StringinParamName= rs.getString("COLUMN_NAME");
        if (isMssqlJdbc && inParamName.startsWith("@")) {
            inParamName = inParamName.substring(1, inParamName.length());
        }
        System.out.println(inParamName);
    }
}

... which produces ...

person
food