4

I am trying to execute a stored procedure using SQL Server JDBC in a method:

//Connection connection, String sp_name, Map<String, Object>params input to the method
DatabaseMetaData dbMetaData = connection.getMetaData();
HashMap<String, Integer> paramInfo = new HashMap<String, Integer>();
if (dbMetaData != null)
{
        ResultSet rs = dbMetaData.getProcedureColumns (null, null, sp_name.toUpperCase(), "%");
        while (rs.next())
            paramInfo.put(rs.getString(4), rs.getInt(6));
        rs.close();
}
String call = "{ call " + sp_name + " ( ";
for (int i = 0; i < paramInfo.size(); i ++)
    call += "?,";
if (paramInfo.size() > 0)
    call = call.substring(0, call.length() - 1);
call += " ) }";
CallableStatement st = connection.prepareCall (call);
for (String paramName: paramInfo.keySet()){
    int paramType = paramInfo.get(paramName);
    System.out.println("paramName="+paramName);
    System.out.println("paramTYpe="+paramType);
    Object paramVal = params.get(paramName);
    st.setInt(paramName, Integer.parseInt(((String)paramVal))); //All stored proc parameters are of type int
}

Let say the stored procedure name is ABC and parameter is @a. Now DatabaseMetaData returns column name @a but setting st.setInt("@a",0) returns following error:

com.microsoft.sqlserver.jdbc.SQLServerException: Parameter @a was not defined for stored procedure ABC.

Instead, I tried this: st.setInt("a",0) and it executed perfectly.

Now the problem is I have to set the parameters dynamically as I have too many stored procedures and too many parameters but jdbc is giving error.

Edit 1:

As pointed out in one answer that my question is a duplicate of: Named parameters in JDBC, I would like to explain that the issue here is not named parameters or positional ones, rather it is about JDBC not handling the SQL server parameters itself properly or my making some error while invoking it.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
HBK
  • 735
  • 1
  • 11
  • 29
  • not sure if I am understanding you correctly, but why can(t you dynamically strip off the `@` ? – Scary Wombat Sep 19 '17 at 07:18
  • Possible duplicate of [Named parameters in JDBC](https://stackoverflow.com/questions/2309970/named-parameters-in-jdbc) – Bejasc Sep 19 '17 at 07:21
  • @ScaryWombat I need something that works with both mysql and sql server and some others too without making specific changes as I expect JDBC to take care of it. – HBK Sep 19 '17 at 07:29
  • @Bejasc `CallableStatement` is the exception to that rule: it does support named parameters. – Mark Rotteveel Sep 19 '17 at 11:24
  • 2
    Sounds like a bug to me, you might want to report it on https://github.com/Microsoft/mssql-jdbc Either the metadata should report the names without `@`, or the `setXXX` should accept it with `@` (and without for backwards compatibility). – Mark Rotteveel Sep 19 '17 at 11:25

1 Answers1

2

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#setInt et. 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 ...

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

... which produces ...

@person
@food

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

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

... which produces ...

person
food
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418