Calling stored procedure to return back a CURSOR.

[Rajkumar Parthasarathi]

Hi Jeff/Carsten,

Could you please provide us a sample code with mapper xml, mapper.java
and test call to achieve a OUT param as a ResultsSet from a stored
procedure returning a CURSOR.
Stored procedure will be used to retrieve a list of custom object,
where stored proc is returning back .

Your help in this would be great for mybatis user community....


Thanks,
Raj

[Jeff Butler]

I can't do a full example now, but it should be fairly simple...

Define your ref cursor output parameter like this:

#{department,mode=OUT,
jdbcType=CURSOR,
javaType=java.sql.ResultSet,
resultMap=departmentResultMap}

The "resultMap" attribute specifies the name of the result map that
MyBatis will use to process the ref cursor. In your parameter class,
the "department" attribute would be List<Department>.

Jeff Butler

[Rajkumar Parthasarathi]

Thanks a lot Jeff.

We will try this out today, and it looks like will be possible.

Cheers,

Raj

[Rajkumar Parthasarathi]

Jeff,

Your inputs on following would help us a lot in calling stored proc returning Cursor.

Our database is DB2 and we get a error saying jdbc type Cursor is not supported. Does it mean that DB2 jdbc driver does not support it or MyBatis does not support DB2 cursor. Please let us know.

Is there a possibility of getting back multiple results sets from stored procedure?  We tried with one and it works fine. We would like to know how the XML needs to map ResultMap for it.

Shall we pass a custom java object to stored procedure instead of passing multiple in params? If so, please provide us with samples.

Your inputs on these will help us a lot in using stored procedure.

Thanks in advance.

Cheers,

Raj

[Jeff Butler]

I believe that cursors as out parameters is an Oracle specific
feature. I'm not sure if DB2 supports them or not - I'm thinking not.

Multiple result sets are supported, just pass a comma delimited set of
result maps or object:

<select ... resultMap="map1, map2, map3">

I can't write your entire application for you :) If you want to pass
a custom object, it will require a custom type handler and some
special code on both sides - this sounds like a good research project
for you to do.

Jeff Butler

On Wed, Nov 10, 2010 at 8:47 AM, Rajkumar Parthasarathi

[Guy Rouillier]

On 11/10/2010 9:47 AM, Rajkumar Parthasarathi wrote:
> Our database is DB2 and we get a error saying jdbc type Cursor is not
> supported. Does it mean that DB2 jdbc driver does not support it or
> MyBatis does not support DB2 cursor. Please let us know.

Handling of cursors returned from stored proc is very much dependent on
the RDBMS and on the JDBC driver for that RDBMS. For example, I tried
running a program on PostgreSQL that I had written against Oracle that
returned a cursor from a stored proc. Didn't work because the
PostgreSQL JDBC driver didn't return type jdbcType CURSOR but type UNKNOWN.

Debug your code and observe what type DB2 is returning.

--
Guy Rouillier

[Rajkumar Parthasarathi]

Hi Jeff,

Thanks for responding.

That's true. We noticed that jdbctype enum have a comment alongside of CURSOR as ' // ORACLE' . Tha's why i got a doubt if it will support DB2.

Yes, in this project we are trying to make all the DB calls as stored procedure (making DB to just expose methods, hence abstracting SQL queries from java part) and that is why we are stuck in lot of issues. In earlier projects we have used all direct CRUD calls to DB using IBatis and it works seamlessly with out any issues. 

I guess after this project we will be able to understand the depth of MyBatis :-). Anyhow we will require your help in moving forward. Thanks.

Cheers,

Raj

[Mario Ds Briggs]

I am not sure which version of DB2 started supporting output parameters as
Cursor, but 9.7 for sure does.

http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.apdv.java.doc/doc/t0054848.html

regards
Mario

Rajkumar
Parthasarathi
<pra...@gmail.co To
m> mybati...@googlegroups.com
Sent by: cc
mybatis-user@goog
legroups.com Subject
Re: Calling stored procedure to
return back a CURSOR.
11/10/2010 08:17
PM


Please respond to
mybatis-user@goog
legroups.com