ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Adriano Labate" <adriano.lab...@sportaccess.com>
Subject RE: Using a stored proc in selectKey?
Date Thu, 07 Apr 2005 06:07:47 GMT
Thank you for all your answers. 
A stored proc cannot be used in a select statement, unlike a function. In fact, I think the
solution could be to create a function that wraps the call to the stored proc and then returns
the id. that way I can then use a statement like :

  select fct_getnextid(category_id) as id from dual 

Adriano Labate

-----Message d'origine-----
De : Jason Hall [mailto:JHall@cihi.ca] 
Envoyé : mercredi, 6 avril 2005 19:26
À : ibatis-user-java@incubator.apache.org; Brandon Goodin
Objet : RE: Using a stored proc in selectKey?

Why don't you call your stored procedure through the select statement.

ex.

<insert ...>
<selectKey KeyProperty="id" ... >
	select sp_get_next_id('CATEGORY_ID',???) as id from dual;
</selectKey>
.
.
</insert>

Jason Hall


-----Original Message-----
From: Brandon Goodin [mailto:brandon.goodin@gmail.com]
Sent: Wednesday, April 06, 2005 12:47 PM
To: ibatis-user-java@incubator.apache.org
Subject: Re: Using a stored proc in selectKey?


AFAIK we don't support CallableStatement in the selectKey. If you
would like it to be, go ahead and place an enhancement  request in
JIRA. But, i would think that if you are doing something like you are
describing, you would simply make your inserts all store procedures
and handle the increment internally. You can then set an OUT parameter
for the ID to pass back into your object.

BTW, Isn't a stored proc a proprietary approach?

Brandon

On Apr 6, 2005 10:35 AM, Adriano Labate <adriano.labate@sportaccess.com> wrote:
> Hi,
> 
> Is it possible to call an Oracle stored procedure in order to get the id
> in the selectKey tag?
> 
> My stored proc is defined as :
> 
> CREATE OR REPLACE PROCEDURE SP_GET_NEXT_ID(nIDTYPE IN NUMBER, nNEXTID
> OUT NUMBER) as
> nIDTYPEBUF NUMBER;
> nTheNewId NUMBER;
> ...
> 
> Where
> 
> IN : The idtype to be generated
>         1,PERSONID
>         2,PERSONTYPEID
>         3,CATEGORYID
>         ...
> OUT : The next id for the type input (nIDTYPE)
>       -1 (Error in the IDTYPE input)
> 
> I know I can use an Oracle sequence, but the reason we are using a
> stored proc is because we don't want the DB client has to deal with a
> proprietary sequence.
> 
> So, is it possible to call that stored proc in the <selectKey> section?
> 
> Thanks,
> Adriano
>

Mime
View raw message