sql exception: inconsistent datatypes: expected TIMESTAMP got NUMBER

Question

i have created a coloumn in sql database with timestamp database. and from java i am trying to run insert query given below

st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
st.executeUpdate("insert into profmessage values('" + uid + "','" + from + "','" + to + "','" + msg + "','" + dt + "',1,0,'" + new java.sql.Timestamp(new Date().getTime()) + "')");
st.close();

It is showing

sqlexception  inconsistent datatypes: expected TIMESTAMP got NUMBER

First thing to first: don't put values into SQL like that. Use a prepared statement. Next, specify the columns in your insert statement, to avoid it being schema-order-specific. Fix both of those issues, then see if it still doesn't work. — Jon Skeet
– Jon Skeet, Commented Jul 3, 2014 at 8:25
your issue must be related to columns ordering, use column name in insert statement also as suggested by Jon you should use prepared statement — coreJavare
– coreJavare, Commented Jul 3, 2014 at 8:27
@coreJavare u r right. I was wrong woth coloumn order. Nut now it is giving exception as ORA-01843: not a valid month — swapnil gandhi
– swapnil gandhi, Commented Jul 3, 2014 at 8:32

Tosz · Accepted Answer · 2014-07-03 08:54:42Z

2

I think you should use PreparedStatement (which is also good for avoiding SQL injections), and insert the value of timestamp like this:

preparedStatement.setTimestamp(theNumberOfTimestampOccurenceInYourQery, new java.sql.Timestamp(new Date().getTime()));

answered Jul 3, 2014 at 8:54

Tosz

3081 gold badge5 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

João Mendes · Accepted Answer · 2014-07-03 08:37:45Z

0

In SQL Server, the TIMESTAMP data type doesn't mean what you think it means. TIMESTAMP columns are meant to be updated by the server itself as it creates and updates rows. Their values look like "0x00000000001CF9F9", which is really just a number in hexadecimal format.

What you need is the DATETIME data type.

answered Jul 3, 2014 at 8:37

João Mendes

1,45516 silver badges36 bronze badges

2 Comments

Anthony Grist Over a year ago

The ORA-01843 error code mentioned in the comments suggests they're not using SQL Server.

João Mendes Over a year ago

This is Oracle, not SQL Server... Yeah, never mind my answer, it's not applicable. I'll leave it up in case someone else googles the problem, but in the future, I would use better question tagging!

Collectives™ on Stack Overflow

sql exception: inconsistent datatypes: expected TIMESTAMP got NUMBER

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related