StoredProcedureQuery.hasMoreResults() throws exception with multiple ResultSets

Description

My Stored Procedure is returning multiple results sets.
Say
ResultSet1 contains columns with name as 'x', 'y', 'z'
ResultSet2 contains columns with name as 't'.
When I give call to StoredProcedureQuery.hasMoreResults() method it throws an exception
"Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The column name x is not valid."
Can you help me in solving this issue.
as per doc, Hibernate supports dealing with multiple results sets.

Attachments

15 Mar 2016, 08:10 AM
15 Mar 2016, 07:23 AM
15 Mar 2016, 07:14 AM

Activity

Show:

Christian Beikov April 8, 2022 at 1:00 PM

Since no test case was provided for years, I'll close this issue as out of date. I also believe it will most probably not be an issue any more in 6.0.
If this still is an issue for you, please create a new issue with a test case that reproduces the problem.

SteGr February 15, 2018 at 8:58 AM

H2 doesn't support multiple result sets. I have to try to change your test template so it uses MSSQL. Is this okay?

Former user February 14, 2018 at 10:57 PM

@SteGr, please attach a test case that reproduces your issue. You can find a test template at https://github.com/hibernate/hibernate-test-case-templates.

SteGr February 14, 2018 at 4:00 PM

I can confirm this bug using Wildfly 10.1 with Hibernate 5.0.11 and Microsoft SQL Server 2012.

The stored procedure returns 2 result sets. The first SELECT should be mapped to ResultCount, the second SELECT to ResultEntity (see below).

SELECT NEWID() as id, 3 as [count]
SELECT 1 as idd, 'A' as name UNION SELECT 2 as idd, 'B' as name UNION SELECT 3 as idd, 'C' as name

I've used JPA to call the stored procedure

StoredProcedureQuery qry =  _em.createStoredProcedureQuery("usp_test", ResultCount.class, ResultEntity.class);
	
do {
	System.out.println(qry.getResultList());
} while(qry.hasMoreResults());

Expected Result (tested with eclipselink 2.6.4):

[ResultCount@6358e26d]
[ResultEntity@182e3ee0, ResultEntity@31488c0a, ResultEntity@15274bc4]

Current Result (using hibernate 5.0.11):

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The column name idd is not valid.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:206)
	at com.microsoft.sqlserver.jdbc.SQLServerResultSet.findColumn(SQLServerResultSet.java:686)
	at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getInt(SQLServerResultSet.java:2337)
	at org.jboss.jca.adapters.jdbc.WrappedResultSet.getInt(WrappedResultSet.java:1521)
	at org.hibernate.type.descriptor.sql.IntegerTypeDescriptor$2.doExtract(IntegerTypeDescriptor.java:62) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]
	at org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:47) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]
	at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:238) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]
	at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:234) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]
	at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:224) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]
	at org.hibernate.type.AbstractStandardBasicType.hydrate(AbstractStandardBasicType.java:300) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]
	at org.hibernate.loader.Loader.extractKeysFromResultSet(Loader.java:789) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]
	at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:714) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]
	at org.hibernate.loader.Loader.processResultSet(Loader.java:972) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]
	at org.hibernate.result.internal.OutputsImpl$CustomLoaderExtension.processResultSet(OutputsImpl.java:285) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]
	at org.hibernate.result.internal.OutputsImpl.extractResults(OutputsImpl.java:136) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]
	... 85 more

The entities:

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;

@Entity
public class ResultCount {
	private String id;
	private long count;
	
	@Id
	@Column(name="id")
	public String getId() {
		return this.id;
	}
	
	public void setId(String id) {
		this.id = id;
	}

	@Column(name="count")
	public long getCount() {
		return this.count;
	}
	
	public void setCount(long count) {
		this.count = count;
	}
}

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;

@Entity
public class ResultEntity {
	private Integer id;
	private String name;
	
	@Id
	@Column(name="idd")
	public Integer getId() {
		return this.id;
	}
	
	public void setId(Integer id) {
		this.id = id;
	}
	
	@Column(name="name")
	public String getName() {
		return this.name;
	}
	
	public void setName(String name) {
		this.name = name;
	}
}