This week's book giveaway is in the OCAJP 8 forum. We're giving away four copies of OCA Java SE 8 Programmer I Study Guide and have Edward Finegan & Robert Liguori on-line! See this thread for details.
Here is a good challenge. I have a program which needs to update a column - DATE type in Oracle, datetime type in SQL Server. I currently am able to get the newDate, with proper value, from this piece of code: SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); long time= System.currentTimeMillis(); java.sql.Date systemsqldate=new java.sql.Date(time); java.util.Date dateString=(java.util.Date)systemsqldate; String newDate = null; newDate = sdf.format(dateString); to look fine in SQL Server. In Oracle I get the infamous ORA-01861: literal does not match format string. I really want this program to function by just switching drivers and connection statements. I do not want to parse actual code based upon the DB. Just for additional color, It will also need to work with DB2. Any great minds out there that have successfully faced this one? [ March 18, 2004: Message edited by: Carty Ellis ]I am using PreparedStatement to avoid DB sensitive issues - but it isn't working that way. [ March 18, 2004: Message edited by: Carty Ellis ]
You should use a java.sql.Date type rather than trying to second-guess the date format for a particular DB. (Are you using it as a Date object or a String? You don't include the code you are trying to execute against the DB so its hard to tell) If you have to support this across three flavours of DB, why not try a persistance layer like Hibernate? [ March 18, 2004: Message edited by: eammon bannon ]
Joined: Oct 04, 2003
I am using a string for the date. Here is the statement using newDate: PreparedStatement statementInsertDate = conn.prepareStatement( "INSERT INTO tca_master " + "(instidq, instid, instcd, source_id," + "source_cd, dpmask, fyt, lyt, iname," + "univ_dpmask, dinstid, dinstcd, tastatus," + "last_mod_user, last_mod_date)" + "VALUES " + "('73', 'MIAMI', 'CAE'," + "'002463 ',' '," + "' ', '200402', '200206', 'ELOM UNIVERSITY'," + "' ', ' ', ' ', ' '," + "'CAE', '" + newDate + "')"); I am sure the approach is incorrect - I looked at NLS-DATE-FORMAT in the Oracle DB (we have no input on how that is set) and on that DB it is DD-Mon-YY so the approach I was considering is doomed to failure. "Hibernate" is a new word to me. Other than Google searches - can you suggest a good direction to learn the concept? (It's not mentioned in "Head First Java")
www.hibernate.org - its a proprietary ORM persistance layer that you can delegate this sort of cross DB platform work. It lets you manipulate relational data as Objects. In this instance with Hibernate, you'd give your PreparedStatement a java.util.Date object, and Hibernate will format it correctly for each DB you are using. (BTW: watch out for Gowrishankar's suggestion - DB2 doesn't have a to_date function) [ March 18, 2004: Message edited by: eammon bannon ]