Why do I get syntax error for "INSERT INTO"?

Question

String pName = getStrFromUser("Product name: ");
int price = getIntFromUser("Price: ", false);
String category = getStrFromUser("Category: ");
String description = getStrFromUser("Description: ");

PreparedStatement statement = connection.prepareStatement("INSERT INTO ws.products (name, price, cid, description) VALUES (?, ?, (SELECT ws.categories.cid FROM ws.categories WHERE ws.categories.name LIKE ?), ?)");

statement.setString(1, pName);
statement.setInt(2, price);
statement.setString(3, category);
statement.setString(4, description);
statement.executeUpdate();

I get:

Error encountered: ERROR: syntax error at or near "INSERT INTO ws

What might be the problem?

It only prints "Error encountered: ERROR: syntax error at or near "INSERT INTO ws" Position: 1" — Corei7
– Corei7, Commented Nov 21, 2019 at 14:51
I am assuming its near like as the syntax for like is Like ' '.. It needs to have quotes — Standin.Wolf
– Standin.Wolf, Commented Nov 21, 2019 at 14:52

Tim Biegeleisen · Accepted Answer · 2019-11-21 14:58:00Z

3

The subquery inside the VALUES clause looks suspicious. Try rephrasing as an INSERT INTO ... SELECT:

String sql = "INSERT INTO ws.products (name, price, cid, description) ";
sql += "SELECT ?, ?, cid, ? FROM ws.categories WHERE name LIKE ?";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setString(1, pName);
statement.setInt(2, price);
statement.setString(3, description);
statement.setString(4, category);
statement.executeUpdate();

answered Nov 21, 2019 at 14:58

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2019-11-21 14:58:56Z

1

I would recommend insert . . . select:

INSERT INTO ws.products (name, price, cid, description) 
    SELECT ?, ?, ws.categories.cid, ?
    FROM ws.categories
    WHERE ws.categories.name LIKE ?;

This will not fix the problem with INSERT, but it will prevent the next problem of a subquery returning more than one row.

My best guess for that problem is that the library you are using only supports SELECT statements. That would be atypical; INSERT is usually allowed.

answered Nov 21, 2019 at 14:58

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Why do I get syntax error for "INSERT INTO"?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related