Java Calendar/Date to SQL Timestamp / Oracle Database

Question

Currently i am trying to insert a Timestamp value into a Oracle Database Timestamp Field.

Using CURRENT_TIMESTAMP i can insert data to the database

The timestamp it returns is (2013-11-20.14.50.7.832000000)

So il explain the issue. I need a created date/time (timestamp) and a expired date/time (timestamp). so i am using Java calendar to process the extra days. for example adding 365 days to get a year expiration.

Here is my current Java Date code:

    public Date GetCurrentDate(HttpServletRequest request, HttpServletResponse response) throws Exception{

    //Create current Date
    Calendar cal = new GregorianCalendar();
    Date creationDate = cal.getTime();

    
    
    return creationDate;


}

     Date datereturn = GetCurrentDate(request,response);
         java.sql.Timestamp timestampcurrent = new Timestamp(datereturn.getTime()); 
         timestampcurrent.setNanos(0);

Below is the code adding 2 hours to the current date to make the expiration date and adding it to a timestamp.

//Set Expired Date/Time Based from xml Evaluation (Days)
            Calendar cal = Calendar.getInstance();
            cal.setTime(datereturn);
            cal.add(Calendar.DAY_OF_YEAR,Integer.parseInt(getServletContext().getInitParameter("EXPIRED_DAYS_EVALUATION"))); // this will add two hours
            expireddatereturn = cal.getTime();
            timestampexpired = new Timestamp(expireddatereturn.getTime());
            timestampexpired.setNanos(0);
            logText.info(timestampexpired   + "    " + timestampcurrent .toString());

so i now have two timestamps, "timestampcurrent" (current date) and "timestampexpired" (expiration date).

i am trying to insert these values into a oracle database but i recieve a error:

                String sqlInsertData ="INSERT INTO EC_TABLE" +
                    "(licenseid, customername, description, servername,licensetype, username,password, createdDateTime,ExpiredDateTime)" +
                    " VALUES ('"+LicenseID+"','"+CustomerName+"','"+Description+"','"+ServerName+"','"+LicenseType+"','"+EncryptedUsername+"','"+EncryptedPassword+"','"+timestampcurrent+"','"+timestampexpired+"')";

THE ERROR IS : ORA-01843: not a valid month

Been trying to fix this for hours but i cannot find the issue!. please help!.

additional information:

logText Returns:

logText.info(timestampcurrent + " \ " + timestampcurrent.toString());

INFO [http-8080-2] (ecsystem.java:233) - 2013-11-20 15:34:55.0 \ 2013-11-20 15:34:55.0

logText.info(timestampexpired + " \ " + timestampexpired.toString());

INFO [http-8080-2] (ecsystem.java:233) - 2013-11-22 15:34:55.0 \ 2013-11-22 15:34:55.0

Hope all this information helps!

Have a look at this [post][1]. That should answer your question. [1]: http://stackoverflow.com/questions/5265000/using-a-java-sql-timestamp-object-in-an-sql-query — benjamin.d, Nov 20 '13 at 16:04
I suppose it should be `cal.add(Calendar.HOUR, ...`. And never assemble SQL this way - use a PreparedStatement and let it do the formatting work. — Gyro Gearless, Nov 20 '13 at 16:10

score 2 · Accepted Answer · answered Nov 20 '13 at 16:11

You should never use String concatenation to add dynamic parameters to a query, for the follwoing reasons:

the formats used for dates, times, etc. vary from database to database, end even locale to locale. And the toString() representation of the Java types don't necessarily match with those expected by the database
as soon as you have a single quote or a line break in a string, the query will become invalid
some types (like byte arrays, etc.) don't have any string representation
this opens your application to SQL injection attacks.

You should thus use prepared statements:

String sql =
    "INSERT INTO EC_TABLE" +
    "(licenseid, customername, description, servername,licensetype, username,password, createdDateTime,ExpiredDateTime)" +
    " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
PreparedStatement stmt = connection.prepareStatement(sql);
stmt.setString(1, licenseId);
...
stmt.setTimestamp(9, timestampexpired);

so i just put a question mark in the values and use the stmt below or swap the ? for 1 2 3 4 5 etc? — Understanding-Tech, Nov 20 '13 at 16:23
Yes. You might also click the link I posted to the official Java tutorial and take 10 minutes to read and fully understand the concept and usage of prepared statements. — JB Nizet, Nov 20 '13 at 16:24
freaking genious!, worked perfectly and i had a read about the security issues you mention and i shall go through the link to get a better understanding of it all. Thank you so much for the information. — Understanding-Tech, Nov 20 '13 at 16:28

score 0 · Answer 2 · answered Nov 20 '13 at 16:10

0

You can use following while inserting

to_date('"+timestampcurrent+"', 'YYYY-DD-MM HH:MI:SS')

and similarly for timestampexpired, this should fix this problem.

answered Nov 20 '13 at 16:10

sumitsabhnani

320
1
6

" ORA-00947: not enough values" error and the info statement returns to_date('2013-11-20 16:14:55.0', 'YYYY-DD-MM HH:MI:SS'),to_date('2013-11-22 16:14:55.0', 'YYYY-DD-MM HH:MI:SS') is it still meant to have to the yyyy-dd-mm their ? – Understanding-Tech Nov 20 '13 at 16:21

Java Calendar/Date to SQL Timestamp / Oracle Database

2 Answers2