Not able to use ResultSet properly

[Nirvikalp Rao]

I wrote a JDBC- program, which simply

1- creates a table (having two columns).
2- inserts a record.
3- read a value using ResultSet object.

The code i wrote is:

import java.sql.SQLException;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.DriverManager;
import java.sql.ResultSet;

public class MyTable
{
	public static void main(String[] as) throws SQLException,ClassNotFoundException
	{
		Connection con = null;
		Statement stmt = null;
		Class.forName("oracle.jdbc.driver.OracleDriver");
		con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","system","great123");
		stmt = con.createStatement();
		stmt.executeQuery("create table Jr_ramanujam(name char(10),age number)");
		ResultSet rset = stmt.executeQuery("insert into Jr_ramanujam values ('ashish',12)");
		while(rset.next())
		{
			String str = rset.getString(1);
			System.out.println("user name is "+str);
		}
		rset.close();
		stmt.close();
		con.close();
	}
}

The above code compiles fine, but I am getting a runtime exception saying:

Exception in thread "main" java.sql.SQLException: Invalid column index
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
        at oracle.jdbc.driver.OracleResultSetImpl.getString(OracleResultSetImpl.java:379)
        at MyTable.main(MyTable.java:20)

The table gets created, data which I inserted is also there. What is going wrong when I try to
access 1st column of the table using ResultSet object ??

[Jeanne Boyarsky]

Insert statements don't return results. The code compiles because Java doesn't know whether you did an insert or select.

Can you add a select statement to the code before the rs.next()?

[Nirvikalp Rao]

Jeanne Boyarsky wrote:Can you add a select statement to the code before the rs.next()?

Thank you i did, and it worked.
Then I think I should have used

stmt.execute("insert into Jr_ramanujam values ('ashish',12)");

in stead of:

stmt.executeQuery("insert into Jr_ramanujam values ('ashish',12)");

am i right here??

[Jeanne Boyarsky]

Nirvikalp Rao wrote:Then I think I should have used

stmt.execute("insert into Jr_ramanujam values ('ashish',12)");

in stead of:

stmt.executeQuery("insert into Jr_ramanujam values ('ashish',12)");

Yes. Good point. I got distracted by the resultset part and didn't pick up on that!

[Nirvikalp Rao]

Jeanne Boyarsky wrote:Yes. Good point. I got distracted by the resultset part and didn't pick up on that!

Thank you Jeanne, I am new to database programming.Can you please suggest me any good book for sql,
which teaches about sql commands,say, which one returns a result and which does'nt ??

[Jeanne Boyarsky]

Nirvikalp Rao wrote:Thank you Jeanne, I am new to database programming.Can you please suggest me any good book for sql,
which teaches about sql commands,say, which one returns a result and which does'nt ??

The JavaDoc covers that. And Oracle's JDBC tutorial trail is excellent.

As far as books:
If you are using MySQL, Murach's MySql is good. In general, Persistence in the Enterprise covers an introduction to a number of topics including JDBC.