Win a copy of Android Programming: The Big Nerd Ranch Guide this week in the Android forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

parameter position out of range SQLException  RSS feed

 
Bob Matthews
Ranch Hand
Posts: 136
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
----- SQLException -----
SQL State: XCL13
Error Code: 20000
Message: The parameter position '2' is out of range.  The number of parameters for this prepared  statement is '1'.

----- SQLException -----
SQL State: 25001

The code that generates this error is as follows.....................


// Updating Derby database first
//[b] if we have the latest trade record to update
// i.e. the latest trade record which has since closed
if (((PL_updates == 1) && ((myCanTrade) || (myCurrPositionValue > 0)))) {
// retrieve the latest trade record
rs = s.executeQuery("SELECT * FROM xxxxxx ORDER BY TRADE_NO DESC NULLS LAST FETCH NEXT 1 ROW ONLY");
rs.next();
String Date3 = rs.getString("Trading_Date");
int Time3 = rs.getInt("Trading_Time");
// get two variable values for penultimate record update
class_required = rs.getString("class_current");
return_required = rs.getInt("return_current");
// and now update the latest trade record
s.setCursorName("MYCURSOR");
rs = s.executeQuery("SELECT CLASS_CORR_P FROM xxxxxx WHERE Trading_Date = '"
        + Date3 + "' AND Trading_Time = " + Time3
+ " FOR UPDATE");
rs.next();
psUpdate = conn.prepareStatement("UPDATE xxxxxx SET CLASS_CORR_P = ? WHERE CURRENT OF MYCURSOR");
statements.add(psUpdate);
psUpdate.setString(1, class_current);
psUpdate.setDouble(2, return_current);
psUpdate.setDouble(4, xxxxx_profit);
psUpdate.executeUpdate();
PL_updates = PL_updates - 1;
} // end of updating the latest trade record

// commit the above transactions
conn.commit();

 
Bear Bibeault
Author and ninkuma
Marshal
Posts: 65973
140
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The SQL prepared statement only has one data placeholder, but you are trying to to give it three parameters. It can accept only one per placeholder.

And in the future, please UseCodeTags.
 
Bob Matthews
Ranch Hand
Posts: 136
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
would you care to show how to correct my code ?
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 36875
481
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Bear did explain the problem. In more detail:


You'd have two remove the last two lines of this code. Or you'd have to add two more parameters to the SQL.
 
Bob Matthews
Ranch Hand
Posts: 136
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
OK
So I wish to update three fields positioned at x, x+1 and x+3

please show me how to add another two parameters to the sql

 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 36875
481
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Forget about the Java part of this for a minute.

This updates one field


This updates two fields


Can you rewrite your sql with the proper column names?
 
Bob Matthews
Ranch Hand
Posts: 136
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
psUpdate = conn.prepareStatement("UPDATE xxxxxx SET CLASS_CORR_P = ?, RETURN_CORR_P = ?, PROFIT_LOSS = ? WHERE CURRENT OF MYCURSOR");
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 36875
481
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Correct. Now you can put that in your Java code. And set the values to positions 1, 2 and 3 respectively in Java code.
 
Bob Matthews
Ranch Hand
Posts: 136
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you for that clear explanation.............I understand that part now

New errors :-

----- SQLException -----
SQL State: 42X01
Error Code: 20000
Message: Syntax error: Encountered "-" at line 1, column 53.

----- SQLException -----
SQL State: 25001
Error Code: 20000
Message: Cannot close a connection while a transaction is still active.

My code is now as follows....................
 
Bob Matthews
Ranch Hand
Posts: 136
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
sorry - ignore the past post - my bad
 
Bob Matthews
Ranch Hand
Posts: 136
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you for your help

Bob M
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 36875
481
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You're welcome. And thanks for bearing with me on the small questions to get you to the answer!
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!