SQL variables in query cause unexpected SQL syntax error [duplicate]

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
– Mark Rotteveel, Commented Sep 14, 2019 at 7:02

Junaid Ansar Sheikh · Accepted Answer · 2019-09-13 22:10:27Z

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, 2019 at 22:10

answered Sep 12, 2019 at 12:09

Junaid Ansar Sheikh

12 bronze badges

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

3 Comments

LightGreenGrass Over a year ago

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.

Junaid Ansar Sheikh Over a year ago

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

LightGreenGrass Over a year ago

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!

Collectives™ on Stack Overflow

SQL variables in query cause unexpected SQL syntax error [duplicate]

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related