MySQL query works fine, but gives error when implemented in java code in Netbeans

Question

I have this sql that insert in multiple tables fields based in the last generated primary key of one of the tables;

INSERT INTO rentals(Customer_idCustomer,RentedDate) VALUES (1,curdate()); 
SET @last_id_in_rentals = LAST_INSERT_ID();
BEGIN;
INSERT INTO rental_movie (Movie_idMovie,Rentals_idRentals) VALUES (1,@last_id_in_rentals);
INSERT INTO transaction(idRentals,DaysRented,Cost,TotalCost) VALUES(@last_id_in_rentals,2,2,4);
COMMIT;

Works fine in MySQL, but when I implement that same query in Netbeans it gaves me this error:

"com.mysql.jdbc.excceptions.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'SET @last_id_in_rentals=LAST_INSERT_ID();
BEGIN;
INSERT INTO rental_movie(Mo'at line2"

This is my whole connection:

ResultSet rs = null;
        PreparedStatement pst = null;
        Statement stmt= null;
        Connection conn = null;
        String url = "jdbc:mysql://localhost:3306/project";
        String userid = "xxx";
        String password = "";
    /**
     * Creates new form NewRental
     */
    public NewRental() {
        initComponents();
    }
private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {                                         
        // TODO add your handling code here:

        try{
            conn = DriverManager.getConnection(url, userid, password);
            String sql="SELECT idCustomer FROM customer\n" +
            " WHERE idCustomer =?;";
            pst=conn.prepareStatement(sql);

            pst.setString(1,jTextField2.getText());
            String idcustomer;
             String idmovie;
            idcustomer=jTextField2.getText();
            idmovie=rental_movie.getText();

           rs=pst.executeQuery();
            if(rs.next()){

                  try{
               asterisco1.setText("");
        sql="SELECT idMovie FROM movie\n" +
            " WHERE idMovie =?;";

            pst=conn.prepareStatement(sql);

            pst.setString(1,rental_movie.getText());

            rs=pst.executeQuery();
                  if(rs.next()){
                      asterisco2.setText("");
                      mandatory.setText("");
            sql="INSERT INTO rentals(Customer_idCustomer,RentedDate) VALUES (1,curdate()); \n" +
"SET @last_id_in_rentals = LAST_INSERT_ID();\n" +
"\n" +
"INSERT INTO rental_movie (Movie_idMovie,Rentals_idRentals) VALUES (1,@last_id_in_rentals);\n" +
"INSERT INTO transaction(idRentals,DaysRented,Cost,TotalCost) VALUES(@last_id_in_rentals,2,2,4);\n" +
"COMMIT;";
                                    //Se crea un id de Rentas automatico.
            stmt=conn.prepareStatement(sql);
            stmt.executeUpdate(sql);
//            sql="UPDATE rental_movie \n" +
//"INNER JOIN rentals  ON rental_movie.Rentals_idRentals = rentals.idRentals\n" +
//"SET Movie_idMovie ="+idmovie+";\n"; 
//            stmt=conn.prepareStatement(sql);
//            stmt.executeUpdate(sql);
//            

//            sql="INSERT INTO rental_movie(Movie_idMovie,Rentals_idRentals)\n" +
//                       "VALUES ("+idmovie+","+idcustomer+");";
//            stmt=conn.prepareStatement(sql);
//            stmt.executeUpdate(sql);
//            sql="INSERT INTO transaction(idRentals)\n" +
//"SELECT idRentals FROM rentals\n" +
//"WHERE rentals.Customer_idCustomer="+idcustomer+";";
//            stmt=conn.prepareStatement(sql);
//            stmt.executeUpdate(sql);
//            sql="update transaction\n" +
//"  set idRentals = (\n" +
//"               select rentals.idRentals \n" +
//"                 from rentals\n" +
//"                where rentals.idRentals = transaction.idRentals\n" +
//"              ),\n" +
//"DaysRented = ,\n" +
//"Cost=,\n" +
//"TotalCost=\n" +
//" where exists (\n" +
//"               select * \n" +
//"                 from rentals \n" +
//"                where rentals.idRentals = transaction.idRentals\n" +
//"              );";


                  }

                  else{
                      asterisco2.setForeground (Color.red);
                      asterisco2.setText("*");
                      mandatory.setForeground (Color.red);
                      mandatory.setText("Mandatory Field.");
                      JOptionPane.showMessageDialog(null, "Record could not be found. ", "Movie don't exist.", JOptionPane.INFORMATION_MESSAGE);
                  }}
                 catch(Exception e){
                     JOptionPane.showMessageDialog(null,e);
                 }


//               
            }
            else{
                asterisco1.setForeground (Color.red);
                asterisco1.setText("*");
                mandatory.setForeground (Color.red);
                mandatory.setText("Mandatory Field.");
                             JOptionPane.showMessageDialog(null, "Record could not be found. ", "Client don't exist.", JOptionPane.INFORMATION_MESSAGE);     

            }
        }
        catch(Exception e){
            JOptionPane.showMessageDialog(null,e);
        }


    }

It looks like your connection doesn't allow multiple queries, can you post your connection(but be sure to XXX out any usernames, passwords and hosts, we don't need to know that information)? — scragar
– scragar, Commented May 12, 2014 at 14:26
already put the connection if so how can I manipulate the query so it can be accepted in Netbeans? Thanks in advance. — Fran Martinez
– Fran Martinez, Commented May 12, 2014 at 14:33
Try adding ?allowMultiQueries=true to the end of your connection string: jdbc:mysql://localhost:3306/project?allowMultiQueries=true — scragar
– scragar, Commented May 12, 2014 at 14:34
post it as an answer so I can accept your comment as an answer. — Fran Martinez
– Fran Martinez, Commented May 12, 2014 at 14:39

scragar · Accepted Answer · 2014-05-12 14:40:24Z

1

Try adding

?allowMultiQueries=true

to the end of your connection string so it looks like:

jdbc:mysql://localhost:3306/project?allowMultiQueries=true

answered May 12, 2014 at 14:40

scragar

6,84530 silver badges37 bronze badges

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

Collectives™ on Stack Overflow

MySQL query works fine, but gives error when implemented in java code in Netbeans

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