> I made a mistake: Line #{returnValue,mode=OUT,jdbcTypeName=NUMERIC,javaType=java.lang.Integer}
> must be converted into #{returnValue,mode=OUT,jdbcType
> =NUMERIC,javaType=java.lang.Integer}

> Dana subota, 6. listopada 2012. 23:35:44 UTC+2, korisnik AntPort napisao
> je:

>> 1.Type handler must be defined for every parameter. Here is one example:
>> if we have wrapperObject having properties listOfMaps1,2,3 (mentioned in
>> my post above) and returnValue  map snippet would look like this:

>> <select id="callMyProcedure" statementType="CALLABLE"
>> parameterType="wrapperObject">
>> call
>> my_proc(#{listOfMaps1,mode=IN,typeHandler=package.Param1TypeHandler},
>>                         #{listOfMaps2,mode=IN,typeHandler=package.
>> Param2TypeHandler},
>>                         #{listOfMaps3,mode=IN,typeHandler=package.
>> Param3TypeHandler},
>>                         #
>> {returnValue,mode=OUT,jdbcTypeName=NUMERIC,javaType=java.lang.Integer})
>> </select>
>> If you have multiple parameters, you have to specify type handler for
>> every one of them.

>> 2. No. Only if you use same type handler for IN and OUT parameters.
>> Integer doesn't need specific type handler, just define OUT parameter like
>> in the example above.

>> Dana petak, 5. listopada 2012. 16:30:47 UTC+2, korisnik grimmel97 napisao
>> je:

>>> Hi,

>>> Thank you again for your time in trying to explain this to me.  I will
>>> try to digest this and write the TypeHandler tomorrow.  However for now I
>>> have a few more questions.

>>> 1. If I have to create a TypeHandler for each of my parameters, then in
>>> my original example I would have 3 type handlers, say

>>> Param1TypeHandler

>>> Param2TypeHandler

>>> Param3TypeHandler

>>> Can I specify more than one TypeHandler for the MyBatis SQLMap?

>>> <update id="callOracleSP" statementType="CALLABLE" typeHandler="Param1TypeHandler,Param2TypeHandler,Param3TypeHAndler">
>>>     {CALL my_proc( #{param1, mode=IN},
>>>                    #{param2, mode=IN},
>>>                    #{param3, mode=IN}

>>>                  )
>>>     }
>>> </update>

>>> 2. I am actually returning a single Integer value from an OUT parameter
>>> in my procedure; Do I have to write an implementation for the getResult in
>>> this case?

>>> 3. Yes, I am using the older Oracle jdbc driver, so will upgrade it in
>>> my project and see how it goes.

>>> Thank you so much for your time.  I will try and write a single type
>>> handler tomorrow and try to get it to compile.

>>> On Friday, 5 October 2012 16:09:11 UTC+10, AntPort wrote:

>>>> 1. Yes, you have to implement TypeHandler<T> interface.

>>>> public interface TypeHandler<T> {

>>>>   public void setParameter(PreparedStatement ps, int i, T parameter,
>>>> JdbcType jdbcType)
>>>>       throws SQLException;

>>>>   public T getResult(ResultSet rs, String columnName)
>>>>       throws SQLException;

>>>>   public T getResult(CallableStatement cs, int columnIndex)
>>>>       throws SQLException;

>>>> }

>>>> If you are not returning anything from your procedure, you can leave
>>>> two getResult methods empty.

>>>> 2. No, you have to do something like this:
>>>> If these are the POJO properties:

>>>> IdReservation
>>>> IdBranch
>>>> IAgn
>>>> NapAgn

>>>> ...then you have to do something like this:

>>>> List<Map<String,Object>> listOfPojoMaps = new
>>>> ArrayList<Map<String,Object>>();
>>>> Map<String,Object> pojoMap = new LinkedHashMap<String,Object>();

>>>> // keys correspond to the properties of the I_RESERVATION_TYPE

>>>> pojoMap.put("ID_RESERVATION",pojo.getIdReservation);
>>>> pojoMap.put("ID_BRANCH",pojo.getIdReservation);
>>>> pojoMap.put("ID_AGN",pojo.getIdReservation);
>>>> pojoMap.put("NAP_AGN",pojo.getIdReservation)

>>>> listOfPojoMaps.add(pojoMap);

>>>> The listOfPojoMaps is the property you set as an input parameter in
>>>> your stored procedure call.

>>>> 3. Every map from the listOfPojoMaps creates one STRUCT object. Since
>>>> we have I_REZERVATION_ARRAY as an array of objects in the database,we
>>>> have to create STRUCT[] array which we pass as a parameter to the
>>>> prepairedStatement. If we didn't have that, we could send only one
>>>> parameter/pojo to the database.
>>>> Remember, you are not sending pojos to typehandler, just it's values
>>>> wrapped in a list of maps.

>>>> 4. In essence, yes, you will have to create typeHandler for your every
>>>> type in the database, although you can do something like this:

>>>> public class StoredProcedurePojo {

>>>> private ArrayList<LinkedHashMap<String, Object>> listOfPojoMaps;
>>>> private String typeName;
>>>> private String arrayName;

>>>>         //getters / setters

>>>> }

>>>> You can send this wrapper as an input parameter to your typeHandler but
>>>> you have to modify the code snippet mentioned in my first post to:

>>>>                 StoredProcedurePojo wrapperPojo = (StoredProcedurePojo)
>>>> passedValue;

>>>> List<LinkedHashMap<String,Object>> inParams =
>>>> wrapperPojo.getListOfPojoMaps();

>>>> STRUCT[] structArray = new STRUCT[inParams.size()];
>>>> conn = ps.getConnection();
>>>> StructDescriptor structDesc = StructDescriptor.createDescriptor(
>>>> wrapperPojo.getTypeName(), conn);
>>>> ArrayDescriptor arrayDesc = ArrayDescriptor.createDescriptor(
>>>> wrapperPojo.getArrayName(), conn);

>>>> 5. Regarding the compiler errors....Are you using oracle.sql.STRUCT
>>>> .STRUCT(StructDescriptor arg0, Connection arg1, Map arg2) ?

>>>> P.S.

>>>> *It is important that you use ojdbc6 oracle driver, ojdbc14 doesn't
>>>> seem to work...*
>>>> *
>>>> *
>>>> *
>>>> *
>>>> I' m not sure if this way is the simplest or the best approach, but it
>>>> works in our environment...*
>>>> *

>>>> Dana petak, 5. listopada 2012. 03:16:00 UTC+2, korisnik grimmel97
>>>> napisao je:

>>>>> Thank you AntPort for taking the time to reply.  Apologies for the
>>>>> additional questions, however if you haven't already guessed, I am new to
>>>>> this, so are you able to clarify for me:

>>>>> 1. When I create the TypeHandler, do I just implement the MyBatis
>>>>> TypeHandler?

>>>>> 2. I can't get my head around the code-listing you have in the
>>>>> TypeHandler.  So say I have a pojo IReservation that matches your
>>>>> I_RESERVATION_TYPE with the properties:

>>>>> IdReservation
>>>>> IdBranch
>>>>> IAgn
>>>>> NapAgn

>>>>> and my application creates and populates a list of IReservation:
>>>>> List<IReservation> and I want to pass the entire list as an Oracle, how do
>>>>> I incorporate this into the TypeHandler code example you have provided?

>>>>> 3. Could you explain why the STRUCT[] array is required?  Is that
>>>>> because that is the type of array that Oracle supports?

>>>>> 4. Are you saying that I would have to create a TypeHandler for each
>>>>> parameter in my example?

>>>>> Many thanks.

>>>>> On Wednesday, 3 October 2012 11:55:06 UTC+10, grimmel97 wrote:

>>>>>> Hi,

>>>>>> I have been googling this for a while and cannot seem to find any
>>>>>> real answers.

>>>>>> I have an Oracle stored procedure that has a number of in parameters
>>>>>> that have a type that is table of the table rowtype.  So for example:

>>>>>> *Declared in the pacakge:*
>>>>>> TYPE param1_type_t IS TABLE OF table1%ROWTYPE;
>>>>>> TYPE param2_type_t IS TABLE OF table2%ROWTYPE;
>>>>>> TYPE param3_type_t IS TABLE OF table3%ROWTYPE;

>>>>>> *Procedure:*
>>>>>> PROCEDURE my_proc
>>>>>> (
>>>>>>    parameter1    IN param1_type_t,
>>>>>>    parameter2    IN param2_type_t,
>>>>>>    parameter3    IN param3_type_t
>>>>>> )

>>>>>> On the java side, I have 3 corresponding POJO objects representing
>>>>>> each of the parameters and the code creates a java List of each of the
>>>>>> POJOs.  Is it possible to call the Oracle procedure using MyBatis in this
>>>>>> scenario?

>>>>>> If so, how would the SQLMap be written?  Or do I need to write more
>>>>>> objects to cater for this scenario?

>>>>>> Thanks in advance.