I am trying to execute a stored procedure in SQL Server using spring boot. The get API works fine but the POST Method to pass JSON as parameters is giving error. I am passing id, state_id, and name(city name) as JSON and the error I am getting is: API test

Now if I run the same thing is SQL server independently, it works just fine. I have designed the stored procedure in such a way that creation_timestamp is generated automatically using GETDATE() in SQL Server. SQL SERVER code

Below is my SpringBoot code:

1. City.java (model for the table):

@NamedStoredProcedureQuery(name = "saveCity", procedureName = "saveCity", parameters = {
        @StoredProcedureParameter(name = "id", mode = ParameterMode.IN, type = Integer.class),
        @StoredProcedureParameter(name = "state_id", mode = ParameterMode.IN, type = Integer.class),
        @StoredProcedureParameter(name = "name", mode = ParameterMode.IN, type = String.class),
        @StoredProcedureParameter(name = "message", mode = ParameterMode.OUT, type = String.class)
})

public class City {
    @Id
    @GeneratedValue
    private Integer id;

    @Column
    private Timestamp creation_timestamp;

    @Column
    private int created_by_id;

    @Column
    private int state_id;

    @Column
    private String name;

    @Column
    private boolean deleted;

    @Column
    private Timestamp deleted_timestamp;

    @Column
    private Integer deleted_by_id;

    @Column
    private String status;
}

2. My Controller file: CityController.java:

    @PostMapping("/city")
    public ResponseEntity<String> createCity(@RequestBody City city) {
        try {
            StoredProcedureQuery query = entityManager.createNamedStoredProcedureQuery("saveCity");
            query.setParameter("id", city.getId());
            query.setParameter("state_id", city.getState_id());
            query.setParameter("name", city.getName());

            // Register the output parameter
            query.registerStoredProcedureParameter("message", String.class, ParameterMode.OUT);

            // Execute the stored procedure
            query.getResultList();

            // Get the output parameter value
            String message = (String) query.getOutputParameterValue("message");

            return new ResponseEntity<>(message, HttpStatus.CREATED);
        } catch (PersistenceException e) {
            // Handle JPA provider-specific exceptions
            return new ResponseEntity<>(e.getMessage(), HttpStatus.INTERNAL_SERVER_ERROR);
        } catch (Exception e) {
            // Handle other exceptions
            return new ResponseEntity<>(e.getMessage(), HttpStatus.INTERNAL_SERVER_ERROR);
        }
    }

I tried to find some other online resources but none worked. And even I didn’t understand the error as I am not passing any data which needs to be converted to datetime format(as its not needed from the SQL Server snippet). ChatGPT and other GPT’s solution also did not work. The expected result is that data is stored in the table using stored procedure and api returns some sort of response to it.