Win a copy of The Business Blockchain this week in the Cloud forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Prepared Statement Problem

 
Shanmugam Muthukumarasamy
Ranch Hand
Posts: 83
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All,
In my application, at runtime, I am getting the following exception:
Description: Class: FdpMbrDatesData Method: executeQuery() : SQLException
java.sql.SQLException: ORA-01008: not all variables bound
I am not able to guess what is the problem with the following portion of the code.
pstmt = rO_conn.prepareStatement(lS_SQLStatement);
pstmt = this.setBindParams(pstmt);
int ii_position = 1;
//set timestamp of query; Take stamp before query as this is the most conservative view
super.setSQLDateTimeStamp(new java.util.Date());
rs = pstmt.executeQuery();

While crossing the "pstmt.executeQuery()" it is throwing the above exception.
It is very Urgent...If any body have any idea please let me know.
Thanks in advance,
Shanmugam.
 
Avi Abrami
Ranch Hand
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Shanmugam,
You need to supply a value for every question mark ("?") in your SQL query that you use when you create your "PreparedStatement". Your error message implies that you are not. So if you would like to post the contents of the "lS_SQLStatement" string, and the definition of the "setBindParams()" method -- this may help me to spot the cause of the problem.
Hope this helps you.
Good Luck,
Avi.
 
Shanmugam Muthukumarasamy
Ranch Hand
Posts: 83
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Avi,
Thanks for your immediate reply. It is really urgent, if you have any idea, please let me know immediately.
Thanks in advance,
Shanmugam.
The Content lS_SQLStatement is :
java.lang.String lS_SQLStatement= "SELECT MBDT_KEY, DT_TYP_NUM, BNTP_KEY, MBR_DT FROM MBR_DATES WHERE CASE_MBR_KEY = ? AND DT_TYP_NUM IN (?) AND REC_STAT_CD = ? "
java.lang.String lS_selectionCriteria= " CASE_MBR_KEY = ? AND DT_TYP_NUM IN (?) AND REC_STAT_CD = ? "
setBindParam method() :
private java.sql.PreparedStatement setBindParams (java.sql.PreparedStatement rO_pstmt,
ArrayList [] ra_Criteria, int v_CriteriaCount)
throws SQLException {
// setup parameters for debugging
String lS_methodName = new String("setBindParams (java.sql.PreparedStatement,ArrayList,int)");
// if debugflag is set log class name and method
if (iO_FdpGlobalObject.debugMode()) {
printLog("Class: " + fS_className + " Method: " + lS_methodName);
printLog("Bind Parameters: ");
for (int i = 0; i < v_CriteriaCount; i++){
printLog(i + " " + ra_Criteria[i].toString());
}
}


int li_nbr_of_params = v_CriteriaCount;
int li_nbr_of_values = 0;
int li_ctr = 0;
Object lO_value = null;
String lS_keyVal = null;
int li_position = 1;

System.out.println("No of parameters : "+li_nbr_of_params);
try {
if (li_nbr_of_params > 0) {
while (li_ctr < li_nbr_of_params) {
ArrayList lA_paramValues = ra_Criteria[li_ctr];
for (int v = 1;v < lA_paramValues.size();v++) {
lO_value = lA_paramValues.get(v);
if (lO_value instanceof Long) {
rO_pstmt.setLong(li_position++,((Long)lO_value).longValue());
continue;}
if (lO_value instanceof String) {
rO_pstmt.setString(li_position++,((String)lO_value));
continue;}
if (lO_value instanceof java.util.Date) {
java.util.Date lDt_tmpDt = (java.util.Date) lO_value;
java.sql.Timestamp lSQLDateTime = new java.sql.Timestamp(lDt_tmpDt.getTime());
rO_pstmt.setTimestamp(li_position++, lSQLDateTime);
continue;
}
if (lO_value instanceof Integer) {
rO_pstmt.setInt(li_position++,((Integer)lO_value).intValue());
continue;}
if (lO_value instanceof Double) {
rO_pstmt.setDouble(li_position++,((Double)lO_value).doubleValue());
continue;}
if (lO_value instanceof java.sql.Timestamp) {
java.sql.Timestamp lSQLDateTime = (java.sql.Timestamp) lO_value;
rO_pstmt.setTimestamp(li_position++, lSQLDateTime);
continue;}
if (lO_value instanceof ArrayList) {
ArrayList lA_elements = null;
lA_elements = (ArrayList) lO_value;
int s = lA_elements.size();
lO_value = null;
for (int i=0;i < s;i++) {
lO_value = lA_elements.get(i);
if (lO_value instanceof Long) {
rO_pstmt.setLong(li_position++,((Long)lO_value).longValue());
continue;}
if (lO_value instanceof String) {
rO_pstmt.setString(li_position++,((String)lO_value));
continue;}
if (lO_value instanceof java.util.Date) {
java.util.Date lDt_tmpDt = (java.util.Date) lO_value;
java.sql.Timestamp lSQLDateTime = new java.sql.Timestamp(lDt_tmpDt.getTime());
rO_pstmt.setTimestamp(li_position++, lSQLDateTime);
continue;
}
if (lO_value instanceof Integer) {
rO_pstmt.setInt(li_position++,((Integer)lO_value).intValue());
continue;}
if (lO_value instanceof Double) {
rO_pstmt.setDouble(li_position++,((Double)lO_value).doubleValue());
continue;}
if (lO_value instanceof java.sql.Timestamp) {
java.sql.Timestamp lSQLDateTime = (java.sql.Timestamp) lO_value;
rO_pstmt.setTimestamp(li_position++, lSQLDateTime);
continue;}
}
continue;
}

} //end for
++li_ctr;
} //end while
} //end if
} catch (SQLException S) {throw S;}//end try
return rO_pstmt;
}
 
Younes Essouabni
Ranch Hand
Posts: 479
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Just a quick guess
with this line of code for (int v = 1;v < lA_paramValues.size();v++)
aren't you missing 1 value? Since an ArrayList (AFAIK) begins at index 0 and ends at index size-1.

I hope it helped you.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic