Convert String into TIMESTAMP for insert into Database oracle from servlet java

Question

I'm trying to insert a date into a TIMESTAMP field in an Oracle database from a servlet. but when I do the insert, I get an error: "ORA-01843: month not valid"

The type of date I see in the table where the date is is this: 05-FEB-19 09.36.10.000000000 AM and I declared it when the table was created as a TIMESTAMP type.

In JSP file i have this 2 input type:

<label>Data
<input type="date" name="date" value="13-AUG-2019" required>
</label>
<label>Time
<input type="time" name="time" value="11:00:00 AM" required>
</label>

In a Servlet Java i have this:

    String data= request.getParameter("date");
    String time= request.getParameter("time");
    String paramData = data.replaceAll("\n", "");
    String paramTime = time.replaceAll("\n", "");
    String dataTime = paramData + " " + paramTime;

    try{
        Class.forName("oracle.jdbc.driver.OracleDriver");
        Connection connCf = DriverManager.getConnection(url, user, pass);

        PreparedStatement pstmtCf=connCf.prepareStatement("insert into screening_tab (film_oid, room_oid, data_hour) values((select ref(fi) from film_tab fi where fi.title=?), (select ref(ro) from room_tab ro where ro.code=?), ?)");

        pstmtCf.setString(1, paramFilm);
        pstmtCf.setString(2, paramRoom);
        String paramDataTime= paramData + " " + paramTime;
        pstmtCf.setString(3, paramDataTime);

        Boolean result= pstmtCf.execute();

how can I solve this problem?

I Have tried this solution in Oracle 10 that given a string from servlet returns timestamp but the problem is the same:

create or replace procedure Sdata (datatime VarCHAR2) is
date_ho TIMESTAMP;
begin
SELECT TO_TIMESTAMP (datatime, 'DD-MON-RR HH.MI.SSXFF AM') into date_ho  FROM DUAL;
dbms_output.enable;
dbms_output.put_line(date_ho);
end sdata;

Between the various attempts and trials, i can not explain how, if I do something like this in Oracle SQL Developer everything works correctly!:

insert into screening_tab (film_oid, room_oid, data_hour) values((select ref(fi) from film_tab fi where fi.title='FilmNumero601'), (select ref(ro) from room_tab ro where ro.code=301), '14-DEC-19 07.00.00.00 PM' );

when I run the exact same query from the servlet I always get the error on the invalid month date

I have tried also this solution: I have created a new Servlet and JSP file where this input for data is:

<h4>Select Data: </h4><br></br>
                                <input type="datetime-local"  name="date" arequired placeholder="Date">
                                <br></br>
                                <input type="reset"  value="Resetta la form"></input>
                                <input type="submit" value="Invia"></input>

In the corresponding servlet i have this:

PreparedStatement pstmtCf=connCf.prepareStatement("insert into screening_tab (film_oid, room_oid, data_hour) values((select ref(fi) from film_tab fi where fi.title=?), (select ref(ro) from room_tab ro where ro.code=?), ?)");



            pstmtCf.setString(1, film);
            pstmtCf.setString(2, room);
            String date= getCorrectFormat(request.getParameter("date")+":00");
            pstmtCf.setString(3, date );


            pstmtCf.execute();



            pstmtCf.close();
            connCf.close();

Where getCorrectFormat() is:

public static String getCorrectFormat(String date){
        String out="";
        out += (date.substring(8,10))+ "-";
        switch(date.substring(5,7)){
        case "01":
            out += "JAN-";
            break;
        case "02":
            out += "FEB-";
            break;
        case "03":
            out += "MAR-";
            break;
        case "04":
            out += "APR-";
            break;
        case "05":
            out += "MAY-";
            break;
        case "06":
            out += "JUN-";
            break;
        case "07":
            out += "JUL-";
            break;
        case "08":
            out += "AUG-";
            break;  
        case "09":
            out += "SEP-";
            break;
        case "10":
            out += "OCT-";
            break;
        case "11":
            out += "NOV-";
            break;
        case "12":
            out += "DEC-";
            break;
        }
        out += (date.substring(0,4))+ " ";
        int hour =Integer.parseInt(date.substring(11,13))%12;
        if(hour == 0)
            out += String.valueOf(12);
        else
            out += String.valueOf(hour);
        out += (date.substring(13));
        if(Integer.parseInt(date.substring(11,13))<12)
            out += " AM";
        else
            out += " PM";
        return out;
    }

The error is always the same: "ORA-01843:not a valid month"

Just to nitpick, I think you need to *convert* from string to timestamp, not *cast*. (Though now that I look for a definition to back this up, I can't find one. But it seems to me that converting implies a more radical restructuring than casting.) — William Robertson, Feb 10 '19 at 13:17

Basil Bourque · Answer 1 · 2019-02-12T23:10:32.493

Use objects, not strings.

As of JDBC 4.2, we can directly exchange java.time objects with the database via PreparedStatement::setObject & ResultSet::getObject methods. In your prepared statement, use ? placeholders as seen in Oracle Tutorial.

LocalDate ld = LocalDate.of( 2019 , 8 , 13 ) ;
myPreparedStatement.setObject( … , ld ) ;

And…

LocalTime lt = LocalTime.of( 11 , 0 ) ;
myPreparedStatement.setObject( … , lt ) ;

Retrieval.

LocalDate ld = myResultSet.getObject( … , LocalDate.class ) ;
LocalDate lt = myResultSet.getObject( … , LocalTime.class ) ;

About java.time

The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes.

Where to obtain the java.time classes?

Java SE 8, Java SE 9, Java SE 10, Java SE 11, and later - Part of the standard Java API with a bundled implementation.
- Java 9 adds some minor features and fixes.
Java SE 6 and Java SE 7
- Most of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport.
Android
- Later versions of Android bundle implementations of the java.time classes.
- For earlier Android (<26), the ThreeTenABP project adapts ThreeTen-Backport (mentioned above). See How to use ThreeTenABP….

The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.

I follow what is written, it would give me a date value not suitable for my Oracle database. I tried different solutions such as: 1) i have created a function that give a string from servlet returns a timestamp: create or replace procedure Sdata (datatime VarCHAR2) is date_ho TIMESTAMP; begin SELECT TO_TIMESTAMP (datatime, 'DD-MON-RR HH.MI.SSXFF AM') into date_ho FROM DUAL; dbms_output.enable; dbms_output.put_line(date_ho); end sdata; — Marco, Feb 11 '19 at 10:31
@Marco As my first heading says, use objects, not strings. You have so much going on in your Question and Comment that I cannot see your actual goal/problem. I suggest you search Stack Overflow to do some more learning about the *java.time* objects, and how to call `PreparedStatement::setObject` and `ResultSet::getObject`. This has all been covered many many many times already on Stack Overflow. Currently you seem to be working much too hard, and all in the wrong direction, grappling with string manipulation instead of learning to use the industry-leading date-time framework (*java.time*). — Basil Bourque, Feb 12 '19 at 23:02

score 1 · Answer 2 · answered Feb 08 '19 at 20:27

1

You're passing in a String/varchar2, and the implicit conversion to timestamp is failing because your date/time format doesn't match your Oracle default date/time format.

The easiest option is to pass a format string so Oracle knows how to do the conversion. Instead of passing ? for the timestamp, you could do to_timestamp(?, 'DD-MON-YYYY HH:MI:SS AM')

If you already had a Java Timestamp, I think you'd want to use setTimestamp() - see this question.

answered Feb 08 '19 at 20:27

kfinity

8,581
1
13
20

1

@Marco It’s often easier for us to help you if report *in the question* what you have tried and in what way it failed. Thx. – Ole V.V. Feb 10 '19 at 11:04
1

I have update my question with the solutions I have implemented but have presented the same problem – Marco Feb 11 '19 at 11:02

Convert String into TIMESTAMP for insert into Database oracle from servlet java

2 Answers2

Use objects, not strings.

About java.time