Prepared Statement

Hi

I am having problem trying to get this code to compile
ps.setString(8, DOB(to_date(DOB,'DD/MM/YYYY')));

Could someone please help  i am connecting to Oracle DB


thanks
irieJah
irieJahAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CEHJCommented:
Try

ps.setDate(8, java.sql.Date.valueOf("1969-12-31"));
0
CEHJCommented:
Or if you want to keep the same format:

SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
java.util.Date d = sdf.parse(DOB);
ps.setDate(8, new java.sql.Date(d.getTime());
0
objectsCommented:
> I am having problem trying to get this code to compile

whats the error?
Check that DOB() method returns a String. If not change the call to pass a string.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

CEHJCommented:
Typo:

>>java.util.Date d = sdf.parse(DOB);

should have been

java.util.Date d = sdf.parse(DOB());
0
irieJahAuthor Commented:
I tried ps.setDate(8, java.sql.Date.valueOf("1969-12-31"));

and it compiled but caught an Ora-01722 invalid number.

this is what i am trying to do:

String DOB1 = req.getParameter("DOB1").trim();
String DOB2 = req.getParameter("DOB2").trim();
String DOB3 = req.getParameter("DOB3").trim(

String DOB = DOB1 + "/" + DOB2 + "/" + DOB3;

String sql;
sql = "INSERT INTO Customer(NAME, GENDER, ADDRESS, PHONE, FAX, EMAIL, PWORD, DOB) VALUES (?,?,?,?,?,?,?,?)";

ps = con.prepareStatement(sql);
stmt = con.createStatement();

if(proceed) {
ps.setString(1, name);
ps.setString(2, gender);
ps.setString(3, address);
ps.setString(4, phone);
ps.setString(5, fax);
ps.setString(6, email);
ps.setString(7, password);
//ps.setDate(8, new java.sql.Date(d.getTime());
//ps.setDate(8, java.sql.Date.valueOf("1969-12-31"));
//ps.setDate(8, DOB(to-date(DOB,"dd/MM/yyyy")));
ps.executeUpdate();

thanks
0
objectsCommented:
whats all the formatting doing?  Why aren't you just using:

ps.setString(DOB);
0
CEHJCommented:
Yes, try

ps.setString(8, DOB);
0
objectsCommented:
yes thanks a fairly obvious typo on by behalf :)
0
irieJahAuthor Commented:
I tried everything and it is not working.  I just cannot capture that data.  

i am capturing the data from a html form.  when i do ps.setString(8,DOB); I get the error message invalid month.  i know i need to used to_date but where and how?

Help!!!!

Thanks
0
objectsCommented:
whats the db type of the field?

And what are the methods DOB() and to_date()?
0
CEHJCommented:
On the face of it (if dd/MM/yyyy), it would seem you can change

>>String DOB = DOB1 + "/" + DOB2 + "/" + DOB3;

to

String DOB = DOB3 + "-" + DOB2 + "-" + DOB1;

then do

ps.setDate(8, java.sql.Date.valueOf(DOB));

Your code would probably be more maintainable if you called those fields DOBday, DOBmonth and DOByear

0
irieJahAuthor Commented:
I keep getting this Ora error: SQL  command not ended properly.  Therefore i cannot executeUpdate.
0
objectsCommented:
whats your schema?
0
CEHJCommented:
Please post the statement you're using
0
irieJahAuthor Commented:
Here you go

try {
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
      con=DriverManager.getConnection("jdbc:odbc:MscProject;PWD=tiger");

String sql;
sql = "INSERT INTO Customer(NAME, DOB, GENDER, ADDRESS, PHONE, FAX, EMAIL) VALUES (?,?,?,?,?,?,?))";

ps = con.prepareStatement(sql);
stmt = con.createStatement();
DOB = "31/12/1969";

SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
java.util.Date d = sdf.parse(DOB);
ps.setDate(2, new java.sql.Date(d.getTime()));

// inserting records

if(proceed)
{
ps.setString(1, name);
ps.setString(2, DOB);
ps.setString(3, gender);
ps.setString(4, address);
ps.setString(5, phone);
ps.setString(6, fax);
ps.setString(7, email);
ps.executeUpdate();
}

Note whether or not i put the code ps.setDate(2, new java.sql.Date(d.getTime())); in the if statement i still get that error.

i am connecting to oracle using the jdbc-odbc driver
thanks
0
objectsCommented:
sql = "INSERT INTO Customer(NAME, DOB, GENDER, ADDRESS, PHONE, FAX, EMAIL) VALUES (?,?,?,?,?,?,?))";

get rid of the last bracket

sql = "INSERT INTO Customer(NAME, DOB, GENDER, ADDRESS, PHONE, FAX, EMAIL) VALUES (?,?,?,?,?,?,?)";
0
CEHJCommented:
If you're going to hardcode the date fro the moment, you can simplify your code to this, until you want to compose it as I mentioned above:

sql = "INSERT INTO Customer(NAME, DOB, GENDER, ADDRESS, PHONE, FAX, EMAIL) VALUES (?,?,?,?,?,?,?)";

ps = con.prepareStatement(sql);
stmt = con.createStatement();
DOB = "1969-12-31";


// inserting records

if(proceed)
{
ps.setString(1, name);
ps.setDate(2, java.sql.Date.valueOf(DOB));
ps.setString(3, gender);
ps.setString(4, address);
ps.setString(5, phone);
ps.setString(6, fax);
ps.setString(7, email);
ps.executeUpdate();
}
0
irieJahAuthor Commented:
This is the exception i caught:

javax.servlet.ServletException: Unparseable date: "31-12-1969"
0
CEHJCommented:
Where are you doing any parsing (there's none in the code i just posted)?
0
irieJahAuthor Commented:
I dont want to hard code the date because remember i using a form and the user will input the info. that is why i am using the first solution you gave me
0
CEHJCommented:
OK - i was just suggesting the hardcoding while you were testing. Smoothest would be  to use the code at Date: 12/03/2003 03:51AM PST
0
objectsCommented:
What format are the users entering the data?
What is the database type of the DOB column?
0
irieJahAuthor Commented:
the format is dd-mm-yyyy
the type for the DOB column is date

I am going crazy just one field giving this much trouble.
0
objectsCommented:
SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy");
java.util.Date d = sdf.parse(DOB);
ps.setDate(8, new java.sql.Date(d.getTime());
0
irieJahAuthor Commented:
Nothing seem to be working here is the entire code for the last time.  I am fed up

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

public class motorServlet2 extends HttpServlet {

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

      res.setContentType("text/html");
      PrintWriter out = res.getWriter();

      out.print("<html><body>");

      out.print("<code><pre>");
      out.println("Welcome to Fast Track");


      // receiving parameters

      String name = req.getParameter("name").trim();
      String DOBday = req.getParameter("DOBday").trim();
      String DOBmth = req.getParameter("DOBmth").trim();
      String DOByear = req.getParameter("DOByear").trim();
      String gender = req.getParameter("gender").trim();
      String address = req.getParameter("address").trim();
      String areaCode = req.getParameter("areaCode").trim();
      String phoneNo = req.getParameter("phoneNo").trim();
      String faxCode = req.getParameter("faxCode").trim();
      String faxNo = req.getParameter("faxNo").trim();
      String email = req.getParameter("email").trim();
      boolean proceed = false;

      String phone = areaCode + "-" + phoneNo;
      String fax = faxCode + "-" + faxNo;
      String DOB = DOBday + "-" + DOBmth + "-" + DOByear;


      if(name != null)
      proceed = true;
      // connecting to database

      Connection con = null;
      Statement stmt = null;
      ResultSet rs = null;
      PreparedStatement ps = null;
      try {
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            con=DriverManager.getConnection   ("jdbc:odbc:MscProject;PWD=tiger");

String sql;
sql = "INSERT INTO Customer(NAME, DOB, GENDER, ADDRESS, PHONE, FAX, EMAIL) VALUES (?,?,?,?,?,?,?)";
      ps = con.prepareStatement(sql);
      stmt = con.createStatement();
      SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy");
      java.util.Date d = sdf.parse(DOB);
      ps.setDate(2, new java.sql.Date(d.getTime()));


      // inserting records

      if(proceed)
      {
      ps.setString(1, name);
      ps.setString(2, DOB);
      ps.setString(3, gender);
      ps.setString(4, address);
      ps.setString(5, phone);
      ps.setString(6, fax);
      ps.setString(7, email);
      ps.executeUpdate();
      }

      // displaying records

      rs = stmt.executeQuery("SELECT * FROM customer");
      while(rs.next())
      {
      out.print(rs.getObject(1).toString());
      out.print("\t");
      out.print(rs.getObject(2).toString());
      out.print("\t");
      out.print(rs.getObject(3).toString());
      out.print("\t");
      out.print(rs.getObject(4).toString());
      out.print("\t");
      out.print(rs.getObject(5).toString());
      out.print("\t");
      out.print(rs.getObject(6).toString());
      out.print("\t");
      out.print(rs.getObject(7).toString());
      out.print("\t");
      out.print(rs.getObject(8).toString());
      out.print("\n");
      }
} catch (SQLException e) {
      throw new ServletException(e);
       }catch (ParseException e) {
      throw new ServletException(e);
} catch (ClassNotFoundException e) {
      throw new ServletException(e);
} finally {
      try {
      if(rs != null)
      rs.close();
      if(stmt != null)
      stmt.close();
      if(ps != null)
      ps.close();
      if(con != null)
      con.close();
} catch (SQLException e) {}
}
out.print("</body></html>");
out.close();
}
}
0
objectsCommented:
> ps.setString(2, DOB);

why do you do that?
Doesn't look like it should be there.
0
irieJahAuthor Commented:
when i remove it the error message is invalid number
0
objectsCommented:
can u post the stack trace for the error.
0
irieJahAuthor Commented:
ORA-01722 invalid number

Cause: The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates.  

Action: Check the character strings in the function or expression; make sure they contain only numbers, a sign, a decimal point, and the character "E" or "e", then retry the operation

Do you understand this?  
0
objectsCommented:
its saying its failing to convert a string to a number.
what types are the other fields in the database?
0
irieJahAuthor Commented:
CUST_ID                                   NOT NULL NUMBER
NAME                                               VARCHAR2(30)
DOB                                                DATE
GENDER                                             VARCHAR2(6)
ADDRESS                                            VARCHAR2(50)
PHONE                                              NUMBER(10)
FAX                                                NUMBER(10)
EMAIL                                              CHAR(25)
0
objectsCommented:
> PHONE                                              NUMBER(10)
> FAX                                                NUMBER(10)

Are you sure you aren't entering any non-numeric data into these fields?
0
irieJahAuthor Commented:
I did test without the DOB field and the code work just fine.  thats why i know it has to do with the DOB

check executeQuery and verify if the code is OK.
0
objectsCommented:
very strange, so breaking it down to its simplest the following will fail yes:

sql = "INSERT INTO Customer(DOB) VALUES (?)";
ps = con.prepareStatement(sql);
ps.setDate(1, new java.sql.Date(System.currentTimeMillis()));
0
irieJahAuthor Commented:
it worked but it gave me the current date and not the one entered.

I resorted back to the original code
SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy");
java.util.Date d = sdf.parse(DOB);
ps.setDate(2, new java.sql.Date(d.getTime()));

and change this line of code
String phone = areaCode + "-" + phoneNo;
String fax = faxCode + "-" + faxNo;
 to
String phone = areaCode + phoneNo;
String fax = faxCode + faxNo;

change the textfield in the html file by removing the -
the format was 216-9263700
and it worked just fine now.   I suppose it does not recognized sign when converting a string to a number.

thanks a lot

ps. how do i get rid of this 00:00:00.0(time)
0
objectsCommented:
> it worked but it gave me the current date and not the one entered.

thats what it was meant to do :)
It was just a test.

> String phone = areaCode + "-" + phoneNo;
> String fax = faxCode + "-" + faxNo;

Yes that'll definitely break as I said earlier:
> Are you sure you aren't entering any non-numeric data into these fields?

But you said earlier it worked when adding those fields??

Anyway as long as its working for you at last :-)

> ps. how do i get rid of this 00:00:00.0(time)

wheres that appearing?

if its when displaying the result set try:

out.print(sdf.format(rs.getDate("DOB")));
0
CEHJCommented:
The main thing is you had the date fields in the wrong order and you had redundant date parsing code. Try this: I've marked the changes with 'WRONG' below:

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

public class motorServlet2 extends HttpServlet {

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

     res.setContentType("text/html");
     PrintWriter out = res.getWriter();

     out.print("<html><body>");

     out.print("<code><pre>");
     out.println("Welcome to Fast Track");


     // receiving parameters

     String name = req.getParameter("name").trim();
     String DOBday = req.getParameter("DOBday").trim();
     String DOBmth = req.getParameter("DOBmth").trim();
     String DOByear = req.getParameter("DOByear").trim();
     String gender = req.getParameter("gender").trim();
     String address = req.getParameter("address").trim();
     String areaCode = req.getParameter("areaCode").trim();
     String phoneNo = req.getParameter("phoneNo").trim();
     String faxCode = req.getParameter("faxCode").trim();
     String faxNo = req.getParameter("faxNo").trim();
     String email = req.getParameter("email").trim();
     boolean proceed = false;

     String phone = areaCode + "-" + phoneNo;
     String fax = faxCode + "-" + faxNo;
     //WRONG!
     //String DOB = DOBday + "-" + DOBmth + "-" + DOByear;
     
     String DOB = DOByear + "-" + DOBmth + "-" + DOBday;

     if(name != null)
     proceed = true;
     // connecting to database

     Connection con = null;
     Statement stmt = null;
     ResultSet rs = null;
     PreparedStatement ps = null;
     try {
     Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
          con=DriverManager.getConnection   ("jdbc:odbc:MscProject;PWD=tiger");

String sql;
sql = "INSERT INTO Customer(NAME, DOB, GENDER, ADDRESS, PHONE, FAX, EMAIL) VALUES (?,?,?,?,?,?,?)";
     ps = con.prepareStatement(sql);
     stmt = con.createStatement();
     //WRONG - you're already soing something about the date below
     /*
     SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy");
     java.util.Date d = sdf.parse(DOB);
     ps.setDate(2, new java.sql.Date(d.getTime()));
     */


     // inserting records

     if(proceed)
     {
     ps.setString(1, name);
     //WRONG - you should use the date
     //ps.setString(2, DOB);
     ps.setDate(2, java.sql.Date.valueOf(DOB));
     ps.setString(3, gender);
     ps.setString(4, address);
     ps.setString(5, phone);
     ps.setString(6, fax);
     ps.setString(7, email);
     ps.executeUpdate();
     }

     // displaying records

     rs = stmt.executeQuery("SELECT * FROM customer");
     while(rs.next())
     {
     out.print(rs.getObject(1).toString());
     out.print("\t");
     out.print(rs.getObject(2).toString());
     out.print("\t");
     out.print(rs.getObject(3).toString());
     out.print("\t");
     out.print(rs.getObject(4).toString());
     out.print("\t");
     out.print(rs.getObject(5).toString());
     out.print("\t");
     out.print(rs.getObject(6).toString());
     out.print("\t");
     out.print(rs.getObject(7).toString());
     out.print("\t");
     out.print(rs.getObject(8).toString());
     out.print("\n");
     }
} catch (SQLException e) {
     throw new ServletException(e);
       }catch (ParseException e) {
     throw new ServletException(e);
} catch (ClassNotFoundException e) {
     throw new ServletException(e);
} finally {
     try {
     if(rs != null)
     rs.close();
     if(stmt != null)
     stmt.close();
     if(ps != null)
     ps.close();
     if(con != null)
     con.close();
} catch (SQLException e) {}
}
out.print("</body></html>");
out.close();
}
}



0
CEHJCommented:
(Have not altered fax./phone number things). Both those fields should be character ones, not numeric btw
0
objectsCommented:
> Try this: I've marked the changes with 'WRONG' below:

it's working already.
0
irieJahAuthor Commented:
it is working now thanks for the advice.  the only problem now is how do i change this code to print out only the date and not the time  in my result set.

out.print(rs.getObject(2).toString());
out.print("\t");
0
objectsCommented:
> the only problem now is how do i change this code to print out only the date and not the time  in my result set.

I posted that above:

out.print(sdf.format(rs.getDate(2)));
0
irieJahAuthor Commented:
javax.servlet.ServletException: [Oracle][ODBC]Restricted data type attribute violation
0
objectsCommented:
maybe the second field is not the date, try:

out.print(sdf.format(rs.getDate("DOB")));
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
objectsCommented:
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.