MySQL and Java – SQL Command Works in Workbench but Not from Java Program

javaMySQL

I am working on a program that manages a database of client information. I am currently stuck on a problem where the SQL statement created by my java program but get the error

SQLException: 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 Contact VALUES ("82136b4240d6ce4ea7d03e51469a393b","b","b","b","b","' at line 2
SQLState: 42000
VendorError: 1064

I would figure that the statement is incorrect, but if I run it in Workbench, it works exact. The SQL statement I am using is as follows (merged into one string with \n for each line)

Begin;
INSERT INTO Contact VALUES ("82136b4240d6ce4ea7d03e51469a393b","b","b","b","b","b","b","b","b","b","b") ON DUPLICATE KEY UPDATE ContactID=ContactID;
INSERT INTO Contact VALUES ("e9b3390206d8dfc5ffc9b09284c0bbde","c","c","c","c","c","c","c","c","c","c") ON DUPLICATE KEY UPDATE ContactID=ContactID;
INSERT INTO People (UID,LastName,FirstName,MiddleName,MadenName,AncestryID,23MeID,GedMatchID,ContactID,BusinessContactID,Notes,Deceased)Values ("a4057edb4d8205f3361c9ca350f67c1f","a","a","a","a","a","a","a","82136b4240d6ce4ea7d03e51469a393b","e9b3390206d8dfc5ffc9b09284c0bbde","",false) ON DUPLICATE KEY UPDATE UID=UID;
COMMIT;

As a quick testing I have a command
SELECT 1 + 1;
which seems to work just fine

It may help for some people to have the creation code for the tables. The two relevant are as follow

CREATE TABLE IF NOT EXISTS `clients`.`Contact` (
  `ContactID` VARCHAR(32) NOT NULL COMMENT 'Hashed number',
  `Address` VARCHAR(45) NOT NULL,
  `Street` VARCHAR(45) NOT NULL,
  `City` VARCHAR(25) NOT NULL,
  `State` VARCHAR(2) NOT NULL,
  `Zipcode` VARCHAR(5) NOT NULL,
  `HPhone` VARCHAR(10) NULL COMMENT 'HomePhone',
  `CPhone` VARCHAR(10) NULL,
  `Website` VARCHAR(45) NULL,
  `BuisnessName` VARCHAR(45) NULL,
  `BuisnessWebsite` VARCHAR(45) NULL,
  UNIQUE INDEX `ContactID_UNIQUE` (`ContactID` ASC),
  PRIMARY KEY (`ContactID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `clients`.`People`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `clients`.`People` ;

CREATE TABLE IF NOT EXISTS `clients`.`People` (
  `UID` VARCHAR(32) NOT NULL,
  `LastName` VARCHAR(45) NOT NULL,
  `FirstName` VARCHAR(25) NOT NULL,
  `MiddleName` VARCHAR(45) NOT NULL,
  `MadenName` VARCHAR(45) NOT NULL,
  `AncestryID` VARCHAR(15) NOT NULL COMMENT 'KitID',
  `23MeID` VARCHAR(15) NOT NULL COMMENT 'KitID',
  `GedMatchID` VARCHAR(9) NOT NULL,
  `ContactID` VARCHAR(32) NOT NULL,
  `BusinessContactID` VARCHAR(32) NOT NULL,
  `Notes` BLOB NULL,
  `Deceased` TINYINT(1) NULL,
  PRIMARY KEY (`UID`),
  UNIQUE INDEX `23MeID_UNIQUE` (`23MeID` ASC),
  UNIQUE INDEX `GedmatchID_UNIQUE` (`GedMatchID` ASC),
  UNIQUE INDEX `UID_UNIQUE` (`UID` ASC),
  INDEX `fk_People_Contact1_idx` (`ContactID` ASC),
  INDEX `fk_People_Contact2_idx` (`BusinessContactID` ASC),
  UNIQUE INDEX `AncestryID_UNIQUE` (`AncestryID` ASC),
  CONSTRAINT `fk_People_Contact1`
    FOREIGN KEY (`ContactID`)
    REFERENCES `clients`.`Contact` (`ContactID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_People_Contact2`
    FOREIGN KEY (`BusinessContactID`)
    REFERENCES `clients`.`Contact` (`ContactID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

As asked here is the java function I use to send the query

public int SQLAddTo(String FinalQuery, String userName, String password) {
    Connection conn = null;
    Statement stmt = null;

    txtStatus.setText("Processing");
    int intrs = 0;

    try {
        conn
                = DriverManager.getConnection("jdbc:mysql://10.0.0.249:3306/clients", userName, password);

        stmt = conn.createStatement();
        intrs = stmt.executeUpdate(FinalQuery);
        txtStatus.setText("Done");

        // Now do something with the ResultSet ....
    } catch (SQLException ex) {
        // handle any errors
        System.out.println("SQLException: " + ex.getMessage());
        System.out.println("SQLState: " + ex.getSQLState());
        System.out.println("VendorError: " + ex.getErrorCode());
        txtStatus.setText("SQLException: " + ex.getMessage());
    } finally {
// it is a good idea to release
        // resources in a finally{} block
        // in reverse-order of their creation
        // if they are no-longer needed

        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException sqlEx) {
            } // ignore

            stmt = null;
        }
    }

    return intrs;
}

And the creation of the SQL statement and sent to the function

String queryContact = "INSERT INTO Contact (ContactID,Address,Street,City,State,Zipcode,Hphone,Cphone,Website,BuisnessName,BuisnessWebsite) "
            + "VALUES ('" + ContactH1 + "','" + txtAddress.getText() + "','" + txtStreet.getText() + "','" + txtCity.getText() + "','" + txtState.getText() + "','" + txtZip.getText() + "','"
            + txtHP.getText() + "','" + txtCP.getText() + "','" + txtWeb.getText() + "','" + txtBName.getText() + "','" + txtBW.getText() + "') ON DUPLICATE KEY UPDATE ContactID=ContactID;";

    String queryBContact = "INSERT INTO Contact (ContactID,Address,Street,City,State,Zipcode,Hphone,Cphone,Website,BuisnessName,BuisnessWebsite) "
            + "VALUES ('" + ContactH2 + "','" + txtAddress1.getText() + "','" + txtStreet1.getText() + "','" + txtCity1.getText() + "','" + txtState1.getText() + "','" + txtZip1.getText() + "','"
            + txtHP1.getText() + "','" + txtCP1.getText() + "','" + txtWeb1.getText() + "','" + txtBName1.getText() + "','" + txtBW1.getText() + "') ON DUPLICATE KEY UPDATE ContactID=ContactID;";

    String People = txtLN.getText() + txtFN.getText() + txtMN.getText() + txtMadN.getText() + txtAncID.getText() + txt23ID.getText()
            + txtGedID.getText() + ContactH1 + ContactH2 + txtNote.getText() + cbDead.isSelected();
    String PeopleH = MD5(People);

    //Final Query statement
    String FinalQuery = "Begin;\n\r" + queryContact + "\n\r" + queryBContact + "\n\r" + queryPeople + "\n\r"
            + queryDonations + "\n\r" + queryMembers + "\n\r" + queryAncAccess + "\n\r" + queryAncesty + "\n\r" + query23Access + "\n\r"
            + query23 + "\n\r" + queryGedAccess + "\n\r" + queryGed + "\n\rCOMMIT;";

    System.out.println(FinalQuery);

    //Execute SQL connection
    int Result = SQLAddTo(FinalQuery, "travis", "password");

Best Answer

You need to change java code to something like (for simplicity sake error handling and closing statements omitted)

 try {
    conn = ..... 
    conn.setAutoCommit(false);
    Statement s1 = conn.createStatement();
    s1.executeUpdate(/*first insert */);
    s1.executeUpdate(/*second insert */);
     // etc
    conn.commit();
  }

Side notes. 1) it's much better to use PreparedStatement if you executing the same query multiple times with different parameters. 2) make sure your catch block has conn.rollback() - you don't want to leave transaction open

Best Answer

Related Solutions

Thesql statement based replication – unsafe statements

Mysql – Unique insert performance: Primary Key vs Unique Index

Related Question