I am unable to insert a record into my table created in oracle database. What mistake am I doing?

Question

    import java.sql.*;
import sun.jdbc.odbc.JdbcOdbcDriver;
class Jdbc1
{
    public static void main(String[] args) throws Exception
    {
        JdbcOdbcDriver jd=new JdbcOdbcDriver();
        DriverManager.registerDriver(jd);
        Connection con=DriverManager.getConnection("jdbc:odbc:sai123","SYSTEM","sai123");
        Statement st=con.createStatement();
        int res=st.executeUpdate("insert into A1 values('1','tomato','10')");
        System.out.println(res+"record is inserted..");
        st.close();
        con.close();
    }
}

and I have creates the table as:

CREATE TABLE A1(
INO INTEGER NOT NULL,
INAME CHAR(30) NOT NULL,
IPRICE INTEGER NOT NULL
);

When I compile and run the java prog it says "1 record inserted.." but when I open my table in SQLdeveloper I dont find any updates in the table. They are all null! I have also set the classpath for ojdbc-6.jar. What mistake am I doing herE? Are there any other files that I should copy and paste? I am just a beginner so kindly please help. And how does my program in the Editplus direct the updates directly into the table created in SQL. What path does it follow?

Please don't create tables in the SYS or SYSTEM account... — wolφi
– wolφi, Commented Jun 25, 2013 at 13:00
but that is how I can connect to the oracle database isnt it? — Sai Krishna
– Sai Krishna, Commented Jun 25, 2013 at 13:01
Don't put single quote marks around integers in your INSERT statement. — Gilbert Le Blanc
– Gilbert Le Blanc, Commented Jun 25, 2013 at 13:33
You can, but you should only do admin stuff as SYSTEM (see docs.oracle.com/cd/E11882_01/server.112/e25789/…), and especially don't create tables there. It's very difficult to get rid of the space you requested for your test tables. Do you need instructions how to create a user? — wolφi
– wolφi, Commented Jun 25, 2013 at 13:39

Mark Rotteveel · Accepted Answer · 2013-06-25 17:56:50Z

4

Oracle has auto-commit set to false by default. Either set autocommit to true from Oracle Sql Developer (or code) or commit your changes from code:

//before con.close()
con.commit();

Or if you want to set to auto-commit:

//after creating Connection
conn.setAutoCommit(true);

edited Jun 25, 2013 at 17:56

Mark Rotteveel

110k240 gold badges160 silver badges232 bronze badges

answered Jun 25, 2013 at 13:01

Random42

9,1996 gold badges60 silver badges87 bronze badges

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

6 Comments

Sai Krishna Over a year ago

Ive added that..its still the same .

GKlimov Over a year ago

The second way is just commit transaction after executeUpdate(). This is done by conn.commit()

wolφi Over a year ago

@SaiKrishna if it works, please consider accepting the answer, you and m3th0dman will get reputation points :-)

wolφi Over a year ago

What does SELECT * FROM DBA_TABLES WHERE table_name='A1' say in SQLDeveloper?

wolφi Over a year ago

So there are many different tables with the name 'A1' in your database. Maybe you insert into one of them and look at another one, as @Sourav suggests. Look at the user 'SYSTEM', and it's table 'A1', this is the one you use in your Java example...

|

Community · Accepted Answer · 2017-05-23 12:11:14Z

0

Try committing the Record by calling conn.commit();

also refer the Why Execute Update Says, one row updated on insert query

the final code for main should look like public static void main(String[] args) throws Exception { JdbcOdbcDriver jd=new JdbcOdbcDriver(); DriverManager.registerDriver(jd); Connection con=DriverManager.getConnection("jdbc:odbc:sai123","SYSTEM","sai123"); Statement st=con.createStatement(); int res=st.executeUpdate("insert into A1 values('1','tomato','10')"); System.out.println(res+"record is inserted.."); con.commit(); st.close(); con.close(); }

edited May 23, 2017 at 12:11

CommunityBot

11 silver badge

answered Jun 25, 2013 at 13:09

Akash Yadav

2,41122 silver badges33 bronze badges

1 Comment

Akash Yadav Over a year ago

Are you sure you are checking the correct table under correct schema and server ?

Sourav · Accepted Answer · 2013-06-25 13:22:18Z

0

Is it possible that your Java code and SQLDeveloper are referring to different schema? I would suggest to do the following

Make INO the Primary Key of the table
Put con.commit(); between int res=st.executeUpdate("insert into A1 values('1','tomato','10')"); and System.out.println(res+"record is inserted..");
Run the program multiple times to check if you are getting Primary Key violation exception. This will tell you if your program is inserting to a table you are not looking at.

answered Jun 25, 2013 at 13:22

Sourav

111 bronze badge

1 Comment

Sai Krishna Over a year ago

And how does my program in the Editplus direct the updates directly into the table created in SQL. What path does it follow?

Collectives™ on Stack Overflow

I am unable to insert a record into my table created in oracle database. What mistake am I doing?

3 Answers 3

6 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related