Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Prepared Statement

Posted on 2003-12-02
44
Medium Priority
?
487 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 

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 90 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Are you developing a Java application and want to create Excel Spreadsheets? You have come to the right place, this article will describe how you can create Excel Spreadsheets from a Java Application. For the purposes of this article, I will be u…
Introduction Java can be integrated with native programs using an interface called JNI(Java Native Interface). Native programs are programs which can directly run on the processor. JNI is simply a naming and calling convention so that the JVM (Java…
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 about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:
Suggested Courses

782 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