This week's book giveaway is in the OCMJEA forum.
We're giving away four copies of OCM Java EE 6 Enterprise Architect Exam Guide and have Paul Allen & Joseph Bambara on-line!
See this thread for details.
The moose likes JDBC and the fly likes java.sql.SQLException: Invalid column index Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "java.sql.SQLException: Invalid column index" Watch "java.sql.SQLException: Invalid column index" New topic
Author

java.sql.SQLException: Invalid column index

saikrishna cinux
Ranch Hand

Joined: Apr 16, 2005
Posts: 689
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();




A = HARDWORK B = LUCK/FATE If C=(A+B) then C=SUCCESSFUL IN LIFE else C=FAILURE IN LIFE
SCJP 1.4
Jeff Albertson
Ranch Hand

Joined: Sep 16, 2005
Posts: 1780
Why 9 and 5?


There is no emoticon for what I am feeling!
Maximilian Xavier Stocker
Ranch Hand

Joined: Sep 20, 2005
Posts: 381
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

Joined: Sep 16, 2005
Posts: 1780
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

Joined: Sep 20, 2005
Posts: 381
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

Joined: Oct 29, 2007
Posts: 6
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...


Java is another world!
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30392
    
150

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

Thanks
Jeanne
Forum Sheriff


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
M. Dooze
Greenhorn

Joined: Oct 29, 2007
Posts: 6
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

Joined: Sep 24, 2012
Posts: 3
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

Joined: Dec 20, 2006
Posts: 2497
    
    8

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

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

You have to remove the quotes.


OCUP UML fundamental and ITIL foundation
youtube channel
yoona yee
Greenhorn

Joined: Sep 24, 2012
Posts: 3
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

Joined: Nov 24, 2005
Posts: 14687
    
  16

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).


[My Blog]
All roads lead to JavaRanch
yoona yee
Greenhorn

Joined: Sep 24, 2012
Posts: 3
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!...
 
Don't get me started about those stupid light bulbs.
 
subject: java.sql.SQLException: Invalid column index