Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Date Format Error java.sql.SQLException: Invalid column type

I am displaying date in JSF using pattern="dd-MMM-yyyy".

When I am trying to insert/update date values into my oracle DB, I am getting

java.sql.SQLException: Invalid column type

because my date format before insert or update is in this format

Wed Feb 09 00:00:00 AST 2011

How can I correctly insert or update my date values to Oracle Db and what is the best approach for doing this?

Update 1

My db insert code.

private void editSchedule(Schedule schedule)
        Object[] values = { schedule.getStartDate(),
                schedule.getVacationId() };             
        Connection connection = null;       
        PreparedStatement preparedStatement = null; 
        try {                           
            connection = datacon.getConnection();               
            preparedStatement = prepareStatement(connection, SQL_EDIT, values);         
            preparedStatement.executeUpdate();          

        } catch (Exception e) {
            logger.info("errro "+e.getMessage());
            e.printStackTrace();
        } finally {
            // TODO: handle exception
            close(connection, preparedStatement);
        }

    }

PreparedStaement code part

public static PreparedStatement prepareStatement
        (Connection connection, String sql, Object... values)
            throws SQLException
    {
        PreparedStatement preparedStatement = connection.prepareStatement(sql
            );
        setValues(preparedStatement, values);
        return preparedStatement;
    }

    public static void setValues(PreparedStatement preparedStatement, Object... values)
        throws SQLException
    {
        for (int i = 0; i < values.length; i++) {
            preparedStatement.setObject(i + 1, values[i]);
            logger.info("sql  "+Arrays.asList(values));
        }
    }
like image 336
Jacob Avatar asked Mar 03 '12 10:03

Jacob


People also ask

How to fix java sql SQLException Invalid column index?

Description. Main reason of SQLException:Invalid Column Index is either you are trying to read an invalid column index from result set returned by Query or you are setting parameter on parametric prepared Statement on invalid index using getString or setString method.

What does invalid column index mean?

The most common cause of "java. sql. SQLException: Invalid column index" is a misconception that column index started with "0" like array or String index but that's not true instead column index starts with "1" so whenever you try to get or Set column data with column index "0" you will get "java. sql.


2 Answers

JDBC only understands java.sql.Date, java.sql.Time and java.sql.Timestamp as SQL column types, not java.util.Date.

You need to change

    Object[] values = { schedule.getStartDate(),
            schedule.getVacationId() };             

by

    Object[] values = { new java.sql.Date(schedule.getStartDate().getTime()),
            schedule.getVacationId() };             

Then it'll work. Just keep using java.util.Date in your model. JSF in turn doesn't understand java.sql.Date.

like image 168
BalusC Avatar answered Nov 15 '22 00:11

BalusC


It sounds like you're trying to include the data as text when you're inserting/updating. Don't do that - use a java.sql.Date in a PreparedStatement. Introducing unnecessary string conversions is a really bad idea - it makes your code very brittle, and makes the code more confusijng: keep your data in an appropriate data type as long as you possibly can.

like image 33
Jon Skeet Avatar answered Nov 15 '22 00:11

Jon Skeet