SQL variables in query cause unexpected SQL syntax error

Question

There is a database that holds music - artists, albums and tracks. Each album has several tracks, each track has it's own track_number inside the album. I try to execute an SQL request that shifts one track number up (for example, track#4 will be track#3) and at the same time shifts another track number down (ex-track#3 should become track#4). SQL code works perfectly while being run in MySQL Workbench or SQL sandbox but it causes an error while being launched via JDBC.

Replacing IF with standart CASE/WHEN/THEN doesn't help a bit. Replacing prepared statement with ordinary statement does not do anything good as well.

SELECT @current_number := track_number, @current_album := album FROM audio_tracks WHERE id = 4;

UPDATE audio_tracks 
    SET track_number = IF(track_number = @current_number - 1, @current_number, @current_number - 1) 
    WHERE @current_number > 1 
        AND track_number IN (@current_number - 1, @current_number) 
        AND album = @current_album;

public void processCustomRequest(String sql) throws DaoException
{
    Statement statement = null;
    try
    {
        statement = connection.createStatement();
        statement.execute(sql);
    } catch (SQLException ex)
    {
        throw new DaoException("Failed to execute custom SQL request", ex);
    } finally
    {
        try
        {
            if (statement != null)
            {
                statement.close();
            }
        } catch (SQLException ex)
        {
            LOGGER.error("Failed to close statement", ex);
        }
    }
}

Instead of expected shifts I got this: 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 'UPDATE audio_tracks SET track_number = IF(track_number = @current_number - 1, @c' at line 1

So... Any ideas how to fix this problem? Again, here is the sandbox that shows how my table looks and how my request works perfectly. In theory.

By default you cannot execute multiple statements in a single execute. — Mark Rotteveel, Sep 14 '19 at 07:02

Junaid Ansar Sheikh · Answer 1 · 2019-09-13T22:10:27.423

0

Try printing the SQL string passed to function and check for the syntax error before execution.

Also use statement.executeUpdate(sql) instead of statement.execute(sql) function.

edited Sep 13 '19 at 22:10

answered Sep 12 '19 at 12:09

Junaid Ansar Sheikh

1
2

I have tried it already. Unfortunately, what I see looks the very same way it should. I can even copypaste that printed string and use it in sandbox - and get proper results. – LightGreenGrass Sep 12 '19 at 12:20
use statement.executeUpdate(sql) instead of statement.execute(sql) function – Junaid Ansar Sheikh Sep 13 '19 at 22:07
Nah, It works fine with usual .execute() method as well. My problem was I didn't set connection properties correctly. This little string `properties.put("allowMultiQueries", "true");` made everything work in a proper way. Thanks for your attempts to help btw! – LightGreenGrass Sep 14 '19 at 13:55

SQL variables in query cause unexpected SQL syntax error

1 Answers1