Solved

Prepared Statement

Posted on 2003-12-02
44
477 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
simple java question 3 59
tomcat startup error 5 102
How to execute a Python program and gather return output in Java 2 40
Convert from a json string array to a Java object 3 52
This was posted to the Netbeans forum a Feb, 2010 and I also sent it to Verisign. Who didn't help much in my struggles to get my application signed. ------------------------- Start The idea here is to target your cell phones with the correct…
Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…

840 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