MyBatis stored procedure not running in postgres

Question

Having a strange error when running a mybatis sproc that I can't quite figure out - the xml mapper I think is pretty straightforward:

<select id="executeStoredProcedure" statementType="CALLABLE">
    { 
      call update_login(aws_oracle_ext.TRUNC(#{date}) - (10 / 1440 || ' days')::INTERVAL, 
        aws_oracle_ext.TRUNC(#{date}) + (5 / 1440 || ' days')::INTERVAL, 
        1000
      ) 
    }
</select>

However, when it runs I get this error:

[ERROR] When the login_trail stored procedure is executed(****)  Time elapsed: 0.135 s  <<< ERROR!
org.springframework.jdbc.BadSqlGrammarException:

### Error updating database.  Cause: org.postgresql.util.PSQLException: ERROR: update_login(timestamp without time zone, timestamp without time zone, integer) is a procedure
  Hint: To call a procedure, use CALL.
  Position: 15
### The error may exist in file [***]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: { call accounts.update_login(aws_oracle_ext.TRUNC(?) - (10 / 1440 || ' days')::INTERVAL, aws_oracle_ext.TRUNC(?) + (5 / 1440 || ' days')::INTERVAL, 1000)      }
### Cause: org.postgresql.util.PSQLException: ERROR: accounts.update_login(timestamp without time zone, timestamp without time zone, integer) is a procedure
  Hint: To call a procedure, use CALL.
  Position: 15
; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: ERROR: accounts.update_account_last_login(timestamp without time zone, timestamp without time zone, integer) is a procedure
  Hint: To call a procedure, use CALL.
  Position: 15
        at ***whenTheLoginTrailSPIsExecuted(LoginTrailSteps.java:83)
Caused by: org.postgresql.util.PSQLException:
ERROR: accounts.update_login(timestamp without time zone, timestamp without time zone, integer) is a procedure
  Hint: To call a procedure, use CALL.
  Position: 15
        at ****(LoginTrailSteps.java:83)

I've run the line of code manually in the db and it runs fine.

Try removing the braces. See this answer.

ave
– ave

2021-10-21 18:40:17 +00:00
Commented Oct 21, 2021 at 18:40 — ave
– ave, Commented Oct 21, 2021 at 18:40

buddemat · Accepted Answer · 2023-03-03 13:56:48Z

0

Add CDATA syntax outside the outermost braces.

edited Mar 3, 2023 at 13:56

buddemat

5,32816 gold badges37 silver badges61 bronze badges

answered Feb 25, 2023 at 7:26

user3545740

1

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MyBatis stored procedure not running in postgres

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related