db-ojb-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Thomas Dudziak (JIRA)" <j...@apache.org>
Subject [jira] Commented: (OJB-16) Support stored procedures in select by pk statement
Date Sat, 09 Apr 2005 13:59:17 GMT
     [ http://issues.apache.org/jira/browse/OJB-16?page=comments#action_62498 ]
     
Thomas Dudziak commented on OJB-16:
-----------------------------------

> As I do not want to go into providing muliple procedures for each reference of collection,
> I decided to go with single retrieve-by-fk procedure, which can handle any relation.
>
> In order to do this, this procedure must take all fields as an argument, so that you
can
> retrieve references/collections regardless of the field used for the foreign key.
>
> As a result, this procedure is less efficient (to some degree) then simple select by
pk.
> Hence, I decided to go with two different procedures. I'd like to hear your suggestions
> for more efficient procedure implementation(s), if you have any.
>
> But ATM, I'd like to go with 2 procedures approach - as it allows for simplier and more
> efficient select-by-pk procedure.

What's more efficient, the OJB impl or the database procedure impl ?
Conceptually, OJB cannot distinguish a database procedure that requires pk values to be fed
in, from a database procedure that want's fk values. So its totally in the hand of the user
to match the two, i.e. give a pk-requiring procedure pk values etc.
Also I do think, that a slightly more generic approach is not less efficient on the OJB side.
That's why I'd prefer a single one retrieve-procedure that uses the pks as default instead
of two selectbypk/selectbyfk because IMO they are too much alike and it fits the way the other
procedures are declared, better.
Of course since we'll add this feature one way or the other, you could cast a vote for whether
to add a single retrieve or two selectbypk/selectbyfk procedures.

> I still do not understand why would anybody need constant-argument. It is a constant,
> right? So just write a constant into stored procedure itself if desired so! :-)

The reason is simple: the database procedure might already be present, but for the task at
hand it could require more arguments than necessary. Hence a constant argument can come in
handy.
For instance, assume you want to use a procedure to retrieve a collection of objects (selectbyfk).
Assume further that the referenced objects have a type column. Now your referencing object
might only be interested in those objects that have type set to 'simple' or somesuch. This
is most easily done by using a constant argument.

> And runtime-argument actually does not provide a way to refer to the runtime variable
-
> all you can do is refer to the field. So its functionality is limited to the renaming
> of the fields (which is: no functionality).
>
> More interesting will be to support JavaBean properties, so that you can pass to the
> stored procedure some (really) runtime intformation.

Agreed, the ability to specify expressions using the current object would be handy, and not
only for the procedures but for othr aspects as well (e.g. the normal reference and collection
descriptors).



> Support stored procedures in select by pk statement
> ---------------------------------------------------
>
>          Key: OJB-16
>          URL: http://issues.apache.org/jira/browse/OJB-16
>      Project: OJB
>         Type: New Feature
>   Components: PB-API
>     Versions: 1.0.x CVS
>     Reporter: Vadim Gritsenko
>  Attachments: SelectByPKProcedureDescriptor.java, db-ojb-selectbypk.diff, xdoclet.diff
>
> This patch adds support for retrieving objects by primary keys through call to stored
procedure instead of using select statement.
> To activate the feature, add xdoclet tag to the class:
> /**
>  * @ojb.class table="MYBEAN"
>  * @ojb.selectbypk-procedure name="FIND_MYBEAN_BYID"
>  */
> public class MyBean {
>     /**
>      * @ojb.field primarykey="true"
>      */
>     Integer id;
> }
> And then, create stored procedure:
> CREATE OR REPLACE PACKAGE TYPES AS
>   TYPE CURSORTYPE IS REF CURSOR;
> END TYPES;
> /
> CREATE OR REPLACE FUNCTION FIND_MYBEAN_BYID (ANID IN MYBEAN.ID%TYPE)
> RETURN TYPES.CURSORTYPE AS
> RESULT TYPES.CURSORTYPE;
> BEGIN
>   OPEN RESULT FOR SELECT * FROM MYBEAN WHERE ID = ANID;
>   RETURN RESULT;
> END;
> /
> Patch is made against OJB_1_0_RELEASE branch.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
   http://www.atlassian.com/software/jira


---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org


Mime
View raw message