Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

java.sql.Timestamp to Oracle date

 
John Tyll
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,
I am new to java, I am selecting data from Oracle that is of type Date in Oracle but in java is java.sql.Timestamp format (2003-03-10 16:13:19.0) it is my rsltset.getTimestamp(3) variable, and I need to insert this into another Oracle table. When I am trying to insert this into an Oracle date column I get a Oracle 1861 error. (Granted I have other data I am inserting but it looks like this is the column causing the error). What's the easy way to fix this problem!!!
 
Gabriel White
Ranch Hand
Posts: 233
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I need to see your insert statement, please use code tags. Normally you would use a getDate in your insert, but I need to see the code that is generating the error.
Peace out
 
John Tyll
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This is the insert, need more info?
toAgent.executeUpdate("insert into EG_VENDOR (VENDOR_NUMBER, CREATION_DATE, INTERFACE_DATE,
INTERFACE_STATUS, VENDOR_NAME, VENDOR_TYPE_LOOKUP_CODE, VENDOR_END_DATE_ACTIVE, VENDOR_SITE_ID,
VENDOR_SITE_CODE, VENDOR_SITE_END_DATE_ACTIVE, ADDRESS_LINE1, ADDRESS_LINE2, ADDRESS_LINE3, CITY,
STATE, ZIP, AREA_CODE, PHONE, FAX_AREA_CODE, FAX_PHONE, VENDOR_CONTACTS, LAST_UPDATED_BY) values ( '"+
rs.getString(2) +"', '"+ rs.getTimestamp(3) +"', '"+ rs.getTimestamp(4) +"', '"+ rs.getString(5) +"',
'"+ rs.getString(6) +"', '"+ rs.getString(7) +"', '"+ rs.getTimestamp(8) +"', "+ rs.getInt(9) +", '"+
rs.getString(10) + "', '"+ rs.getTimestamp(11) +"', '"+ rs.getString(12) + "', '"+ rs.getString(13) +
"', '"+ rs.getString(14) + "', '"+ rs.getString(15) + "', '"+ rs.getString(16) + "', '"+
rs.getString(17) + "', '"+ rs.getString(18) + "', '"+ rs.getString(19) + "', '"+ rs.getString(20) + "',
'"+ rs.getString(21) + "', '"+ rs.getString(22) + "', '"+ rs.getString(23) + "')");

Thanks
 
John Tyll
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

sorry about the code tag...
 
Gabriel White
Ranch Hand
Posts: 233
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Error: ORA 1861 Text: literal does not match format string.
This means that your literal (rs.timestamp) is not mapped to a format string. You need to include this date format in your insert statement by using the TO_DATE function. (This function will convert the string into the date)
For example:

Peace out bro
 
John Tyll
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This is probably very easy but I keep getting the error "unclosed character literal". Given my code above how should I format your solution in quotes.
Thanks
 
Gabriel White
Ranch Hand
Posts: 233
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
John, I need to see how you added the new format in your insert statement. Please use the code tags.
Thanks bro
 
John Tyll
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sorry, I appreciate the help I had a long day of meetings...
toAgent.executeUpdate("insert into EG_VENDOR (VENDOR_NUMBER, CREATION_DATE, INTERFACE_DATE,INTERFACE_STATUS, VENDOR_NAME, VENDOR_TYPE_LOOKUP_CODE, VENDOR_END_DATE_ACTIVE, VENDOR_SITE_ID,VENDOR_SITE_CODE, VENDOR_SITE_END_DATE_ACTIVE, ADDRESS_LINE1, ADDRESS_LINE2, ADDRESS_LINE3, CITY,STATE, ZIP, AREA_CODE, PHONE, FAX_AREA_CODE, FAX_PHONE, VENDOR_CONTACTS, LAST_UPDATED_BY) values ( '"+rs.getString(2) +"', "+TO_DATE('"+ rs.getTimestamp(3) +"', 'DD-MON-YYYY HH24 :MI :SS') +", "+TO_DATE('"+ rs.getTimestamp(4) +"', 'DD-MON-YYYY HH24 :MI :SS') +", '"+ rs.getString(5) +"','"+ rs.getString(6) +"', '"+ rs.getString(7) +"', "+TO_DATE('"+ rs.getTimestamp(8) +"', 'DD-MON-YYYY HH24 :MI :SS') +", "+ rs.getInt(9) +", '"+rs.getString(10) + "', "+TO_DATE('"+ rs.getTimestamp(11) +"', 'DD-MON-YYYY HH24 :MI :SS') +", '"+ rs.getString(12) + "', '"+ rs.getString(13) +"', '"+ rs.getString(14) + "', '"+ rs.getString(15) + "', '"+ rs.getString(16) + "', '"+rs.getString(17) + "', '"+ rs.getString(18) + "', '"+ rs.getString(19) + "', '"+ rs.getString(20) + "','"+ rs.getString(21) + "', '"+ rs.getString(22) + "', '"+ rs.getString(23) + "')");

[ removed the code tag surrounding the big long unbroken line of code -ds ]
[ March 14, 2003: Message edited by: Dirk Schreckmann ]
 
Gabriel White
Ranch Hand
Posts: 233
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ok John, this is what I need from you:
The error code number (if there is one)
and
what the getTimestamp format physically looks like.
I was just giving you an example before, you weren't supposed to cut and paste it right into your code because your date format may be different. The example I gave you has to match the format of the getTimestamp (what format is it asking for)
Let me know bro
Peace out
 
John Tyll
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Alright, I am basically selecting from one database and doing some stuff then inserting the exact information from the select to a new different database.
The date fields were giving me trouble, the date values look like this 2003-03-10 16:13:19.0. Here is my recent error message: The ^ is first located under the ' in '"+ rs.getTimestamp(3), then 11 more times throughout the code...
Compiler errors.
C:\EGATE\Client\bin\temp\15316\cr_Vendor_Transport.java:83: unclosed character literal
toAgent.executeUpdate("insert into EG_VENDOR (VENDOR_NUMBER, CREATION_DATE, INTERFACE_DATE, INTERFACE_STATUS, VENDOR_NAME, VENDOR_TYPE_LOOKUP_CODE, VENDOR_END_DATE_ACTIVE, VENDOR_SITE_ID, VENDOR_SITE_CODE, VENDOR_SITE_END_DATE_ACTIVE, ADDRESS_LINE1, ADDRESS_LINE2, ADDRESS_LINE3, CITY, STATE, ZIP, AREA_CODE, PHONE, FAX_AREA_CODE, FAX_PHONE, VENDOR_CONTACTS, LAST_UPDATED_BY) values ( '"+rs.getString(2) +"', '"+ to_date('"+ rs.getTimestamp(3) +"','YYYY-MM-DD HH24:MI:SS') +"', '"+ to_date('"+ rs.getTimestamp(4) +"','YYYY-MM-DD HH24:MI:SS') +"', '"+ rs.getString(5) +"','"+ rs.getString(6) +"', '"+ rs.getString(7) +"', '"+ to_date('"+ rs.getTimestamp(8) +"','YYYY-MM-DD HH24:MI:SS') +"', "+ rs.getInt(9) +", '"+rs.getString(10) + "', '"+ to_date('"+ rs.getTimestamp(11) +"','YYYY-MM-DD HH24:MI:SS') +"', '"+ rs.getString(12) + "', '"+ rs.getString(13) + "', '"+ rs.getString(14) + "', '"+ rs.getString(15) + "', '"+ rs.getString(16) + "', '"+ rs.getString(17) + "', '"+ rs.getString(18) + "', '"+ rs.getString(19) + "', '"+ rs.getString(20) + "','"+ rs.getString(21) + "', '"+ rs.getString(22) + "', '"+ rs.getString(23) + "')");
^
12 errors
Compile Failed.
 
Dirk Schreckmann
Sheriff
Posts: 7023
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
All of those double quotes are maybe breaking apart your String literal unintentionally (so you should escape them). It's very difficult to help you if you don't post a more complete code example that replicates your problem. Please be sure to not have big long unbroken lines inside the code tags - scrolling left and right is a pain.
[ March 17, 2003: Message edited by: Dirk Schreckmann ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic