I should be able to provide several scenarios by e.o.d. Saturday. For now,
here are couple crude cases:
1. Entities INSURANCE_CLAIM and INSURANCE_CLAIM_RECORD have one-to-many
relationship. Class InsuranceClaimRecord has field "workflowStatus", which
is determined as the result of the very complex query. Consequently, to
retrieve the collection of InsuranceClaimRecord-s for the claim we are using
spLoad_Claim_Detail(vrClaim_ID As NUMBER), which returns the result set
including INSURANCE_CLAIM_RECORD table and "WORKFLOW_STATUS". There is no
stored procedure for retrieving the INSURANCE_CLAIM one at a time using ID.
2. spMy_Inbox_Claims(vrUser_ID As NUMBER) returns the tabular, read-only
record set based on INSURANCE_CLAIM with the results of the search. When
user selects the row in the table, the insurance claim is opened for
editing. Ideally we would have one-to-one relationship, which would allow to
navigate easily from MyInboxInsuranceClaim to InsuranceClaim.
3. When creating new InsuranceClaimRecord, we have to generate associated
workflow items, which is done by calling corresponding stored procedure (too
complex to be done through trigger). Need the event, which would allow to
add this call for the CREATE event in the same transaction frame.
4. When creating new InsuranceClaim results in generating of the
corresponding workflow, which is encapsulated in spClaim_InsUpd(). This
stored procedure has the set of fields matching INSURANCE_CLAIM table, and
does all the workflow generation. Moving it to the trigger is not possible
for various reasons, so all INSERT and UPDATE operations for InsuranceClaim
should be done using spClaim_InsUpd() stored procedure, with the fields of
the class mapped to the fields of the sp. The generated ID is always
returned in the OUT recordset.
That's it for now. Shoould polish it over the next couple days.
-----Original Message-----
From: Andrus Adamchik [mailto:andru..bjectstyle.org]
Sent: Wednesday, October 23, 2002 4:57 PM
To: msheng..obox.com
Cc: cayenne-deve..bjectstyle.org
Subject: Stored Procedures Support
Misha, and everybody,
I am working on a design for the stored procedure support in Cayenne.
Originally this feature came out of Misha's need to use Cayenne and still be
able to access existing business logic that was implemented in the stored
procedures. But now I think more and more that it is very important to fill
in this hole in the mapping support in general.
Anyway. I started working on the design recently, and there is a few things
I wanted to ask Misha (who uses sp's daily) and others.
1. Do you think we can assume that an ObjEntity is always mapped to a
DbEntity (i.e. a table or a view) even when we are using stored procedure
for "read/save/delete"? I suspect this is going to be the easiest approach
and would be completely based on current mapping practices. If this works,
we will be able to use stored procedures and/or raw SQL for most object
operations - totally transparent to the object layer.
2. Misha (or anyone), could you post some use cases of how this is normally
used (I was lucky enough to avoid using stored proc with O/R). Along the
lines of "Class Customer maps to table CUSTOMER, to read it we use the proc
with the following parameters ... and the following logic .... To update, we
use ...".
Any ideas?
Andrus
This archive was generated by hypermail 2.0.0 : Wed Oct 23 2002 - 17:36:48 EDT