File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Unable to insert the record into a table creates in oracle sql developer using a Java program. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Unable to insert the record into a table creates in oracle sql developer using a Java program." Watch "Unable to insert the record into a table creates in oracle sql developer using a Java program." New topic
Author

Unable to insert the record into a table creates in oracle sql developer using a Java program.

saikrishn viru
Greenhorn

Joined: Jun 14, 2013
Posts: 3

and I have creates the table as:


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?
K. Tsang
Bartender

Joined: Sep 13, 2007
Posts: 1966
    
    7

Did the sql insert got committed? You may want to explicit set the connection's commit level (conn.setAutoCommit(true);)

Also your table takes a integer, string and integer ... but your insert sql is passing in 3 strings? Shouldn't an exception be thrown?

Have you tried the actual sql in SQL Developer not in a java program? Did it work? If so the above can be ignored.

And welcome to the Ranch.


K. Tsang JavaRanch SCJP5 SCJD/OCM-JD OCPJP7
saikrishn viru
Greenhorn

Joined: Jun 14, 2013
Posts: 3
Yes ive tried the program with autocommit too..and yes ive changed the string to the number thankyou. Can you kindly tell me what softwares do i need for jdbc? I have jdk 1.6, JdbcOdbc driver,Oracle SQL developer. Are there any jar files required?
K. Tsang
Bartender

Joined: Sep 13, 2007
Posts: 1966
    
    7

saikrishn viru wrote:Yes ive tried the program with autocommit too..and yes ive changed the string to the number thankyou. Can you kindly tell me what softwares do i need for jdbc? I have jdk 1.6, JdbcOdbc driver,Oracle SQL developer. Are there any jar files required?


You are using ODBC-JDBC bridge driver? In java you should use a purse JDBC driver. Depending on what DB you are using, you can google the jar file. For example Oracle will be file ojdbc6.jar and mysql is mysql-connector-java-xxx.jar (xxx is the version).
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3438
    
  47

K. Tsang is right about not using the JDBC-ODBC bridge. You can find links to JDBC drivers for Oracle at our Oracle FAQ. I'd suggest switching to Oracle driver immediately, it will save you a lot of trouble.

Unfortunately, I don't see a clear problem in your code. It does look like a forgotten commit, but autocommit is the default in JDBC, so it probably isn't the reason. Are you sure you're looking into the right table in SQL Developer?

I have a few lesser points, though:

1) You're using the SYSTEM account for your application. It is not a good practice. In real environment, it is outright impossible, as it would bring terrible security issues. But even for small learning project it is not appropriate (you might run into issues allocating too much space in the SYSTEM tablespace, for example). The best is to learn from your first day that SYSTEM is just for administering the database. You need to learn how to create users (look up the CREATE USER command in Oracle documentation) and grant them necessary privileges (the GRANT command). You'll do this using your SYSTEM account, but then you'll use the newly created user in your application.

If you don't know how to start, try this book. It doesn't teach you to create a new account, just to unlock and use an existing one, but still far better than using SYSTEM. And perhaps it might be useful to you for other reasons as well.

2) Learn how to use PreparedStatements. All of the reasons mentioned in the article apply fully to Oracle database.

3) Specify target column in the insert into statement (here is why).

4) The Avoid Implicit Conversions advice applies to your case as well. (If you start using PreparedStatement, this will be less of a concern probably.)

Neither of these are connected to your current problem, but the sooner you get used to these practices, the better for you.
saikrishn viru
Greenhorn

Joined: Jun 14, 2013
Posts: 3
I am just a beginner. I will use prepared statement after executing this program. Please help me execute this program.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3438
    
  47

I understand that. Please take my other notes as an advice for your longer term.

As I've said, I don't see why your code behaves the way you're describing. So my immediate advice would be:

1) Stop using ODBC and switch to Oracle's JDBC driver. I've given the necessary links in my earlier post. This is not probably the problem, but the JDBC-ODBC bridge is known to have some problems, and also while configuring the ODBC datasource a mistake can be made. Without completing this step, I won't be able to assist you further.

2) Make sure your program and the SQL Developer are connecting to the same database/user. When you switch to Oracle JDBC driver, you'll be able to use the exact same connect string in your application and in SQL Developer, so we'll all know they are connecting to the same DB.

How many rows do you see in the A1 table? Isn't it possible that the new rows are at the bottom, and you just haven't scrolled down enough?

3) Add a con.commit() after st.executeUpdate(). It's probably not the problem, but it can't hurt, and we'll eliminate another possible source of uncertainty.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Unable to insert the record into a table creates in oracle sql developer using a Java program.
 
Similar Threads
Cannot perform SQL UPDATE null
How to insert date into MS Access Date/Time?
DB2 700 and 701 - Test Sample
db2 - 703 certification
Serious error in JPA with @Version