Antrophy
Antrophy

Reputation: 29

How to enter timestamp format from java to oracle database that also has format timestamp?

I have this table in SQL Oracle database

MESSAGES
--------
Name           Null?    Type           
-------------- -------- -------------- 
ID_MESSAGE      NOT NULL NUMBER(38)     
CONTENT          NOT NULL NVARCHAR2(500) 
TIME_STAMP NOT NULL TIMESTAMP(6)  

And I'm trying to insert a data into this table but I'm having a problem with inserting a timestamp.

I've generated a timestamp in Java and executed the following query:

Timestamp timeStamp = new Timestamp(System.currentTimeMillis());


String query = "INSERT INTO messages(content, time_stamp) VALUES ('"+textAreaSendMessage.getText()+"', "+timeStamp+")";

st.executeQuery(query);

And I'm getting this error:

Caused by: java.sql.SQLSyntaxErrorException: ORA-00917: missing comma

How to fix this so it inserts timestamp into a database?

Upvotes: 0

Views: 325

Answers (2)

Basil Bourque
Basil Bourque

Reputation: 340350

Use DEFAULT when defining your table to call a function that captures the current moment. This function will automatically run every time you INSERT a new row. Then no need for any Java for this purpose.

If you do want to do something like this in Java, do not use java.time.Timestamp. That class was supplanted years ago by the java.time classes.

OffsetDateTime odt = OffsetDateTime.now( ZoneOffset.UTC ) ;

Build your SQL string using ? placeholders. Do not wrangle with string manipulations as seen in your code sample.

Pass the OffsetDateTime object to fill in for the ? placeholder using a PreparedStatement object.

myPreparedStatement.setObject( … , odt ) ;

Table of date-time types in Java (both modern and legacy) and in standard SQL.

Upvotes: 2

Antrophy
Antrophy

Reputation: 29

I found out that I don't even have to create timestamp in java, just add CURRENT_TIMESTAMP to the place of timestamp into the query where Timestamp should be.

Upvotes: 0

Related Questions