Problem in inserting data into Oracle database using JDBC

Question

I am trying to insert data using prepared statement in oracle 10g database but I am getting "SQL Exception:General Error" while executing the code given below. I think the problem is either with the DATE field or PASSWORD field data retrieval. Please Help me through this. Thanks.

Student Table:-

                      Sid       VARCHAR2(200) PRIMARY KEY  CHECK(Sid>0),
                      Pass_word VARCHAR2(10) NOT NULL,
                      S_name    VARCHAR2(20) NOT NULL,
                      G_name    VARCHAR2(20)         ,
                      Branch    VARCHAR2(10) NOT NULL,
                      D_company VARCHAR2(20)         ,
                      B_Percent INT NOT NULL CHECK(B_Percent<100),
                      twelth_percent INT NOT NULL CHECK(twelth_percent<100),
                      tenth_percent INT NOT NULL  CHECK(tenth_percent<100),
                      Certify   VARCHAR2(30),
                      Semester  INT NOT NULL CHECK(Semester<9),
                      D_Birth   DATE NOT NULL,
                      Sex       VARCHAR2(6) NOT NULL

Code:-

    int bpercent ;
    int twelthpercent;
    int tenthpercent;
    int semester;  
    String studentID = null;
    String studentpassword = null;
    String studentname = null;
    String Gname = null;
    String branch = null;
    String dcompany = null;
    String certify = null;
    String sex = null;
    Date date = new Date(00-00-0000);

    Connection connection = null;

try {

// Load the JDBC driver

String driverName = "sun.jdbc.odbc.JdbcOdbcDriver";

Class.forName(driverName);

connection = DriverManager.getConnection("jdbc:odbc:placement","siddharth","sid");

studentID = StudentID.getText();
spassword = PasswordField.getPassword();
studentname = NameField.getText();
Gname = GuardianField.getText();
branch = BranchField.getText();
dcompany = DcompanyField.getText();
bpercent = Integer.parseInt(BtechField1.getText());
twelthpercent = Integer.parseInt(TwelthField.getText());
tenthpercent = Integer.parseInt(TenthField.getText());
semester =Integer.parseInt(SemesterField.getText());
certify = CertificationField.getText();
date =  (Date) DateTextField.getValue();
sex = SexCombo.getActionCommand();



PreparedStatement state = connection.prepareStatement("insert into student " +"(sid,pass_word,s_name,g_name,branch,d_company,b_percent,twelth_percent,tenth_percent,certify,semester,d_birth,sex)"+
             "values(?,?,?,?,?,?,?,?,?,?,?,?,?)");

state.setString(1, studentID);
state.setString(2, spassword.toString());
state.setString(3,studentname);
state.setString(4,Gname);
state.setString(5,branch);
state.setString(6,dcompany);
state.setInt(7,bpercent);
state.setInt(8,twelthpercent);
state.setInt(9,tenthpercent);
state.setInt(10,semester);
state.setString(11,certify);
state.setDate(1, new java.sql.Date(date.getTime())); 
state.setString(12,sex);

state.executeUpdate();
state.close();

 JOptionPane.showMessageDialog(null,"Data Inserted","Information Messgage",JOptionPane.INFORMATION_MESSAGE);
 connection.close();
}                                         

catch (ClassNotFoundException e) {
// Could not find the database driver
  JOptionPane.showMessageDialog(null,e);
} 
catch (SQLException e) {
// Could not connect to the database
  JOptionPane.showMessageDialog(null,e);
 }

}

@kal -I am using netBeans 7.0 and there is no stacktrace of error given here in netBeans when I execute this. I think the problem is with DATE format mismatch with database but dont know how to correct it. You please check and tell if you can. thanks — Siddharth Jain
– Siddharth Jain, Commented Aug 22, 2011 at 18:00

Dan W · Accepted Answer · 2011-08-22 17:55:28Z

3

I believe you have a typo, you have:

state.setDate(1, new java.sql.Date(date.getTime())); 
state.setString(12,sex);

And I think you want:

state.setDate(12, new java.sql.Date(date.getTime())); 
state.setString(13,sex);

answered Aug 22, 2011 at 17:55

Dan W

5,7824 gold badges36 silver badges47 bronze badges

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

1 Comment

Siddharth Jain Over a year ago

thanks for correcting me but i have corrected that mistake and now it is giving an error saying "java.sql.SQLException:[Oracle][ODBC][Ora]ORA-01722:invalid number". Now what should I do. Please help. thanks

beny23 · Accepted Answer · 2011-08-22 18:16:40Z

2

You've got certify and semester the wrong way round

In your insert sql string:

insert into (... tenth_percent,certify,semester,d_birth, ...)

in your

state.setInt(9,tenthpercent);
state.setInt(10,semester);
state.setString(11,certify);
state.setDate(12, new java.sql.Date(date.getTime()));

so it tries to set the semester column to a string, which is invalid.

answered Aug 22, 2011 at 18:16

beny23

35.1k6 gold badges84 silver badges86 bronze badges

Comments

jagbandhuster · Accepted Answer · 2011-09-03 07:22:34Z

0

Instead of using the lines:

`String driverName = "sun.jdbc.odbc.JdbcOdbcDriver";    
Class.forName(driverName);`

Use the line:

DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver);

Sometimes, the class cannot be read by the drivermanager, so you need to register the driver with the drivermanager.

Best of Luck!

Well! there was a mistake in the code... Instead:

DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver);

Use:

DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver());

edited Sep 3, 2011 at 7:22

answered Aug 22, 2011 at 18:02

jagbandhuster

7072 gold badges7 silver badges21 bronze badges

Collectives™ on Stack Overflow

Problem in inserting data into Oracle database using JDBC

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related