asked on

i want to uadate database table

hai,
i want to update the table,by using this i am getting error i.e ora-00093,
sql command not properly ended.reply
as early as possible.

fstate.html
------------

<html>
<body bgcolor="#aacbcc">
<pre>
<h1><center>FileDetails</center></h1><hr width=600>
<form action="http://localhost:8080/servlet/fstate" method="post">
File Id: <input type=text name="fname" value="">
Employee Id: <input type=text name="id" value="">
Select the Status:<select name="process">
<option value=i>insert
<option value=u>update
<option value=d>delete
<option value=r>reset

</select>
<input type=submit value=submit>
</form>
</pre>
</body>
</html>

fstate.java
------------

import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.sql.*;

public class fstate extends HttpServlet
{

Connection con;
Statement st;
public void init(ServletConfig sc)throws ServletException
{

try
{
super.init(sc);

}

catch(Exception e)
{
System.out.println(e.getMessage());

}

}//end of init method

public void service (HttpServletRequest req,HttpServletResponse res)
throws ServletException,IOException
{

try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con=DriverManager.getConnection("jdbc:odbc:care","scott","tiger");
st=con.createStatement();

res.setContentType("text/html");
PrintWriter pw=res.getWriter();
String fname1=req.getParameter("fname");
String id1=req.getParameter("id");
String p=req.getParameter("process");

pw.println(fname1);
pw.println(id1);
pw.println(p);

if(p.equals("i"))
{
int y=st.executeUpdate("insert into statustable1(filenameid,insertid,status)values('"+fname1+"','"+id1+"','"+p+"')");
}

else if(p.equals("u"))
{
int y=st.executeUpdate("update statustable1 set status='"+p+"', updateid='"+id1+"' where filenameid='"+fname1+"')");
}

else if(p.equals("d"))
{
int y=st.executeUpdate("update statustable1 set status='"+p+"', deleteid='"+id1+"' where filenameid='"+fname1+"')");
}

else if(p.equals("r"))
{
int y=st.executeUpdate("update statustable1 set status='"+p+"', resetid='"+id1+"' where filenameid='"+fname1+"')");
}

else
{
pw.println("not inserted properly");
}

st.close();
con.close();
pw.close();
}

catch(Exception e)
{
System.out.println(e.getMessage());
}

}// end of service

}// end of class

databasetable
--------------
create table statustable(filenameid varchar2(10) primary key,
insertid varchar2(10),
updateid varchar2(10),
delete varchar2(10),
reset varchar2(10));

rwarrior

hi!

propably a syntax error.Maybe the insert statement needs a space around the keyword values.

my way in situations like this is to print out the sql strings to system.out or as html (if its a servlet) and if you dont see the syntax error immiadiatly paste it in the sql worksheet manager and execute it there. there you have a better error report and you can directly manipulate your string till it has the right syntax

good luck
martin

sobhan_genie

Hello indupriya,

The error comes for the reason below. Probably the length of the fields are more than what is defined in database check it out.

00093, 00000, "%s must be between %s and %s"
// *Cause: The parameter value is not in a valid range.
// *Action: Modify the parameter value to be within the specified range.

bye,
Sobhan

vpadma

You cannot pass variables in sql statement to a Statement Object like you have done here. You have to PreparedStatement and then use '? ?' for those fields and then later on, call setString() (or setXXX() statements to pass variables to it.)

Also another thing is, in your query below,
("update statustable1 set status='"+p+"', deleteid='"+id1+"' where filenameid='"+fname1+"')");
why do you have the last ")" for? It does not make sense to me.

Try this:
urString = "update statustable1 set status=?, deleteid=? where filenameid=?);
pst=con.prepareStatement(urString);
pst.setString(1, p);
pst.setString(2, id1);
.
.
.

and then run the execute command on pst.

I hope this made things clear.

Padma.

goldwarlock

2vpadma: 1st you can use update here
2indupriya: puu space aroud all words in sql
and what the exact error can you specify...

btw: visit my site
www.xanga.com/goldwarlock

indupriya

ASKER

hai goldwarlock,
The error it shows in the webserver2.0 is

[Microsoft][ODBC Driver for oracle][oracle]ORA-00933:SQL command not properly ended.

I tried that ,but i did't get.any one can help.
as early as possible.
by

thirumalaisamy

hi,
1. Remove ")" from the update query string.
2. If id is a integer,remove sinle quotes.

thirumalaisamy

sorry.
I didn't see id is a varchar.
SO you try first step.

indupriya

ASKER

hai thirumalaisamy,
sorry, it is the synatx ,when we are passing sql statements ,we shold include those quotations, am i right.
The statements at the ending is like that ')").
i did't gave like ")").
reply.
by

ASKER CERTIFIED SOLUTION

thirumalaisamy

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

indupriya

ASKER

hai thiru,
if u don't mind can you write down the update statement as for my program.
reply

indupriya

ASKER

sorry thiru,
i did't see that last brackets. i got it.
thank a lot.
by

vpadma

I had pointed out the extra ")" first. Please read my comments. It is not a fair practice to simply repeat someone else's suggestions and get the points for it.

Padma.

thirumalaisamy

hi vpadma,
I didn't see your comments. If i would have read then why should i repeat it. Before type anything,just think what you've written. DONT HURT OTHERS BY WRITING LIKE THIS.

vpadma

It is nothing personal, thirumalaisamy. I just pointed out that even if these repeats happen(which I have observed quite often) the points should go to the one that points out first. So, it is more a responsibility of the owner of the question who grants the points.
Again, this forum is nothing personal, so no room for hard feelings. It is simply an exchange of technology practicing a general rule. That is all.
I am sorry that it hurt your feelings. But I would not take anything that serious if I were you.

Padma.