Misleading SQLServerException: Parameter was not defined for stored procedure #608
Description
Driver version or jar name
mssql-jdbc:6.3.6.jre8-previous (also tested with latest stable release)
SQL Server version
SQL Server 2017 (also tested with 2016)
Client operating system
Windows 8.1 (also tested with Windows Server 2012 R2)
Java/JVM version
Oracle 9.0.4 (also tested with Java 8)
Table schema
n/a
Problem description
When trying to execute a simple stored procedure providing one incoming parameter the driver gave me:
WARN: SQL Error: 0, SQLState: S1093
Jan 24, 2018 10:21:57 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
ERROR: Parameter id was not defined for stored procedure test.
Exception in thread "main" javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: Error preparing CallableStatement
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:149)
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:157)
at org.hibernate.procedure.internal.ProcedureCallImpl.getResultList(ProcedureCallImpl.java:765)
at org.hibernate.procedure.internal.ProcedureCallImpl.getSingleResult(ProcedureCallImpl.java:775)
at Main.main(Main.java:10)
Caused by: org.hibernate.exception.GenericJDBCException: Error preparing CallableStatement
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
at org.hibernate.procedure.internal.ProcedureCallImpl.buildOutputs(ProcedureCallImpl.java:456)
at org.hibernate.procedure.internal.ProcedureCallImpl.getOutputs(ProcedureCallImpl.java:404)
at org.hibernate.procedure.internal.ProcedureCallImpl.outputs(ProcedureCallImpl.java:663)
at org.hibernate.procedure.internal.ProcedureCallImpl.getResultList(ProcedureCallImpl.java:751)
... 2 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Parameter id was not defined for stored procedure test.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:228)
at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.findColumn(SQLServerCallableStatement.java:1499)
at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.setInt(SQLServerCallableStatement.java:2682)
at org.hibernate.type.descriptor.sql.IntegerTypeDescriptor$1.doBind(IntegerTypeDescriptor.java:52)
at org.hibernate.type.descriptor.sql.BasicBinder.bind(BasicBinder.java:104)
at org.hibernate.type.AbstractStandardBasicType.nullSafeSet(AbstractStandardBasicType.java:400)
at org.hibernate.type.AbstractStandardBasicType.nullSafeSet(AbstractStandardBasicType.java:395)
at org.hibernate.procedure.internal.AbstractParameterRegistrationImpl.prepare(AbstractParameterRegistrationImpl.java:335)
at org.hibernate.procedure.internal.ProcedureCallImpl.buildOutputs(ProcedureCallImpl.java:444)
... 5 more
This behavior seemed to me very weird since the same code has worked on production site for months - until I finally detected different object permissions of the executing user. The exception arises just in case of missing execute permission on the stored procedure and can therefore be simply fixed by just granting that permission. However, the exception is somewhat misleading, it should be changed to a more appropriate, meaningful one.
Although, the code producing the above stack trace is based on JPA/Hibernate, you should be able to reproduce it with plain JDBC as well - or even in SQL directly (see repro code below).
Entity (just for declaring the named stored procedure):
import javax.persistence.*;
@Entity
@NamedStoredProcedureQuery(procedureName = "test", name = "test", parameters = @StoredProcedureParameter(type = Integer.class, mode = ParameterMode.IN, name = "id"))
public class TestEnt {
@Id
private int id;
}Main method for testing:
public class Main {
private static final EntityManagerFactory emf = Persistence.createEntityManagerFactory("NewPersistenceUnit"); -- specified in JPA's persistence.xml
public static void main(String[] args) {
EntityManager entityManager = emf.createEntityManager();
StoredProcedureQuery storedProcedureQuery = entityManager.createNamedStoredProcedureQuery("test");
storedProcedureQuery.setParameter("id", 1);
Object res = storedProcedureQuery.getSingleResult();
System.out.println("Return value " + res);
}
}I think the misleading exception arises because SQLServerCallableStatement.findColumn("id") retrieves zero results from sp_proc_columns and assumes that there is no column named 'id' in the stored procedure. This happens before actually trying to execute the user stored procedure which would probably lead to a meaningful 'The EXECUTE permission was denied' message.
Expected behavior and actual behavior
Expected: 'The EXECUTE permission was denied'
Actual: 'Parameter was not defined for stored procedure'
Repro code
create database test_sp
go
use test_sp
go
create proc test @id int as select @id
go
create login reader with password='password_reader',default_database=test_sp
create user reader for login reader
alter role db_datareader add member reader
go
create login executor with password='password_executor',default_database=test_sp
create user executor for login executor
alter role db_datareader add member executor
grant execute on test to executor
go
exec as user='executor'
exec test @id=1 --works as expected
exec sp_sproc_columns @procedure_name=test , @ODBCVer=3 --works as expected: returns two rows, @RETURN_VALUE and @id
revert
go
exec as user='reader'
exec test @id=1 --works as expected: The EXECUTE permission was denied on the object 'test', database 'test_sp', schema 'dbo'.
exec sp_sproc_columns @procedure_name=test , @ODBCVer=3 --does NOT work as expected: returns zero rows instead of throwing an meaningful exception
revert
go
drop database test_sp
go