Thread: SQL in Java

java.sql.SQLException: Invalid column index

Hi i am trying to retrieve some data from the database using the following code

public class Database {
 
 
	public static void main(String arg[]) throws Throwable
 
	{
		Database db=new Database();
		db.execute();
 
		}
	public  static Connection getOracleConnection() throws Exception {
		String driver = "oracle.jdbc.driver.OracleDriver";
		String url = "dbc:oracle:thin:@10.118.0.22:1521:DEVCENO3";
		String username = "cenevo";
		String password = "cenevo";
        System.out.println("Connected");
		Class.forName(driver); // load Oracle driver
		Connection conn = DriverManager.getConnection(url, username, password);
		System.out.println("Connected");
		return conn;
	}
 
	public void execute() throws Exception{
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		int output=0;
		conn = getOracleConnection();
		stmt = conn.createStatement();
		rs = stmt.executeQuery("SELECT DISTINCT BUSID FROM LNKBUSEXEC WHERE EXECID =8");
 
		System.out.println(rs.getInt(0));
 
 
		while (rs.next()) {
			ArrayList ret=new ArrayList();
			Connection conn1 = null;
			Statement stmt1 = null;
			ResultSet rs1 = null;
			int output1=0;
			conn1 = getOracleConnection();
			stmt1 = conn.createStatement();
			rs1 = stmt.executeQuery("SELECT AWSWSDL FROM ASPWEBSERVICE WHERE AWSIDENT = (SELECT ATSWISDENT FROM ASPTRANSACTION WHERE ATSCINT = (SELECT APACINT FROM ASPLNKPANELACTION WHERE (APACINA = (SELECT APACINA FROM LNKBUSTRAPAACT WHERE BUSID = (SELECT BUSID FROM BUSINESSSCENARIO WHERE BUSID = '"+rs.getInt(1)+"' ))) AND (APACINT = (SELECT APACINT FROM LNKBUSTRAPAACT WHERE BUSID = (SELECT BUSID FROM BUSINESSSCENARIO WHERE BUSID = '"+rs.getInt(1)+"' ))) AND (APACINP = (SELECT APACINP FROM LNKBUSTRAPAACT WHERE BUSID = (SELECT BUSID FROM BUSINESSSCENARIO WHERE BUSID = '"+rs.getInt(1)+"' )))))");
 
			System.out.println(rs1.getString(1));
		}
 
 
	}
 
 
}

i am encountering the exception as follows,

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.getInt(Orac leResultSetImpl.java:503)
at com.gold_solutions.http://www.webservices.Database.exec...tabase.java:48)
at com.gold_solutions.www.webservices.Database.main(Database.java:23)


Please help me...

if your problem is

rs = stmt.executeQuery("SELECT DISTINCT BUSID FROM LNKBUSEXEC WHERE EXECID =8");

Keep in mind that tables/columns/ect. with multi-word names need to be treated differently than tables/columns/ect. with just one-word names. Specifically, you need to add quotes before and after the names. In JAVA, you add a quote to a String with the symbol: \". So, try your code with this line instead:

rs = stmt.executeQuery("SELECT \"DISTINCT BUSID\" FROM LNKBUSEXEC WHERE EXECID =8");