java.sql.SQLException: Error preparing query: No tables used

Question

I'm using a prepared Statement to insert whit WHERE NOT EXISTS. I have tried different ways, but it simply doesn't work. The error I catch with catch (SQLException sqle) is

java.sql.SQLException: Error preparing query: No tables used

My code is:

try {
                        //hacer los inserts
                        sql = "INSERT INTO calendario (year, mes, dia, date) "
                                + "SELECT * FROM (SELECT ? as year,? as mes ,? as dia,? as date) AS tmp "
                                + "WHERE NOT EXISTS (SELECT * FROM calendario WHERE "
                                + "year=? AND mes=? AND dia=?)";

                        injerto = conn.prepareStatement(sql);
                        injerto.setInt(1, year);
                        injerto.setInt(2, mes);
                        injerto.setInt(3, dia);
                        injerto.setDate(4, sqldate);
                        injerto.setInt(5, year);
                        injerto.setInt(6, mes);
                        injerto.setInt(7, dia);
                        injerto.addBatch();
                        injerto.executeBatch();

                    }  catch (SQLException sqle) {
                        sqle.printStackTrace();
                        System.out.println("Calendario dice: me muero");
                        System.exit(0);
                    }

I'm using MariaDB, which print this error:

Caused by: org.mariadb.jdbc.internal.util.dao.QueryException: Error preparing query: No tables used

I have tried also with:

sql = "INSERT INTO calendario (year, mes, dia, date) "
                                    + "SELECT ?,?,?,?  "
                                    + "WHERE NOT EXISTS ("
                                    + "SELECT * FROM calendario "
                                    + "WHERE year = ? AND mes = ? AND dia = ?)";

And the error change:

Error preparing query: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE NOT EXISTS (SELECT * FROM calendario WHERE year = ? AND mes = ? AND dia = ' at line 1

Help me please.

You probably want "INSERT INTO calendario (year, mes, dia, date) " + "VALUES(?,?,?,?) " + "WHERE NOT EXISTS (SELECT * FROM calendario WHERE " + "year=? AND mes=? AND dia=?)" — user180100
– user180100, Commented Nov 30, 2016 at 19:58
You either go full Spanish or full English on your data schema and field names, don't mix them! — Hackerman
– Hackerman, Commented Nov 30, 2016 at 20:22
Suggest you read the syntax for INSERT mariadb.com/kb/en/mariadb/insert This part of your SQL is weird: (SELECT ? as year,? as mes ,? as dia,? as date) You're selecting but not FROM anything. — Taylor
– Taylor, Commented Nov 30, 2016 at 21:06

Rick James · Accepted Answer · 2016-12-01 06:28:14Z

1

Get rid of both SELECTs, turn the INSERT into INSERT IGNORE:

INSERT IGNORE INTO calendario (year, mes, dia, date)
    VALUES
    ( ?, ?, ?, ? )

will insert a new row if it does not already exist. And add UNIQUE(year, mes, dia) so that it knows when to "ignore".

answered Dec 1, 2016 at 6:28

Rick James

144k15 gold badges144 silver badges255 bronze badges

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

Collectives™ on Stack Overflow

java.sql.SQLException: Error preparing query: No tables used

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related