Quantcast

Stored Procedures in myBatis

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Stored Procedures in myBatis

Fadye
Hello, I've been looking all day but can't find a solution to my
problem, and it's not mentionned in the user manual either.
I'm trying to migrate from iBatis 2.4 to myBatis.
In my mapper files, I had declared stored procedures the following
way:

  <procedure id="getSingleBackupRow" resultMap =
"abatorgenerated_BackupResult" parameterType="Backup" >
      {call ps_getSingleBackupRow (#deviceId:BIGINT#)}
  </procedure>

myBatis is returning me the error:

### Error building SqlSession.
### The error may exist in com/.../mappers/Backup.xml
### Cause: org.apache.ibatis.builder.BuilderException: Error parsing
SQL Mapper Configuration. Cause:
org.apache.ibatis.builder.BuilderException: Error creating document
instance.  Cause: org.xml.sax.SAXParseException: Element type
"procedure" must be declared.

I looked at the mapper dtd and realized that procedure is not an
allowed tag anymore.
How should I then declare my stored procedures?
(My DB server is SQL Server 2005 if that matters)

Thank You
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Stored Procedures in myBatis

Jeff Butler
Use

<select statementType="CALLABLE" id="..." parameterType="...">
  {call  ... }
</select>


Jeff Butler


On Thu, Oct 21, 2010 at 2:59 PM, Fadye <[hidden email]> wrote:

> Hello, I've been looking all day but can't find a solution to my
> problem, and it's not mentionned in the user manual either.
> I'm trying to migrate from iBatis 2.4 to myBatis.
> In my mapper files, I had declared stored procedures the following
> way:
>
>  <procedure id="getSingleBackupRow" resultMap =
> "abatorgenerated_BackupResult" parameterType="Backup" >
>      {call ps_getSingleBackupRow (#deviceId:BIGINT#)}
>  </procedure>
>
> myBatis is returning me the error:
>
> ### Error building SqlSession.
> ### The error may exist in com/.../mappers/Backup.xml
> ### Cause: org.apache.ibatis.builder.BuilderException: Error parsing
> SQL Mapper Configuration. Cause:
> org.apache.ibatis.builder.BuilderException: Error creating document
> instance.  Cause: org.xml.sax.SAXParseException: Element type
> "procedure" must be declared.
>
> I looked at the mapper dtd and realized that procedure is not an
> allowed tag anymore.
> How should I then declare my stored procedures?
> (My DB server is SQL Server 2005 if that matters)
>
> Thank You
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Stored Procedures in myBatis

Jan Žanda
In reply to this post by Fadye
Hi!

Use

<select id="getSingleBackupRow" statementType="CALLABLE"> ... </
select>

instead.

Hope this kicks you the right way.

On 21 říj, 21:59, Fadye <[hidden email]> wrote:

> Hello, I've been looking all day but can't find a solution to my
> problem, and it's not mentionned in the user manual either.
> I'm trying to migrate from iBatis 2.4 to myBatis.
> In my mapper files, I had declared stored procedures the following
> way:
>
>   <procedure id="getSingleBackupRow" resultMap =
> "abatorgenerated_BackupResult" parameterType="Backup" >
>       {call ps_getSingleBackupRow (#deviceId:BIGINT#)}
>   </procedure>
>
> myBatis is returning me the error:
>
> ### Error building SqlSession.
> ### The error may exist in com/.../mappers/Backup.xml
> ### Cause: org.apache.ibatis.builder.BuilderException: Error parsing
> SQL Mapper Configuration. Cause:
> org.apache.ibatis.builder.BuilderException: Error creating document
> instance.  Cause: org.xml.sax.SAXParseException: Element type
> "procedure" must be declared.
>
> I looked at the mapper dtd and realized that procedure is not an
> allowed tag anymore.
> How should I then declare my stored procedures?
> (My DB server is SQL Server 2005 if that matters)
>
> Thank You
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Stored Procedures in myBatis

Fadye
In reply to this post by Jeff Butler
Thanks to both of you. It worked.

On Oct 21, 4:03 pm, Jeff Butler <[hidden email]> wrote:

> Use
>
> <select statementType="CALLABLE" id="..." parameterType="...">
>   {call  ... }
> </select>
>
> Jeff Butler
>
> On Thu, Oct 21, 2010 at 2:59 PM, Fadye <[hidden email]> wrote:
> > Hello, I've been looking all day but can't find a solution to my
> > problem, and it's not mentionned in the user manual either.
> > I'm trying to migrate from iBatis 2.4 to myBatis.
> > In my mapper files, I had declared stored procedures the following
> > way:
>
> >  <procedure id="getSingleBackupRow" resultMap =
> > "abatorgenerated_BackupResult" parameterType="Backup" >
> >      {call ps_getSingleBackupRow (#deviceId:BIGINT#)}
> >  </procedure>
>
> > myBatis is returning me the error:
>
> > ### Error building SqlSession.
> > ### The error may exist in com/.../mappers/Backup.xml
> > ### Cause: org.apache.ibatis.builder.BuilderException: Error parsing
> > SQL Mapper Configuration. Cause:
> > org.apache.ibatis.builder.BuilderException: Error creating document
> > instance.  Cause: org.xml.sax.SAXParseException: Element type
> > "procedure" must be declared.
>
> > I looked at the mapper dtd and realized that procedure is not an
> > allowed tag anymore.
> > How should I then declare my stored procedures?
> > (My DB server is SQL Server 2005 if that matters)
>
> > Thank You
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Stored Procedures in myBatis

Franco
Hi all,

I have two comments/questions concerning stored procedures and parameter maps:

(1)

What was actually the reason to remove the <procedure> and <statement> tags
from the MyBatis DTD? I make heavy use of these tags in iBatis (2.3.4.726).

For pure CRUD operations, it indeed makes more sense to use the <insert>,
<select>, <update>, and <delete> tags. By the way, I very much like
this statementType="STATEMENT|PREPARED|CALLABLE" attribute! I guess
this makes the difference between the formerly procedure/statement tags
and the insert/select/update/delete tags...

However, in my iBatis based code I use the procedure/statement tags to call
some arbitrary stored procedures that are not directly related to CRUD
operations - e.g. a stored procedure that does some DB maintenance stuff,
and another one that does CUD operations in one shot. For that, these
removed tags were more meaningful than squeezing CUD ops in either one
of <insert>, <update>, <delete>, or even a <select>. Well, in the end it
is probably just a naming issue. Anyway, I kind of miss these "catch all"
tags, even though it would probably be the same thing that would go on
behind the scenes.

No way to include at least the <statement> tag again, maybe rename it to
<routine> or something else that makes sense?

(2)

What about the parameterMap attribute, why was that removed? From the docs
I can see that these things can now be acomplished with @Param annotations,
but in the same docs I read that annotations are not that flexible, and some
things are not even possible unless you use .NET... So in MyBatis there is
no way to specify a parameterMap in XML, correct?

It is kind of confusing to have the resultMap defined in XML, and the mapping
of the parameters in the java code, isn't it? Wasn't the original idea of the
parameterMap attribute to act as kind of an interface to the java world? The
problem I see with annotations is that the mappings will be distributed all
over the java code... the mapping has to be repeated in each method that
uses a mapped statement, or did I get something completely wrong?

Would be happy if someone could enlighten me a bit ;-)

Greetings from Zurich/Switzerland,

Franco

On 22.10.2010 21:29, Fadye wrote:

> Thanks to both of you. It worked.
>
> On Oct 21, 4:03 pm, Jeff Butler<[hidden email]>  wrote:
>> Use
>>
>> <select statementType="CALLABLE" id="..." parameterType="...">
>>    {call  ... }
>> </select>
>>
>> Jeff Butler
>>
>> On Thu, Oct 21, 2010 at 2:59 PM, Fadye<[hidden email]>  wrote:
>>> Hello, I've been looking all day but can't find a solution to my
>>> problem, and it's not mentionned in the user manual either.
>>> I'm trying to migrate from iBatis 2.4 to myBatis.
>>> In my mapper files, I had declared stored procedures the following
>>> way:
>>
>>>   <procedure id="getSingleBackupRow" resultMap =
>>> "abatorgenerated_BackupResult" parameterType="Backup">
>>>       {call ps_getSingleBackupRow (#deviceId:BIGINT#)}
>>>   </procedure>
>>
>>> myBatis is returning me the error:
>>
>>> ### Error building SqlSession.
>>> ### The error may exist in com/.../mappers/Backup.xml
>>> ### Cause: org.apache.ibatis.builder.BuilderException: Error parsing
>>> SQL Mapper Configuration. Cause:
>>> org.apache.ibatis.builder.BuilderException: Error creating document
>>> instance.  Cause: org.xml.sax.SAXParseException: Element type
>>> "procedure" must be declared.
>>
>>> I looked at the mapper dtd and realized that procedure is not an
>>> allowed tag anymore.
>>> How should I then declare my stored procedures?
>>> (My DB server is SQL Server 2005 if that matters)
>>
>>> Thank You
>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Stored Procedures in myBatis

Jeff Butler
(1)
I can't speak to why.  I do think there was some confusion about
<procedure> if the procedure did any insert, update, or delete -
something like IB2 wouldn't commit in that case and you had to wrap it
in an <insert>, <update>, or <delete> anyway.  But I may be
misremembering that.

My recommendation is to use <select> for procedures that do not modify
the database (read only), and <update> for all others.

(2)
I believe parameterMap is still supported, but discouraged.  We found
a significant amount of user confusion over the parameter map and the
fact that you had to use ? instead of #param# syntax.  If I'd had my
way, we would have removed parameterMap altogether!  Inline parameters
in XML are just as functional and far more intuitive IMHO (you don't
have to use annotations).

Jeff Butler



On Sat, Oct 23, 2010 at 5:08 PM, Franco <[hidden email]> wrote:

> Hi all,
>
> I have two comments/questions concerning stored procedures and parameter
> maps:
>
> (1)
>
> What was actually the reason to remove the <procedure> and <statement> tags
> from the MyBatis DTD? I make heavy use of these tags in iBatis (2.3.4.726).
>
> For pure CRUD operations, it indeed makes more sense to use the <insert>,
> <select>, <update>, and <delete> tags. By the way, I very much like
> this statementType="STATEMENT|PREPARED|CALLABLE" attribute! I guess
> this makes the difference between the formerly procedure/statement tags
> and the insert/select/update/delete tags...
>
> However, in my iBatis based code I use the procedure/statement tags to call
> some arbitrary stored procedures that are not directly related to CRUD
> operations - e.g. a stored procedure that does some DB maintenance stuff,
> and another one that does CUD operations in one shot. For that, these
> removed tags were more meaningful than squeezing CUD ops in either one
> of <insert>, <update>, <delete>, or even a <select>. Well, in the end it
> is probably just a naming issue. Anyway, I kind of miss these "catch all"
> tags, even though it would probably be the same thing that would go on
> behind the scenes.
>
> No way to include at least the <statement> tag again, maybe rename it to
> <routine> or something else that makes sense?
>
> (2)
>
> What about the parameterMap attribute, why was that removed? From the docs
> I can see that these things can now be acomplished with @Param annotations,
> but in the same docs I read that annotations are not that flexible, and some
> things are not even possible unless you use .NET... So in MyBatis there is
> no way to specify a parameterMap in XML, correct?
>
> It is kind of confusing to have the resultMap defined in XML, and the
> mapping
> of the parameters in the java code, isn't it? Wasn't the original idea of
> the
> parameterMap attribute to act as kind of an interface to the java world? The
> problem I see with annotations is that the mappings will be distributed all
> over the java code... the mapping has to be repeated in each method that
> uses a mapped statement, or did I get something completely wrong?
>
> Would be happy if someone could enlighten me a bit ;-)
>
> Greetings from Zurich/Switzerland,
>
> Franco
>
> On 22.10.2010 21:29, Fadye wrote:
>>
>> Thanks to both of you. It worked.
>>
>> On Oct 21, 4:03 pm, Jeff Butler<[hidden email]>  wrote:
>>>
>>> Use
>>>
>>> <select statementType="CALLABLE" id="..." parameterType="...">
>>>   {call  ... }
>>> </select>
>>>
>>> Jeff Butler
>>>
>>> On Thu, Oct 21, 2010 at 2:59 PM, Fadye<[hidden email]>  wrote:
>>>>
>>>> Hello, I've been looking all day but can't find a solution to my
>>>> problem, and it's not mentionned in the user manual either.
>>>> I'm trying to migrate from iBatis 2.4 to myBatis.
>>>> In my mapper files, I had declared stored procedures the following
>>>> way:
>>>
>>>>  <procedure id="getSingleBackupRow" resultMap =
>>>> "abatorgenerated_BackupResult" parameterType="Backup">
>>>>      {call ps_getSingleBackupRow (#deviceId:BIGINT#)}
>>>>  </procedure>
>>>
>>>> myBatis is returning me the error:
>>>
>>>> ### Error building SqlSession.
>>>> ### The error may exist in com/.../mappers/Backup.xml
>>>> ### Cause: org.apache.ibatis.builder.BuilderException: Error parsing
>>>> SQL Mapper Configuration. Cause:
>>>> org.apache.ibatis.builder.BuilderException: Error creating document
>>>> instance.  Cause: org.xml.sax.SAXParseException: Element type
>>>> "procedure" must be declared.
>>>
>>>> I looked at the mapper dtd and realized that procedure is not an
>>>> allowed tag anymore.
>>>> How should I then declare my stored procedures?
>>>> (My DB server is SQL Server 2005 if that matters)
>>>
>>>> Thank You
>>
>
Loading...