Invalid SQL when using JDBC Driver
Posted by: Kevin Jones
Date: October 15, 2021 07:43AM

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

Options: ReplyQuote


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.