wood burning stoves 2.0*
The moose likes JDBC and the fly likes Callable Statement returning forward only Recordset Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Callable Statement returning forward only Recordset" Watch "Callable Statement returning forward only Recordset" New topic
Author

Callable Statement returning forward only Recordset

Piyush Daiya
Ranch Hand

Joined: Jun 13, 2002
Posts: 67
Hi,
Im having problem, Callable Statement returning forward only Recordset eventhough i have mentioned scrollable resultset.Im using Oracle 8i.
The code is as given below:-
<%
// String Search msg
String srchmsg="No Matching Records found.Please try search again.";
// get parameters from the search form
String strUserName= request.getParameter("txt_username");
//define variables to execute the stored procedure
String chksql= "{call IT_SYSUSERS_PCK.IT_SYSUSERS_VWS_PRC(?,?,?)}";
CallableStatement chkcstmt = itsconn.prepareCall(chksql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY); //this is where im specifying scrollable resultset

// initialise the parameters of the stored procedure
chkcstmt.registerOutParameter(1 , Types.VARCHAR);
chkcstmt.registerOutParameter(2 , oracle.jdbc.driver.OracleTypes.CURSOR);
chkcstmt.setString(3,strUserName);


//execute stored procedure
chkcstmt.executeQuery();

//get the values from stored procedure
String chkerrmsg= chkcstmt.getString(1);
ResultSet chkresultset = (ResultSet)chkcstmt.getObject(2);
%>

<%
//Check if any error message was returned
if(chkerrmsg.equals("-")){

// if no error then check if resultset returned any results
if(chkresultset!=null){
int i=0;
String rowclass=null;
if(chkresultset.next()){
chkresultset.beforeFirst(); //getting error here
while(chkresultset.next())
{

if (i%2==0)
{
rowclass="r1";
}
else
{
rowclass="r2";
}
%>

<tr class="<%=rowclass%>">
<td nowrap>
<div align="center">
<input type="radio" name="rdb_user" value="<%=chkresultset.getString("SRU_ID")%>">
</div></td>
<td nowrap>
<div align="left"><%=checkNull(chkresultset.getString("SRU_NAME"))%></div></td>
<td nowrap>
<div align="left"><%=checkNull(chkresultset.getString("SRU_USERID"))%></div></td>
<td nowrap>
<div align="left"><%=checkNull(chkresultset.getString("SRU_RNAME"))%></div></td>
</tr>
<%
}//while loop

} // next()
else {%>
<tr class="r1">
<td colspan="4" nowrap>
<%=srchmsg%>
</td>
</tr>
<%}
} // resultset not null
}//no error
else {
//if there is error display it
%>
<tr class="r1">
<td colspan="4" nowrap>
<%=chkerrmsg%>
</td>
</tr>
<%} //no error
//cleanup data access variables
if(chkresultset !=null) chkresultset.close();
if(chkcstmt!=null) chkcstmt.close();

%>
-------------------------------------------------
If any1 can help me out, it will be gr8.
Piyush


"A scientist is not person who gives right answers but a person who asks right questions"
lechon manok
Greenhorn

Joined: Nov 12, 2002
Posts: 7
Hi, I'm having the same problem. Just wondering if you've figured out the solution already. If so, could you please tell me?
Thanks!
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

Originally posted by lechon manok:
Hi, I'm having the same problem. Just wondering if you've figured out the solution already. If so, could you please tell me?
Thanks!

which driver are you using?
 
 
subject: Callable Statement returning forward only Recordset