Invalid SQL when using JDBC Driver
I'm trying to create stored procedures in MySql. The DDL is run through JDBC (I'm using flyway to migrate) and everything was working well right up until I add a CASE in an ORDER BY clause.
I've simplified the code to this:
create procedure test_proc(IN sort_by INT)
begin
select * from Matches
order by
(CASE WHEN sort_by = 1 THEN HomeTeamId END) ASC,
(CASE WHEN sort_by = 4 THEN AwayTeamId END) DESC;
end;
If I enter the same code in Mysql Workbench it executes OK and adds the sproc.
Running it through JDBC gives:
SQL State : 42000
Error Code : 1064
Message : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 6
This is running the 8.0.26 version of the JDBC driver and of mysql 8.0.26 (on a Mac).
I've tried various things including the MariaDB driver and changing the syntax in various ways but I end up with the same error. Removing the CASE WHEN statements causes the creation to work.
Any ideas?
Thanks,
Kevin
Subject
Written By
Posted
Invalid SQL when using JDBC Driver
October 15, 2021 07:43AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.