Solved

i want to uadate database table

Posted on 2001-06-22
14
233 Views
Last Modified: 2008-02-26
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));


0
Comment
Question by:indupriya
  • 4
  • 4
  • 3
  • +3
14 Comments
 

Expert Comment

by:rwarrior
ID: 6218095
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
0
 

Expert Comment

by:sobhan_genie
ID: 6218669
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
0
 
LVL 2

Expert Comment

by:vpadma
ID: 6218942
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.
0
 
LVL 1

Expert Comment

by:goldwarlock
ID: 6219720
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
0
 

Author Comment

by:indupriya
ID: 6226151

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
0
 

Expert Comment

by:thirumalaisamy
ID: 6226674
hi,
1. Remove ")" from the update query string.
2. If id is a integer,remove sinle quotes.
0
 

Expert Comment

by:thirumalaisamy
ID: 6226677
sorry.
I didn't see id is a varchar.
SO you try first step.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:indupriya
ID: 6226755
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
0
 

Accepted Solution

by:
thirumalaisamy earned 50 total points
ID: 6226812
hi,
quotes ok.
but why ")" ?
I think you should give like '") instead of ')") in update statement. You can see the no. of left "("s and right ")"s.
Both should be equal.

thiru
0
 

Author Comment

by:indupriya
ID: 6226932
hai thiru,
           if u don't mind can you write down the update statement as for my program.
reply
0
 

Author Comment

by:indupriya
ID: 6226953
sorry thiru,
             i did't see that last brackets. i got it.
thank a lot.
by
0
 
LVL 2

Expert Comment

by:vpadma
ID: 6265594
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.
0
 

Expert Comment

by:thirumalaisamy
ID: 6268602
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.
0
 
LVL 2

Expert Comment

by:vpadma
ID: 6269234
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.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

An old method to applying the Singleton pattern in your Java code is to check if a static instance, defined in the same class that needs to be instantiated once and only once, is null and then create a new instance; otherwise, the pre-existing insta…
INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now