0

I've tried running the following query with JDBC4, which leads to a syntax error:

SELECT m.* 
FROM schema.table m 
INNER JOIN (
    SELECT :id0 as id, :from0 as date_from, :to0 as date_to 
    UNION ALL SELECT :id1 as id, :from1 as date_from, :to1 as date_to 
) tmp 
ON tmp.id = m.id AND m.date BETWEEN tmp.date_from AND tmp.date_to

Strangely enough, when I run the query via mysql (on the same database server), the query is accepted and run.

The error message is even more confusing, as there is no closing ) on line 1.

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 1

The server is running MySQL 5.1.

Bashir
  • 2,057
  • 5
  • 19
  • 44
Martin Fink
  • 1,746
  • 3
  • 16
  • 32
  • I don't understand why this question was closed as a duplicate, as the linked question has nothing to do with this one. I am not trying to select variable columns, the nested select creates a temporary table which I use to match records from the outer table. I do this, because I have many id, from- and to dates that all need to match for a record to be selected. – Martin Fink Jun 17 '20 at 19:14
  • Neither JDBC4 nor MySQL accepts the colon-prefixed host-variable syntax. Tou have accepted an answer that mentions placeholder parameters, which don't appear here either. *Ergo* this isn't the real code. – user207421 Jun 22 '20 at 03:16

1 Answers1

1

JDBC accepts parameters on very specific locations only. Usually in place of scalar values. You can't use parameters for structural items such as columns, tables, or SQL clauses.

If you are in need of this, then you should consider Dynamic SQL or proper SQL Injection.

The Impaler
  • 45,731
  • 9
  • 39
  • 76