0

I'm trying to find functional dependencies, my professor told us that we needed to use the following line of code. I believe that the error comes when referencing the table (t1.?) but I am not sure. I can not find anything online about this. Any help is appreciated.

PreparedStatement ps = con.prepareStatement("SELECT * FROM X AS t1 JOIN X AS t2 ON t1.? = t2.? WHERE t1.? <> t2.?");

The Error:

Exception in thread "main" java.sql.SQLSyntaxErrorException: 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 ''pi' = t2.'pi' WHERE t1.'hy' <> t2.'hy'' at line 1

The Question: Question

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
DL_Engineer
  • 59
  • 1
  • 9
  • You can't use placeholders for column names. If you replace the question marks with the column names, the query is syntactically correct. – Kayaman Feb 22 '20 at 15:59
  • @Kayaman I am passing in the columns as a variable so that's why I need them. – DL_Engineer Feb 22 '20 at 16:05
  • You can't do that. As I said, you can't use placeholders for column names. – Kayaman Feb 22 '20 at 16:06
  • @Kayaman Okay I just edited the post to include the question. If you look at the last part it describes how we need to use this statement to find functional dependencies. Do you know any other way to go about this? – DL_Engineer Feb 22 '20 at 16:08
  • You just can't do it with placeholders, i.e. the question marks. Of course there are plenty of other ways to make that string. – Kayaman Feb 22 '20 at 16:09
  • When you use placeholders for fieldnames then inserted names are wrapped with qoutes which is synthactically wrong. Palceholders may be used for insertion values literals only. – Akina Feb 22 '20 at 16:12
  • @Akina that's not true. The query string is parsed and that's where the syntax error is noticed. You assume that the `?` is directly replaced by the value as text, which is not true. – Kayaman Feb 22 '20 at 16:14
  • @Kayaman *The query string is parsed* You "forgot" to say the most important thing-WHO is parsing this query string. Read the error message carefully. This is MySQL server error message. Java had prepared the statement (as it can), and send it to MySQL. MySQL gets it and tell that it it synthactically wrong - because MySQL sees the string literal wrapped with quotes in a place where field name should be. – Akina Feb 22 '20 at 16:41
  • @Kayaman *You assume that the ? is directly replaced by the value as text, which is not true* I have said *When you use placeholders for fieldnames then inserted names are **wrapped with qoutes** which is synthactically wrong*. I don't understand how this may be interpret as "directly". – Akina Feb 22 '20 at 16:44
  • @Akina you're wrong. The query string is parsed with `con.prepareStatement()`. At this point there are only placeholder values, i.e. `?`. The method (if it succeeds) returns a `PreparedStatement`, after which you can call `setParameter`. The server never saw any values before it threw that error. – Kayaman Feb 22 '20 at 16:47
  • @Kayaman Again - look at the eror message: *.. right syntax to use near ''pi' = t2.'pi' ..*. You can see that a string-type literal value `'pi'` was inserted into the query text, and MySQL received this query text with this literals already inserted into the text. This means that java have sent the query text with this literals inserted into. Prepared statement in Java is not the same as prepared statement in MySQL - if the last used the error message should contain question chars which is MySQL prepared statement placeholders. – Akina Feb 22 '20 at 16:52
  • @Kayaman *The server never saw any values before it threw that error* Please each time when you tell "server" specify does you mean Java or MySQL. This error message is NOT thrown by Java. It is MySQL server parsing error message which is returned to Java and then transfered to client as is. – Akina Feb 22 '20 at 16:58
  • @Akina ah, well I guess it shouldn't be surprising, it's MySQL after all. Although with server side prepared statements that would fail. – Kayaman Feb 22 '20 at 16:59

0 Answers0