Could Not Extract Resultset When Performing Customized Native Query

Could not extract ResultSet When i using Native Query in Spring Jpa

Since you are using native query =true,
that's why you need to use the table name and not the entity name inside your query.

Try this -->

public interface RegisterAgentRepository extends JpaRepository<RegisterAgentDetails, String>, JpaSpecificationExecutor<RegisterAgentDetails> {
    @Query(value = "SELECT case when ABS(am_registeragent.AGENT_VERSION)< ?1 then 'true' else 'false' end as bool FROM am_registeragent where am_registeragent.SAASBOX_ID=?2", nativeQuery = true)
    public Optional<List<Object>> findByAgentVersionAndSaasboxId(String currentVersion, String saasboxid);
}

'Could not extract ResultSet' error when using nativeQuery in JPA

I added the following code above my query and

@Modifying
@Transactional

Like this :

@Modifying
@Transactional
@Query(value = "DELETE FROM played_sheet WHERE user_id =  ?1 AND sheet_music_id = ?2", nativeQuery = true)
void deleteByUserIdAndSheetMusicId(Integer userId,Integer sheetMusicId);

JPA Stored Procedure throwing exception: could not extract ResultSet

According to the documentation for Azure Sql, you have to call the stored procedure with the following

EXECUTE HumanResources.uspGetEmployeesTest2 N'Ackerman', N'Pilar';

-- Or

EXEC HumanResources.uspGetEmployeesTest2 @LastName = N'Ackerman', @FirstName = N'Pilar';

So in your case this would be translated as

 @Query(value = "EXECUTE testProc :TableName", nativeQuery = true)
 Long invokeTestProc(@Param("TableName") String TableName);

or

 @Query(value = "EXEC testProc @TableName = :TableName", nativeQuery = true)
 Long invokeTestProc(@Param("TableName") String TableName);

So considering that you use native queries, the

exception which says "Incorrect syntax near '@P0'" and
SQLGrammarException: could not extract ResultSet.

reffers to the wrong use of sql grammar where you don't use execute or exec and you also pass the parameter in a way not expected for Azure SQL.

GenericJDBCException: could not extract ResultSet

I know is not the best solution, but you can try to use a query SELECT to find your reservation object and then do this repo.deleteById(reservation.getId())

This should allow you to go ahead while you find a better way to do it

---

Related Topics