Solved

Prepared Statement

Posted on 2003-12-02
44
474 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:irieJah
  • 18
  • 15
  • 11
44 Comments
 
LVL 86

Expert Comment

by:CEHJ
ID: 9862220
Try

ps.setDate(8, java.sql.Date.valueOf("1969-12-31"));
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 9862253
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
 
LVL 92

Expert Comment

by:objects
ID: 9862366
> 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
 
LVL 86

Expert Comment

by:CEHJ
ID: 9862664
Typo:

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

should have been

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

Author Comment

by:irieJah
ID: 9862861
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
 
LVL 92

Expert Comment

by:objects
ID: 9862874
whats all the formatting doing?  Why aren't you just using:

ps.setString(DOB);
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 9862950
Yes, try

ps.setString(8, DOB);
0
 
LVL 92

Expert Comment

by:objects
ID: 9862989
yes thanks a fairly obvious typo on by behalf :)
0
 

Author Comment

by:irieJah
ID: 9864423
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
 
LVL 92

Expert Comment

by:objects
ID: 9864467
whats the db type of the field?

And what are the methods DOB() and to_date()?
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 9865914
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
 

Author Comment

by:irieJah
ID: 9870037
I keep getting this Ora error: SQL  command not ended properly.  Therefore i cannot executeUpdate.
0
 
LVL 92

Expert Comment

by:objects
ID: 9870066
whats your schema?
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 9870071
Please post the statement you're using
0
 

Author Comment

by:irieJah
ID: 9870240
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
 
LVL 92

Expert Comment

by:objects
ID: 9870292
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 9870548
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
 

Author Comment

by:irieJah
ID: 9870757
This is the exception i caught:

javax.servlet.ServletException: Unparseable date: "31-12-1969"
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 9870766
Where are you doing any parsing (there's none in the code i just posted)?
0
 

Author Comment

by:irieJah
ID: 9870865
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 9870879
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
 
LVL 92

Expert Comment

by:objects
ID: 9871096
What format are the users entering the data?
What is the database type of the DOB column?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:irieJah
ID: 9871642
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
 
LVL 92

Expert Comment

by:objects
ID: 9871674
SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy");
java.util.Date d = sdf.parse(DOB);
ps.setDate(8, new java.sql.Date(d.getTime());
0
 

Author Comment

by:irieJah
ID: 9871806
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
 
LVL 92

Expert Comment

by:objects
ID: 9871857
> ps.setString(2, DOB);

why do you do that?
Doesn't look like it should be there.
0
 

Author Comment

by:irieJah
ID: 9871873
when i remove it the error message is invalid number
0
 
LVL 92

Expert Comment

by:objects
ID: 9871894
can u post the stack trace for the error.
0
 

Author Comment

by:irieJah
ID: 9871897
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
 
LVL 92

Expert Comment

by:objects
ID: 9871942
its saying its failing to convert a string to a number.
what types are the other fields in the database?
0
 

Author Comment

by:irieJah
ID: 9871968
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
 
LVL 92

Expert Comment

by:objects
ID: 9872012
> PHONE                                              NUMBER(10)
> FAX                                                NUMBER(10)

Are you sure you aren't entering any non-numeric data into these fields?
0
 

Author Comment

by:irieJah
ID: 9872135
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
 
LVL 92

Expert Comment

by:objects
ID: 9872169
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
 

Author Comment

by:irieJah
ID: 9872432
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
 
LVL 92

Expert Comment

by:objects
ID: 9872469
> 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
 
LVL 86

Expert Comment

by:CEHJ
ID: 9873702
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 9873719
(Have not altered fax./phone number things). Both those fields should be character ones, not numeric btw
0
 
LVL 92

Expert Comment

by:objects
ID: 9877095
> Try this: I've marked the changes with 'WRONG' below:

it's working already.
0
 

Author Comment

by:irieJah
ID: 9877167
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
 
LVL 92

Expert Comment

by:objects
ID: 9877212
> 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
 

Author Comment

by:irieJah
ID: 9877545
javax.servlet.ServletException: [Oracle][ODBC]Restricted data type attribute violation
0
 
LVL 92

Accepted Solution

by:
objects earned 30 total points
ID: 9877593
maybe the second field is not the date, try:

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

Expert Comment

by:objects
ID: 10285499
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

Suggested Solutions

Title # Comments Views Activity
hash value 2 39
scoresSpecial  challenge 13 42
noX challenge 17 77
groupSum6 challenge 6 48
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

747 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

13 Experts available now in Live!

Get 1:1 Help Now