Db2 Sql Error Sqlcode=-181 Sqlstate=22007
Contents |
columns effective_date[DATE] and end_date[DATE - nullable] Here, end_date values are all null. I am trying to set effective_date value to end_date for each row, but I am getting the db2 sql error sqlcode 204 sqlstate 42704 error mentioned below (in red) Here are the list of things I have tried
Db2 Sql Error Sqlcode 302 Sqlstate 22001
1) Directly assigning effective_date value to end_date in UPDATE step 2) Trying java script step to create new variables like var
Db2 Sql Error Sqlcode 803 Sqlstate 23505
TEST_DATE='2009/01/01'; var TEST_DATE2= new Date(); and then use SELECT VAULES step to convert it into desired date type and format yyyy-MM-dd Not sure what I am misssing. Any idea? [SQLCODE=-181: THE STRING REPRESENTATION OF A http://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/codes/src/tpc/n181.html DATETIME VALUE IS NOT A VALID DATETIME VALUE] Error inserting/updating row DB2 SQL Error: SQLCODE=-181, SQLSTATE=22007, SQLERRMC=null, DRIVER=3.62.56 at org.pentaho.di.core.database.Database.insertRow(Database.java:1411) at org.pentaho.di.trans.steps.update.Update.lookupValues(Update.java:196) at org.pentaho.di.trans.steps.update.Update.processRow(Update.java:327) at org.pentaho.di.trans.step.RunThread.run(RunThread.java:50) at java.lang.Thread.run(Unknown Source) Caused by: com.ibm.db2.jcc.am.SqlDataException: DB2 SQL Error: SQLCODE=-181, SQLSTATE=22007, SQLERRMC=null, DRIVER=3.62.56 at com.ibm.db2.jcc.am.fd.a(fd.java:668) at com.ibm.db2.jcc.am.fd.a(fd.java:60) at com.ibm.db2.jcc.am.fd.a(fd.java:127) gutlez01-25-2013, 12:19 PMWhy are you converting the date to string and then asking DB2 to convert it back to a date? Leave it as a date! http://forums.pentaho.com/archive/index.php/t-136376.html omm01-25-2013, 01:16 PMWhy are you converting the date to string and then asking DB2 to convert it back to a date? Leave it as a date! Bacause, it is not working the normal way. First, I have tried fetching the Effective date and assigning it to the end date directly. It was not working. Later, I felt like kettle was converting the Date into time stamp field, So I have tried using Select Values step to format it to just Date format. Even that was not working, So, I have tried taking a string value and then convert it into date. Anyways, nothing seems to be working gutlez01-25-2013, 01:20 PMAbout the only thing that I can suggest at this point is to post your transform, and we'll work through it. Perhaps you could make a copy of the effective_date (as a DATE. Don't convert it to string!) with a select values step to end_date, then send it to the Update step. omm01-25-2013, 02:01 PMAbout the only thing that I can suggest at this point is to post your transform, and we'll work through it. Perhaps you could make a copy of the effective_date (as a DATE. Don't convert it to string!) with a select values step to end_date, then send
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring http://stackoverflow.com/questions/13772210/hibernate-query-causing-error-181-on-db2-for-dates developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Hibernate query causing error 181 on DB2 for Dates up vote 1 down vote favorite I am using Hibernate 4 on DB2 v9 with JPA 2's CriteriaBuilder to build a query on db2 sql the fly that can filter by date. The relevant part of the predicate is... criteriaBuilder.greaterThan(myRoot.get(MyEntity_.myDate), aDateValue) My mapping is pretty straight forward... @Column(name="MY_DATE") @Temporal(value = TemporalType.DATE) public Date getMyDate() { return myDate; } The column on the table is of type DATE. The error I get when I run a query with this restriction is... org.hibernate.exception.DataException: DB2 SQL Error: SQLCODE=-181, SQLSTATE=22007 If I take the generated SQL and run it in a stand alone SQL editor, it runs fines. db2 sql error I have tried changing to TemporalType.TIMESTAMP with no luck. I have double-checked that the bind input is valid at runtime as well. hibernate jpa db2 share|improve this question asked Dec 7 '12 at 22:44 Andrew White 31.8k974114 add a comment| 2 Answers 2 active oldest votes up vote 2 down vote accepted Turns out that if aDateValue is of type java.sql.Date rather than java.util.Date the error goes away. share|improve this answer answered Dec 8 '12 at 0:47 Andrew White 31.8k974114 add a comment| up vote 0 down vote Another option that worked for me was to add @Type(type = "date") to the java.util.Date field in the entity. It seems that Hibernate maps the type java.util.Date with a Sql Timestamp while when the Type annotation is added it is maped as Date share|improve this answer answered May 9 '14 at 18:31 jesantana 175115 add a comment| Your Answer draft saved draft discarded Sign up or log in Sign up using Google Sign up using Facebook Sign up using Email and Password Post as a guest Name Email Post as a guest Name Email discard By posting your answer, you agree to the privacy policy and terms of service. Not the answer you're looking for? Browse other questions tagged hibernate jpa db2 or ask your own question. asked 3 years ago viewed 1595 times active 2 years ago Related 3Mapping a table called “group” in