You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-dev@db.apache.org by "Vadim Gritsenko (JIRA)" <ji...@apache.org> on 2005/04/08 18:05:16 UTC
[jira] Created: (OJB-16) Support stored procedures in select by pk statement
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: db-ojb-selectbypk.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
Re: OJB11 Incompatibilities, Was: Support stored procedures in select
by pk statement (OJB-16)
Posted by Vadim Gritsenko <va...@reverycodes.com>.
Thomas Dudziak wrote:
>>According to stacktraces I see, it tries to instantiate
>>ConnectionFactoryPooledImpl, and I see that it is mentioned in ContainerHelper:
>>
>> ...ensureImplementationClass(ConnectionFactory.class,
>> ConnectionFactoryPooledImpl.class);
>>
>>So it seems like it does not pick up the value from properties file.
>>Additionally, OJB.properties which I found in OJB 1.1 CVS has no such property
>>as well...
>
>
> Ah, right, Armin (if I remember correctly) moved the connection
> factory setting into the jdbc connection descriptor. Have a look at
> the repository.dtd and the repository_database.xml in OJB's tests.
That's it - this fixes the problem.
Thanks,
Vadim
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org
Re: OJB11 Incompatibilities, Was: Support stored procedures in select by pk statement (OJB-16)
Posted by Thomas Dudziak <to...@gmail.com>.
> According to stacktraces I see, it tries to instantiate
> ConnectionFactoryPooledImpl, and I see that it is mentioned in ContainerHelper:
>
> ...ensureImplementationClass(ConnectionFactory.class,
> ConnectionFactoryPooledImpl.class);
>
> So it seems like it does not pick up the value from properties file.
> Additionally, OJB.properties which I found in OJB 1.1 CVS has no such property
> as well...
Ah, right, Armin (if I remember correctly) moved the connection
factory setting into the jdbc connection descriptor. Have a look at
the repository.dtd and the repository_database.xml in OJB's tests.
Tom
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org
Re: OJB11 Incompatibilities, Was: Support stored procedures in select
by pk statement (OJB-16)
Posted by Vadim Gritsenko <va...@reverycodes.com>.
Thomas Dudziak wrote:
>>I tried to pull over an application into the OJB 1.1, but there are lots of
>>changes between OJB 1.0 and OJB 1.1, and while I managed to reconcile many, I at
>>the moment stumbled onto one I can't seem to know how to fix.
>>
>>OJB 1.0 had property ConnectionFactoryClass, I have it set to
>>ConnectionFactoryAvalonDataSource [1], but seems like OJB 1.1 ignores this
>>property. What it has been replaced with?
>
>
> Mhmm, it shouldn't, the setting hasn't been changed. You can have a
> look at which connection factory class is instantiated in class
> PersistenceConfiguration, line 78.
According to stacktraces I see, it tries to instantiate
ConnectionFactoryPooledImpl, and I see that it is mentioned in ContainerHelper:
...ensureImplementationClass(ConnectionFactory.class,
ConnectionFactoryPooledImpl.class);
So it seems like it does not pick up the value from properties file.
Additionally, OJB.properties which I found in OJB 1.1 CVS has no such property
as well...
Vadim
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org
Re: OJB11 Incompatibilities, Was: Support stored procedures in select by pk statement (OJB-16)
Posted by Thomas Dudziak <to...@gmail.com>.
> I tried to pull over an application into the OJB 1.1, but there are lots of
> changes between OJB 1.0 and OJB 1.1, and while I managed to reconcile many, I at
> the moment stumbled onto one I can't seem to know how to fix.
>
> OJB 1.0 had property ConnectionFactoryClass, I have it set to
> ConnectionFactoryAvalonDataSource [1], but seems like OJB 1.1 ignores this
> property. What it has been replaced with?
Mhmm, it shouldn't, the setting hasn't been changed. You can have a
look at which connection factory class is instantiated in class
PersistenceConfiguration, line 78.
> If it's of interest, I can also collect all the other changes I had to do in
> order to run OJB10 app under OJB11.
Yup, that would be interesting, please post it to the dev list once
you got your app running.
regards,
Tom
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org
OJB11 Incompatibilities, Was: Support stored procedures in select
by pk statement (OJB-16)
Posted by Vadim Gritsenko <va...@reverycodes.com>.
Thomas Dudziak (JIRA) wrote:
> I applied your patch to the 1.1 branch with small changes, except to the
> XDoclet module (will do that during the week). Please try it out to see whether it works.
I tried to pull over an application into the OJB 1.1, but there are lots of
changes between OJB 1.0 and OJB 1.1, and while I managed to reconcile many, I at
the moment stumbled onto one I can't seem to know how to fix.
OJB 1.0 had property ConnectionFactoryClass, I have it set to
ConnectionFactoryAvalonDataSource [1], but seems like OJB 1.1 ignores this
property. What it has been replaced with?
If it's of interest, I can also collect all the other changes I had to do in
order to run OJB10 app under OJB11.
Thanks,
Vadim
[1]
http://svn.apache.org/viewcvs.cgi/cocoon/branches/BRANCH_2_1_X/src/blocks/ojb/conf/OJB.properties?rev=154169&view=markup
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org
[jira] Updated: (OJB-16) Support stored procedures in select by pk statement
Posted by "Vadim Gritsenko (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/OJB-16?page=all ]
Vadim Gritsenko updated OJB-16:
-------------------------------
Attachment: stored-procedures-patch.zip
Attaching refactored patch, in stored-procedures-patch.zip, with following changes: removed QueryByCriteriaBase, and QueryByFKCriteria renamed to QueryByExample and now extends from QueryByCriteria.
These changes are aligned more with changes in OJB 1.1 (trunk), so it will be much easier to refactor it for applying to trunk. This patch is against OJB_1_0_RELEASE.
> 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
> Assignee: Thomas Dudziak
> Attachments: QueryByCriteriaBase.java, QueryByFKCriteria.java, SelectByFKProcedureDescriptor.java, SelectByPKProcedureDescriptor.java, SqlProcedureFKStatement.java, db-ojb-patch.diff, db-ojb-selectbypk.diff, stored-procedures-patch.zip, 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
-
For more information on JIRA, 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
[jira] Updated: (OJB-16) Support stored procedures in select by pk statement
Posted by "Vadim Gritsenko (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/OJB-16?page=history ]
Vadim Gritsenko updated OJB-16:
-------------------------------
Attachment: SelectByFKProcedureDescriptor.java
New file for db-ojb-patch.diff
> 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
> Assignee: Thomas Dudziak
> Attachments: QueryByCriteriaBase.java, QueryByFKCriteria.java, SelectByFKProcedureDescriptor.java, SelectByPKProcedureDescriptor.java, SqlProcedureFKStatement.java, db-ojb-patch.diff, 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
[jira] Commented: (OJB-16) Support stored procedures in select by pk statement
Posted by "Thomas Dudziak (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/OJB-16?page=comments#action_62714 ]
Thomas Dudziak commented on OJB-16:
-----------------------------------
OJB uses the procedure whose name is given in the name attribute of the procedure element in the repository.xml.
See here for the syntax of the already supported procedures:
http://db.apache.org/ojb/docu/guides/repository.html#Stored+Procedure+Support
> 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
> Assignee: Thomas Dudziak
> 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
[jira] Updated: (OJB-16) Support stored procedures in select by pk statement
Posted by "Vadim Gritsenko (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/OJB-16?page=history ]
Vadim Gritsenko updated OJB-16:
-------------------------------
Attachment: QueryByFKCriteria.java
New file for db-ojb-patch.diff
> 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
> Assignee: Thomas Dudziak
> Attachments: QueryByCriteriaBase.java, QueryByFKCriteria.java, SelectByFKProcedureDescriptor.java, SelectByPKProcedureDescriptor.java, SqlProcedureFKStatement.java, db-ojb-patch.diff, 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
[jira] Commented: (OJB-16) Support stored procedures in select by pk statement
Posted by "Thomas Dudziak (JIRA)" <ji...@apache.org>.
[ 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
[jira] Commented: (OJB-16) Support stored procedures in select by pk statement
Posted by "Vadim Gritsenko (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/OJB-16?page=comments#action_62715 ]
Vadim Gritsenko commented on OJB-16:
------------------------------------
I know.
Still, how it will pick correct procedure out of the two I described above, if they will use same tag?
/**
* @ojb.retrieve-procedure FIND_MYBEAN_BYPK
* @ojb.retrieve-procedure FIND_MYBEAN_BYFK
*/
Or, tell me if I understood you correctly, you are suggesting to pick a procedure which has correct set of arguments? So it means iterating through all such procedures and their arguments until correct one is found?
> 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
> Assignee: Thomas Dudziak
> 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
[jira] Commented: (OJB-16) Support stored procedures in select by pk statement
Posted by "Thomas Dudziak (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/OJB-16?page=comments#action_62449 ]
Thomas Dudziak commented on OJB-16:
-----------------------------------
Mhmm, I don't like the name of the repository.xml and xdoclet tag. How about 'retrieve-procedure' ? That would be more in alignment with the naming of the auto-* settings.
Also, the XDoclet patch needs more work (including unit tests). I can add these once the rest is in, so please apply the patch without the XDoclet changes (only the last change to ojb.xml).
> 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
>
> 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
[jira] Commented: (OJB-16) Support stored procedures in select by pk statement
Posted by "Thomas Dudziak (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/OJB-16?page=comments#action_62752 ]
Thomas Dudziak commented on OJB-16:
-----------------------------------
> That's exactly what I proposed [1], last paragraph. But this can be
> an extension to this existing patch.
Agreed.
> by-pk can't be used if you retrieve collection - you have to query
> by non pk fields, like B.aId from example above. That's why by-fk
> was introduced. If you meant by-fk, then yes, I agree with you. If
> no collection specific SP is specified, then by-fk can be used.
Sure, but I take it from this, that by-pk is used to retrieve a reference ?
> Agreed. But that should be step 3, in three step process:
> * Implement by-pk
> * Implement by-fk
> * Implement SP per relation
I'm not so happy with the specification of by-fk at the element class level because it implicitly belongs to the collection. But anyway.
> It is not feasible to convert arbitrary Criteria query into SP call.
> It's possible though for query-by-example, and that's what I did in
> QueryFactory.
I rather meant an extension to the query stuff that allows for usage of stored procedures instead of the sql select statement, i.e. query by criteria. But it may not be possible because you probably cannot specify the procedure arguments by name rather than by position. But was just an idea anyway.
Ok, anyway, how about calling them select-by-pk-procedure and select-by-fk-procedure then (for better readability) ? I can probably apply the patch at the weekend.
Btw, could you perhaps create some unit tests (oracle specific is ok, I can adapt them to PostgreSQL) ?
Tom
> 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
> Assignee: Thomas Dudziak
> Attachments: QueryByCriteriaBase.java, QueryByFKCriteria.java, SelectByFKProcedureDescriptor.java, SelectByPKProcedureDescriptor.java, SqlProcedureFKStatement.java, db-ojb-patch.diff, 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
[jira] Updated: (OJB-16) Support stored procedures in select by pk statement
Posted by "Vadim Gritsenko (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/OJB-16?page=history ]
Vadim Gritsenko updated OJB-16:
-------------------------------
Attachment: db-ojb-patch.diff
Patch including both selectbypk and selectbyfk stored procedures support.
> 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
> Assignee: Thomas Dudziak
> Attachments: QueryByCriteriaBase.java, QueryByFKCriteria.java, SelectByFKProcedureDescriptor.java, SelectByPKProcedureDescriptor.java, SqlProcedureFKStatement.java, db-ojb-patch.diff, 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
[jira] Updated: (OJB-16) Support stored procedures in select by pk statement
Posted by "Vadim Gritsenko (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/OJB-16?page=history ]
Vadim Gritsenko updated OJB-16:
-------------------------------
Attachment: db-ojb-selectbypk.diff
> 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: db-ojb-selectbypk.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
[jira] Commented: (OJB-16) Support stored procedures in select by pk statement
Posted by "Vadim Gritsenko (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/OJB-16?page=comments#action_62746 ]
Vadim Gritsenko commented on OJB-16:
------------------------------------
Ok, that one is easy.
> Ok, but how do the procedures get chosen?
As you can see, for retrieving objects by their identity always select-by-pk procedure is used. Collections are always retrieved using select-by-fk procedure. References are converted in the ObjectReferenceBroker into the Identity queries, so again, select-by-pk is used.
select-by-pk is implemented in SqlGeneratorDefaultImpl.getPreparedSelectByPkStatement - that's where query by identity ends up.
select-by-fk is implemented using new Query class, constructed in QueryReferenceBroker.getFKQuery1toN and converted to procedure call in the SqlGeneratorDefaultImpl.getPreparedSelectStatement.
> IMO it is unclear which one is used when I retrieve objects by a
> simple query,
What is a simple query? If it's query by identity, then select-by-pk is used. If it's query by example, select-by-fk is used. If it's query by SQL, then it depends on your SQL (I use stored procedures). If neither of those (like query by criteria), then behaviour as it is now (select statement).
> or which one is used for retrieving the collection
> of B's within A.
select-by-fk is always used in this case, as you need to select instances of B using its foreign key field aId.
> And if the by-fk procedure is used for the
> collection, where do the values come from ?
If you mean where B.aId is coming from: that what QueryReferenceBroker.getFKQuery1toN is for, and it is invoked from getFKQuery, it creates query using passed to it A object instance.
Regards,
Vadim
> 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
> Assignee: Thomas Dudziak
> Attachments: QueryByCriteriaBase.java, QueryByFKCriteria.java, SelectByFKProcedureDescriptor.java, SelectByPKProcedureDescriptor.java, SqlProcedureFKStatement.java, db-ojb-patch.diff, 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
[jira] Updated: (OJB-16) Support stored procedures in select by pk statement
Posted by "Vadim Gritsenko (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/OJB-16?page=history ]
Vadim Gritsenko updated OJB-16:
-------------------------------
Attachment: QueryByCriteriaBase.java
New file for patch db-ojb-patch.diff
> 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
> Assignee: Thomas Dudziak
> Attachments: QueryByCriteriaBase.java, QueryByFKCriteria.java, SelectByFKProcedureDescriptor.java, SelectByPKProcedureDescriptor.java, SqlProcedureFKStatement.java, db-ojb-patch.diff, 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
[jira] Commented: (OJB-16) Support stored procedures in select by pk statement
Posted by "Vadim Gritsenko (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/OJB-16?page=comments#action_62730 ]
Vadim Gritsenko commented on OJB-16:
------------------------------------
(stupid jira does not places comments against files)
Complete bypk and byfk patch:
db-ojb-patch.diff
Files added by the patch:
QueryByCriteriaBase.java
QueryByFKCriteria.java
SelectByFKProcedureDescriptor.java
SqlProcedureFKStatement.java
As well as previously attached:
SelectByPKProcedureDescriptor.java
Patch is against OJB_1_0_RELEASE branch.
> 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
> Assignee: Thomas Dudziak
> Attachments: QueryByCriteriaBase.java, QueryByFKCriteria.java, SelectByFKProcedureDescriptor.java, SelectByPKProcedureDescriptor.java, SqlProcedureFKStatement.java, db-ojb-patch.diff, 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
[jira] Commented: (OJB-16) Support stored procedures in select by pk statement
Posted by "Vadim Gritsenko (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/OJB-16?page=comments#action_63701 ]
Vadim Gritsenko commented on OJB-16:
------------------------------------
Thanks for applying the patch. I did some testing and OJB still works Ok - goes over stored procedures as configured.
Any chance of applying the patch to 1.0? It seems 1.1 has some significant changes, I'm wary of using in in the production project (yet).
Thanks,
Vadim
> 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
> Assignee: Thomas Dudziak
> Attachments: QueryByCriteriaBase.java, QueryByFKCriteria.java, SelectByFKProcedureDescriptor.java, SelectByPKProcedureDescriptor.java, SqlProcedureFKStatement.java, db-ojb-patch.diff, db-ojb-selectbypk.diff, stored-procedures-patch.zip, 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
-
For more information on JIRA, 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
[jira] Commented: (OJB-16) Support stored procedures in select by pk statement
Posted by "Vadim Gritsenko (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/OJB-16?page=comments#action_62463 ]
Vadim Gritsenko commented on OJB-16:
------------------------------------
> I don't like the name of the repository.xml and xdoclet tag.
> How about 'retrieve-procedure' ?
Any name is Ok to me, but please keep in mind that you'd have to come up with one more name for "selectbyfk-procedure", which stands for "select by foreign key", and is used to retrieve object references and collections.
> XDoclet patch needs more work
Beside unit tests, what work does it requires?
Thanks,
Vadim
> 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
>
> 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
[jira] Commented: (OJB-16) Support stored procedures in select by pk statement
Posted by "Vadim Gritsenko (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/OJB-16?page=comments#action_62713 ]
Vadim Gritsenko commented on OJB-16:
------------------------------------
> Yes, but for OJB they are virtually identical: they
> both want a list of (field) values of the current
> object as input and return a resultset. That's why
> I'd rather use one descriptor for these, where per
> default the pks are used unless the user specifies
> the fields (as arguments) himself. This emphasizes
> the fact that all OJB does is to give the database
> procedure some values from the current object
> (or constants). Whether the database procedure then
> uses them as pk values or something different,
> doesn't matter to OJB as it is in the application domain.
How then OJB will pick the right procedure out of the two?
Vadim
> 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
> Assignee: Thomas Dudziak
> 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
[jira] Updated: (OJB-16) Support stored procedures in select by pk statement
Posted by "Thomas Dudziak (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/OJB-16?page=history ]
Thomas Dudziak updated OJB-16:
------------------------------
Assign To: Thomas Dudziak
> 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
> Assignee: Thomas Dudziak
> 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
[jira] Commented: (OJB-16) Support stored procedures in select by pk statement
Posted by "Vadim Gritsenko (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/OJB-16?page=comments#action_62755 ]
Vadim Gritsenko commented on OJB-16:
------------------------------------
>> by-pk can't be used if you retrieve collection - you have to query
>> by non pk fields, like B.aId from example above. That's why by-fk
>> was introduced. If you meant by-fk, then yes, I agree with you. If
>> no collection specific SP is specified, then by-fk can be used.
>
>Sure, but I take it from this, that by-pk is used to retrieve a reference ?
Yes :-)
Internally (in QueryReferenceBroker), retrieve-a-reference is converted into QueryByIdentity, which triggers select-by-pk.
> Ok, anyway, how about calling them select-by-pk-procedure and
> select-by-fk-procedure then (for better readability) ?
No problem at all.
> I can probably apply the patch at the weekend.
That would be great!
> Btw, could you perhaps create some unit tests (oracle specific is ok,
> I can adapt them to PostgreSQL) ?
Ok, I'll take a look at existing tests. I never wrote OJB tests yet...
Vadim
> 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
> Assignee: Thomas Dudziak
> Attachments: QueryByCriteriaBase.java, QueryByFKCriteria.java, SelectByFKProcedureDescriptor.java, SelectByPKProcedureDescriptor.java, SqlProcedureFKStatement.java, db-ojb-patch.diff, 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
[jira] Commented: (OJB-16) Support stored procedures in select by pk statement
Posted by "Vadim Gritsenko (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/OJB-16?page=comments#action_62501 ]
Vadim Gritsenko commented on OJB-16:
------------------------------------
>> 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 ?
I meant more efficient SP impl, from DB POV. For example, compare the two:
public class MyBean {
/** ojb.field primarykey="true" */
private String a;
/** ojb.field primarykey="true" */
private String b;
/** ojb.field */
private String c;
...
/** ojb.field */
private String z;
}
PK Procedure:
CREATE FUNCTION FIND_MYBEAN_BYPK (AA IN VARCHAR, BB IN VARCHAR)
RETURN TYPES.CURSORTYPE AS
RESULT TYPES.CURSORTYPE;
BEGIN
OPEN RESULT FOR SELECT * FROM MYBEAN WHERE A = AA AND B = BB;
RETURN RESULT;
END;
/
FK Procedure:
CREATE FUNCTION FIND_MYBEAN (AA IN VARCHAR, ..., ZZ IN VARCHAR)
RETURN TYPES.CURSORTYPE AS
RESULT TYPES.CURSORTYPE;
BEGIN
OPEN RESULT FOR SELECT * FROM MYBEAN WHERE
(AA IS NULL OR A = AA) AND ... AND (ZZ IS NULL OR Z = ZZ)
RETURN RESULT;
END;
/
I'm no expert on Oracle or PL/SQL, but seems to me latter is less performant then former, and I don't see a better, more performant way for implementing the latter.
SP per relation would fix this issue (at the expense of having to create multiple procedures), but ATM I don't know from what side to approach this in order to implement this in OJB. And still, this two-procedures approach does not prevent one to implement procedure-per-relation approach. So it would give ability to specify generic function for all relations, and some customized, tailored to the relation, function for specific relation(s).
Thanks,
Vadim
> 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
> Assignee: Thomas Dudziak
> 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
[jira] Updated: (OJB-16) Support stored procedures in select by pk statement
Posted by "Vadim Gritsenko (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/OJB-16?page=history ]
Vadim Gritsenko updated OJB-16:
-------------------------------
Attachment: xdoclet.diff
I'm sorry; forgot patch for one more file - for xdoclet tag - as already noted above.
> 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
[jira] Commented: (OJB-16) Support stored procedures in select by pk statement
Posted by "Thomas Dudziak (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/OJB-16?page=comments#action_63703 ]
Thomas Dudziak commented on OJB-16:
-----------------------------------
I'm reluctant to add new features to the 1.0 branch because it is supposed to be feature-closed. You could however start a vote on the dev list concerning the backport of this particular feature to the 1.0 branch if you want.
Btw, in its current state OJB 1.1 contains mostly internal refactorings/reworkings (i.e. IoC) and a few new features (cache stuff, collection factory, etc.)
> 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
> Assignee: Thomas Dudziak
> Attachments: QueryByCriteriaBase.java, QueryByFKCriteria.java, SelectByFKProcedureDescriptor.java, SelectByPKProcedureDescriptor.java, SqlProcedureFKStatement.java, db-ojb-patch.diff, db-ojb-selectbypk.diff, stored-procedures-patch.zip, 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
-
For more information on JIRA, 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
[jira] Commented: (OJB-16) Support stored procedures in select by pk statement
Posted by "Thomas Dudziak (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/OJB-16?page=comments#action_62716 ]
Thomas Dudziak commented on OJB-16:
-----------------------------------
Hmm, when would you need to specify two retrieve procedures at the same entity (class, collection, reference) ?
Could you perhaps post a sample scenario where you use both a procedure that selects by pk, and a procedure that selects by fk ?
> 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
> Assignee: Thomas Dudziak
> 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
[jira] Commented: (OJB-16) Support stored procedures in select by pk statement
Posted by "Thomas Dudziak (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/OJB-16?page=comments#action_63610 ]
Thomas Dudziak commented on OJB-16:
-----------------------------------
I applied your patch to the 1.1 branch with small changes, except to the XDoclet module (will do that during the week). Please try it out to see whether it works.
> 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
> Assignee: Thomas Dudziak
> Attachments: QueryByCriteriaBase.java, QueryByFKCriteria.java, SelectByFKProcedureDescriptor.java, SelectByPKProcedureDescriptor.java, SqlProcedureFKStatement.java, db-ojb-patch.diff, db-ojb-selectbypk.diff, stored-procedures-patch.zip, 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
-
For more information on JIRA, 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
[jira] Commented: (OJB-16) Support stored procedures in select by pk statement
Posted by "Vadim Gritsenko (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/OJB-16?page=comments#action_62751 ]
Vadim Gritsenko commented on OJB-16:
------------------------------------
> Ok, so you can at most specify two procedures,
> one by-pk and one by-fk, at class level ?!
Yes. Additionally, you can specify update-, delete-, insert- procedures: also only one of each, at most, at the class level.
> Hmm, WDYT, wouldn't it make more sense to specify the by-fk at the
> collection/reference rather than the class ? This way, you could
> use different procedures,
That's exactly what I proposed [1], last paragraph. But this can be an extension to this existing patch.
> and per default the by-pk would be used
> (i.e. the current handling which matches fk field values against the
> corresponding pks).
by-pk can't be used if you retrieve collection - you have to query by non pk fields, like B.aId from example above. That's why by-fk was introduced. If you meant by-fk, then yes, I agree with you. If no collection specific SP is specified, then by-fk can be used.
> Then you don't have any name problem, and the retrieval is way more
> flexible.
Agreed. But that should be step 3, in three step process:
* Implement by-pk
* Implement by-fk
* Implement SP per relation
Where first two steps provide simple yet generic implementation, while step 3 provides more complex but flexible implementation. I don't see why OJB can't have all three implemented.
> As for queries, IMO it would make sense to generally allow the
> specification of a stored procedure to be used for retrieval whenever
> a select would be issued. But I'm no expert in the query stuff.
It is not feasible to convert arbitrary Criteria query into SP call. It's possible though for query-by-example, and that's what I did in QueryFactory.
Vadim
[1] http://issues.apache.org/jira/browse/OJB-16#action_62501
> 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
> Assignee: Thomas Dudziak
> Attachments: QueryByCriteriaBase.java, QueryByFKCriteria.java, SelectByFKProcedureDescriptor.java, SelectByPKProcedureDescriptor.java, SqlProcedureFKStatement.java, db-ojb-patch.diff, 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
[jira] Updated: (OJB-16) Support stored procedures in select by pk statement
Posted by "Vadim Gritsenko (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/OJB-16?page=history ]
Vadim Gritsenko updated OJB-16:
-------------------------------
Attachment: SelectByPKProcedureDescriptor.java
New file, in addition to patch.
> 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
>
> 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
[jira] Commented: (OJB-16) Support stored procedures in select by pk statement
Posted by "Vadim Gritsenko (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/OJB-16?page=comments#action_62718 ]
Vadim Gritsenko commented on OJB-16:
------------------------------------
Please see above [1]: two procedures, IMHO, are more efficient (from performance POV) then single universal one. AFAIU, you had agreed with this conclusion [2] by saying "Yes, but...". So now we need to find a way to implement this, unless there are better suggestions.
If problem is with the naming only, I can suggest following names:
get-procedure
find-procedure
Let me know which way you want to proceed; so that I can modify patches accordingly.
Thanks,
Vadim
[1] http://issues.apache.org/jira/browse/OJB-16#action_62501
[2] http://issues.apache.org/jira/browse/OJB-16#action_62502
> 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
> Assignee: Thomas Dudziak
> 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
[jira] Commented: (OJB-16) Support stored procedures in select by pk statement
Posted by "Vadim Gritsenko (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/OJB-16?page=comments#action_62474 ]
Vadim Gritsenko commented on OJB-16:
------------------------------------
> on the OJB level the find-by-pk and find-by-fk (or rather,
> find-by-some-fields) differ only in that in the first case
> OJB can determine which fields to use,
True. All primary key values has to be fed into the stored procedure. That's how patch currently implements it. Hence, no need for additional configuration (as for other procedure types).
> and in which order
> whereas in the second case, the user has to specify this.
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.
> Therefore, they are alike and you can use the same format as e.g.
> delete-procedure uses:
>
> <!ELEMENT delete-procedure
> (documentation?, (runtime-argument | constant-argument)*, attribute*)>
>
> If no runtime/constant arguments were given, then OJB uses the pk
> fields. This mapping is also already defined for the XDoclet tags
> so nothing new there. Btw, it also allows to use constant values if
> one so desires.
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! :-) 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. Just to give an example:
/**
* @ojb.runtime-argument name="user"
* property="currentUser"
*/
public class Bean {
public String getCurrentUser() {
return context.getCallerPrincipal().getName();
}
}
Now, that could be more valuable then constant-argument.
> As for what is required for the XDoclet tags, the ojb.xml file
> is only for documentation purposes.
I'm sorry, my first patch was not properly created, it did not include patch to *.xdt file - attached now as separate patch.
Thanks,
Vadim
> 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
[jira] Commented: (OJB-16) Support stored procedures in select by pk statement
Posted by "Thomas Dudziak (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/OJB-16?page=comments#action_62720 ]
Thomas Dudziak commented on OJB-16:
-----------------------------------
That's not what I meant. Could you perhaps post a complete sample with source xdoclet tags or repository.xml that uses both variants, e.g. perhaps even something that runs, because I don't yet understand how you would use the by-fk variant (the by-pk is clear as it matches the current way OJB retrieves collections/references).
> 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
> Assignee: Thomas Dudziak
> 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
[jira] Commented: (OJB-16) Support stored procedures in select by pk statement
Posted by "Thomas Dudziak (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/OJB-16?page=comments#action_62750 ]
Thomas Dudziak commented on OJB-16:
-----------------------------------
Ok, so you can at most specify two procedures, one by-pk and one by-fk, at class level ?!
Hmm, WDYT, wouldn't it make more sense to specify the by-fk at the collection/reference rather than the class ? This way, you could use different procedures, and per default the by-pk would be used (i.e. the current handling which matches fk field values against the corresponding pks).
Then you don't have any name problem, and the retrieval is way more flexible.
As for queries, IMO it would make sense to generally allow the specification of a stored procedure to be used for retrieval whenever a select would be issued. But I'm no expert in the query stuff.
> 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
> Assignee: Thomas Dudziak
> Attachments: QueryByCriteriaBase.java, QueryByFKCriteria.java, SelectByFKProcedureDescriptor.java, SelectByPKProcedureDescriptor.java, SqlProcedureFKStatement.java, db-ojb-patch.diff, 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
[jira] Commented: (OJB-16) Support stored procedures in select by pk statement
Posted by "Thomas Dudziak (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/OJB-16?page=comments#action_62465 ]
Thomas Dudziak commented on OJB-16:
-----------------------------------
Mhmm, on the OJB level the find-by-pk and find-by-fk (or rather, find-by-some-fields) differ only in that in the first case OJB can determine which fields to use, and in which order whereas in the second case, the user has to specify this.
Therefore, they are alike and you can use the same format as e.g. delete-procedure uses:
<!ELEMENT delete-procedure
(documentation?, (runtime-argument | constant-argument)*, attribute*)>
If no runtime/constant arguments were given, then OJB uses the pk fields. This mapping is also already defined for the XDoclet tags so nothing new there. Btw, it also allows to use constant values if one so desires.
As for what is required for the XDoclet tags, the ojb.xml file is only for documentation purposes. If you're interested in how to add a new tag, have a look at how @ojb.delete-procedure is handled. E.g. you need to check the .xdt files, the OjbTagsHelper class and the relevant model objects.
> 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
>
> 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
[jira] Resolved: (OJB-16) Support stored procedures in select by pk statement
Posted by "Thomas Dudziak (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/OJB-16?page=all ]
Thomas Dudziak resolved OJB-16:
-------------------------------
Resolution: Fixed
Fix Version: 1.1 CVS
DTD has been updated, new corresponding XDoclet tags have been introduced
> 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
> Assignee: Thomas Dudziak
> Fix For: 1.1 CVS
> Attachments: QueryByCriteriaBase.java, QueryByFKCriteria.java, SelectByFKProcedureDescriptor.java, SelectByPKProcedureDescriptor.java, SqlProcedureFKStatement.java, db-ojb-patch.diff, db-ojb-selectbypk.diff, stored-procedures-patch.zip, 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
-
For more information on JIRA, 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
[jira] Commented: (OJB-16) Support stored procedures in select by pk statement
Posted by "Thomas Dudziak (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/OJB-16?page=comments#action_62502 ]
Thomas Dudziak commented on OJB-16:
-----------------------------------
> PK Procedure:
>
> CREATE FUNCTION FIND_MYBEAN_BYPK (AA IN VARCHAR, BB IN VARCHAR)
> RETURN TYPES.CURSORTYPE AS
> RESULT TYPES.CURSORTYPE;
> BEGIN
> OPEN RESULT FOR SELECT * FROM MYBEAN WHERE A = AA AND B = BB;
> RETURN RESULT;
> END;
> /
>
> FK Procedure:
>
> CREATE FUNCTION FIND_MYBEAN (AA IN VARCHAR, ..., ZZ IN VARCHAR)
> RETURN TYPES.CURSORTYPE AS
> RESULT TYPES.CURSORTYPE;
> BEGIN
> OPEN RESULT FOR SELECT * FROM MYBEAN WHERE
> (AA IS NULL OR A = AA) AND ... AND (ZZ IS NULL OR Z = ZZ)
> RETURN RESULT;
> END;
> /
>
> I'm no expert on Oracle or PL/SQL, but seems to me latter is less performant then
> former, and I don't see a better, more performant way for implementing the latter.
Yes, but for OJB they are virtually identical: they both want a list of (field) values of the current object as input and return a resultset. That's why I'd rather use one descriptor for these, where per default the pks are used unless the user specifies the fields (as arguments) himself. This emphasizes the fact that all OJB does is to give the database procedure some values from the current object (or constants). Whether the database procedure then uses them as pk values or something different, doesn't matter to OJB as it is in the application domain.
> SP per relation would fix this issue (at the expense of having to create multiple
> procedures), but ATM I don't know from what side to approach this in order to
> implement this in OJB. And still, this two-procedures approach does not prevent one
> to implement procedure-per-relation approach. So it would give ability to specify
> generic function for all relations, and some customized, tailored to the relation,
> function for specific relation(s).
Mhmm, yes, it might be useful to also be able to specify the procedure to use for retrieving a reference/collection at the reference/collection rather than at the target class.
> 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
> Assignee: Thomas Dudziak
> 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
[jira] Commented: (OJB-16) Support stored procedures in select by pk statement
Posted by "Thomas Dudziak (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/OJB-16?page=comments#action_62732 ]
Thomas Dudziak commented on OJB-16:
-----------------------------------
Ok, but how do the procedures get chosen ? IMO it is unclear which one is used when I retrieve objects by a simple query, or which one is used for retrieving the collection of B's within A. And if the by-fk procedure is used for the collection, where do the values come from ?
> 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
> Assignee: Thomas Dudziak
> Attachments: QueryByCriteriaBase.java, QueryByFKCriteria.java, SelectByFKProcedureDescriptor.java, SelectByPKProcedureDescriptor.java, SqlProcedureFKStatement.java, db-ojb-patch.diff, 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
[jira] Commented: (OJB-16) Support stored procedures in select by pk statement
Posted by "Vadim Gritsenko (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/OJB-16?page=comments#action_62722 ]
Vadim Gritsenko commented on OJB-16:
------------------------------------
> Could you perhaps post a complete sample with source xdoclet tags or
> repository.xml that uses both variants, e.g. perhaps even something
> that runs, because I don't yet understand how you would use the by-fk
> variant (the by-pk is clear as it matches the current way OJB retrieves
> collections/references).
Most certainly. I'll attach complete patch including both by-pk and by-fk variants. Problem is, by-fk implementation, I'm afraid, will not get accepted as-is, and will need some refactorings. But it sure works and you can see the code.
Here is simple example:
/**
* @ojb.class
* @ojb.selectbypk-procedure name="FIND_A_BYID"
* @ojb.selectbyfk-procedure name="FIND_A"
*/
public class A {
/**
* @ojb.field primarykey="true"
* autoincrement="ojb"
* sequence-name="a_seq"
*/
public Integer id;
/**
* @ojb.field
*/
public String name;
/**
* @ojb.collection element-class-ref="B"
* foreignkey="aId"
*/
public Collection b;
}
/**
* @ojb.class
* @ojb.selectbypk-procedure name="FIND_B_BYID"
* @ojb.selectbyfk-procedure name="FIND_B"
*/
public class B {
/**
* @ojb.field primarykey="true"
* autoincrement="ojb"
* sequence-name="b_seq"
*/
public Integer id;
/**
* @ojb.field
*/
public Integer aId;
/**
* @ojb.reference foreignkey="aId"
*/
public A a;
/**
* @ojb.field
*/
public String value;
}
CREATE FUNCTION FIND_A_BYID (A_ID IN NUMBER)
RETURN TYPES.CURSORTYPE AS
RESULT TYPES.CURSORTYPE;
BEGIN
OPEN RESULT FOR SELECT ID, NAME FROM A WHERE ID = A_ID;
RETURN RESULT;
END;
/
CREATE FUNCTION FIND_A (A_ID IN NUMBER, A_NAME IN VARCHAR)
RETURN TYPES.CURSORTYPE AS
RESULT TYPES.CURSORTYPE;
BEGIN
OPEN RESULT FOR SELECT ID, NAME FROM A WHERE
(A_ID IS NULL OR A_ID = ID) AND (A_NAME IS NULL OR A_NAME = NAME);
RETURN RESULT;
END;
/
CREATE FUNCTION FIND_B_BYID (A_ID IN NUMBER)
RETURN TYPES.CURSORTYPE AS
RESULT TYPES.CURSORTYPE;
BEGIN
OPEN RESULT FOR SELECT ID, AID, VALUE FROM B WHERE ID = A_ID;
RETURN RESULT;
END;
/
CREATE FUNCTION FIND_B (A_ID IN NUMBER, A_AID IN NUMBER, A_VALUE IN VARCHAR)
RETURN TYPES.CURSORTYPE AS
RESULT TYPES.CURSORTYPE;
BEGIN
OPEN RESULT FOR SELECT ID, AID, VALUE FROM B WHERE
(A_ID IS NULL OR A_ID = ID) AND
(A_AID IS NULL OR A_AID = AID) AND
(A_VALUE IS NULL OR A_VALUE = VALUE);
RETURN RESULT;
END;
/
> 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
> Assignee: Thomas Dudziak
> Attachments: QueryByCriteriaBase.java, QueryByFKCriteria.java, SelectByFKProcedureDescriptor.java, SelectByPKProcedureDescriptor.java, SqlProcedureFKStatement.java, db-ojb-patch.diff, 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
[jira] Updated: (OJB-16) Support stored procedures in select by pk statement
Posted by "Vadim Gritsenko (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/OJB-16?page=history ]
Vadim Gritsenko updated OJB-16:
-------------------------------
Attachment: SqlProcedureFKStatement.java
New file for db-ojb-patch.diff
> 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
> Assignee: Thomas Dudziak
> Attachments: QueryByCriteriaBase.java, QueryByFKCriteria.java, SelectByFKProcedureDescriptor.java, SelectByPKProcedureDescriptor.java, SqlProcedureFKStatement.java, db-ojb-patch.diff, 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