SQL ERROR while executing the code java.sql.SQLException: ORA-01008: not all variables bound

mahesh · Dec 11, 2006

Hi all
i'm getting the above error
plz tell me how to solve that error
the code snippet is as bellow

while(rst.next()){
con1=DriverManager.getConnection(url2,"rcxdev1","rcxdev1");
PreparedStatement pstmt=null;
pstmt=con1.prepareStatement(sb.toString());
for(int i=0;i<mp.size();i++){

if((mp.get(rst.getMetaData().getColumnLabel(i+1)).equals("NUMBER")))
{
pstmt.setInt(i+1,rst.getInt(i+1));
System.out.println("number "+i+" "+rst.getInt(i+1));
}

if((mp.get(rst.getMetaData().getColumnLabel(i+1)).equals("VARCHAR2")))
{
pstmt.setString(i+1,rst.getString(i+1));
System.out.println("char2 "+i+" "+rst.getString(i+1));
}

if((mp.get(rst.getMetaData().getColumnLabel(i+1)).equals("DATE")))
{
pstmt.setDate(i+1,rst.getDate(i+1));
System.out.println("date "+i+" "+rst.getDate(i+1));
}

if((mp.get(rst.getMetaData().getColumnLabel(i+1)).equals("CHAR")))
{
pstmt.setString(i+1,rst.getString(i+1));
System.out.println("char");
}
}

pstmt.executeUpdate(sb.toString());
pstmt.close();
}
con1.close();

}catch(Exception e){
e.printStackTrace();
}

Ingo R. Homann · Dec 11, 2006

Hi,

The error says that your SQL-Statement (which you did not post) has some
variables (like "select * fom table where id=?") and that the variables
are not set (via pstmt.setXYZ(...)).

Try debugging your code!

Ciao,
Ingo

wesley.hall · Dec 11, 2006

mahesh said:
Hi all
i'm getting the above error
plz tell me how to solve that error
the code snippet is as bellow

while(rst.next()){
con1=DriverManager.getConnection(url2,"rcxdev1","rcxdev1");
PreparedStatement pstmt=null;
pstmt=con1.prepareStatement(sb.toString());
for(int i=0;i<mp.size();i++){

if((mp.get(rst.getMetaData().getColumnLabel(i+1)).equals("NUMBER")))
{
pstmt.setInt(i+1,rst.getInt(i+1));
System.out.println("number "+i+" "+rst.getInt(i+1));
}

if((mp.get(rst.getMetaData().getColumnLabel(i+1)).equals("VARCHAR2")))
{
pstmt.setString(i+1,rst.getString(i+1));
System.out.println("char2 "+i+" "+rst.getString(i+1));
}

if((mp.get(rst.getMetaData().getColumnLabel(i+1)).equals("DATE")))
{
pstmt.setDate(i+1,rst.getDate(i+1));
System.out.println("date "+i+" "+rst.getDate(i+1));
}

if((mp.get(rst.getMetaData().getColumnLabel(i+1)).equals("CHAR")))
{
pstmt.setString(i+1,rst.getString(i+1));
System.out.println("char");
}
}

pstmt.executeUpdate(sb.toString());
pstmt.close();
}
con1.close();

}catch(Exception e){
e.printStackTrace();
}

It would be been wise to provide us with the value of 'sb.toString()',
which forms your prepared statement, as without that, we only have 1/2
of the picture... however...

I would say that the problem is either...

A) Your prepared statement has more than 1 unknown (? characters)

B) (more likely) that 'mp.get(rst.getMetaData().getColumnLabel(i+1)'
does not equal "NUMBER", "VARCHAR2", "DATE" or "CHAR" and therefore,
the code is not entereing any of the if statements and the PS variable
is never being set. Try printing out the value of this expression to
discover its true value.

cb_1987 · Apr 6, 2010

hi can anyone plz help me out

Below is the code of the jsp file named search.jsp
I am working on a project where I need to fetch the data from table property_master and display it on search.jsp
I am using Oracle 9i at backend.
search.html has a form called searchform and after search is clicked the sql query should fetch the data according to the values selected. so I have used prepared statement. when in the search.jsp I fire simple query like select * from property_master, it retrieves all the data. but when i am using prepared statement this just doesnt work at all.
as given in the code, i can see all the outputs upto System.out.println("while") on tomcat prompt
so it means resultset is not null right? after above command neither tomcat prompt shows anything nor the redirected jsp file. it's totally blank....
I am not understanding where the problem lies... plzz help me out

code of search.jsp

<%..... usual details in jsp file uptil </head> %>

<body>
<%
String driver="oracle.jdbc.OracleDriver";
String url="jdbc

racle:thin

//charuta:1521/sct";
Class.forName(driver);
Connection con=null;
try{
con=DriverManager.getConnection(url,"scott","tiger");
System.out.println("Connection gotestablished");

String stype="";
String ptype="";
//String minprice="";
//String maxprice="";

stype=request.getParameter("servicetype");
System.out.println(stype);
ptype=request.getParameter("propertytype");
System.out.println(ptype);

/*String sql="select * from property_master where service_type='%"+stype+"%' and property_type='%"+ptype+"%'";
Statement stmt=con.createStatement();
ResultSet rs=stmt.executeQuery(sql);*/

PreparedStatement pstmt=con.prepareStatement("select * from property_master where service_type=? and property_type=?");
pstmt.setString(1,stype);
pstmt.setString(2,ptype);

ResultSet rs=pstmt.executeQuery();
%>
<%
System.out.println(" WHILE");
while(rs.next())
{
System.out.println("in while");
%>
<%=rs.getString(1)%>
<%=rs.getString(2)%>
<%=rs.getString(3)%>
<%=rs.getString(4)%>
<%=rs.getString(5)%>
<%=rs.getString(6)%>
<%=rs.getString(7)%>
<%=rs.getString(8)%>
<%=rs.getString(9)%>
<%
System.out.println("going out of while");
}
rs.close();
rs=null;
pstmt.close();
pstmt=null;
}catch(Exception e){

System.out.println("Exception e : "+e);
}
finally{
if(con!=null)
{con.close();}
}
%>
</body>
</html>

rest of the things i cannot see anywhere

PreparedStatement Insert statement MS SQL Server problem	1	Feb 7, 2007
using JSP and JavaBeans to enter data in MySQL database	2	May 10, 2007
error when returning a ResultSet	3	Nov 21, 2003
Unable to compile class for JSP	3	Nov 7, 2003

SQL ERROR while executing the code java.sql.SQLException: ORA-01008: not all variables bound

mahesh

Ingo R. Homann

wesley.hall

cb_1987

Ask a Question

Similar Threads

Members online

Forum statistics

Latest Threads