Re: Entity With Compound PK And CLOB Field
Re: Entity With Compound PK And CLOB Field
- Subject: Re: Entity With Compound PK And CLOB Field
- From: Mike Schrag <email@hidden>
- Date: Wed, 25 May 2011 07:14:19 -0400
Out of curiosity ..... Any chance the external types of those other locking attributes are not in all caps? "varchar2" vs "VARCHAR2"?
Sent from my iPhone
On May 25, 2011, at 1:07 AM, Chuck Hill <email@hidden> wrote:
> Hi Michael,
>
> On May 24, 2011, at 8:43 PM, Michael Hast wrote:
>
>> Hi:
>>
>> We had an interesting problem today. We have an entity called ReviewerData which has a personId and fiscalYear compound primary key. It also has 3 more attributes, a CLOB field, a varchar2(4000) and an Integer attribute. All attributes except the CLOB are marked as a locking attribute (see attached image).
>>
>> When we are updating the CLOB field and any of the other 2 attributes and call EC saveChanges, we are getting the error:
>>
>> Expected a LOB count of 0 but could not fetch that many objects.
>>
>> The issues is that there are 2 SQL statements being generated by the Oracle plugin within 1 transaction:
>>
>> UPDATE REVIEWER_DATA SET HAS_DONE_EXTERNAL_REVIEW = ?, REVIEW_QUALIFICATIONS = EMPTY_CLOB() WHERE (PERSON_ID = ? AND FISCAL_YEAR = ? AND DESCRIPTION_OF_WORK = ? AND HAS_DONE_EXTERNAL_REVIEW = ?) withBindings: 1:0, 2:97208, 3:2012, 4:"ABC", 5:1
>>
>> SELECT t0.REVIEW_QUALIFICATIONS FROM REVIEWER_DATA t0 WHERE (t0.PERSON_ID = ? AND t0.FISCAL_YEAR = ? AND t0.DESCRIPTION_OF_WORK = ? AND t0.HAS_DONE_EXTERNAL_REVIEW = ?) FOR UPDATE withBindings: 1:97208, 2:2012, 3:"ABC", 4:1
>
> When you see "SELECT ... FOR UPDATE" that is often a sign that something has gone wrong in EOF (e.g. you have hit a bug). In this case, however, I think it is just trying to update the CLOB.
>
>>
>> The SELECT statement fails, causing the error above. If I don't have a compound PK, only have a single PK attribute (personId), the SELECT statement only has one column in the where clause and all works great:
>>
>> SELECT t0.REVIEW_QUALIFICATIONS FROM REVIEWER_DATA t0 WHERE (t0.PERSON_ID = ?) FOR UPDATE withBindings: 1:97208
>>
>> I believe there is a bug in how the SQL is being generated. In the where clause it should only use the 2 PK attributes rather than all the lock attributes.
>>
>> My question is how can I fix this? Is it the Oracle plugin? Wonder has an Oracle plugin, should I try that out first?
>
>
> I would try that first. Second, you could move REVIEW_QUALIFICATIONS to its own table with a generated PK and a 1-1 relationship from Reviewer Data. There is a good chance that would avoid the problem.
>
>
> Chuck
>
> --
> Chuck Hill Senior Consultant / VP Development
>
> Come to WOWODC this July for unparalleled WO learning opportunities and real peer to peer problem solving! Network, socialize, and enjoy a great cosmopolitan city. See you there! http://www.wocommunity.org/wowodc11/
>
> _______________________________________________
> Do not post admin requests to the list. They will be ignored.
> Webobjects-dev mailing list (email@hidden)
> Help/Unsubscribe/Update your Subscription:
>
> This email sent to email@hidden
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden