Why do I get a sql grammar error when I can successfully execute in MySQL

Liu Hantao :

I am trying to insert data into multiple tables, user_inventories, user_inventory_details, and user_inventory_images. I am using jdbcTemplate provided by Spring framework Below are my method to insert.

public List<UserListing> postUserListing(String userId, String vin, String price,
      String mileage, String color, List<String> images) {
    Preconditions.checkArgument(Strings.isNotEmpty(userId), "user id cannot be empty");
    Preconditions.checkArgument(Strings.isNotEmpty(vin), "vin cannot be empty");
    Preconditions.checkArgument(Strings.isNotEmpty(price), "price cannot be empty");
    Preconditions.checkArgument(Strings.isNotEmpty(mileage), "mileage cannot be empty");
    Preconditions.checkArgument(Strings.isNotEmpty(color), "color cannot be empty");
    Preconditions.checkArgument((!images.isEmpty()), "images cannot be empty");

    UserListing userListing = this.getSingleUserListingByVin(userId, vin);
    if (userListing != null) {
      throw new InvalidParameterException(
          String.format("This listing with vin: %s already exist", vin)
      );
    }

    String postStatement = "START TRANSACTION; " +
        this.POST_USER_INVENTORY +
        this.POST_USER_INVENTORY_DETAIL +
        this.preparePostStatementHelper(images) +
        "COMMIT; ";

    String userInventoryId = UUID.randomUUID().toString();

    jdbcTemplate.update(
        connection -> {
          PreparedStatement ps = connection.prepareStatement(postStatement);
          ps.setString(1, userInventoryId);
          ps.setString(2, userId);
          ps.setString(3, UUID.randomUUID().toString());
          ps.setString(4, userInventoryId);
          ps.setString(5, vin);
          ps.setString(6, price);
          ps.setString(7, mileage);
          ps.setString(8, color);
          for (int i = 0, j = 0; i < images.size(); i++) {
            ps.setString(9 + j++, UUID.randomUUID().toString());
            ps.setString(9 + j++, userInventoryId);
            ps.setString(9 + j++, images.get(i));
          }
          System.out.println(ps.toString());
          return ps;
        }
    );

    return this.getUserListingsByUserId(userId);
  }

And here is what console prints out for the ps.

START TRANSACTION; 
INSERT INTO user_inventories (`id`, `user_id`) VALUES ('db75330e-1da0-455c-850f-22ba08db697e', '7ff0ca63-11e3-4aa4-b5cf-08d9091901fe'); 
INSERT INTO user_inventory_details (`id`, `user_inventory_id`, `vin`, `price`, `mileage`, `color`) VALUES ('55df5adc-b0c6-4303-9671-1b562fd9a687', 'db75330e-1da0-455c-850f-22ba08db697e', '2FMPK4K99LBA82434', '36,454', '12300', 'silver'); 
INSERT INTO user_inventory_images (`id`, `user_inventory_id`, `image_url`) VALUES ('17bc4717-9967-4402-a87c-c729d29d7b11', 'db75330e-1da0-455c-850f-22ba08db697e', 'https://www.cstatic-images.com/supersized/in/v1/424056/2FMPK4K99LBA82434/4d915c4f3ea38e2d44d879d143277468.jpg'); 
INSERT INTO user_inventory_images (`id`, `user_inventory_id`, `image_url`) VALUES ('bd23ec85-edfe-4a46-b0f7-4608f258a84d', 'db75330e-1da0-455c-850f-22ba08db697e', 'https://www.cstatic-images.com/supersized/in/v1/424056/2FMPK4K99LBA82434/d26beabd124e6638c935739f92c056c2.jpg'); 
INSERT INTO user_inventory_images (`id`, `user_inventory_id`, `image_url`) VALUES ('dc53e3e2-5f4d-4a3c-8cef-3539dee0e873', 'db75330e-1da0-455c-850f-22ba08db697e', 'https://www.cstatic-images.com/supersized/in/v1/424056/2FMPK4K99LBA82434/389ceb910bef8f8255c21d6823b269fe.jpg'); 
INSERT INTO user_inventory_images (`id`, `user_inventory_id`, `image_url`) VALUES ('9b6201f4-8ae8-4483-b33e-3a61ffa2cb90', 'db75330e-1da0-455c-850f-22ba08db697e', 'https://www.cstatic-images.com/supersized/in/v1/424056/2FMPK4K99LBA82434/7d7e014e4dab59f9026837a37fb0a697.jpg'); 
COMMIT;

Here is the error that jdbc gave me

Exception while fetching data (/postUserListing) : PreparedStatementCallback; bad SQL grammar []; nested exception 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 'INSERT INTO user_inventories (id, user_id) VALUES ('db75330e-1da0-455c-850f-' at line 1

I am very confused since I typed the same sql command on my mysql workbench, it successfully execute and do all the insertion. However, when I try to use jdbc, it gives me a sql grammar error every time. Thanks so much for helping!

Here are three constant fields

  private final String POST_USER_INVENTORY = "INSERT INTO user_inventories (`id`, `user_id`) VALUES (?, ?); ";
  private final String POST_USER_INVENTORY_DETAIL = "INSERT INTO user_inventory_details (`id`, `user_inventory_id`, `vin`, `price`, `mileage`, `color`) VALUES (?, ?, ?, ?, ?, ?); ";
  private final String POST_USER_INVENTORY_IMAGES = "INSERT INTO user_inventory_images (`id`, `user_inventory_id`, `image_url`) VALUES (?, ?, ?); ";

And a private helper method

private String preparePostStatementHelper(List<String> images) {
    String postImagesStatement = "";
    for (int i = 0; i < images.size(); i++) {
      postImagesStatement += this.POST_USER_INVENTORY_IMAGES;
    }
    return postImagesStatement;
}
Simon G. :

You are sending multiple statements as one. The error refers to the first ';'.

Instead of sending START TRANSACTION;, call Connection.setAutoCommit(false);

Send each insert statement individually.

Instead of sending COMMIT;, call Connection.commit();, then Connection.setAutoCommit(true);

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

Why do I get a "cannot execute LOCK TABLE during recovery error" when I run my SQL query?

SQL, Why do I get this error when creating the reports table?

Why do I get this 'GROUP BY' error when using 'UPDATE' (SQL)?

Why do I get this error in SQL Server?

Why do I get error in SQL query?

Why do I get the error: "cannot communicate with the debug host process" when attempting to execute an SSIS package?

why do I get this error, how do can I fix it

Why do I get a MySQL syntax error (1604) when I select by email address?

Why do I get syntax error when I update from MYSQL?

Why do I get a Can not find module error when I run npx run dev?

Why do I get an SQL parsing error when I use parameters?

Why do I get this error and how can I fix it? Swift

Why do I get this error when I run 'bower install'?

Why do I get an error for "__CrtGetFileInformationByHandleEx " when I try to compile

Why do I get an error when I use transduce?

Why do i get this error when i connect snowflake and python

Why do I get an error when I blit an image

Why do I get a 401 error when I enable csrf?

Why do I get a segmentation fault when attempting to execute an instruction writing to memory when the debugger can manually write to the same memory?

Why my data get undefined when I successfully get the response?

Why I get ERROR_INVALID_FUNCTION ( winapi error 1 ), when I execute GetFileSizeEx?

Why do I get an error when create a function but not when a variable?

Why do i get this error when trying to create a new table mysql?

Why do I get a lifetime error when I store closure as a variable and no error when I declare it as an expression?

Why do I get the "can't read properties of null" error, when using "querySelectorAll.forEach"?

Why do I get ESC successfully only by _getch()?

Why I get error SQL command not properly ended while I execute UPDATE query

Why do I get PInvoceStackImbalance when I try to execute umanaged code

Why do I get this error installing MySQL server?