how to execute 2 queries together?

Question

I am working on a project and I want to insert into 2 different tables, so I wrote those 2 queries (query1, query2), when i run the program with just one query i don't get any exceptions but when executing together i have a bunch of exceptions, i used preparedStatement execute() and didn't work note : im not very experienced please explain easily

private void loadBusesToDB() throws SQLException{
            Connection connection = connect();

            String query = "INSERT INTO Bus (nomLigne, Marque, Matricule, Capacite)"
                    + "VALUES (?, ?, ?, ?)";

            String query2 = "INSERT INTO Lignes (nomLigne, Sntv Depart, SNTV Arrive, prix)"
                    + "VALUES (?, ?, ?, ?)";

            PreparedStatement ps = null;
            PreparedStatement ps2 = null;
        try {

            ps = connection.prepareStatement(query);
            ps2 = connection.prepareStatement(query2);

            for(Bus bus : Bus.buses){
                ps.setString(1, bus.getNomLigne());
                ps.setString(2, bus.getMarque());
                ps.setString(3, bus.getMatricule());
                ps.setInt(4, bus.getCapacite());
                ps.addBatch();   // THE INSERT HAPPENS HERE
            }
            ps.executeBatch();


            for(Lignes ligne : Lignes.lignes){
                ps2.setString(1, ligne.getNomLigne());
                ps2.setString(2, ligne.getDepart());
                ps2.setString(3, ligne.getArrive());
                ps2.setFloat(4, ligne.getPrix());
                ps2.addBatch();   // THE INSERT HAPPENS HERE
            }
            ps2.executeBatch();

        } catch (SQLException ex) {
            ex.printStackTrace();
            System.out.println("ERROR HERE");
            throw ex;
        }finally{
            ps.close();
            ps2.close();
            connection.close();
        }
        }

Error:

net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.2 user lacks privilege or object not found: SNTV at net.ucanaccess.jdbc.UcanaccessConnection.prepareStatement(UcanaccessConnection.java:509) at sntv.MainMenuController.loadBusesToDB(MainMenuController.java:135) at sntv.MainMenuController.initialize(MainMenuController.java:277) at javafx.fxml.FXMLLoader.loadImpl(FXMLLoader.java:2548) at javafx.fxml.FXMLLoader.loadImpl(FXMLLoader.java:2441)

The error message seems to be some sort of database plumbing message. Can you try something simple like just doing a `SELECT *` on either table, to see if at least that works without error? — Tim Biegeleisen, Aug 06 '19 at 14:20
As an aside, I recommend using `try-with` resources so you don't need to close the `ps` in the finally block, it would look like this `try(PreparedStatement ps = connection.prepareStatement(query)){}` (if you are on at least Java 8). — Nexevis, Aug 06 '19 at 14:21
To narrow down the issue, first try to insert into one table and see. — Sambit, Aug 06 '19 at 14:21
But the exception says that there is no privilege or no object of type SNTV. If you have other exceptions please post them. You have made an error. the 2nd query code should look like: INSERT INTO Lignes (nomLigne, Depart, Arrive, prix). — PrzemyslawP, Aug 06 '19 at 14:21
This `INSERT INTO Lignes (nomLigne, Sntv Depart, SNTV Arrive, prix)`, is `Sntv Depart` a column name or you're trying to sett alias for the column? If that's indeed a column name with a space in it (it is, judging from the error message) then you need to quote it, see for example [this question](https://stackoverflow.com/questions/14190798/how-to-select-a-column-name-with-a-space-in-mysql) — uaraven, Aug 06 '19 at 14:22

Parfait · Answer 1 · 2019-08-06T16:39:56.313

As @uaraven points out, you have a syntax error regarding column names in the second query. In SQL, any identifier (including table, column, stored procedures, functions, etc.) with spaces or special characters/symbols in their names or names match reserved words need to be escaped when referenced in any clause (SELECT,FROM, JOIN, WHERE, GROUP BY, HAVING, ORDER BY ).

Now, different RDBMS's handle such escaping differently. Consider the following depending on your database. SQLite may be the only RDBMS that includes all.

Double Quotes (ANSI-SQL standard) (Oracle, DB2, Postgres, RedShift, Teradata, SQLite, with added rules of capitalization for some; SQL Server/MySQL does support with mode changes)

String query2 = "INSERT INTO Lignes (nomLigne, \"SNTV DEPART\", \"SNTV ARRIVE\", prix)"
                    + " VALUES (?, ?, ?, ?)";

Square Brackets (SQL Server, Sybase, SQLite, MS Access)

String query2 = "INSERT INTO Lignes (nomLigne, [Sntv Depart], [SNTV Arrive], prix)"
                    + " VALUES (?, ?, ?, ?)";

Backticks (MySQL, MariaDB, Google BigQuery Standard SQL, SQLite, MS Access)

String query2 = "INSERT INTO Lignes (nomLigne, `Sntv Depart`, `SNTV Arrive`, prix)"
                    + " VALUES (?, ?, ?, ?)";

thanks i did the backticks and it worked thank you so much – Foued Sedrati Aug 06 '19 at 18:38 — Foued Sedrati, Aug 06 '19 at 18:38

how to execute 2 queries together?

1 Answers1