CODEWITHSUNDEEP
javaoracle-databasejspjdbctimestamp
Marco
Marco

Reputation: 77

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

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"

Upvotes: 1

Views: 1629

Answers (2)

Basil Bourque
Basil Bourque

Reputation: 339342

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?

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.

Upvotes: 2

kfinity
kfinity

Reputation: 9091

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.

Upvotes: 1

Related Questions