Re: Index problems
Re: Index problems
- Subject: Re: Index problems
- From: Calven Eggert <email@hidden>
- Date: Wed, 8 Dec 2010 13:41:24 -0500
I still don't follow what you are saying. actually, I want to insert two records at a time. I have a page that allows the user to insert records, but I don't actually do the insert until the user clicks on the save button. that's when I do the saving/inserting into the database. but then the sequence numbers are incorrect for the second one.
what is the other insert you are talking about?
Still confused on what you are suggesting I do...
On 2010-12-08, at 1:24 PM, Chuck Hill wrote:
> On Dec 8, 2010, at 10:20 AM, Calven Eggert wrote:
>
>> the second record is valid. I am indeed wanting to insert two records. so why would the retrieving of the next sequence number return me a number not in sequence?
>
> It is not. I am pretty sure that it is doing what I said. There is another (later) insert that you don't see due to the DB error.
>
>
> Chuck
>
>
>
>> On 2010-12-08, at 12:24 PM, Chuck Hill wrote:
>>
>>>
>>> On Dec 8, 2010, at 8:28 AM, Calven Eggert wrote:
>>>
>>>> Recently a WO app has been getting the unique constraint error when creating a record. This application has been running for years and so I'm surprised at this 'just' showing up. here is the problem: you'll see in the following lines that an index is retrieved for jobs_notes_seq twice because I'm inserting two records. next you'll see the insert statements. look at the notesid field.
>>>>
>>>> [2010-12-8 10:54:32 EST] <WorkerThread1> === Begin Internal Transaction
>>>> [2010-12-8 10:54:32 EST] <WorkerThread1> evaluateExpression: <com.webobjects.jdbcadaptor.OraclePlugIn$OracleExpression: "SELECT JOBS_NOTES_SEQ.NEXTVAL FROM DUAL" withBindings: >
>>>> [2010-12-8 10:54:32 EST] <WorkerThread1> 1 row(s) processed
>>>> [2010-12-8 10:54:32 EST] <WorkerThread1> === Commit Internal Transaction
>>>> [2010-12-8 10:54:32 EST] <WorkerThread1> === Begin Internal Transaction
>>>> [2010-12-8 10:54:32 EST] <WorkerThread1> evaluateExpression: <com.webobjects.jdbcadaptor.OraclePlugIn$OracleExpression: "SELECT JOBS_NOTES_SEQ.NEXTVAL FROM DUAL" withBindings: >
>>>> [2010-12-8 10:54:32 EST] <WorkerThread1> 1 row(s) processed
>>>> [2010-12-8 10:54:32 EST] <WorkerThread1> === Commit Internal Transaction
>>>> [2010-12-8 10:54:32 EST] <WorkerThread1> === Begin Internal Transaction
>>>> [2010-12-8 10:54:32 EST] <WorkerThread1> evaluateExpression: <com.webobjects.jdbcadaptor.OraclePlugIn$OracleExpression: "INSERT INTO JOBS_NOTES(NOTES_SUBMITTED_BY, NOTES_HYPERLINK, NOTES_USER_VISIBLE, NOTES_JOB_ID, NOTES_SUBMITTED_DATE, NOTES_DESCRIPTION, NOTES_ID, NOTES_ATTACHMENT_NAME) VALUES (?, NULL, ?, ?, ?, ?, ?, ?)" withBindings: 1:204(submittedby), 2:"N"(uservisible), 3:292(jobid), 4:2010-12-08 10:54:17(submitteddate), 5:"gg"(notesdescription), 6:922(notesid), 7:"Overview.graffle"(attachmentname)>
>>>> [2010-12-8 10:54:32 EST] <WorkerThread1> evaluateExpression: <com.webobjects.jdbcadaptor.OraclePlugIn$OracleExpression: "INSERT INTO JOBS_NOTES(NOTES_SUBMITTED_BY, NOTES_HYPERLINK, NOTES_USER_VISIBLE, NOTES_JOB_ID, NOTES_SUBMITTED_DATE, NOTES_DESCRIPTION, NOTES_ID, NOTES_ATTACHMENT_NAME) VALUES (?, NULL, ?, ?, ?, ?, ?, ?)" withBindings: 1:204(submittedby), 2:"N"(uservisible), 3:292(jobid), 4:2010-12-08 10:54:23(submitteddate), 5:"hh"(notesdescription), 6:56(notesid), 7:"Overview.graffle"(attachmentname)>
>>>> [2010-12-8 10:54:33 EST] <WorkerThread1> === Rollback Internal Transaction
>>>>
>>>> see how one is 922 and another is 56? 922 is correct but the 56 should be 923.
>>>
>>> I don't think that is the problem. I think the problem is that the second was supposed to be an UPDATE or DELETE statement. This happens when you violate EOF commandments (not locking properly for example). EOF gets confused between one operation and another.
>>>
>>> Go forth and sin no more!
>>>
>>> Chuck
>>>
>>>
>>>> very strange. then when the records are committed, sometimes I get the dreaded error message:
>>>>
>>>> com.webobjects.eoaccess.EOGeneralAdaptorException: EvaluateExpression failed: <com.webobjects.jdbcadaptor.OraclePlugIn$OracleExpression: "INSERT INTO JOBS_NOTES(NOTES_SUBMITTED_BY, NOTES_HYPERLINK, NOTES_USER_VISIBLE, NOTES_JOB_ID, NOTES_SUBMITTED_DATE, NOTES_DESCRIPTION, NOTES_ID, NOTES_ATTACHMENT_NAME) VALUES (?, NULL, ?, ?, ?, ?, ?, ?)" withBindings: 1:204(submittedby), 2:"N"(uservisible), 3:292(jobid), 4:2010-12-08 10:54:23(submitteddate), 5:"hh"(notesdescription), 6:56(notesid), 7:"Overview.graffle"(attachmentname)>
>>>> Next exception:SQL State:23000 -- error code: 1 -- msg: ORA-00001: unique constraint (COREDEV3.SYS_C0031657) violated
>>>>
>>>> Obviously this is a problem because number 56 was already a record in the table. Sometimes, there is no error message and the record is not even created. And sometimes, when the index is correct the record is created successfully. argh!
>>>>
>>>> Anyone have this problem before or know what I can do to solve this mystery? (I've dropped the sequences and recreated them a couple of times. but it doesn't solve the problem)
>>>>
>>>> Thanks for your time.
>>>>
>>>> Calven
>>>>
>>>>
>>>> _______________________________________________
>>>> 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
>>>
>>> --
>>> Chuck Hill Senior Consultant / VP Development
>>>
>>> Practical WebObjects - for developers who want to increase their overall knowledge of WebObjects or who are trying to solve specific problems.
>>> http://www.global-village.net/products/practical_webobjects
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>
> --
> Chuck Hill Senior Consultant / VP Development
>
> Practical WebObjects - for developers who want to increase their overall knowledge of WebObjects or who are trying to solve specific problems.
> http://www.global-village.net/products/practical_webobjects
>
>
>
>
>
>
>
_______________________________________________
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