0

I have a problem to insert a new row of data into a table I created via JDBC. It throws SQLException after the ExecuteUpdate() line.

Below I provide a code which created the DB and the Table in this DB. The second part has the code which is supposed to insert values into row in a PatientsData table.

public class DbSetUp {

private static Connection con;
private static String mySqlString = "CREATE TABLE PatientsData" +
        "(id INTEGER PRIMARY KEY," +
        "fname VARCHAR(30) NOT NULL," +
        "lname VARCHAR(30) NOT NULL," +
        "sex VARCHAR(1) NOT NULL," +
        "insurance VARCHAR(1) NOT NULL," +
        "profession VARCHAR(30) NOT NULL)";
//private boolean end;
private static String strTemp = "CREATE TABLE PatientsTemp" +
        "(id INTEGER PRIMARY KEY," +
        "name VARCHAR(256) NOT NULL," +
        "date DATE NOT NULL," + 
        "temp NUMERIC NOT NULL)";


public static void main(String[] args) {

    try {
        Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
    } catch (ClassNotFoundException e) {
        System.out.println("Driver not found");
        e.printStackTrace();
    }   

    try {
        con = DriverManager.getConnection("jdbc:derby:PatientDb;create=true");
    } catch (SQLException e) {
        System.out.println("Db not found");
        e.printStackTrace();
    }

    Statement statement = null;
    try{
        statement = con.createStatement();
        statement.execute(mySqlString);
        statement.execute(strTemp);

    } catch(SQLException ex){
        ex.printStackTrace();
    }



}

The above code works fine throwing no exceptions. I assume that both tables have been created and the DB exists:)

Not the part which is supposed to insert data:

public Patient createNewPatient(int id, String fname, String lname,
        String sex, String insurance, String profession) {

    try {
        DriverManager.registerDriver(new org.apache.derby.jdbc.EmbeddedDriver());
    } catch (SQLException e1) {
        System.out.println("to na poczatku");
        e1.printStackTrace();
    }

    try{
        con = DriverManager.getConnection("jdbc:derby:PatientDb"); 
        PreparedStatement ps = con.prepareStatement("INSERT INTO PatientsData VALUES(?,?,?,?,?,?)");
        System.out.println("Prepared Statement");
        ps.setInt(1, id);
        System.out.println("set int id");
        ps.setString(2, fname);
        ps.setString(3,lname);
        ps.setString(4,sex);
        ps.setString(5, insurance);
        ps.setString(6,profession);
        System.out.println("set string profession");
        result = ps.executeUpdate();
        System.out.println(result);
        return new Patient(id,fname,lname,sex,insurance,profession);
        //System.out.println("set string profession");


    } catch(SQLException e){
        System.out.println("SQL exception");
        return null;
    }

}

The line: result = ps.executeUpdate(); throws SQLException, I have no idea where is the mistake. I have added derby.jar into my build path.

5
  • 3
    please post exception details. Commented Apr 8, 2012 at 13:10
  • 1
    Please show the exception error message and stack trace Commented Apr 8, 2012 at 13:11
  • 'java.sql.SQLIntegrityConstraintViolationException: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'SQL120407181654820' defined on 'PATIENTSDATA'. at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)' Commented Apr 8, 2012 at 13:19
  • 2
    You're inserting duplicate keys, which is illegal. This is not related to your code but to your data. Commented Apr 8, 2012 at 13:20
  • Yes, now I know :) Thanks for reminding about printing stack trace. Now I have another problem, but I'll try to fix it myself first. Commented Apr 8, 2012 at 13:20

2 Answers 2

1

your SQL statement is wrong

INSERT INTO PatientsData VALUES(?,?,?,?,?,?)

neets to be

INSERT INTO PatientsData(columnname1,columname2...) VALUES(?,?,?,?,?,?)

i suspect...didnt really read about the functionality but looks fine.

Ah misread ... its a Derby query... my format is from MS SQL.. so.. donno if its answer

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

1 Comment

The statement isn't wrong. It is allowed to leave out the column names, but that is considered bad style. Explicitely listing the columns in the INSERT statement is definitely the better choice.
0

The problem would be you are trying to insert duplicate value in ID column

Ensure that you are generating unique value for ID.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.