0

So I have an AWS RDS instance with a MySQL 5.6.37 engine, and I'm checking the syntax of my code on sqlfiddle.com with mySQL version 5.6.

The code I put in sqlfiddle is 

CREATE TABLE Teacher_Times(
  TEACHER_ID TEXT,
  BEG_TIME TIME,
  END_TIME TIME,
  DAYS VARCHAR(9) DEFAULT "MTWRF",
  TYPE VARCHAR(11) DEFAULT "unavailable");

The code I have that connects to the rds is using jdbc configured very similarly to this guy's stack overflow post: Not able to set spring.datasource.type

In my code I have 

    result =
            "CREATE TABLE Teacher_Times(\n" +
            "   TEACHER_ID TEXT,\n" +
            "   BEG_TIME TIME,\n" +
            "   END_TIME TIME,\n" +
            "   DAYS VARCHAR(9) DEFAULT \"MTWRF\",\n" +
            "   TYPE VARCHAR(11) DEFAULT \"unavailable\");";
    jdbc_t.execute(result);
    result = "";

This general format for making queries has works in a bunch of other places, and I don't know if the AWS stuff is even relevant.

The exact error is "com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 '"MTWRF"' at line 5"

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Avi Rudich
  • 11
  • 3

1 Answers1

1

Looks like ANSI_QUOTES mode is enabled. I can reproduce the error with

SET sql_mode = 'ANSI_QUOTES';

http://rextester.com/JXV28208

Try using single quotes instead:

CREATE TABLE Teacher_Times(
  TEACHER_ID TEXT,
  BEG_TIME TIME,
  END_TIME TIME,
  DAYS VARCHAR(9) DEFAULT 'MTWRF',
  TYPE VARCHAR(11) DEFAULT 'unavailable');
Paul Spiegel
  • 30,925
  • 5
  • 44
  • 53
  • When I use single quotes the error doesn't change. It even expresses the error as being near '"MTWRF"' which still has double quotes – Avi Rudich Feb 03 '18 at 19:50
  • @AviRudich Don't you find that strange? Try this `DAYS VARCHAR(100) DEFAULT "if_you_do_not_see_this_in_the_error_message_then_you_are_executing_something_else"` – Paul Spiegel Feb 03 '18 at 20:02
  • I did that, and I saw that error message. All I can think of is that jdbc.execute() might be replacing the single quote for me – Avi Rudich Feb 03 '18 at 20:33
  • And just for kicks I tried setting sql_mode to '', and it didn't change anything – Avi Rudich Feb 03 '18 at 20:51
  • Sorry, can't help you with JDBC. Disabling ANSI_QUOTES [works here](http://rextester.com/CCQGQ95670). – Paul Spiegel Feb 03 '18 at 20:57
  • Edit: I got it to start working. Before I was only replacing the double quotes on MTWRF to see if the error would move to the lower line, but it the error stayed on MTWRF until I replaced the TYPE field default with quotes too. – Avi Rudich Feb 03 '18 at 21:02
  • Well.. strange. I guess JDBC has some logic to determine which mode to use. When it sees double quotes in the query, it replaces all single quotes. Just a guess. – Paul Spiegel Feb 03 '18 at 21:10