java.sql.SQLException: Invalid column index

I am reading a col. from a table... but it gives java.sql.SQLException: Invalid column index runtime sql exception

ResultSet resultset = statement.executeQuery("select PIN,TESTTYPE from TestUser where PIN='" + s + "'");
System.out.println("IN IFFFFFFFFFFFFFF");
while(resultset.next())
{
s2 = resultset.getString(9);
System.out.println(s2);
ttype = resultset.getString(5);
System.out.println(ttype);
}

resultset.close();
statement.close();

Why 9 and 5?

Errr yes as mentioned you are selecting two columns and then trying to get columns 9 and 5 which don't exist in that result set. You should be getting columns 1 and 2. (Remember result set column indexing begins at 1 not zero!)

Again, the column numbering (starting with 1 :roll: ) is in respect to the SELECT statement's field list, not the db's ordering of the columns on your table

You should never write SQL that relies on the table's column ordering, because it is easy for your DBA to alter that ordering as he goes about his job :roll:
[ December 16, 2005: Message edited by: Jeff Albrechtsen ]

Originally posted by Jeff Albrechtsen:
Again, the column numbering (starting with 1 :roll: ) is in respect to the SELECT statement's field list, not the db's ordering of the columns on your table You should never write SQL that relies on the table's column ordering, because it is easy for your DBA to alter that ordering as he goes about his job :roll:

[ December 16, 2005: Message edited by: Jeff Albrechtsen ]

I don't understand this post at all.

There is a lot of :roll: and I am not sure for whom or what. The original query has the field names specified. If it was * then I could understand. Also I addressed the columns specifically as result set columns in my post.

I mean if you are saying that doing x (in this case explictly naming your columns in SELECT) is a good practice okay. But that is what we are doing. I just don't get the :roll:

but you have made it even a more by adding two extra ones...so now there's a whooooping count of 5 count of

with one looking uninterested and sort of shocked at how its friends are bling blinging...

"dooze dooze",
Please check your private messages for a message from me.

Thanks
Jeanne
Forum Sheriff

Hey jeanne,
thanks for pointing out the mistake...I have made the necessary corrections and I'd say big appology on that one..
Dooze

i also got the same problem...
help me

tq

this is my code

sql =
" INSERT INTO " +Constants.DATABASE_SCHEME+ ".STD_LANGUAGE_SKILL SLS " +
"(SLS.SLSK_ID,SLS.LGDA_CODE,SLS.LANP_CODE,SLS.CREATED_BY,SLS.CREATED_DATE) " +
" VALUES " +
"(?,'?','?',?,?) ";

ps = conn.prepareStatement(sql);
String IDs_BMArray[] = p.val("IDs_BM").split(",");
String CODEs_BMArray[] = p.val("CODEs_BM").split(",");
for (int a = 0; a < IDs_BMArray.length; a++)
{
System.out.println("masuk cniiiiiiii loopp for"+SLSK_ID);
System.out.println("IDs_BMArray :- "+IDs_BMArray[a]);
System.out.println("CODEs_BMArray[a] :- "+CODEs_BMArray[a]);

String PK=Integer.toString(SLSK_ID);
ps.setString(1, PK);values.add(PK);
System.out.println("111111111 :- ");
ps.setString(2, IDs_BMArray[a]);values.add(IDs_BMArray[a]);
System.out.println("2222222222222 :- ");
ps.setString(3, CODEs_BMArray[a]);values.add(CODEs_BMArray[a]);
System.out.println("333333333333 :- "+s.userID());
ps.setString(4,s.userID());values.add(s.userID());
System.out.println("4444444444 :- ");
ps.setString(5, "SYSDATE");values.add("SYSDATE");
System.out.println("5555555555555555 :- ");
ps.executeQuery();

}

Do you get exactly the same error, or a different one?

(?,'?','?',?,?)

You have to remove the quotes.

Jan Cumps wrote:Do you get exactly the same error, or a different one?

(?,'?','?',?,?)

You have to remove the quotes.

yes..i got the same problem...
i cant remove the quote because datatype is varchar..
stop until System.out.println("333333333333 :- "+s.userID());

i cant remove the quote because datatype is varchar..

Little misunderstanding. You must not use single quotes with the dynamic parameters (?) of a prepared statement. They will be automatically added for varchars (or other kind of string types).

Christophe Verré wrote:

i cant remove the quote because datatype is varchar..

Little misunderstanding. You must not use single quotes with the dynamic parameters (?) of a prepared statement. They will be automatically added for varchars (or other kind of string types).

oh...oke...tq2!...