JDBC lock not working with PSQL due to SQL syntax

[VincentXiao-jpg]

Hi, the recent changes to the SQL query for MySql is causing PSQL syntax errors.

  public static final String ACQUIRE_FORMATTED_QUERY = "INSERT INTO `%s` (lock_key, token, expireAt) VALUES (?, ?, ?);";
  public static final String RELEASE_FORMATTED_QUERY = "DELETE FROM `%s` WHERE lock_key = ? AND token = ?;";
  public static final String DELETE_EXPIRED_FORMATTED_QUERY = "DELETE FROM `%s` WHERE expireAt < ?;";
  public static final String REFRESH_FORMATTED_QUERY = "UPDATE `%s` SET expireAt = ? WHERE lock_key = ? AND token = ?;";

It seems to be that the inclusion of " ` " is breaking PSQL syntax.

org.postgresql.util.PSQLException: ERROR: syntax error at or near "`"

Not too sure on a fix, but it looks like MySql and PSQL syntax are conflicting, would appreciate if you could look into this.
Thanks.

[ssiahetiong]

same issue with sqlserver:

org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [DELETE FROM `locked` WHERE expireAt < ?;]; SQL state [S0001]; error code [102]; Incorrect syntax near '`'.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '`'.
	at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1542)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:667)
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:960)
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:1015)
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:1025)
	at com.github.alturkovic.lock.jdbc.service.SimpleJdbcLockSingleKeyService.acquire(SimpleJdbcLockSingleKeyService.java:51)

[alturkovic]

I currently don't have the time to tackle this, if any of you have time to solve this, I would be happy to merge a PR.

[cipianpascu]

Hi Guys,

I tried to create a pull request, but I've got some errors. Maybe it is because of firewall issues.
Please find bellow the queries tested with oracle:

public static final String ACQUIRE_FORMATTED_QUERY = "INSERT INTO %s (lock_key, token, expireAt) VALUES (?, ?, ?)";
public static final String RELEASE_FORMATTED_QUERY = "DELETE FROM %s WHERE lock_key = ? AND token = ?";
public static final String DELETE_EXPIRED_FORMATTED_QUERY = "DELETE FROM %s WHERE expireAt < ?";
public static final String REFRESH_FORMATTED_QUERY = "UPDATE %s SET expireAt = ? WHERE lock_key = ? AND token = ?";

Best Regards,
Ciprian

[alturkovic]

@cipianpascu that was the first version of the queries. The issue is that INSERT INTO %s gets resolved into INSERT INTO lock and lock is a keyword in MySQL so it has to be escaped.
The current issue is that different databases escape keywords differently. So ` (backtick escape) is breaking PSQL syntax.

[cipianpascu]

Oh, I didn't know. But that is a small/corner case. No one will use the name 'lock' in production for a table. I propose to change the default to something else. For example 'tbl_lock' or 'table_lock' or anything else that is not interfering with the sql sintax.

[alturkovic]

Should be fixed in 1.5.0, I decided to change the default table name as suggested to avoid further complications

[cipianpascu]

not yet. sadly I get ORA-00933: SQL command not properly ended in this moment

[alturkovic]

It seems like it might be due to the semicolon at the end? I released a new fix version, could you try with that one? (1.5.1)

[cipianpascu]

Hey Alen,
All good now, thank you
Best Regards,
Ciprian