File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

java.sql.SQLException: Invalid column index

 
saikrishna cinux
Ranch Hand
Posts: 689
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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();


 
Jeff Albertson
Ranch Hand
Posts: 1780
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why 9 and 5?
 
Maximilian Xavier Stocker
Ranch Hand
Posts: 381
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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!)
 
Jeff Albertson
Ranch Hand
Posts: 1780
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
Maximilian Xavier Stocker
Ranch Hand
Posts: 381
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:
 
M. Dooze
Greenhorn
Posts: 6
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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...
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 33691
316
Eclipse IDE Java VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"dooze dooze",
Please check your private messages for a message from me.

Thanks
Jeanne
Forum Sheriff
 
M. Dooze
Greenhorn
Posts: 6
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hey jeanne,
thanks for pointing out the mistake...I have made the necessary corrections and I'd say big appology on that one..
Dooze
 
yoona yee
Greenhorn
Posts: 3
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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();

}
 
Jan Cumps
Bartender
Posts: 2576
11
C++ Linux Netbeans IDE
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Do you get exactly the same error, or a different one?

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

You have to remove the quotes.
 
yoona yee
Greenhorn
Posts: 3
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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());
 
Christophe Verré
Sheriff
Pie
Posts: 14691
16
Eclipse IDE Ubuntu VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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).
 
yoona yee
Greenhorn
Posts: 3
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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!...
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic