Ibatis issue with SQL server procedure returning Cursor

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Ibatis issue with SQL server procedure returning Cursor

Priyanka Jain
Hi,

I am trying the fetch details returned by STored procedures using CURSOR. But it seems jdbcType="CURSOR" is not supported for sql server. Could you please alternate solution?

Thanks,
Priyanka

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Ibatis issue with SQL server procedure returning Cursor

Iwao AVE!

Hi Priyanka,

With MS SQL Server, a stored procedure can return result set(s).
Here is a simple example.

create procedure get_users as begin
  select id, name from users;
end
<resultMap type="User" id="userMap">
  <id property="id" column="id" />
  <result property="name" column="name" />
</resultMap>

<select id="callGetUsers" resultMap="userMap"
    statementType="CALLABLE">
  {call get_users}
</select>

Regards,
Iwao

2018-02-22 2:12 GMT+09:00 Priyanka Jain <[hidden email]>:

Hi,

I am trying the fetch details returned by STored procedures using CURSOR. But it seems jdbcType="CURSOR" is not supported for sql server. Could you please alternate solution?

Thanks,
Priyanka

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Ibatis issue with SQL server procedure returning Cursor

Priyanka Jain
Hi Iwao,

Thanks for the response. Do we have annotation based implementation for this. As with annotation, we have to mention jdbcType as well.

Thanks,
Priyanka

On Feb 23, 2018 10:55 PM, "Iwao AVE!" <[hidden email]> wrote:

Hi Priyanka,

With MS SQL Server, a stored procedure can return result set(s).
Here is a simple example.

create procedure get_users as begin
  select id, name from users;
end
<resultMap type="User" id="userMap">
  <id property="id" column="id" />
  <result property="name" column="name" />
</resultMap>

<select id="callGetUsers" resultMap="userMap"
    statementType="CALLABLE">
  {call get_users}
</select>

Regards,
Iwao

2018-02-22 2:12 GMT+09:00 Priyanka Jain <[hidden email]>:

Hi,

I am trying the fetch details returned by STored procedures using CURSOR. But it seems jdbcType="CURSOR" is not supported for sql server. Could you please alternate solution?

Thanks,
Priyanka

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to a topic in the Google Groups "mybatis-user" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/mybatis-user/q0qUSTmRPOo/unsubscribe.
To unsubscribe from this group and all its topics, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Ibatis issue with SQL server procedure returning Cursor

Iwao AVE!

The simplest case is not much different from normal SELECT, so annotation version would look as follows.

@Results({
  @Result(property = "id", column = "id", id = true),
  @Result(property = "name", column = "name")
})
@Select("{call getUsers}")
@Options(statementType = StatementType.CALLABLE)
List<User> getSingleResultSetAnno();

Please post the details if you want more specific answers.

// Iwao

2018-02-24 13:00 GMT+09:00 Priyanka Jain <[hidden email]>:

Hi Iwao,

Thanks for the response. Do we have annotation based implementation for this. As with annotation, we have to mention jdbcType as well.

Thanks,
Priyanka

On Feb 23, 2018 10:55 PM, "Iwao AVE!" <[hidden email]> wrote:

Hi Priyanka,

With MS SQL Server, a stored procedure can return result set(s).
Here is a simple example.

create procedure get_users as begin
  select id, name from users;
end
<resultMap type="User" id="userMap">
  <id property="id" column="id" />
  <result property="name" column="name" />
</resultMap>

<select id="callGetUsers" resultMap="userMap"
    statementType="CALLABLE">
  {call get_users}
</select>

Regards,
Iwao

2018-02-22 2:12 GMT+09:00 Priyanka Jain <[hidden email]>:

Hi,

I am trying the fetch details returned by STored procedures using CURSOR. But it seems jdbcType="CURSOR" is not supported for sql server. Could you please alternate solution?

Thanks,
Priyanka

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to a topic in the Google Groups "mybatis-user" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/mybatis-user/q0qUSTmRPOo/unsubscribe.
To unsubscribe from this group and all its topics, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Ibatis issue with SQL server procedure returning Cursor

Erwan Letessier
Hi
I have found out that unlike Oracle: 
stored procedures with cursor OUTPUT parameters cannot be called from the database APIs. These procedures can be called from Transact-SQL batches, stored procedures, or triggers only when the cursor OUTPUT variable is assigned to a Transact-SQL local cursor variable.

Not found date for article but it seems not that old.

Cheers
Erwan 

On Feb 24, 2018 08:58, "Iwao AVE!" <[hidden email]> wrote:

The simplest case is not much different from normal SELECT, so annotation version would look as follows.

@Results({
  @Result(property = "id", column = "id", id = true),
  @Result(property = "name", column = "name")
})
@Select("{call getUsers}")
@Options(statementType = StatementType.CALLABLE)
List<User> getSingleResultSetAnno();

Please post the details if you want more specific answers.

// Iwao

2018-02-24 13:00 GMT+09:00 Priyanka Jain <[hidden email]>:

Hi Iwao,

Thanks for the response. Do we have annotation based implementation for this. As with annotation, we have to mention jdbcType as well.

Thanks,
Priyanka

On Feb 23, 2018 10:55 PM, "Iwao AVE!" <[hidden email]> wrote:

Hi Priyanka,

With MS SQL Server, a stored procedure can return result set(s).
Here is a simple example.

create procedure get_users as begin
  select id, name from users;
end
<resultMap type="User" id="userMap">
  <id property="id" column="id" />
  <result property="name" column="name" />
</resultMap>

<select id="callGetUsers" resultMap="userMap"
    statementType="CALLABLE">
  {call get_users}
</select>

Regards,
Iwao

2018-02-22 2:12 GMT+09:00 Priyanka Jain <[hidden email]>:

Hi,

I am trying the fetch details returned by STored procedures using CURSOR. But it seems jdbcType="CURSOR" is not supported for sql server. Could you please alternate solution?

Thanks,
Priyanka

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to a topic in the Google Groups "mybatis-user" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/mybatis-user/q0qUSTmRPOo/unsubscribe.
To unsubscribe from this group and all its topics, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.