Re: Need support for Dynamic procedure invocation

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

Re: Need support for Dynamic procedure invocation

Nicky Jha

Hi

with approach mentioned below(DynamicProcedureParams) , I want to also pass some of parameter as null((private List<Object> params = new ArrayList<Object>(); params.add(null))), but when I pass string value as null , it executes parametes procedure as

call procname(?,?,?,?,?)
Parameters: [#IMNT_RISK_SENSITIVITY, IMNT_RISK_SENSITIVITY, null, null, 0]
Types: [java.lang.String, java.lang.String, null, null, java.lang.Byte]

I want type to be java.lang.String instead of null(as with null, I am getting Unsupported SQL type 0 )

If I pass blank string "" in place of null in (private List<Object> params = new ArrayList<Object>(); params.add("")), Types become java.lang.String, but then I think it no more considers it as null.

Please help

Thanks
Nicky



-----Original Message-----
From: Nicky Jha
Sent: Friday, May 14, 2010 8:46 PM
To: '[hidden email]'
Subject: RE: Need support for Dynamic procedure invocation

Joe/Jeff

This worked with your suggestion.Thank you so much!!!

Nicky

-----Original Message-----
From: Jeff Butler [mailto:[hidden email]]
Sent: Friday, May 14, 2010 6:31 PM
To: [hidden email]
Subject: Re: Need support for Dynamic procedure invocation

Yes - this the best approach.

Jeff Butler


On 5/14/10, Joe Gooch <[hidden email]> wrote:

> I suggest going with a dynamic SQL approach in the mapped statement.
>
> public class DynamicProcedureParams {
>   private String procedureName;
>   private List<Object> params = new ArrayList<Object>();
>
>   // getters and setters here
> }
>
> <procedure id="executeCopyProcs"
> parameterClass="path.to.DynamicProcedureParams">
> {call $procedureName$ <iterate property="params" open="(" close=")"
> conjunction=",">#params[]#</iterate> }
> </procedure>
>
>
>
> Joe
>
> Confidentiality Notice:
> This e-mail transmission may contain confidential and legally privileged
> information that is intended only for the individual named in the e-mail
> address. If you are not the intended recipient, you are hereby notified that
> any disclosure, copying, distribution, or reliance upon the contents of this
> e-mail message is strictly prohibited. If you have received this e-mail
> transmission in error, please reply to the sender, so that proper delivery
> can be arranged, and please delete the message from your mail box.
>
>> -----Original Message-----
>> From: Nicky Jha [mailto:[hidden email]]
>> Sent: Friday, May 14, 2010 7:38 AM
>> To: [hidden email]
>> Subject: RE: Need support for Dynamic procedure invocation
>>
>> Hi Jeff,
>>
>> Thanks for this, if I was to do as suggested and compose the entire
>> string with parameter values, how will the parameter types (e.g. date,
>> smallint etc) be handled?
>>
>> Nicky!
>>
>> -----Original Message-----
>> From: Jeff Butler [mailto:[hidden email]]
>> Sent: Friday, May 14, 2010 4:53 PM
>> To: [hidden email]
>> Subject: Re: Need support for Dynamic procedure invocation
>>
>> This won't work (as you've discovered).  iBATIS 2.x does not reparse
>> the string for variables after string substitution.  You'll need to do
>> this with the dynamic tags, or you'll need to compose the entire
>> string (including parameter values - like {call myproc('fred')}).
>>
>> Jeff Butler
>>
>>
>> On Fri, May 14, 2010 at 4:12 AM, Nicky Jha <[hidden email]>
>> wrote:
>> > Hi Team,
>> >
>> >
>> >
>> > I am having hard time resolving following issue.Please help.
>> >
>> > We are using Ibatis 2.X.
>> >
>> > In our application we want capability to invoke stored procedure by
>> reading
>> > it from property xml file.We provide procedure name parameters,
>> parameters
>> > type to  property xml file.
>> >
>> > Now from this xml file I have created one dynamic procedure string
>> like
>> >
>> procedureName(#stringVal1#,#stringVal2#,#stringVal3#,#intVal1#,#byteVal
>> 1#).
>> >
>> > Now I want to call this procedure from Ibatis SQL mapping XML like
>> this
>> >
>> >
>> >
>> > <procedure id="executeCopyProcs"
>> >
>> parameterClass="com.jpmorgan.pyramid.pyrsyst.configure.ProcParameterMap
>> per">
>> >
>> > {call $procedureName$ }
>> >
>> >
>> >
>> > </procedure>
>> >
>> >
>> >
>> > Please refer below for ProcParameterMapper class. Now as soon as
>> iBatis sees
>> >  $procedureName$, it replaces it with say
>> > (#stringVal1#,#stringVal2#,#stringVal3#,#intVal1#,#byteVal1#) but it
>> does
>> > not replace the placeHolder(#), it passes on this as it is.Which is a
>> issue.
>> >
>> >
>> >
>> > If we type
>> >
>> procedureName(#stringVal1#,#stringVal2#,#stringVal3#,#intVal1#,#byteVal
>> 1)
>> > directly into SQL mapping XML, it replaces place holder, but with
>> string
>> > substitution , it does not work.We can't type directly into mapping
>> xML, as
>> > this string is composed at run time.Also dynamic tags are not of
>> help, as
>> > logic to create
>> > (#stringVal1#,#stringVal2#,#stringVal3#,#intVal1#,#byteVal1#) complex
>> > procedure with different types of parameter can't be written in
>> mapping
>> > XML.At least I am unable to do.
>> >
>> >
>> >
>> > I am really struck.Please suggest us the best way to deal with it.
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> > package com.jpmorgan.pyramid.pyrsyst.configure;
>> >
>> >
>> >
>> > import java.lang.reflect.Field;
>> >
>> >
>> >
>> > public class ProcParameterMapper {
>> >
>> >
>> >
>> >       StringBuffer procedureName = new StringBuffer("");
>> >
>> >       boolean firstParam = true;
>> >
>> >       boolean lastParam = false;
>> >
>> >
>> >
>> >       String stringVal1;
>> >
>> >       String stringVal2;
>> >
>> >       String stringVal3;
>> >
>> >       String stringVal4;
>> >
>> >       String stringVal5;
>> >
>> >       String stringVal6;
>> >
>> >       String stringVal7;
>> >
>> >       String stringVal8;
>> >
>> >       String stringVal9;
>> >
>> >       String stringVal10;
>> >
>> >
>> >
>> >       int intVal1;
>> >
>> >       int intVal2;
>> >
>> >       int intVal3;
>> >
>> >       int intVal4;
>> >
>> >       int intVal5;
>> >
>> >       int intVal6;
>> >
>> >       int intVal7;
>> >
>> >       int intVal8;
>> >
>> >       int intVal9;
>> >
>> >       int intVal10;
>> >
>> >
>> >
>> >       byte byteVal1;
>> >
>> >       byte byteVal2;
>> >
>> >       byte byteVal3;
>> >
>> >       byte byteVal4;
>> >
>> >       byte byteVal5;
>> >
>> >
>> >
>> >       public void setStringVal(String value, int count,String
>> jdbcType)
>> > throws ConfigureException {
>> >
>> >
>> >
>> >             Field[] field =
>> ProcParameterMapper.class.getDeclaredFields();
>> >
>> >
>> >
>> >             for (int i = 0; i < field.length; i++) {
>> >
>> >                   try {
>> >
>> >                         if
>> > (field[i].getName().endsWith(String.valueOf(count))
>> >
>> >                                     && ("String")
>> >
>> >
>> > .equals(field[i].getType().getSimpleName())) {
>> >
>> >                               field[i].set(this, value);
>> >
>> >                               if (firstParam) {
>> >
>> >                                     procedureName.append("(#" +
>> > field[i].getName()+"#");
>> >
>> >                                     firstParam = false;
>> >
>> >                               } else if (lastParam) {
>> >
>> >                                     procedureName.append(",#" +
>> > field[i].getName() +"#)");
>> >
>> >                               } else {
>> >
>> >                                     procedureName.append(",#" +
>> > field[i].getName()+"#");
>> >
>> >                               }
>> >
>> >                               break;
>> >
>> >
>> >
>> >                         }
>> >
>> >                   } catch (Exception e) {
>> >
>> >                         throw new ConfigureException(
>> >
>> >                                     "Exception setting String value
>> in
>> > paramMapper"
>> >
>> >                                                 + e.getStackTrace());
>> >
>> >                   }
>> >
>> >
>> >
>> >             }
>> >
>> >
>> >
>> >       }
>> >
>> >
>> >
>> >       public void setIntVal(int value, int count,String jdbcType)
>> throws
>> > ConfigureException {
>> >
>> >
>> >
>> >             Field[] field =
>> ProcParameterMapper.class.getDeclaredFields();
>> >
>> >
>> >
>> >             for (int i = 0; i < field.length; i++) {
>> >
>> >                   try {
>> >
>> >                         if
>> > (field[i].getName().endsWith(String.valueOf(count))
>> >
>> >                                     &&
>> > ("int").equals(field[i].getType().getSimpleName())) {
>> >
>> >
>> >
>> >                               field[i].set(this, value);
>> >
>> >                               if (firstParam) {
>> >
>> >                                     procedureName.append("(#" +
>> > field[i].getName()+"#");
>> >
>> >                                     firstParam = false;
>> >
>> >                               } else if (lastParam) {
>> >
>> >                                     procedureName.append(",#" +
>> > field[i].getName() +"#)");
>> >
>> >                               } else {
>> >
>> >                                     procedureName.append(",#" +
>> > field[i].getName()+"#");
>> >
>> >                               }
>> >
>> >                               break;
>> >
>> >                         }
>> >
>> >                   } catch (Exception e) {
>> >
>> >                         throw new ConfigureException(
>> >
>> >                                     "Exception setting int value in
>> > paramMapper"
>> >
>> >                                                 + e.getStackTrace());
>> >
>> >                   }
>> >
>> >
>> >
>> >             }
>> >
>> >
>> >
>> >       }
>> >
>> >
>> >
>> >       public void setByteVal(Byte value, int count,String jdbcType)
>> throws
>> > ConfigureException {
>> >
>> >
>> >
>> >             Field[] field =
>> ProcParameterMapper.class.getDeclaredFields();
>> >
>> >
>> >
>> >             for (int i = 0; i < field.length; i++) {
>> >
>> >                   try {
>> >
>> >                         if
>> > (field[i].getName().endsWith(String.valueOf(count))
>> >
>> >                                     &&
>> > ("byte").equals(field[i].getType().getSimpleName())) {
>> >
>> >
>> >
>> >                               field[i].set(this, value);
>> >
>> >                               if (firstParam) {
>> >
>> >                                     procedureName.append("(#" +
>> > field[i].getName()+"#");
>> >
>> >                                     firstParam = false;
>> >
>> >                               } else if (lastParam) {
>> >
>> >                                     procedureName.append(",#" +
>> > field[i].getName() + "#)");
>> >
>> >                               } else {
>> >
>> >                                     procedureName.append(",#" +
>> > field[i].getName()+"#");
>> >
>> >                               }
>> >
>> >                               break;
>> >
>> >                         }
>> >
>> >                   } catch (Exception e) {
>> >
>> >                         throw new ConfigureException(
>> >
>> >                                     "Exception setting byte value in
>> > paramMapper"
>> >
>> >                                                 + e.getStackTrace());
>> >
>> >                   }
>> >
>> >
>> >
>> >             }
>> >
>> >
>> >
>> >       }
>> >
>> >
>> >
>> >       public String toString() {
>> >
>> >
>> >
>> >             return new String("stringVal1 is" + stringVal1 +
>> "stringVal2 is"
>> >
>> >                         + stringVal2 + "stringVal3 is" + stringVal3 +
>> > "intVal1 is"
>> >
>> >                         + intVal1 + "intVal2 is" + intVal2 + "intVal3
>> is" +
>> > intVal3);
>> >
>> >
>> >
>> >       }
>> >
>> >
>> >
>> >       public void setProcedure(String procName) {
>> >
>> >             procedureName.append(procName);
>> >
>> >
>> >
>> >       }
>> >
>> >
>> >
>> >       public String getProcedure(){
>> >
>> >             return procedureName.toString();
>> >
>> >       }
>> >
>> >
>> >
>> > }
>> >
>> >
>> >
>> > May thanks in advance
>> >
>> > Waiting for reply
>> >
>> > Nicky Jha
>> >
>> >
>> >
>> > This communication is for informational purposes only. It is not
>> intended as
>> > an offer or solicitation for the purchase or sale of any financial
>> > instrument or as an official confirmation of any transaction. All
>> market
>> > prices, data and other information are not warranted as to
>> completeness or
>> > accuracy and are subject to change without notice. Any comments or
>> > statements made herein do not necessarily reflect those of JPMorgan
>> Chase &
>> > Co., its subsidiaries and affiliates. This transmission may contain
>> > information that is privileged, confidential, legally privileged,
>> and/or
>> > exempt from disclosure under applicable law. If you are not the
>> intended
>> > recipient, you are hereby notified that any disclosure, copying,
>> > distribution, or use of the information contained herein (including
>> any
>> > reliance thereon) is STRICTLY PROHIBITED. Although this transmission
>> and any
>> > attachments are believed to be free of any virus or other defect that
>> might
>> > affect any computer system into which it is received and opened, it
>> is the
>> > responsibility of the recipient to ensure that it is virus free and
>> no
>> > responsibility is accepted by JPMorgan Chase & Co., its subsidiaries
>> and
>> > affiliates, as applicable, for any loss or damage arising in any way
>> from
>> > its use. If you received this transmission in error, please
>> immediately
>> > contact the sender and destroy the material in its entirety, whether
>> in
>> > electronic or hard copy format. Thank you. Please refer to
>> > http://www.jpmorgan.com/pages/disclosures for disclosures relating to
>> > European legal entities.
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [hidden email]
>> For additional commands, e-mail: [hidden email]
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>

--
Sent from my mobile device

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

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

Re: Need support for Dynamic procedure invocation

Clinton Begin
Are you setting the jdbcType in your parameter map for all nullable columns?

Clinton

On Mon, May 24, 2010 at 11:03 PM, Nicky Jha <[hidden email]> wrote:

Hi

with approach mentioned below(DynamicProcedureParams) , I want to also pass some of parameter as null((private List<Object> params = new ArrayList<Object>(); params.add(null))), but when I pass string value as null , it executes parametes procedure as

call procname(?,?,?,?,?)
Parameters: [#IMNT_RISK_SENSITIVITY, IMNT_RISK_SENSITIVITY, null, null, 0]
Types: [java.lang.String, java.lang.String, null, null, java.lang.Byte]

I want type to be java.lang.String instead of null(as with null, I am getting Unsupported SQL type 0 )

If I pass blank string "" in place of null in (private List<Object> params = new ArrayList<Object>(); params.add("")), Types become java.lang.String, but then I think it no more considers it as null.

Please help

Thanks
Nicky



-----Original Message-----
From: Nicky Jha
Sent: Friday, May 14, 2010 8:46 PM
To: '[hidden email]'
Subject: RE: Need support for Dynamic procedure invocation

Joe/Jeff

This worked with your suggestion.Thank you so much!!!

Nicky

-----Original Message-----
From: Jeff Butler [mailto:[hidden email]]
Sent: Friday, May 14, 2010 6:31 PM
To: [hidden email]
Subject: Re: Need support for Dynamic procedure invocation

Yes - this the best approach.

Jeff Butler


On 5/14/10, Joe Gooch <[hidden email]> wrote:
> I suggest going with a dynamic SQL approach in the mapped statement.
>
> public class DynamicProcedureParams {
>   private String procedureName;
>   private List<Object> params = new ArrayList<Object>();
>
>   // getters and setters here
> }
>
> <procedure id="executeCopyProcs"
> parameterClass="path.to.DynamicProcedureParams">
> {call $procedureName$ <iterate property="params" open="(" close=")"
> conjunction=",">#params[]#</iterate> }
> </procedure>
>
>
>
> Joe
>
> Confidentiality Notice:
> This e-mail transmission may contain confidential and legally privileged
> information that is intended only for the individual named in the e-mail
> address. If you are not the intended recipient, you are hereby notified that
> any disclosure, copying, distribution, or reliance upon the contents of this
> e-mail message is strictly prohibited. If you have received this e-mail
> transmission in error, please reply to the sender, so that proper delivery
> can be arranged, and please delete the message from your mail box.
>
>> -----Original Message-----
>> From: Nicky Jha [mailto:[hidden email]]
>> Sent: Friday, May 14, 2010 7:38 AM
>> To: [hidden email]
>> Subject: RE: Need support for Dynamic procedure invocation
>>
>> Hi Jeff,
>>
>> Thanks for this, if I was to do as suggested and compose the entire
>> string with parameter values, how will the parameter types (e.g. date,
>> smallint etc) be handled?
>>
>> Nicky!
>>
>> -----Original Message-----
>> From: Jeff Butler [mailto:[hidden email]]
>> Sent: Friday, May 14, 2010 4:53 PM
>> To: [hidden email]
>> Subject: Re: Need support for Dynamic procedure invocation
>>
>> This won't work (as you've discovered).  iBATIS 2.x does not reparse
>> the string for variables after string substitution.  You'll need to do
>> this with the dynamic tags, or you'll need to compose the entire
>> string (including parameter values - like {call myproc('fred')}).
>>
>> Jeff Butler
>>
>>
>> On Fri, May 14, 2010 at 4:12 AM, Nicky Jha <[hidden email]>
>> wrote:
>> > Hi Team,
>> >
>> >
>> >
>> > I am having hard time resolving following issue.Please help.
>> >
>> > We are using Ibatis 2.X.
>> >
>> > In our application we want capability to invoke stored procedure by
>> reading
>> > it from property xml file.We provide procedure name parameters,
>> parameters
>> > type to  property xml file.
>> >
>> > Now from this xml file I have created one dynamic procedure string
>> like
>> >
>> procedureName(#stringVal1#,#stringVal2#,#stringVal3#,#intVal1#,#byteVal
>> 1#).
>> >
>> > Now I want to call this procedure from Ibatis SQL mapping XML like
>> this
>> >
>> >
>> >
>> > <procedure id="executeCopyProcs"
>> >
>> parameterClass="com.jpmorgan.pyramid.pyrsyst.configure.ProcParameterMap
>> per">
>> >
>> > {call $procedureName$ }
>> >
>> >
>> >
>> > </procedure>
>> >
>> >
>> >
>> > Please refer below for ProcParameterMapper class. Now as soon as
>> iBatis sees
>> >  $procedureName$, it replaces it with say
>> > (#stringVal1#,#stringVal2#,#stringVal3#,#intVal1#,#byteVal1#) but it
>> does
>> > not replace the placeHolder(#), it passes on this as it is.Which is a
>> issue.
>> >
>> >
>> >
>> > If we type
>> >
>> procedureName(#stringVal1#,#stringVal2#,#stringVal3#,#intVal1#,#byteVal
>> 1)
>> > directly into SQL mapping XML, it replaces place holder, but with
>> string
>> > substitution , it does not work.We can't type directly into mapping
>> xML, as
>> > this string is composed at run time.Also dynamic tags are not of
>> help, as
>> > logic to create
>> > (#stringVal1#,#stringVal2#,#stringVal3#,#intVal1#,#byteVal1#) complex
>> > procedure with different types of parameter can't be written in
>> mapping
>> > XML.At least I am unable to do.
>> >
>> >
>> >
>> > I am really struck.Please suggest us the best way to deal with it.
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> > package com.jpmorgan.pyramid.pyrsyst.configure;
>> >
>> >
>> >
>> > import java.lang.reflect.Field;
>> >
>> >
>> >
>> > public class ProcParameterMapper {
>> >
>> >
>> >
>> >       StringBuffer procedureName = new StringBuffer("");
>> >
>> >       boolean firstParam = true;
>> >
>> >       boolean lastParam = false;
>> >
>> >
>> >
>> >       String stringVal1;
>> >
>> >       String stringVal2;
>> >
>> >       String stringVal3;
>> >
>> >       String stringVal4;
>> >
>> >       String stringVal5;
>> >
>> >       String stringVal6;
>> >
>> >       String stringVal7;
>> >
>> >       String stringVal8;
>> >
>> >       String stringVal9;
>> >
>> >       String stringVal10;
>> >
>> >
>> >
>> >       int intVal1;
>> >
>> >       int intVal2;
>> >
>> >       int intVal3;
>> >
>> >       int intVal4;
>> >
>> >       int intVal5;
>> >
>> >       int intVal6;
>> >
>> >       int intVal7;
>> >
>> >       int intVal8;
>> >
>> >       int intVal9;
>> >
>> >       int intVal10;
>> >
>> >
>> >
>> >       byte byteVal1;
>> >
>> >       byte byteVal2;
>> >
>> >       byte byteVal3;
>> >
>> >       byte byteVal4;
>> >
>> >       byte byteVal5;
>> >
>> >
>> >
>> >       public void setStringVal(String value, int count,String
>> jdbcType)
>> > throws ConfigureException {
>> >
>> >
>> >
>> >             Field[] field =
>> ProcParameterMapper.class.getDeclaredFields();
>> >
>> >
>> >
>> >             for (int i = 0; i < field.length; i++) {
>> >
>> >                   try {
>> >
>> >                         if
>> > (field[i].getName().endsWith(String.valueOf(count))
>> >
>> >                                     && ("String")
>> >
>> >
>> > .equals(field[i].getType().getSimpleName())) {
>> >
>> >                               field[i].set(this, value);
>> >
>> >                               if (firstParam) {
>> >
>> >                                     procedureName.append("(#" +
>> > field[i].getName()+"#");
>> >
>> >                                     firstParam = false;
>> >
>> >                               } else if (lastParam) {
>> >
>> >                                     procedureName.append(",#" +
>> > field[i].getName() +"#)");
>> >
>> >                               } else {
>> >
>> >                                     procedureName.append(",#" +
>> > field[i].getName()+"#");
>> >
>> >                               }
>> >
>> >                               break;
>> >
>> >
>> >
>> >                         }
>> >
>> >                   } catch (Exception e) {
>> >
>> >                         throw new ConfigureException(
>> >
>> >                                     "Exception setting String value
>> in
>> > paramMapper"
>> >
>> >                                                 + e.getStackTrace());
>> >
>> >                   }
>> >
>> >
>> >
>> >             }
>> >
>> >
>> >
>> >       }
>> >
>> >
>> >
>> >       public void setIntVal(int value, int count,String jdbcType)
>> throws
>> > ConfigureException {
>> >
>> >
>> >
>> >             Field[] field =
>> ProcParameterMapper.class.getDeclaredFields();
>> >
>> >
>> >
>> >             for (int i = 0; i < field.length; i++) {
>> >
>> >                   try {
>> >
>> >                         if
>> > (field[i].getName().endsWith(String.valueOf(count))
>> >
>> >                                     &&
>> > ("int").equals(field[i].getType().getSimpleName())) {
>> >
>> >
>> >
>> >                               field[i].set(this, value);
>> >
>> >                               if (firstParam) {
>> >
>> >                                     procedureName.append("(#" +
>> > field[i].getName()+"#");
>> >
>> >                                     firstParam = false;
>> >
>> >                               } else if (lastParam) {
>> >
>> >                                     procedureName.append(",#" +
>> > field[i].getName() +"#)");
>> >
>> >                               } else {
>> >
>> >                                     procedureName.append(",#" +
>> > field[i].getName()+"#");
>> >
>> >                               }
>> >
>> >                               break;
>> >
>> >                         }
>> >
>> >                   } catch (Exception e) {
>> >
>> >                         throw new ConfigureException(
>> >
>> >                                     "Exception setting int value in
>> > paramMapper"
>> >
>> >                                                 + e.getStackTrace());
>> >
>> >                   }
>> >
>> >
>> >
>> >             }
>> >
>> >
>> >
>> >       }
>> >
>> >
>> >
>> >       public void setByteVal(Byte value, int count,String jdbcType)
>> throws
>> > ConfigureException {
>> >
>> >
>> >
>> >             Field[] field =
>> ProcParameterMapper.class.getDeclaredFields();
>> >
>> >
>> >
>> >             for (int i = 0; i < field.length; i++) {
>> >
>> >                   try {
>> >
>> >                         if
>> > (field[i].getName().endsWith(String.valueOf(count))
>> >
>> >                                     &&
>> > ("byte").equals(field[i].getType().getSimpleName())) {
>> >
>> >
>> >
>> >                               field[i].set(this, value);
>> >
>> >                               if (firstParam) {
>> >
>> >                                     procedureName.append("(#" +
>> > field[i].getName()+"#");
>> >
>> >                                     firstParam = false;
>> >
>> >                               } else if (lastParam) {
>> >
>> >                                     procedureName.append(",#" +
>> > field[i].getName() + "#)");
>> >
>> >                               } else {
>> >
>> >                                     procedureName.append(",#" +
>> > field[i].getName()+"#");
>> >
>> >                               }
>> >
>> >                               break;
>> >
>> >                         }
>> >
>> >                   } catch (Exception e) {
>> >
>> >                         throw new ConfigureException(
>> >
>> >                                     "Exception setting byte value in
>> > paramMapper"
>> >
>> >                                                 + e.getStackTrace());
>> >
>> >                   }
>> >
>> >
>> >
>> >             }
>> >
>> >
>> >
>> >       }
>> >
>> >
>> >
>> >       public String toString() {
>> >
>> >
>> >
>> >             return new String("stringVal1 is" + stringVal1 +
>> "stringVal2 is"
>> >
>> >                         + stringVal2 + "stringVal3 is" + stringVal3 +
>> > "intVal1 is"
>> >
>> >                         + intVal1 + "intVal2 is" + intVal2 + "intVal3
>> is" +
>> > intVal3);
>> >
>> >
>> >
>> >       }
>> >
>> >
>> >
>> >       public void setProcedure(String procName) {
>> >
>> >             procedureName.append(procName);
>> >
>> >
>> >
>> >       }
>> >
>> >
>> >
>> >       public String getProcedure(){
>> >
>> >             return procedureName.toString();
>> >
>> >       }
>> >
>> >
>> >
>> > }
>> >
>> >
>> >
>> > May thanks in advance
>> >
>> > Waiting for reply
>> >
>> > Nicky Jha
>> >
>> >
>> >
>> > This communication is for informational purposes only. It is not
>> intended as
>> > an offer or solicitation for the purchase or sale of any financial
>> > instrument or as an official confirmation of any transaction. All
>> market
>> > prices, data and other information are not warranted as to
>> completeness or
>> > accuracy and are subject to change without notice. Any comments or
>> > statements made herein do not necessarily reflect those of JPMorgan
>> Chase &
>> > Co., its subsidiaries and affiliates. This transmission may contain
>> > information that is privileged, confidential, legally privileged,
>> and/or
>> > exempt from disclosure under applicable law. If you are not the
>> intended
>> > recipient, you are hereby notified that any disclosure, copying,
>> > distribution, or use of the information contained herein (including
>> any
>> > reliance thereon) is STRICTLY PROHIBITED. Although this transmission
>> and any
>> > attachments are believed to be free of any virus or other defect that
>> might
>> > affect any computer system into which it is received and opened, it
>> is the
>> > responsibility of the recipient to ensure that it is virus free and
>> no
>> > responsibility is accepted by JPMorgan Chase & Co., its subsidiaries
>> and
>> > affiliates, as applicable, for any loss or damage arising in any way
>> from
>> > its use. If you received this transmission in error, please
>> immediately
>> > contact the sender and destroy the material in its entirety, whether
>> in
>> > electronic or hard copy format. Thank you. Please refer to
>> > http://www.jpmorgan.com/pages/disclosures for disclosures relating to
>> > European legal entities.
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [hidden email]
>> For additional commands, e-mail: [hidden email]
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>

--
Sent from my mobile device

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]


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

RE: Need support for Dynamic procedure invocation

Nicky Jha

Hi

 

I am using parameter class DynamicProcedureParams(as sugessted by Joe Gooch ), as my requirement was to build paramaters at run time, so with this approach where can I set jdbcType?

 

Nicky

 

From: Clinton Begin [mailto:[hidden email]]
Sent: Tuesday, May 25, 2010 10:39 AM
To: [hidden email]
Subject: Re: Need support for Dynamic procedure invocation

 

Are you setting the jdbcType in your parameter map for all nullable columns?

Clinton

On Mon, May 24, 2010 at 11:03 PM, Nicky Jha <[hidden email]> wrote:


Hi

with approach mentioned below(DynamicProcedureParams) , I want to also pass some of parameter as null((private List<Object> params = new ArrayList<Object>(); params.add(null))), but when I pass string value as null , it executes parametes procedure as

call procname(?,?,?,?,?)
Parameters: [#IMNT_RISK_SENSITIVITY, IMNT_RISK_SENSITIVITY, null, null, 0]
Types: [java.lang.String, java.lang.String, null, null, java.lang.Byte]

I want type to be java.lang.String instead of null(as with null, I am getting Unsupported SQL type 0 )

If I pass blank string "" in place of null in (private List<Object> params = new ArrayList<Object>(); params.add("")), Types become java.lang.String, but then I think it no more considers it as null.

Please help

Thanks
Nicky



-----Original Message-----
From: Nicky Jha
Sent: Friday, May 14, 2010 8:46 PM
To: '[hidden email]'
Subject: RE: Need support for Dynamic procedure invocation

Joe/Jeff

This worked with your suggestion.Thank you so much!!!

Nicky

-----Original Message-----
From: Jeff Butler [mailto:[hidden email]]
Sent: Friday, May 14, 2010 6:31 PM
To: [hidden email]
Subject: Re: Need support for Dynamic procedure invocation

Yes - this the best approach.

Jeff Butler


On 5/14/10, Joe Gooch <[hidden email]> wrote:
> I suggest going with a dynamic SQL approach in the mapped statement.
>
> public class DynamicProcedureParams {
>   private String procedureName;
>   private List<Object> params = new ArrayList<Object>();
>
>   // getters and setters here
> }
>
> <procedure id="executeCopyProcs"
> parameterClass="path.to.DynamicProcedureParams">
> {call $procedureName$ <iterate property="params" open="(" close=")"
> conjunction=",">#params[]#</iterate> }
> </procedure>
>
>
>
> Joe
>
> Confidentiality Notice:
> This e-mail transmission may contain confidential and legally privileged
> information that is intended only for the individual named in the e-mail
> address. If you are not the intended recipient, you are hereby notified that
> any disclosure, copying, distribution, or reliance upon the contents of this
> e-mail message is strictly prohibited. If you have received this e-mail
> transmission in error, please reply to the sender, so that proper delivery
> can be arranged, and please delete the message from your mail box.
>
>> -----Original Message-----
>> From: Nicky Jha [mailto:[hidden email]]
>> Sent: Friday, May 14, 2010 7:38 AM
>> To: [hidden email]
>> Subject: RE: Need support for Dynamic procedure invocation
>>
>> Hi Jeff,
>>
>> Thanks for this, if I was to do as suggested and compose the entire
>> string with parameter values, how will the parameter types (e.g. date,
>> smallint etc) be handled?
>>
>> Nicky!
>>
>> -----Original Message-----
>> From: Jeff Butler [mailto:[hidden email]]
>> Sent: Friday, May 14, 2010 4:53 PM
>> To: [hidden email]
>> Subject: Re: Need support for Dynamic procedure invocation
>>
>> This won't work (as you've discovered).  iBATIS 2.x does not reparse
>> the string for variables after string substitution.  You'll need to do
>> this with the dynamic tags, or you'll need to compose the entire
>> string (including parameter values - like {call myproc('fred')}).
>>
>> Jeff Butler
>>
>>
>> On Fri, May 14, 2010 at 4:12 AM, Nicky Jha <[hidden email]>
>> wrote:
>> > Hi Team,
>> >
>> >
>> >
>> > I am having hard time resolving following issue.Please help.
>> >
>> > We are using Ibatis 2.X.
>> >
>> > In our application we want capability to invoke stored procedure by
>> reading
>> > it from property xml file.We provide procedure name parameters,
>> parameters
>> > type to  property xml file.
>> >
>> > Now from this xml file I have created one dynamic procedure string
>> like
>> >
>> procedureName(#stringVal1#,#stringVal2#,#stringVal3#,#intVal1#,#byteVal
>> 1#).
>> >
>> > Now I want to call this procedure from Ibatis SQL mapping XML like
>> this
>> >
>> >
>> >
>> > <procedure id="executeCopyProcs"
>> >
>> parameterClass="com.jpmorgan.pyramid.pyrsyst.configure.ProcParameterMap
>> per">
>> >
>> > {call $procedureName$ }
>> >
>> >
>> >
>> > </procedure>
>> >
>> >
>> >
>> > Please refer below for ProcParameterMapper class. Now as soon as
>> iBatis sees
>> >  $procedureName$, it replaces it with say
>> > (#stringVal1#,#stringVal2#,#stringVal3#,#intVal1#,#byteVal1#) but it
>> does
>> > not replace the placeHolder(#), it passes on this as it is.Which is a
>> issue.
>> >
>> >
>> >
>> > If we type
>> >
>> procedureName(#stringVal1#,#stringVal2#,#stringVal3#,#intVal1#,#byteVal
>> 1)
>> > directly into SQL mapping XML, it replaces place holder, but with
>> string
>> > substitution , it does not work.We can't type directly into mapping
>> xML, as
>> > this string is composed at run time.Also dynamic tags are not of
>> help, as
>> > logic to create
>> > (#stringVal1#,#stringVal2#,#stringVal3#,#intVal1#,#byteVal1#) complex
>> > procedure with different types of parameter can't be written in
>> mapping
>> > XML.At least I am unable to do.
>> >
>> >
>> >
>> > I am really struck.Please suggest us the best way to deal with it.
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> > package com.jpmorgan.pyramid.pyrsyst.configure;
>> >
>> >
>> >
>> > import java.lang.reflect.Field;
>> >
>> >
>> >
>> > public class ProcParameterMapper {
>> >
>> >
>> >
>> >       StringBuffer procedureName = new StringBuffer("");
>> >
>> >       boolean firstParam = true;
>> >
>> >       boolean lastParam = false;
>> >
>> >
>> >
>> >       String stringVal1;
>> >
>> >       String stringVal2;
>> >
>> >       String stringVal3;
>> >
>> >       String stringVal4;
>> >
>> >       String stringVal5;
>> >
>> >       String stringVal6;
>> >
>> >       String stringVal7;
>> >
>> >       String stringVal8;
>> >
>> >       String stringVal9;
>> >
>> >       String stringVal10;
>> >
>> >
>> >
>> >       int intVal1;
>> >
>> >       int intVal2;
>> >
>> >       int intVal3;
>> >
>> >       int intVal4;
>> >
>> >       int intVal5;
>> >
>> >       int intVal6;
>> >
>> >       int intVal7;
>> >
>> >       int intVal8;
>> >
>> >       int intVal9;
>> >
>> >       int intVal10;
>> >
>> >
>> >
>> >       byte byteVal1;
>> >
>> >       byte byteVal2;
>> >
>> >       byte byteVal3;
>> >
>> >       byte byteVal4;
>> >
>> >       byte byteVal5;
>> >
>> >
>> >
>> >       public void setStringVal(String value, int count,String
>> jdbcType)
>> > throws ConfigureException {
>> >
>> >
>> >
>> >             Field[] field =
>> ProcParameterMapper.class.getDeclaredFields();
>> >
>> >
>> >
>> >             for (int i = 0; i < field.length; i++) {
>> >
>> >                   try {
>> >
>> >                         if
>> > (field[i].getName().endsWith(String.valueOf(count))
>> >
>> >                                     && ("String")
>> >
>> >
>> > .equals(field[i].getType().getSimpleName())) {
>> >
>> >                               field[i].set(this, value);
>> >
>> >                               if (firstParam) {
>> >
>> >                                     procedureName.append("(#" +
>> > field[i].getName()+"#");
>> >
>> >                                     firstParam = false;
>> >
>> >                               } else if (lastParam) {
>> >
>> >                                     procedureName.append(",#" +
>> > field[i].getName() +"#)");
>> >
>> >                               } else {
>> >
>> >                                     procedureName.append(",#" +
>> > field[i].getName()+"#");
>> >
>> >                               }
>> >
>> >                               break;
>> >
>> >
>> >
>> >                         }
>> >
>> >                   } catch (Exception e) {
>> >
>> >                         throw new ConfigureException(
>> >
>> >                                     "Exception setting String value
>> in
>> > paramMapper"
>> >
>> >                                                 + e.getStackTrace());
>> >
>> >                   }
>> >
>> >
>> >
>> >             }
>> >
>> >
>> >
>> >       }
>> >
>> >
>> >
>> >       public void setIntVal(int value, int count,String jdbcType)
>> throws
>> > ConfigureException {
>> >
>> >
>> >
>> >             Field[] field =
>> ProcParameterMapper.class.getDeclaredFields();
>> >
>> >
>> >
>> >             for (int i = 0; i < field.length; i++) {
>> >
>> >                   try {
>> >
>> >                         if
>> > (field[i].getName().endsWith(String.valueOf(count))
>> >
>> >                                     &&
>> > ("int").equals(field[i].getType().getSimpleName())) {
>> >
>> >
>> >
>> >                               field[i].set(this, value);
>> >
>> >                               if (firstParam) {
>> >
>> >                                     procedureName.append("(#" +
>> > field[i].getName()+"#");
>> >
>> >                                     firstParam = false;
>> >
>> >                               } else if (lastParam) {
>> >
>> >                                     procedureName.append(",#" +
>> > field[i].getName() +"#)");
>> >
>> >                               } else {
>> >
>> >                                     procedureName.append(",#" +
>> > field[i].getName()+"#");
>> >
>> >                               }
>> >
>> >                               break;
>> >
>> >                         }
>> >
>> >                   } catch (Exception e) {
>> >
>> >                         throw new ConfigureException(
>> >
>> >                                     "Exception setting int value in
>> > paramMapper"
>> >
>> >                                                 + e.getStackTrace());
>> >
>> >                   }
>> >
>> >
>> >
>> >             }
>> >
>> >
>> >
>> >       }
>> >
>> >
>> >
>> >       public void setByteVal(Byte value, int count,String jdbcType)
>> throws
>> > ConfigureException {
>> >
>> >
>> >
>> >             Field[] field =
>> ProcParameterMapper.class.getDeclaredFields();
>> >
>> >
>> >
>> >             for (int i = 0; i < field.length; i++) {
>> >
>> >                   try {
>> >
>> >                         if
>> > (field[i].getName().endsWith(String.valueOf(count))
>> >
>> >                                     &&
>> > ("byte").equals(field[i].getType().getSimpleName())) {
>> >
>> >
>> >
>> >                               field[i].set(this, value);
>> >
>> >                               if (firstParam) {
>> >
>> >                                     procedureName.append("(#" +
>> > field[i].getName()+"#");
>> >
>> >                                     firstParam = false;
>> >
>> >                               } else if (lastParam) {
>> >
>> >                                     procedureName.append(",#" +
>> > field[i].getName() + "#)");
>> >
>> >                               } else {
>> >
>> >                                     procedureName.append(",#" +
>> > field[i].getName()+"#");
>> >
>> >                               }
>> >
>> >                               break;
>> >
>> >                         }
>> >
>> >                   } catch (Exception e) {
>> >
>> >                         throw new ConfigureException(
>> >
>> >                                     "Exception setting byte value in
>> > paramMapper"
>> >
>> >                                                 + e.getStackTrace());
>> >
>> >                   }
>> >
>> >
>> >
>> >             }
>> >
>> >
>> >
>> >       }
>> >
>> >
>> >
>> >       public String toString() {
>> >
>> >
>> >
>> >             return new String("stringVal1 is" + stringVal1 +
>> "stringVal2 is"
>> >
>> >                         + stringVal2 + "stringVal3 is" + stringVal3 +
>> > "intVal1 is"
>> >
>> >                         + intVal1 + "intVal2 is" + intVal2 + "intVal3
>> is" +
>> > intVal3);
>> >
>> >
>> >
>> >       }
>> >
>> >
>> >
>> >       public void setProcedure(String procName) {
>> >
>> >             procedureName.append(procName);
>> >
>> >
>> >
>> >       }
>> >
>> >
>> >
>> >       public String getProcedure(){
>> >
>> >             return procedureName.toString();
>> >
>> >       }
>> >
>> >
>> >
>> > }
>> >
>> >
>> >
>> > May thanks in advance
>> >
>> > Waiting for reply
>> >
>> > Nicky Jha
>> >
>> >
>> >
>> > This communication is for informational purposes only. It is not
>> intended as
>> > an offer or solicitation for the purchase or sale of any financial
>> > instrument or as an official confirmation of any transaction. All
>> market
>> > prices, data and other information are not warranted as to
>> completeness or
>> > accuracy and are subject to change without notice. Any comments or
>> > statements made herein do not necessarily reflect those of JPMorgan
>> Chase &
>> > Co., its subsidiaries and affiliates. This transmission may contain
>> > information that is privileged, confidential, legally privileged,
>> and/or
>> > exempt from disclosure under applicable law. If you are not the
>> intended
>> > recipient, you are hereby notified that any disclosure, copying,
>> > distribution, or use of the information contained herein (including
>> any
>> > reliance thereon) is STRICTLY PROHIBITED. Although this transmission
>> and any
>> > attachments are believed to be free of any virus or other defect that
>> might
>> > affect any computer system into which it is received and opened, it
>> is the
>> > responsibility of the recipient to ensure that it is virus free and
>> no
>> > responsibility is accepted by JPMorgan Chase & Co., its subsidiaries
>> and
>> > affiliates, as applicable, for any loss or damage arising in any way
>> from
>> > its use. If you received this transmission in error, please
>> immediately
>> > contact the sender and destroy the material in its entirety, whether
>> in
>> > electronic or hard copy format. Thank you. Please refer to
>> > http://www.jpmorgan.com/pages/disclosures for disclosures relating to
>> > European legal entities.
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [hidden email]
>> For additional commands, e-mail: [hidden email]
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>

--
Sent from my mobile device

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

 

This communication is for informational purposes only. It is not intended as an offer or solicitation for the purchase or sale of any financial instrument or as an official confirmation of any transaction. All market prices, data and other information are not warranted as to completeness or accuracy and are subject to change without notice. Any comments or statements made herein do not necessarily reflect those of JPMorgan Chase & Co., its subsidiaries and affiliates. This transmission may contain information that is privileged, confidential, legally privileged, and/or exempt from disclosure under applicable law. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or use of the information contained herein (including any reliance thereon) is STRICTLY PROHIBITED. Although this transmission and any attachments are believed to be free of any virus or other defect that might affect any computer system into which it is received and opened, it is the responsibility of the recipient to ensure that it is virus free and no responsibility is accepted by JPMorgan Chase & Co., its subsidiaries and affiliates, as applicable, for any loss or damage arising in any way from its use. If you received this transmission in error, please immediately contact the sender and destroy the material in its entirety, whether in electronic or hard copy format. Thank you. Please refer to http://www.jpmorgan.com/pages/disclosures for disclosures relating to European legal entities.

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

Re: Need support for Dynamic procedure invocation

Clinton Begin
I'm not familiar with that class, as it's not part of the framework
(maybe post it here).  Short story is that JDBC requires nullable
column types to be specified.  Ibatis allows for this in the parameter
map (crack open the user guide and search for "jdbcType" for more).

Cheers,
Clinton



On 2010-05-24, Nicky Jha <[hidden email]> wrote:

> Hi
>
> I am using parameter class DynamicProcedureParams(as sugessted by Joe Gooch
> ), as my requirement was to build paramaters at run time, so with this
> approach where can I set jdbcType?
>
> Nicky
>
> From: Clinton Begin [mailto:[hidden email]]
> Sent: Tuesday, May 25, 2010 10:39 AM
> To: [hidden email]
> Subject: Re: Need support for Dynamic procedure invocation
>
> Are you setting the jdbcType in your parameter map for all nullable
> columns?
>
> Clinton
> On Mon, May 24, 2010 at 11:03 PM, Nicky Jha
> <[hidden email]<mailto:[hidden email]>> wrote:
>
> Hi
>
> with approach mentioned below(DynamicProcedureParams) , I want to also pass
> some of parameter as null((private List<Object> params = new
> ArrayList<Object>(); params.add(null))), but when I pass string value as
> null , it executes parametes procedure as
>
> call procname(?,?,?,?,?)
> Parameters: [#IMNT_RISK_SENSITIVITY, IMNT_RISK_SENSITIVITY, null, null, 0]
> Types: [java.lang.String, java.lang.String, null, null, java.lang.Byte]
>
> I want type to be java.lang.String instead of null(as with null, I am
> getting Unsupported SQL type 0 )
>
> If I pass blank string "" in place of null in (private List<Object> params =
> new ArrayList<Object>(); params.add("")), Types become java.lang.String, but
> then I think it no more considers it as null.
>
> Please help
>
> Thanks
> Nicky
>
>
>
> -----Original Message-----
> From: Nicky Jha
> Sent: Friday, May 14, 2010 8:46 PM
> To: '[hidden email]<mailto:[hidden email]>'
> Subject: RE: Need support for Dynamic procedure invocation
>
> Joe/Jeff
>
> This worked with your suggestion.Thank you so much!!!
>
> Nicky
>
> -----Original Message-----
> From: Jeff Butler
> [mailto:[hidden email]<mailto:[hidden email]>]
> Sent: Friday, May 14, 2010 6:31 PM
> To: [hidden email]<mailto:[hidden email]>
> Subject: Re: Need support for Dynamic procedure invocation
>
> Yes - this the best approach.
>
> Jeff Butler
>
>
> On 5/14/10, Joe Gooch
> <[hidden email]<mailto:[hidden email]>> wrote:
>> I suggest going with a dynamic SQL approach in the mapped statement.
>>
>> public class DynamicProcedureParams {
>>   private String procedureName;
>>   private List<Object> params = new ArrayList<Object>();
>>
>>   // getters and setters here
>> }
>>
>> <procedure id="executeCopyProcs"
>> parameterClass="path.to.DynamicProcedureParams">
>> {call $procedureName$ <iterate property="params" open="(" close=")"
>> conjunction=",">#params[]#</iterate> }
>> </procedure>
>>
>>
>>
>> Joe
>>
>> Confidentiality Notice:
>> This e-mail transmission may contain confidential and legally privileged
>> information that is intended only for the individual named in the e-mail
>> address. If you are not the intended recipient, you are hereby notified
>> that
>> any disclosure, copying, distribution, or reliance upon the contents of
>> this
>> e-mail message is strictly prohibited. If you have received this e-mail
>> transmission in error, please reply to the sender, so that proper
>> delivery
>> can be arranged, and please delete the message from your mail box.
>>
>>> -----Original Message-----
>>> From: Nicky Jha
>>> [mailto:[hidden email]<mailto:[hidden email]>]
>>> Sent: Friday, May 14, 2010 7:38 AM
>>> To: [hidden email]<mailto:[hidden email]>
>>> Subject: RE: Need support for Dynamic procedure invocation
>>>
>>> Hi Jeff,
>>>
>>> Thanks for this, if I was to do as suggested and compose the entire
>>> string with parameter values, how will the parameter types (e.g. date,
>>> smallint etc) be handled?
>>>
>>> Nicky!
>>>
>>> -----Original Message-----
>>> From: Jeff Butler
>>> [mailto:[hidden email]<mailto:[hidden email]>]
>>> Sent: Friday, May 14, 2010 4:53 PM
>>> To: [hidden email]<mailto:[hidden email]>
>>> Subject: Re: Need support for Dynamic procedure invocation
>>>
>>> This won't work (as you've discovered).  iBATIS 2.x does not reparse
>>> the string for variables after string substitution.  You'll need to do
>>> this with the dynamic tags, or you'll need to compose the entire
>>> string (including parameter values - like {call myproc('fred')}).
>>>
>>> Jeff Butler
>>>
>>>
>>> On Fri, May 14, 2010 at 4:12 AM, Nicky Jha
>>> <[hidden email]<mailto:[hidden email]>>
>>> wrote:
>>> > Hi Team,
>>> >
>>> >
>>> >
>>> > I am having hard time resolving following issue.Please help.
>>> >
>>> > We are using Ibatis 2.X.
>>> >
>>> > In our application we want capability to invoke stored procedure by
>>> reading
>>> > it from property xml file.We provide procedure name parameters,
>>> parameters
>>> > type to  property xml file.
>>> >
>>> > Now from this xml file I have created one dynamic procedure string
>>> like
>>> >
>>> procedureName(#stringVal1#,#stringVal2#,#stringVal3#,#intVal1#,#byteVal
>>> 1#).
>>> >
>>> > Now I want to call this procedure from Ibatis SQL mapping XML like
>>> this
>>> >
>>> >
>>> >
>>> > <procedure id="executeCopyProcs"
>>> >
>>> parameterClass="com.jpmorgan.pyramid.pyrsyst.configure.ProcParameterMap
>>> per">
>>> >
>>> > {call $procedureName$ }
>>> >
>>> >
>>> >
>>> > </procedure>
>>> >
>>> >
>>> >
>>> > Please refer below for ProcParameterMapper class. Now as soon as
>>> iBatis sees
>>> >  $procedureName$, it replaces it with say
>>> > (#stringVal1#,#stringVal2#,#stringVal3#,#intVal1#,#byteVal1#) but it
>>> does
>>> > not replace the placeHolder(#), it passes on this as it is.Which is a
>>> issue.
>>> >
>>> >
>>> >
>>> > If we type
>>> >
>>> procedureName(#stringVal1#,#stringVal2#,#stringVal3#,#intVal1#,#byteVal
>>> 1)
>>> > directly into SQL mapping XML, it replaces place holder, but with
>>> string
>>> > substitution , it does not work.We can't type directly into mapping
>>> xML, as
>>> > this string is composed at run time.Also dynamic tags are not of
>>> help, as
>>> > logic to create
>>> > (#stringVal1#,#stringVal2#,#stringVal3#,#intVal1#,#byteVal1#) complex
>>> > procedure with different types of parameter can't be written in
>>> mapping
>>> > XML.At least I am unable to do.
>>> >
>>> >
>>> >
>>> > I am really struck.Please suggest us the best way to deal with it.
>>> >
>>> >
>>> >
>>> >
>>> >
>>> >
>>> >
>>> >
>>> >
>>> >
>>> >
>>> >
>>> >
>>> > package com.jpmorgan.pyramid.pyrsyst.configure;
>>> >
>>> >
>>> >
>>> > import java.lang.reflect.Field;
>>> >
>>> >
>>> >
>>> > public class ProcParameterMapper {
>>> >
>>> >
>>> >
>>> >       StringBuffer procedureName = new StringBuffer("");
>>> >
>>> >       boolean firstParam = true;
>>> >
>>> >       boolean lastParam = false;
>>> >
>>> >
>>> >
>>> >       String stringVal1;
>>> >
>>> >       String stringVal2;
>>> >
>>> >       String stringVal3;
>>> >
>>> >       String stringVal4;
>>> >
>>> >       String stringVal5;
>>> >
>>> >       String stringVal6;
>>> >
>>> >       String stringVal7;
>>> >
>>> >       String stringVal8;
>>> >
>>> >       String stringVal9;
>>> >
>>> >       String stringVal10;
>>> >
>>> >
>>> >
>>> >       int intVal1;
>>> >
>>> >       int intVal2;
>>> >
>>> >       int intVal3;
>>> >
>>> >       int intVal4;
>>> >
>>> >       int intVal5;
>>> >
>>> >       int intVal6;
>>> >
>>> >       int intVal7;
>>> >
>>> >       int intVal8;
>>> >
>>> >       int intVal9;
>>> >
>>> >       int intVal10;
>>> >
>>> >
>>> >
>>> >       byte byteVal1;
>>> >
>>> >       byte byteVal2;
>>> >
>>> >       byte byteVal3;
>>> >
>>> >       byte byteVal4;
>>> >
>>> >       byte byteVal5;
>>> >
>>> >
>>> >
>>> >       public void setStringVal(String value, int count,String
>>> jdbcType)
>>> > throws ConfigureException {
>>> >
>>> >
>>> >
>>> >             Field[] field =
>>> ProcParameterMapper.class.getDeclaredFields();
>>> >
>>> >
>>> >
>>> >             for (int i = 0; i < field.length; i++) {
>>> >
>>> >                   try {
>>> >
>>> >                         if
>>> > (field[i].getName().endsWith(String.valueOf(count))
>>> >
>>> >                                     && ("String")
>>> >
>>> >
>>> > .equals(field[i].getType().getSimpleName())) {
>>> >
>>> >                               field[i].set(this, value);
>>> >
>>> >                               if (firstParam) {
>>> >
>>> >                                     procedureName.append("(#" +
>>> > field[i].getName()+"#");
>>> >
>>> >                                     firstParam = false;
>>> >
>>> >                               } else if (lastParam) {
>>> >
>>> >                                     procedureName.append(",#" +
>>> > field[i].getName() +"#)");
>>> >
>>> >                               } else {
>>> >
>>> >                                     procedureName.append(",#" +
>>> > field[i].getName()+"#");
>>> >
>>> >                               }
>>> >
>>> >                               break;
>>> >
>>> >
>>> >
>>> >                         }
>>> >
>>> >                   } catch (Exception e) {
>>> >
>>> >                         throw new ConfigureException(
>>> >
>>> >                                     "Exception setting String value
>>> in
>>> > paramMapper"
>>> >
>>> >                                                 + e.getStackTrace());
>>> >
>>> >                   }
>>> >
>>> >
>>> >
>>> >             }
>>> >
>>> >
>>> >
>>> >       }
>>> >
>>> >
>>> >
>>> >       public void setIntVal(int value, int count,String jdbcType)
>>> throws
>>> > ConfigureException {
>>> >
>>> >
>>> >
>>> >             Field[] field =
>>> ProcParameterMapper.class.getDeclaredFields();
>>> >
>>> >
>>> >
>>> >             for (int i = 0; i < field.length; i++) {
>>> >
>>> >                   try {
>>> >
>>> >                         if
>>> > (field[i].getName().endsWith(String.valueOf(count))
>>> >
>>> >                                     &&
>>> > ("int").equals(field[i].getType().getSimpleName())) {
>>> >
>>> >
>>> >
>>> >                               field[i].set(this, value);
>>> >
>>> >                               if (firstParam) {
>>> >
>>> >                                     procedureName.append("(#" +
>>> > field[i].getName()+"#");
>>> >
>>> >                                     firstParam = false;
>>> >
>>> >                               } else if (lastParam) {
>>> >
>>> >                                     procedureName.append(",#" +
>>> > field[i].getName() +"#)");
>>> >
>>> >                               } else {
>>> >
>>> >                                     procedureName.append(",#" +
>>> > field[i].getName()+"#");
>>> >
>>> >                               }
>>> >
>>> >                               break;
>>> >
>>> >                         }
>>> >
>>> >                   } catch (Exception e) {
>>> >
>>> >                         throw new ConfigureException(
>>> >
>>> >                                     "Exception setting int value in
>>> > paramMapper"
>>> >
>>> >                                                 + e.getStackTrace());
>>> >
>>> >                   }
>>> >
>>> >
>>> >
>>> >             }
>>> >
>>> >
>>> >
>>> >       }
>>> >
>>> >
>>> >
>>> >       public void setByteVal(Byte value, int count,String jdbcType)
>>> throws
>>> > ConfigureException {
>>> >
>>> >
>>> >
>>> >             Field[] field =
>>> ProcParameterMapper.class.getDeclaredFields();
>>> >
>>> >
>>> >
>>> >             for (int i = 0; i < field.length; i++) {
>>> >
>>> >                   try {
>>> >
>>> >                         if
>>> > (field[i].getName().endsWith(String.valueOf(count))
>>> >
>>> >                                     &&
>>> > ("byte").equals(field[i].getType().getSimpleName())) {
>>> >
>>> >
>>> >
>>> >                               field[i].set(this, value);
>>> >
>>> >                               if (firstParam) {
>>> >
>>> >                                     procedureName.append("(#" +
>>> > field[i].getName()+"#");
>>> >
>>> >                                     firstParam = false;
>>> >
>>> >                               } else if (lastParam) {
>>> >
>>> >                                     procedureName.append(",#" +
>>> > field[i].getName() + "#)");
>>> >
>>> >                               } else {
>>> >
>>> >                                     procedureName.append(",#" +
>>> > field[i].getName()+"#");
>>> >
>>> >                               }
>>> >
>>> >                               break;
>>> >
>>> >                         }
>>> >
>>> >                   } catch (Exception e) {
>>> >
>>> >                         throw new ConfigureException(
>>> >
>>> >                                     "Exception setting byte value in
>>> > paramMapper"
>>> >
>>> >                                                 + e.getStackTrace());
>>> >
>>> >                   }
>>> >
>>> >
>>> >
>>> >             }
>>> >
>>> >
>>> >
>>> >       }
>>> >
>>> >
>>> >
>>> >       public String toString() {
>>> >
>>> >
>>> >
>>> >             return new String("stringVal1 is" + stringVal1 +
>>> "stringVal2 is"
>>> >
>>> >                         + stringVal2 + "stringVal3 is" + stringVal3 +
>>> > "intVal1 is"
>>> >
>>> >                         + intVal1 + "intVal2 is" + intVal2 + "intVal3
>>> is" +
>>> > intVal3);
>>> >
>>> >
>>> >
>>> >       }
>>> >
>>> >
>>> >
>>> >       public void setProcedure(String procName) {
>>> >
>>> >             procedureName.append(procName);
>>> >
>>> >
>>> >
>>> >       }
>>> >
>>> >
>>> >
>>> >       public String getProcedure(){
>>> >
>>> >             return procedureName.toString();
>>> >
>>> >       }
>>> >
>>> >
>>> >
>>> > }
>>> >
>>> >
>>> >
>>> > May thanks in advance
>>> >
>>> > Waiting for reply
>>> >
>>> > Nicky Jha
>>> >
>>> >
>>> >
>>> > This communication is for informational purposes only. It is not
>>> intended as
>>> > an offer or solicitation for the purchase or sale of any financial
>>> > instrument or as an official confirmation of any transaction. All
>>> market
>>> > prices, data and other information are not warranted as to
>>> completeness or
>>> > accuracy and are subject to change without notice. Any comments or
>>> > statements made herein do not necessarily reflect those of JPMorgan
>>> Chase &
>>> > Co., its subsidiaries and affiliates. This transmission may contain
>>> > information that is privileged, confidential, legally privileged,
>>> and/or
>>> > exempt from disclosure under applicable law. If you are not the
>>> intended
>>> > recipient, you are hereby notified that any disclosure, copying,
>>> > distribution, or use of the information contained herein (including
>>> any
>>> > reliance thereon) is STRICTLY PROHIBITED. Although this transmission
>>> and any
>>> > attachments are believed to be free of any virus or other defect that
>>> might
>>> > affect any computer system into which it is received and opened, it
>>> is the
>>> > responsibility of the recipient to ensure that it is virus free and
>>> no
>>> > responsibility is accepted by JPMorgan Chase & Co., its subsidiaries
>>> and
>>> > affiliates, as applicable, for any loss or damage arising in any way
>>> from
>>> > its use. If you received this transmission in error, please
>>> immediately
>>> > contact the sender and destroy the material in its entirety, whether
>>> in
>>> > electronic or hard copy format. Thank you. Please refer to
>>> > http://www.jpmorgan.com/pages/disclosures for disclosures relating to
>>> > European legal entities.
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail:
>>> [hidden email]<mailto:[hidden email]>
>>> For additional commands, e-mail:
>>> [hidden email]<mailto:[hidden email]>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail:
>> [hidden email]<mailto:[hidden email]>
>> For additional commands, e-mail:
>> [hidden email]<mailto:[hidden email]>
>>
>>
>
> --
> Sent from my mobile device
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail:
> [hidden email]<mailto:[hidden email]>
> For additional commands, e-mail:
> [hidden email]<mailto:[hidden email]>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail:
> [hidden email]<mailto:[hidden email]>
> For additional commands, e-mail:
> [hidden email]<mailto:[hidden email]>
>
>
>

--
Sent from my mobile device

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

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

RE: Need support for Dynamic procedure invocation

Nicky Jha
Joe/Jeff
Please help.

this class is not part of framework, this is approach, we have used in case our procedure changes at runtime, so will its parameter.For that we using

public class DynamicProcedureParams {

>>   private String procedureName;
>>   private List<Object> params = new ArrayList<Object>();
>>
>>   // getters and setters here
>> }
>>
>> <procedure id="executeCopyProcs"
>> parameterClass="path.to.DynamicProcedureParams">
>> {call $procedureName$ <iterate property="params" open="(" close=")"
>> conjunction=",">#params[]#</iterate> }
>> </procedure>
>>

Thanks
Nicky




-----Original Message-----
From: Clinton Begin [mailto:[hidden email]]
Sent: Tuesday, May 25, 2010 11:07 AM
To: [hidden email]
Subject: Re: Need support for Dynamic procedure invocation

I'm not familiar with that class, as it's not part of the framework
(maybe post it here).  Short story is that JDBC requires nullable
column types to be specified.  Ibatis allows for this in the parameter
map (crack open the user guide and search for "jdbcType" for more).

Cheers,
Clinton



On 2010-05-24, Nicky Jha <[hidden email]> wrote:

> Hi
>
> I am using parameter class DynamicProcedureParams(as sugessted by Joe Gooch
> ), as my requirement was to build paramaters at run time, so with this
> approach where can I set jdbcType?
>
> Nicky
>
> From: Clinton Begin [mailto:[hidden email]]
> Sent: Tuesday, May 25, 2010 10:39 AM
> To: [hidden email]
> Subject: Re: Need support for Dynamic procedure invocation
>
> Are you setting the jdbcType in your parameter map for all nullable
> columns?
>
> Clinton
> On Mon, May 24, 2010 at 11:03 PM, Nicky Jha
> <[hidden email]<mailto:[hidden email]>> wrote:
>
> Hi
>
> with approach mentioned below(DynamicProcedureParams) , I want to also pass
> some of parameter as null((private List<Object> params = new
> ArrayList<Object>(); params.add(null))), but when I pass string value as
> null , it executes parametes procedure as
>
> call procname(?,?,?,?,?)
> Parameters: [#IMNT_RISK_SENSITIVITY, IMNT_RISK_SENSITIVITY, null, null, 0]
> Types: [java.lang.String, java.lang.String, null, null, java.lang.Byte]
>
> I want type to be java.lang.String instead of null(as with null, I am
> getting Unsupported SQL type 0 )
>
> If I pass blank string "" in place of null in (private List<Object> params =
> new ArrayList<Object>(); params.add("")), Types become java.lang.String, but
> then I think it no more considers it as null.
>
> Please help
>
> Thanks
> Nicky
>
>
>
> -----Original Message-----
> From: Nicky Jha
> Sent: Friday, May 14, 2010 8:46 PM
> To: '[hidden email]<mailto:[hidden email]>'
> Subject: RE: Need support for Dynamic procedure invocation
>
> Joe/Jeff
>
> This worked with your suggestion.Thank you so much!!!
>
> Nicky
>
> -----Original Message-----
> From: Jeff Butler
> [mailto:[hidden email]<mailto:[hidden email]>]
> Sent: Friday, May 14, 2010 6:31 PM
> To: [hidden email]<mailto:[hidden email]>
> Subject: Re: Need support for Dynamic procedure invocation
>
> Yes - this the best approach.
>
> Jeff Butler
>
>
> On 5/14/10, Joe Gooch
> <[hidden email]<mailto:[hidden email]>> wrote:
>> I suggest going with a dynamic SQL approach in the mapped statement.
>>
>> public class DynamicProcedureParams {
>>   private String procedureName;
>>   private List<Object> params = new ArrayList<Object>();
>>
>>   // getters and setters here
>> }
>>
>> <procedure id="executeCopyProcs"
>> parameterClass="path.to.DynamicProcedureParams">
>> {call $procedureName$ <iterate property="params" open="(" close=")"
>> conjunction=",">#params[]#</iterate> }
>> </procedure>
>>
>>
>>
>> Joe
>>
>> Confidentiality Notice:
>> This e-mail transmission may contain confidential and legally privileged
>> information that is intended only for the individual named in the e-mail
>> address. If you are not the intended recipient, you are hereby notified
>> that
>> any disclosure, copying, distribution, or reliance upon the contents of
>> this
>> e-mail message is strictly prohibited. If you have received this e-mail
>> transmission in error, please reply to the sender, so that proper
>> delivery
>> can be arranged, and please delete the message from your mail box.
>>
>>> -----Original Message-----
>>> From: Nicky Jha
>>> [mailto:[hidden email]<mailto:[hidden email]>]
>>> Sent: Friday, May 14, 2010 7:38 AM
>>> To: [hidden email]<mailto:[hidden email]>
>>> Subject: RE: Need support for Dynamic procedure invocation
>>>
>>> Hi Jeff,
>>>
>>> Thanks for this, if I was to do as suggested and compose the entire
>>> string with parameter values, how will the parameter types (e.g. date,
>>> smallint etc) be handled?
>>>
>>> Nicky!
>>>
>>> -----Original Message-----
>>> From: Jeff Butler
>>> [mailto:[hidden email]<mailto:[hidden email]>]
>>> Sent: Friday, May 14, 2010 4:53 PM
>>> To: [hidden email]<mailto:[hidden email]>
>>> Subject: Re: Need support for Dynamic procedure invocation
>>>
>>> This won't work (as you've discovered).  iBATIS 2.x does not reparse
>>> the string for variables after string substitution.  You'll need to do
>>> this with the dynamic tags, or you'll need to compose the entire
>>> string (including parameter values - like {call myproc('fred')}).
>>>
>>> Jeff Butler
>>>
>>>
>>> On Fri, May 14, 2010 at 4:12 AM, Nicky Jha
>>> <[hidden email]<mailto:[hidden email]>>
>>> wrote:
>>> > Hi Team,
>>> >
>>> >
>>> >
>>> > I am having hard time resolving following issue.Please help.
>>> >
>>> > We are using Ibatis 2.X.
>>> >
>>> > In our application we want capability to invoke stored procedure by
>>> reading
>>> > it from property xml file.We provide procedure name parameters,
>>> parameters
>>> > type to  property xml file.
>>> >
>>> > Now from this xml file I have created one dynamic procedure string
>>> like
>>> >
>>> procedureName(#stringVal1#,#stringVal2#,#stringVal3#,#intVal1#,#byteVal
>>> 1#).
>>> >
>>> > Now I want to call this procedure from Ibatis SQL mapping XML like
>>> this
>>> >
>>> >
>>> >
>>> > <procedure id="executeCopyProcs"
>>> >
>>> parameterClass="com.jpmorgan.pyramid.pyrsyst.configure.ProcParameterMap
>>> per">
>>> >
>>> > {call $procedureName$ }
>>> >
>>> >
>>> >
>>> > </procedure>
>>> >
>>> >
>>> >
>>> > Please refer below for ProcParameterMapper class. Now as soon as
>>> iBatis sees
>>> >  $procedureName$, it replaces it with say
>>> > (#stringVal1#,#stringVal2#,#stringVal3#,#intVal1#,#byteVal1#) but it
>>> does
>>> > not replace the placeHolder(#), it passes on this as it is.Which is a
>>> issue.
>>> >
>>> >
>>> >
>>> > If we type
>>> >
>>> procedureName(#stringVal1#,#stringVal2#,#stringVal3#,#intVal1#,#byteVal
>>> 1)
>>> > directly into SQL mapping XML, it replaces place holder, but with
>>> string
>>> > substitution , it does not work.We can't type directly into mapping
>>> xML, as
>>> > this string is composed at run time.Also dynamic tags are not of
>>> help, as
>>> > logic to create
>>> > (#stringVal1#,#stringVal2#,#stringVal3#,#intVal1#,#byteVal1#) complex
>>> > procedure with different types of parameter can't be written in
>>> mapping
>>> > XML.At least I am unable to do.
>>> >
>>> >
>>> >
>>> > I am really struck.Please suggest us the best way to deal with it.
>>> >
>>> >
>>> >
>>> >
>>> >
>>> >
>>> >
>>> >
>>> >
>>> >
>>> >
>>> >
>>> >
>>> > package com.jpmorgan.pyramid.pyrsyst.configure;
>>> >
>>> >
>>> >
>>> > import java.lang.reflect.Field;
>>> >
>>> >
>>> >
>>> > public class ProcParameterMapper {
>>> >
>>> >
>>> >
>>> >       StringBuffer procedureName = new StringBuffer("");
>>> >
>>> >       boolean firstParam = true;
>>> >
>>> >       boolean lastParam = false;
>>> >
>>> >
>>> >
>>> >       String stringVal1;
>>> >
>>> >       String stringVal2;
>>> >
>>> >       String stringVal3;
>>> >
>>> >       String stringVal4;
>>> >
>>> >       String stringVal5;
>>> >
>>> >       String stringVal6;
>>> >
>>> >       String stringVal7;
>>> >
>>> >       String stringVal8;
>>> >
>>> >       String stringVal9;
>>> >
>>> >       String stringVal10;
>>> >
>>> >
>>> >
>>> >       int intVal1;
>>> >
>>> >       int intVal2;
>>> >
>>> >       int intVal3;
>>> >
>>> >       int intVal4;
>>> >
>>> >       int intVal5;
>>> >
>>> >       int intVal6;
>>> >
>>> >       int intVal7;
>>> >
>>> >       int intVal8;
>>> >
>>> >       int intVal9;
>>> >
>>> >       int intVal10;
>>> >
>>> >
>>> >
>>> >       byte byteVal1;
>>> >
>>> >       byte byteVal2;
>>> >
>>> >       byte byteVal3;
>>> >
>>> >       byte byteVal4;
>>> >
>>> >       byte byteVal5;
>>> >
>>> >
>>> >
>>> >       public void setStringVal(String value, int count,String
>>> jdbcType)
>>> > throws ConfigureException {
>>> >
>>> >
>>> >
>>> >             Field[] field =
>>> ProcParameterMapper.class.getDeclaredFields();
>>> >
>>> >
>>> >
>>> >             for (int i = 0; i < field.length; i++) {
>>> >
>>> >                   try {
>>> >
>>> >                         if
>>> > (field[i].getName().endsWith(String.valueOf(count))
>>> >
>>> >                                     && ("String")
>>> >
>>> >
>>> > .equals(field[i].getType().getSimpleName())) {
>>> >
>>> >                               field[i].set(this, value);
>>> >
>>> >                               if (firstParam) {
>>> >
>>> >                                     procedureName.append("(#" +
>>> > field[i].getName()+"#");
>>> >
>>> >                                     firstParam = false;
>>> >
>>> >                               } else if (lastParam) {
>>> >
>>> >                                     procedureName.append(",#" +
>>> > field[i].getName() +"#)");
>>> >
>>> >                               } else {
>>> >
>>> >                                     procedureName.append(",#" +
>>> > field[i].getName()+"#");
>>> >
>>> >                               }
>>> >
>>> >                               break;
>>> >
>>> >
>>> >
>>> >                         }
>>> >
>>> >                   } catch (Exception e) {
>>> >
>>> >                         throw new ConfigureException(
>>> >
>>> >                                     "Exception setting String value
>>> in
>>> > paramMapper"
>>> >
>>> >                                                 + e.getStackTrace());
>>> >
>>> >                   }
>>> >
>>> >
>>> >
>>> >             }
>>> >
>>> >
>>> >
>>> >       }
>>> >
>>> >
>>> >
>>> >       public void setIntVal(int value, int count,String jdbcType)
>>> throws
>>> > ConfigureException {
>>> >
>>> >
>>> >
>>> >             Field[] field =
>>> ProcParameterMapper.class.getDeclaredFields();
>>> >
>>> >
>>> >
>>> >             for (int i = 0; i < field.length; i++) {
>>> >
>>> >                   try {
>>> >
>>> >                         if
>>> > (field[i].getName().endsWith(String.valueOf(count))
>>> >
>>> >                                     &&
>>> > ("int").equals(field[i].getType().getSimpleName())) {
>>> >
>>> >
>>> >
>>> >                               field[i].set(this, value);
>>> >
>>> >                               if (firstParam) {
>>> >
>>> >                                     procedureName.append("(#" +
>>> > field[i].getName()+"#");
>>> >
>>> >                                     firstParam = false;
>>> >
>>> >                               } else if (lastParam) {
>>> >
>>> >                                     procedureName.append(",#" +
>>> > field[i].getName() +"#)");
>>> >
>>> >                               } else {
>>> >
>>> >                                     procedureName.append(",#" +
>>> > field[i].getName()+"#");
>>> >
>>> >                               }
>>> >
>>> >                               break;
>>> >
>>> >                         }
>>> >
>>> >                   } catch (Exception e) {
>>> >
>>> >                         throw new ConfigureException(
>>> >
>>> >                                     "Exception setting int value in
>>> > paramMapper"
>>> >
>>> >                                                 + e.getStackTrace());
>>> >
>>> >                   }
>>> >
>>> >
>>> >
>>> >             }
>>> >
>>> >
>>> >
>>> >       }
>>> >
>>> >
>>> >
>>> >       public void setByteVal(Byte value, int count,String jdbcType)
>>> throws
>>> > ConfigureException {
>>> >
>>> >
>>> >
>>> >             Field[] field =
>>> ProcParameterMapper.class.getDeclaredFields();
>>> >
>>> >
>>> >
>>> >             for (int i = 0; i < field.length; i++) {
>>> >
>>> >                   try {
>>> >
>>> >                         if
>>> > (field[i].getName().endsWith(String.valueOf(count))
>>> >
>>> >                                     &&
>>> > ("byte").equals(field[i].getType().getSimpleName())) {
>>> >
>>> >
>>> >
>>> >                               field[i].set(this, value);
>>> >
>>> >                               if (firstParam) {
>>> >
>>> >                                     procedureName.append("(#" +
>>> > field[i].getName()+"#");
>>> >
>>> >                                     firstParam = false;
>>> >
>>> >                               } else if (lastParam) {
>>> >
>>> >                                     procedureName.append(",#" +
>>> > field[i].getName() + "#)");
>>> >
>>> >                               } else {
>>> >
>>> >                                     procedureName.append(",#" +
>>> > field[i].getName()+"#");
>>> >
>>> >                               }
>>> >
>>> >                               break;
>>> >
>>> >                         }
>>> >
>>> >                   } catch (Exception e) {
>>> >
>>> >                         throw new ConfigureException(
>>> >
>>> >                                     "Exception setting byte value in
>>> > paramMapper"
>>> >
>>> >                                                 + e.getStackTrace());
>>> >
>>> >                   }
>>> >
>>> >
>>> >
>>> >             }
>>> >
>>> >
>>> >
>>> >       }
>>> >
>>> >
>>> >
>>> >       public String toString() {
>>> >
>>> >
>>> >
>>> >             return new String("stringVal1 is" + stringVal1 +
>>> "stringVal2 is"
>>> >
>>> >                         + stringVal2 + "stringVal3 is" + stringVal3 +
>>> > "intVal1 is"
>>> >
>>> >                         + intVal1 + "intVal2 is" + intVal2 + "intVal3
>>> is" +
>>> > intVal3);
>>> >
>>> >
>>> >
>>> >       }
>>> >
>>> >
>>> >
>>> >       public void setProcedure(String procName) {
>>> >
>>> >             procedureName.append(procName);
>>> >
>>> >
>>> >
>>> >       }
>>> >
>>> >
>>> >
>>> >       public String getProcedure(){
>>> >
>>> >             return procedureName.toString();
>>> >
>>> >       }
>>> >
>>> >
>>> >
>>> > }
>>> >
>>> >
>>> >
>>> > May thanks in advance
>>> >
>>> > Waiting for reply
>>> >
>>> > Nicky Jha
>>> >
>>> >
>>> >
>>> > This communication is for informational purposes only. It is not
>>> intended as
>>> > an offer or solicitation for the purchase or sale of any financial
>>> > instrument or as an official confirmation of any transaction. All
>>> market
>>> > prices, data and other information are not warranted as to
>>> completeness or
>>> > accuracy and are subject to change without notice. Any comments or
>>> > statements made herein do not necessarily reflect those of JPMorgan
>>> Chase &
>>> > Co., its subsidiaries and affiliates. This transmission may contain
>>> > information that is privileged, confidential, legally privileged,
>>> and/or
>>> > exempt from disclosure under applicable law. If you are not the
>>> intended
>>> > recipient, you are hereby notified that any disclosure, copying,
>>> > distribution, or use of the information contained herein (including
>>> any
>>> > reliance thereon) is STRICTLY PROHIBITED. Although this transmission
>>> and any
>>> > attachments are believed to be free of any virus or other defect that
>>> might
>>> > affect any computer system into which it is received and opened, it
>>> is the
>>> > responsibility of the recipient to ensure that it is virus free and
>>> no
>>> > responsibility is accepted by JPMorgan Chase & Co., its subsidiaries
>>> and
>>> > affiliates, as applicable, for any loss or damage arising in any way
>>> from
>>> > its use. If you received this transmission in error, please
>>> immediately
>>> > contact the sender and destroy the material in its entirety, whether
>>> in
>>> > electronic or hard copy format. Thank you. Please refer to
>>> > http://www.jpmorgan.com/pages/disclosures for disclosures relating to
>>> > European legal entities.
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail:
>>> [hidden email]<mailto:[hidden email]>
>>> For additional commands, e-mail:
>>> [hidden email]<mailto:[hidden email]>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail:
>> [hidden email]<mailto:[hidden email]>
>> For additional commands, e-mail:
>> [hidden email]<mailto:[hidden email]>
>>
>>
>
> --
> Sent from my mobile device
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail:
> [hidden email]<mailto:[hidden email]>
> For additional commands, e-mail:
> [hidden email]<mailto:[hidden email]>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail:
> [hidden email]<mailto:[hidden email]>
> For additional commands, e-mail:
> [hidden email]<mailto:[hidden email]>
>
>
>

--
Sent from my mobile device

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

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

RE: Need support for Dynamic procedure invocation

Nicky Jha
In reply to this post by Clinton Begin
Hi

Is there a way I can modify {call $procedureName$ <iterate property="params" open="(" close=")"
 conjunction=",">#params[]#</iterate> } to include jdbc types also, so that I can pass null value to database?

Thanks
Nicky



-----Original Message-----
From: Nicky Jha
Sent: Tuesday, May 25, 2010 11:12 AM
To: '[hidden email]'
Subject: RE: Need support for Dynamic procedure invocation

Joe/Jeff
Please help.

this class is not part of framework, this is approach, we have used in case our procedure changes at runtime, so will its parameter.For that we using

public class DynamicProcedureParams {

>>   private String procedureName;
>>   private List<Object> params = new ArrayList<Object>();
>>
>>   // getters and setters here
>> }
>>
>> <procedure id="executeCopyProcs"
>> parameterClass="path.to.DynamicProcedureParams">
>> {call $procedureName$ <iterate property="params" open="(" close=")"
>> conjunction=",">#params[]#</iterate> }
>> </procedure>
>>

Thanks
Nicky




-----Original Message-----
From: Clinton Begin [mailto:[hidden email]]
Sent: Tuesday, May 25, 2010 11:07 AM
To: [hidden email]
Subject: Re: Need support for Dynamic procedure invocation

I'm not familiar with that class, as it's not part of the framework
(maybe post it here).  Short story is that JDBC requires nullable
column types to be specified.  Ibatis allows for this in the parameter
map (crack open the user guide and search for "jdbcType" for more).

Cheers,
Clinton



On 2010-05-24, Nicky Jha <[hidden email]> wrote:

> Hi
>
> I am using parameter class DynamicProcedureParams(as sugessted by Joe Gooch
> ), as my requirement was to build paramaters at run time, so with this
> approach where can I set jdbcType?
>
> Nicky
>
> From: Clinton Begin [mailto:[hidden email]]
> Sent: Tuesday, May 25, 2010 10:39 AM
> To: [hidden email]
> Subject: Re: Need support for Dynamic procedure invocation
>
> Are you setting the jdbcType in your parameter map for all nullable
> columns?
>
> Clinton
> On Mon, May 24, 2010 at 11:03 PM, Nicky Jha
> <[hidden email]<mailto:[hidden email]>> wrote:
>
> Hi
>
> with approach mentioned below(DynamicProcedureParams) , I want to also pass
> some of parameter as null((private List<Object> params = new
> ArrayList<Object>(); params.add(null))), but when I pass string value as
> null , it executes parametes procedure as
>
> call procname(?,?,?,?,?)
> Parameters: [#IMNT_RISK_SENSITIVITY, IMNT_RISK_SENSITIVITY, null, null, 0]
> Types: [java.lang.String, java.lang.String, null, null, java.lang.Byte]
>
> I want type to be java.lang.String instead of null(as with null, I am
> getting Unsupported SQL type 0 )
>
> If I pass blank string "" in place of null in (private List<Object> params =
> new ArrayList<Object>(); params.add("")), Types become java.lang.String, but
> then I think it no more considers it as null.
>
> Please help
>
> Thanks
> Nicky
>
>
>
> -----Original Message-----
> From: Nicky Jha
> Sent: Friday, May 14, 2010 8:46 PM
> To: '[hidden email]<mailto:[hidden email]>'
> Subject: RE: Need support for Dynamic procedure invocation
>
> Joe/Jeff
>
> This worked with your suggestion.Thank you so much!!!
>
> Nicky
>
> -----Original Message-----
> From: Jeff Butler
> [mailto:[hidden email]<mailto:[hidden email]>]
> Sent: Friday, May 14, 2010 6:31 PM
> To: [hidden email]<mailto:[hidden email]>
> Subject: Re: Need support for Dynamic procedure invocation
>
> Yes - this the best approach.
>
> Jeff Butler
>
>
> On 5/14/10, Joe Gooch
> <[hidden email]<mailto:[hidden email]>> wrote:
>> I suggest going with a dynamic SQL approach in the mapped statement.
>>
>> public class DynamicProcedureParams {
>>   private String procedureName;
>>   private List<Object> params = new ArrayList<Object>();
>>
>>   // getters and setters here
>> }
>>
>> <procedure id="executeCopyProcs"
>> parameterClass="path.to.DynamicProcedureParams">
>> {call $procedureName$ <iterate property="params" open="(" close=")"
>> conjunction=",">#params[]#</iterate> }
>> </procedure>
>>
>>
>>
>> Joe
>>
>> Confidentiality Notice:
>> This e-mail transmission may contain confidential and legally privileged
>> information that is intended only for the individual named in the e-mail
>> address. If you are not the intended recipient, you are hereby notified
>> that
>> any disclosure, copying, distribution, or reliance upon the contents of
>> this
>> e-mail message is strictly prohibited. If you have received this e-mail
>> transmission in error, please reply to the sender, so that proper
>> delivery
>> can be arranged, and please delete the message from your mail box.
>>
>>> -----Original Message-----
>>> From: Nicky Jha
>>> [mailto:[hidden email]<mailto:[hidden email]>]
>>> Sent: Friday, May 14, 2010 7:38 AM
>>> To: [hidden email]<mailto:[hidden email]>
>>> Subject: RE: Need support for Dynamic procedure invocation
>>>
>>> Hi Jeff,
>>>
>>> Thanks for this, if I was to do as suggested and compose the entire
>>> string with parameter values, how will the parameter types (e.g. date,
>>> smallint etc) be handled?
>>>
>>> Nicky!
>>>
>>> -----Original Message-----
>>> From: Jeff Butler
>>> [mailto:[hidden email]<mailto:[hidden email]>]
>>> Sent: Friday, May 14, 2010 4:53 PM
>>> To: [hidden email]<mailto:[hidden email]>
>>> Subject: Re: Need support for Dynamic procedure invocation
>>>
>>> This won't work (as you've discovered).  iBATIS 2.x does not reparse
>>> the string for variables after string substitution.  You'll need to do
>>> this with the dynamic tags, or you'll need to compose the entire
>>> string (including parameter values - like {call myproc('fred')}).
>>>
>>> Jeff Butler
>>>
>>>
>>> On Fri, May 14, 2010 at 4:12 AM, Nicky Jha
>>> <[hidden email]<mailto:[hidden email]>>
>>> wrote:
>>> > Hi Team,
>>> >
>>> >
>>> >
>>> > I am having hard time resolving following issue.Please help.
>>> >
>>> > We are using Ibatis 2.X.
>>> >
>>> > In our application we want capability to invoke stored procedure by
>>> reading
>>> > it from property xml file.We provide procedure name parameters,
>>> parameters
>>> > type to  property xml file.
>>> >
>>> > Now from this xml file I have created one dynamic procedure string
>>> like
>>> >
>>> procedureName(#stringVal1#,#stringVal2#,#stringVal3#,#intVal1#,#byteVal
>>> 1#).
>>> >
>>> > Now I want to call this procedure from Ibatis SQL mapping XML like
>>> this
>>> >
>>> >
>>> >
>>> > <procedure id="executeCopyProcs"
>>> >
>>> parameterClass="com.jpmorgan.pyramid.pyrsyst.configure.ProcParameterMap
>>> per">
>>> >
>>> > {call $procedureName$ }
>>> >
>>> >
>>> >
>>> > </procedure>
>>> >
>>> >
>>> >
>>> > Please refer below for ProcParameterMapper class. Now as soon as
>>> iBatis sees
>>> >  $procedureName$, it replaces it with say
>>> > (#stringVal1#,#stringVal2#,#stringVal3#,#intVal1#,#byteVal1#) but it
>>> does
>>> > not replace the placeHolder(#), it passes on this as it is.Which is a
>>> issue.
>>> >
>>> >
>>> >
>>> > If we type
>>> >
>>> procedureName(#stringVal1#,#stringVal2#,#stringVal3#,#intVal1#,#byteVal
>>> 1)
>>> > directly into SQL mapping XML, it replaces place holder, but with
>>> string
>>> > substitution , it does not work.We can't type directly into mapping
>>> xML, as
>>> > this string is composed at run time.Also dynamic tags are not of
>>> help, as
>>> > logic to create
>>> > (#stringVal1#,#stringVal2#,#stringVal3#,#intVal1#,#byteVal1#) complex
>>> > procedure with different types of parameter can't be written in
>>> mapping
>>> > XML.At least I am unable to do.
>>> >
>>> >
>>> >
>>> > I am really struck.Please suggest us the best way to deal with it.
>>> >
>>> >
>>> >
>>> >
>>> >
>>> >
>>> >
>>> >
>>> >
>>> >
>>> >
>>> >
>>> >
>>> > package com.jpmorgan.pyramid.pyrsyst.configure;
>>> >
>>> >
>>> >
>>> > import java.lang.reflect.Field;
>>> >
>>> >
>>> >
>>> > public class ProcParameterMapper {
>>> >
>>> >
>>> >
>>> >       StringBuffer procedureName = new StringBuffer("");
>>> >
>>> >       boolean firstParam = true;
>>> >
>>> >       boolean lastParam = false;
>>> >
>>> >
>>> >
>>> >       String stringVal1;
>>> >
>>> >       String stringVal2;
>>> >
>>> >       String stringVal3;
>>> >
>>> >       String stringVal4;
>>> >
>>> >       String stringVal5;
>>> >
>>> >       String stringVal6;
>>> >
>>> >       String stringVal7;
>>> >
>>> >       String stringVal8;
>>> >
>>> >       String stringVal9;
>>> >
>>> >       String stringVal10;
>>> >
>>> >
>>> >
>>> >       int intVal1;
>>> >
>>> >       int intVal2;
>>> >
>>> >       int intVal3;
>>> >
>>> >       int intVal4;
>>> >
>>> >       int intVal5;
>>> >
>>> >       int intVal6;
>>> >
>>> >       int intVal7;
>>> >
>>> >       int intVal8;
>>> >
>>> >       int intVal9;
>>> >
>>> >       int intVal10;
>>> >
>>> >
>>> >
>>> >       byte byteVal1;
>>> >
>>> >       byte byteVal2;
>>> >
>>> >       byte byteVal3;
>>> >
>>> >       byte byteVal4;
>>> >
>>> >       byte byteVal5;
>>> >
>>> >
>>> >
>>> >       public void setStringVal(String value, int count,String
>>> jdbcType)
>>> > throws ConfigureException {
>>> >
>>> >
>>> >
>>> >             Field[] field =
>>> ProcParameterMapper.class.getDeclaredFields();
>>> >
>>> >
>>> >
>>> >             for (int i = 0; i < field.length; i++) {
>>> >
>>> >                   try {
>>> >
>>> >                         if
>>> > (field[i].getName().endsWith(String.valueOf(count))
>>> >
>>> >                                     && ("String")
>>> >
>>> >
>>> > .equals(field[i].getType().getSimpleName())) {
>>> >
>>> >                               field[i].set(this, value);
>>> >
>>> >                               if (firstParam) {
>>> >
>>> >                                     procedureName.append("(#" +
>>> > field[i].getName()+"#");
>>> >
>>> >                                     firstParam = false;
>>> >
>>> >                               } else if (lastParam) {
>>> >
>>> >                                     procedureName.append(",#" +
>>> > field[i].getName() +"#)");
>>> >
>>> >                               } else {
>>> >
>>> >                                     procedureName.append(",#" +
>>> > field[i].getName()+"#");
>>> >
>>> >                               }
>>> >
>>> >                               break;
>>> >
>>> >
>>> >
>>> >                         }
>>> >
>>> >                   } catch (Exception e) {
>>> >
>>> >                         throw new ConfigureException(
>>> >
>>> >                                     "Exception setting String value
>>> in
>>> > paramMapper"
>>> >
>>> >                                                 + e.getStackTrace());
>>> >
>>> >                   }
>>> >
>>> >
>>> >
>>> >             }
>>> >
>>> >
>>> >
>>> >       }
>>> >
>>> >
>>> >
>>> >       public void setIntVal(int value, int count,String jdbcType)
>>> throws
>>> > ConfigureException {
>>> >
>>> >
>>> >
>>> >             Field[] field =
>>> ProcParameterMapper.class.getDeclaredFields();
>>> >
>>> >
>>> >
>>> >             for (int i = 0; i < field.length; i++) {
>>> >
>>> >                   try {
>>> >
>>> >                         if
>>> > (field[i].getName().endsWith(String.valueOf(count))
>>> >
>>> >                                     &&
>>> > ("int").equals(field[i].getType().getSimpleName())) {
>>> >
>>> >
>>> >
>>> >                               field[i].set(this, value);
>>> >
>>> >                               if (firstParam) {
>>> >
>>> >                                     procedureName.append("(#" +
>>> > field[i].getName()+"#");
>>> >
>>> >                                     firstParam = false;
>>> >
>>> >                               } else if (lastParam) {
>>> >
>>> >                                     procedureName.append(",#" +
>>> > field[i].getName() +"#)");
>>> >
>>> >                               } else {
>>> >
>>> >                                     procedureName.append(",#" +
>>> > field[i].getName()+"#");
>>> >
>>> >                               }
>>> >
>>> >                               break;
>>> >
>>> >                         }
>>> >
>>> >                   } catch (Exception e) {
>>> >
>>> >                         throw new ConfigureException(
>>> >
>>> >                                     "Exception setting int value in
>>> > paramMapper"
>>> >
>>> >                                                 + e.getStackTrace());
>>> >
>>> >                   }
>>> >
>>> >
>>> >
>>> >             }
>>> >
>>> >
>>> >
>>> >       }
>>> >
>>> >
>>> >
>>> >       public void setByteVal(Byte value, int count,String jdbcType)
>>> throws
>>> > ConfigureException {
>>> >
>>> >
>>> >
>>> >             Field[] field =
>>> ProcParameterMapper.class.getDeclaredFields();
>>> >
>>> >
>>> >
>>> >             for (int i = 0; i < field.length; i++) {
>>> >
>>> >                   try {
>>> >
>>> >                         if
>>> > (field[i].getName().endsWith(String.valueOf(count))
>>> >
>>> >                                     &&
>>> > ("byte").equals(field[i].getType().getSimpleName())) {
>>> >
>>> >
>>> >
>>> >                               field[i].set(this, value);
>>> >
>>> >                               if (firstParam) {
>>> >
>>> >                                     procedureName.append("(#" +
>>> > field[i].getName()+"#");
>>> >
>>> >                                     firstParam = false;
>>> >
>>> >                               } else if (lastParam) {
>>> >
>>> >                                     procedureName.append(",#" +
>>> > field[i].getName() + "#)");
>>> >
>>> >                               } else {
>>> >
>>> >                                     procedureName.append(",#" +
>>> > field[i].getName()+"#");
>>> >
>>> >                               }
>>> >
>>> >                               break;
>>> >
>>> >                         }
>>> >
>>> >                   } catch (Exception e) {
>>> >
>>> >                         throw new ConfigureException(
>>> >
>>> >                                     "Exception setting byte value in
>>> > paramMapper"
>>> >
>>> >                                                 + e.getStackTrace());
>>> >
>>> >                   }
>>> >
>>> >
>>> >
>>> >             }
>>> >
>>> >
>>> >
>>> >       }
>>> >
>>> >
>>> >
>>> >       public String toString() {
>>> >
>>> >
>>> >
>>> >             return new String("stringVal1 is" + stringVal1 +
>>> "stringVal2 is"
>>> >
>>> >                         + stringVal2 + "stringVal3 is" + stringVal3 +
>>> > "intVal1 is"
>>> >
>>> >                         + intVal1 + "intVal2 is" + intVal2 + "intVal3
>>> is" +
>>> > intVal3);
>>> >
>>> >
>>> >
>>> >       }
>>> >
>>> >
>>> >
>>> >       public void setProcedure(String procName) {
>>> >
>>> >             procedureName.append(procName);
>>> >
>>> >
>>> >
>>> >       }
>>> >
>>> >
>>> >
>>> >       public String getProcedure(){
>>> >
>>> >             return procedureName.toString();
>>> >
>>> >       }
>>> >
>>> >
>>> >
>>> > }
>>> >
>>> >
>>> >
>>> > May thanks in advance
>>> >
>>> > Waiting for reply
>>> >
>>> > Nicky Jha
>>> >
>>> >
>>> >
>>> > This communication is for informational purposes only. It is not
>>> intended as
>>> > an offer or solicitation for the purchase or sale of any financial
>>> > instrument or as an official confirmation of any transaction. All
>>> market
>>> > prices, data and other information are not warranted as to
>>> completeness or
>>> > accuracy and are subject to change without notice. Any comments or
>>> > statements made herein do not necessarily reflect those of JPMorgan
>>> Chase &
>>> > Co., its subsidiaries and affiliates. This transmission may contain
>>> > information that is privileged, confidential, legally privileged,
>>> and/or
>>> > exempt from disclosure under applicable law. If you are not the
>>> intended
>>> > recipient, you are hereby notified that any disclosure, copying,
>>> > distribution, or use of the information contained herein (including
>>> any
>>> > reliance thereon) is STRICTLY PROHIBITED. Although this transmission
>>> and any
>>> > attachments are believed to be free of any virus or other defect that
>>> might
>>> > affect any computer system into which it is received and opened, it
>>> is the
>>> > responsibility of the recipient to ensure that it is virus free and
>>> no
>>> > responsibility is accepted by JPMorgan Chase & Co., its subsidiaries
>>> and
>>> > affiliates, as applicable, for any loss or damage arising in any way
>>> from
>>> > its use. If you received this transmission in error, please
>>> immediately
>>> > contact the sender and destroy the material in its entirety, whether
>>> in
>>> > electronic or hard copy format. Thank you. Please refer to
>>> > http://www.jpmorgan.com/pages/disclosures for disclosures relating to
>>> > European legal entities.
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail:
>>> [hidden email]<mailto:[hidden email]>
>>> For additional commands, e-mail:
>>> [hidden email]<mailto:[hidden email]>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail:
>> [hidden email]<mailto:[hidden email]>
>> For additional commands, e-mail:
>> [hidden email]<mailto:[hidden email]>
>>
>>
>
> --
> Sent from my mobile device
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail:
> [hidden email]<mailto:[hidden email]>
> For additional commands, e-mail:
> [hidden email]<mailto:[hidden email]>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail:
> [hidden email]<mailto:[hidden email]>
> For additional commands, e-mail:
> [hidden email]<mailto:[hidden email]>
>
>
>

--
Sent from my mobile device

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

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

Re: Need support for Dynamic procedure invocation

Jeff Butler-2
This would be a great time to switch to mybatis and usa a
@SelectProvider to write the query exactly as you need it. I think
that the dynamic SQL tags won't work in this case.

Jeff Butler


On 5/25/10, Nicky Jha <[hidden email]> wrote:

> Hi
>
> Is there a way I can modify {call $procedureName$ <iterate property="params"
> open="(" close=")"
>  conjunction=",">#params[]#</iterate> } to include jdbc types also, so that
> I can pass null value to database?
>
> Thanks
> Nicky
>
>
>
> -----Original Message-----
> From: Nicky Jha
> Sent: Tuesday, May 25, 2010 11:12 AM
> To: '[hidden email]'
> Subject: RE: Need support for Dynamic procedure invocation
>
> Joe/Jeff
> Please help.
>
> this class is not part of framework, this is approach, we have used in case
> our procedure changes at runtime, so will its parameter.For that we using
>
> public class DynamicProcedureParams {
>>>   private String procedureName;
>>>   private List<Object> params = new ArrayList<Object>();
>>>
>>>   // getters and setters here
>>> }
>>>
>>> <procedure id="executeCopyProcs"
>>> parameterClass="path.to.DynamicProcedureParams">
>>> {call $procedureName$ <iterate property="params" open="(" close=")"
>>> conjunction=",">#params[]#</iterate> }
>>> </procedure>
>>>
>
> Thanks
> Nicky
>
>
>
>
> -----Original Message-----
> From: Clinton Begin [mailto:[hidden email]]
> Sent: Tuesday, May 25, 2010 11:07 AM
> To: [hidden email]
> Subject: Re: Need support for Dynamic procedure invocation
>
> I'm not familiar with that class, as it's not part of the framework
> (maybe post it here).  Short story is that JDBC requires nullable
> column types to be specified.  Ibatis allows for this in the parameter
> map (crack open the user guide and search for "jdbcType" for more).
>
> Cheers,
> Clinton
>
>
>
> On 2010-05-24, Nicky Jha <[hidden email]> wrote:
>> Hi
>>
>> I am using parameter class DynamicProcedureParams(as sugessted by Joe
>> Gooch
>> ), as my requirement was to build paramaters at run time, so with this
>> approach where can I set jdbcType?
>>
>> Nicky
>>
>> From: Clinton Begin [mailto:[hidden email]]
>> Sent: Tuesday, May 25, 2010 10:39 AM
>> To: [hidden email]
>> Subject: Re: Need support for Dynamic procedure invocation
>>
>> Are you setting the jdbcType in your parameter map for all nullable
>> columns?
>>
>> Clinton
>> On Mon, May 24, 2010 at 11:03 PM, Nicky Jha
>> <[hidden email]<mailto:[hidden email]>> wrote:
>>
>> Hi
>>
>> with approach mentioned below(DynamicProcedureParams) , I want to also
>> pass
>> some of parameter as null((private List<Object> params = new
>> ArrayList<Object>(); params.add(null))), but when I pass string value as
>> null , it executes parametes procedure as
>>
>> call procname(?,?,?,?,?)
>> Parameters: [#IMNT_RISK_SENSITIVITY, IMNT_RISK_SENSITIVITY, null, null, 0]
>> Types: [java.lang.String, java.lang.String, null, null, java.lang.Byte]
>>
>> I want type to be java.lang.String instead of null(as with null, I am
>> getting Unsupported SQL type 0 )
>>
>> If I pass blank string "" in place of null in (private List<Object> params
>> =
>> new ArrayList<Object>(); params.add("")), Types become java.lang.String,
>> but
>> then I think it no more considers it as null.
>>
>> Please help
>>
>> Thanks
>> Nicky
>>
>>
>>
>> -----Original Message-----
>> From: Nicky Jha
>> Sent: Friday, May 14, 2010 8:46 PM
>> To: '[hidden email]<mailto:[hidden email]>'
>> Subject: RE: Need support for Dynamic procedure invocation
>>
>> Joe/Jeff
>>
>> This worked with your suggestion.Thank you so much!!!
>>
>> Nicky
>>
>> -----Original Message-----
>> From: Jeff Butler
>> [mailto:[hidden email]<mailto:[hidden email]>]
>> Sent: Friday, May 14, 2010 6:31 PM
>> To: [hidden email]<mailto:[hidden email]>
>> Subject: Re: Need support for Dynamic procedure invocation
>>
>> Yes - this the best approach.
>>
>> Jeff Butler
>>
>>
>> On 5/14/10, Joe Gooch
>> <[hidden email]<mailto:[hidden email]>> wrote:
>>> I suggest going with a dynamic SQL approach in the mapped statement.
>>>
>>> public class DynamicProcedureParams {
>>>   private String procedureName;
>>>   private List<Object> params = new ArrayList<Object>();
>>>
>>>   // getters and setters here
>>> }
>>>
>>> <procedure id="executeCopyProcs"
>>> parameterClass="path.to.DynamicProcedureParams">
>>> {call $procedureName$ <iterate property="params" open="(" close=")"
>>> conjunction=",">#params[]#</iterate> }
>>> </procedure>
>>>
>>>
>>>
>>> Joe
>>>
>>> Confidentiality Notice:
>>> This e-mail transmission may contain confidential and legally privileged
>>> information that is intended only for the individual named in the e-mail
>>> address. If you are not the intended recipient, you are hereby notified
>>> that
>>> any disclosure, copying, distribution, or reliance upon the contents of
>>> this
>>> e-mail message is strictly prohibited. If you have received this e-mail
>>> transmission in error, please reply to the sender, so that proper
>>> delivery
>>> can be arranged, and please delete the message from your mail box.
>>>
>>>> -----Original Message-----
>>>> From: Nicky Jha
>>>> [mailto:[hidden email]<mailto:[hidden email]>]
>>>> Sent: Friday, May 14, 2010 7:38 AM
>>>> To: [hidden email]<mailto:[hidden email]>
>>>> Subject: RE: Need support for Dynamic procedure invocation
>>>>
>>>> Hi Jeff,
>>>>
>>>> Thanks for this, if I was to do as suggested and compose the entire
>>>> string with parameter values, how will the parameter types (e.g. date,
>>>> smallint etc) be handled?
>>>>
>>>> Nicky!
>>>>
>>>> -----Original Message-----
>>>> From: Jeff Butler
>>>> [mailto:[hidden email]<mailto:[hidden email]>]
>>>> Sent: Friday, May 14, 2010 4:53 PM
>>>> To: [hidden email]<mailto:[hidden email]>
>>>> Subject: Re: Need support for Dynamic procedure invocation
>>>>
>>>> This won't work (as you've discovered).  iBATIS 2.x does not reparse
>>>> the string for variables after string substitution.  You'll need to do
>>>> this with the dynamic tags, or you'll need to compose the entire
>>>> string (including parameter values - like {call myproc('fred')}).
>>>>
>>>> Jeff Butler
>>>>
>>>>
>>>> On Fri, May 14, 2010 at 4:12 AM, Nicky Jha
>>>> <[hidden email]<mailto:[hidden email]>>
>>>> wrote:
>>>> > Hi Team,
>>>> >
>>>> >
>>>> >
>>>> > I am having hard time resolving following issue.Please help.
>>>> >
>>>> > We are using Ibatis 2.X.
>>>> >
>>>> > In our application we want capability to invoke stored procedure by
>>>> reading
>>>> > it from property xml file.We provide procedure name parameters,
>>>> parameters
>>>> > type to  property xml file.
>>>> >
>>>> > Now from this xml file I have created one dynamic procedure string
>>>> like
>>>> >
>>>> procedureName(#stringVal1#,#stringVal2#,#stringVal3#,#intVal1#,#byteVal
>>>> 1#).
>>>> >
>>>> > Now I want to call this procedure from Ibatis SQL mapping XML like
>>>> this
>>>> >
>>>> >
>>>> >
>>>> > <procedure id="executeCopyProcs"
>>>> >
>>>> parameterClass="com.jpmorgan.pyramid.pyrsyst.configure.ProcParameterMap
>>>> per">
>>>> >
>>>> > {call $procedureName$ }
>>>> >
>>>> >
>>>> >
>>>> > </procedure>
>>>> >
>>>> >
>>>> >
>>>> > Please refer below for ProcParameterMapper class. Now as soon as
>>>> iBatis sees
>>>> >  $procedureName$, it replaces it with say
>>>> > (#stringVal1#,#stringVal2#,#stringVal3#,#intVal1#,#byteVal1#) but it
>>>> does
>>>> > not replace the placeHolder(#), it passes on this as it is.Which is a
>>>> issue.
>>>> >
>>>> >
>>>> >
>>>> > If we type
>>>> >
>>>> procedureName(#stringVal1#,#stringVal2#,#stringVal3#,#intVal1#,#byteVal
>>>> 1)
>>>> > directly into SQL mapping XML, it replaces place holder, but with
>>>> string
>>>> > substitution , it does not work.We can't type directly into mapping
>>>> xML, as
>>>> > this string is composed at run time.Also dynamic tags are not of
>>>> help, as
>>>> > logic to create
>>>> > (#stringVal1#,#stringVal2#,#stringVal3#,#intVal1#,#byteVal1#) complex
>>>> > procedure with different types of parameter can't be written in
>>>> mapping
>>>> > XML.At least I am unable to do.
>>>> >
>>>> >
>>>> >
>>>> > I am really struck.Please suggest us the best way to deal with it.
>>>> >
>>>> >
>>>> >
>>>> >
>>>> >
>>>> >
>>>> >
>>>> >
>>>> >
>>>> >
>>>> >
>>>> >
>>>> >
>>>> > package com.jpmorgan.pyramid.pyrsyst.configure;
>>>> >
>>>> >
>>>> >
>>>> > import java.lang.reflect.Field;
>>>> >
>>>> >
>>>> >
>>>> > public class ProcParameterMapper {
>>>> >
>>>> >
>>>> >
>>>> >       StringBuffer procedureName = new StringBuffer("");
>>>> >
>>>> >       boolean firstParam = true;
>>>> >
>>>> >       boolean lastParam = false;
>>>> >
>>>> >
>>>> >
>>>> >       String stringVal1;
>>>> >
>>>> >       String stringVal2;
>>>> >
>>>> >       String stringVal3;
>>>> >
>>>> >       String stringVal4;
>>>> >
>>>> >       String stringVal5;
>>>> >
>>>> >       String stringVal6;
>>>> >
>>>> >       String stringVal7;
>>>> >
>>>> >       String stringVal8;
>>>> >
>>>> >       String stringVal9;
>>>> >
>>>> >       String stringVal10;
>>>> >
>>>> >
>>>> >
>>>> >       int intVal1;
>>>> >
>>>> >       int intVal2;
>>>> >
>>>> >       int intVal3;
>>>> >
>>>> >       int intVal4;
>>>> >
>>>> >       int intVal5;
>>>> >
>>>> >       int intVal6;
>>>> >
>>>> >       int intVal7;
>>>> >
>>>> >       int intVal8;
>>>> >
>>>> >       int intVal9;
>>>> >
>>>> >       int intVal10;
>>>> >
>>>> >
>>>> >
>>>> >       byte byteVal1;
>>>> >
>>>> >       byte byteVal2;
>>>> >
>>>> >       byte byteVal3;
>>>> >
>>>> >       byte byteVal4;
>>>> >
>>>> >       byte byteVal5;
>>>> >
>>>> >
>>>> >
>>>> >       public void setStringVal(String value, int count,String
>>>> jdbcType)
>>>> > throws ConfigureException {
>>>> >
>>>> >
>>>> >
>>>> >             Field[] field =
>>>> ProcParameterMapper.class.getDeclaredFields();
>>>> >
>>>> >
>>>> >
>>>> >             for (int i = 0; i < field.length; i++) {
>>>> >
>>>> >                   try {
>>>> >
>>>> >                         if
>>>> > (field[i].getName().endsWith(String.valueOf(count))
>>>> >
>>>> >                                     && ("String")
>>>> >
>>>> >
>>>> > .equals(field[i].getType().getSimpleName())) {
>>>> >
>>>> >                               field[i].set(this, value);
>>>> >
>>>> >                               if (firstParam) {
>>>> >
>>>> >                                     procedureName.append("(#" +
>>>> > field[i].getName()+"#");
>>>> >
>>>> >                                     firstParam = false;
>>>> >
>>>> >                               } else if (lastParam) {
>>>> >
>>>> >                                     procedureName.append(",#" +
>>>> > field[i].getName() +"#)");
>>>> >
>>>> >                               } else {
>>>> >
>>>> >                                     procedureName.append(",#" +
>>>> > field[i].getName()+"#");
>>>> >
>>>> >                               }
>>>> >
>>>> >                               break;
>>>> >
>>>> >
>>>> >
>>>> >                         }
>>>> >
>>>> >                   } catch (Exception e) {
>>>> >
>>>> >                         throw new ConfigureException(
>>>> >
>>>> >                                     "Exception setting String value
>>>> in
>>>> > paramMapper"
>>>> >
>>>> >                                                 + e.getStackTrace());
>>>> >
>>>> >                   }
>>>> >
>>>> >
>>>> >
>>>> >             }
>>>> >
>>>> >
>>>> >
>>>> >       }
>>>> >
>>>> >
>>>> >
>>>> >       public void setIntVal(int value, int count,String jdbcType)
>>>> throws
>>>> > ConfigureException {
>>>> >
>>>> >
>>>> >
>>>> >             Field[] field =
>>>> ProcParameterMapper.class.getDeclaredFields();
>>>> >
>>>> >
>>>> >
>>>> >             for (int i = 0; i < field.length; i++) {
>>>> >
>>>> >                   try {
>>>> >
>>>> >                         if
>>>> > (field[i].getName().endsWith(String.valueOf(count))
>>>> >
>>>> >                                     &&
>>>> > ("int").equals(field[i].getType().getSimpleName())) {
>>>> >
>>>> >
>>>> >
>>>> >                               field[i].set(this, value);
>>>> >
>>>> >                               if (firstParam) {
>>>> >
>>>> >                                     procedureName.append("(#" +
>>>> > field[i].getName()+"#");
>>>> >
>>>> >                                     firstParam = false;
>>>> >
>>>> >                               } else if (lastParam) {
>>>> >
>>>> >                                     procedureName.append(",#" +
>>>> > field[i].getName() +"#)");
>>>> >
>>>> >                               } else {
>>>> >
>>>> >                                     procedureName.append(",#" +
>>>> > field[i].getName()+"#");
>>>> >
>>>> >                               }
>>>> >
>>>> >                               break;
>>>> >
>>>> >                         }
>>>> >
>>>> >                   } catch (Exception e) {
>>>> >
>>>> >                         throw new ConfigureException(
>>>> >
>>>> >                                     "Exception setting int value in
>>>> > paramMapper"
>>>> >
>>>> >                                                 + e.getStackTrace());
>>>> >
>>>> >                   }
>>>> >
>>>> >
>>>> >
>>>> >             }
>>>> >
>>>> >
>>>> >
>>>> >       }
>>>> >
>>>> >
>>>> >
>>>> >       public void setByteVal(Byte value, int count,String jdbcType)
>>>> throws
>>>> > ConfigureException {
>>>> >
>>>> >
>>>> >
>>>> >             Field[] field =
>>>> ProcParameterMapper.class.getDeclaredFields();
>>>> >
>>>> >
>>>> >
>>>> >             for (int i = 0; i < field.length; i++) {
>>>> >
>>>> >                   try {
>>>> >
>>>> >                         if
>>>> > (field[i].getName().endsWith(String.valueOf(count))
>>>> >
>>>> >                                     &&
>>>> > ("byte").equals(field[i].getType().getSimpleName())) {
>>>> >
>>>> >
>>>> >
>>>> >                               field[i].set(this, value);
>>>> >
>>>> >                               if (firstParam) {
>>>> >
>>>> >                                     procedureName.append("(#" +
>>>> > field[i].getName()+"#");
>>>> >
>>>> >                                     firstParam = false;
>>>> >
>>>> >                               } else if (lastParam) {
>>>> >
>>>> >                                     procedureName.append(",#" +
>>>> > field[i].getName() + "#)");
>>>> >
>>>> >                               } else {
>>>> >
>>>> >                                     procedureName.append(",#" +
>>>> > field[i].getName()+"#");
>>>> >
>>>> >                               }
>>>> >
>>>> >                               break;
>>>> >
>>>> >                         }
>>>> >
>>>> >                   } catch (Exception e) {
>>>> >
>>>> >                         throw new ConfigureException(
>>>> >
>>>> >                                     "Exception setting byte value in
>>>> > paramMapper"
>>>> >
>>>> >                                                 + e.getStackTrace());
>>>> >
>>>> >                   }
>>>> >
>>>> >
>>>> >
>>>> >             }
>>>> >
>>>> >
>>>> >
>>>> >       }
>>>> >
>>>> >
>>>> >
>>>> >       public String toString() {
>>>> >
>>>> >
>>>> >
>>>> >             return new String("stringVal1 is" + stringVal1 +
>>>> "stringVal2 is"
>>>> >
>>>> >                         + stringVal2 + "stringVal3 is" + stringVal3 +
>>>> > "intVal1 is"
>>>> >
>>>> >                         + intVal1 + "intVal2 is" + intVal2 + "intVal3
>>>> is" +
>>>> > intVal3);
>>>> >
>>>> >
>>>> >
>>>> >       }
>>>> >
>>>> >
>>>> >
>>>> >       public void setProcedure(String procName) {
>>>> >
>>>> >             procedureName.append(procName);
>>>> >
>>>> >
>>>> >
>>>> >       }
>>>> >
>>>> >
>>>> >
>>>> >       public String getProcedure(){
>>>> >
>>>> >             return procedureName.toString();
>>>> >
>>>> >       }
>>>> >
>>>> >
>>>> >
>>>> > }
>>>> >
>>>> >
>>>> >
>>>> > May thanks in advance
>>>> >
>>>> > Waiting for reply
>>>> >
>>>> > Nicky Jha
>>>> >
>>>> >
>>>> >
>>>> > This communication is for informational purposes only. It is not
>>>> intended as
>>>> > an offer or solicitation for the purchase or sale of any financial
>>>> > instrument or as an official confirmation of any transaction. All
>>>> market
>>>> > prices, data and other information are not warranted as to
>>>> completeness or
>>>> > accuracy and are subject to change without notice. Any comments or
>>>> > statements made herein do not necessarily reflect those of JPMorgan
>>>> Chase &
>>>> > Co., its subsidiaries and affiliates. This transmission may contain
>>>> > information that is privileged, confidential, legally privileged,
>>>> and/or
>>>> > exempt from disclosure under applicable law. If you are not the
>>>> intended
>>>> > recipient, you are hereby notified that any disclosure, copying,
>>>> > distribution, or use of the information contained herein (including
>>>> any
>>>> > reliance thereon) is STRICTLY PROHIBITED. Although this transmission
>>>> and any
>>>> > attachments are believed to be free of any virus or other defect that
>>>> might
>>>> > affect any computer system into which it is received and opened, it
>>>> is the
>>>> > responsibility of the recipient to ensure that it is virus free and
>>>> no
>>>> > responsibility is accepted by JPMorgan Chase & Co., its subsidiaries
>>>> and
>>>> > affiliates, as applicable, for any loss or damage arising in any way
>>>> from
>>>> > its use. If you received this transmission in error, please
>>>> immediately
>>>> > contact the sender and destroy the material in its entirety, whether
>>>> in
>>>> > electronic or hard copy format. Thank you. Please refer to
>>>> > http://www.jpmorgan.com/pages/disclosures for disclosures relating to
>>>> > European legal entities.
>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail:
>>>> [hidden email]<mailto:[hidden email]>
>>>> For additional commands, e-mail:
>>>> [hidden email]<mailto:[hidden email]>
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail:
>>> [hidden email]<mailto:[hidden email]>
>>> For additional commands, e-mail:
>>> [hidden email]<mailto:[hidden email]>
>>>
>>>
>>
>> --
>> Sent from my mobile device
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail:
>> [hidden email]<mailto:[hidden email]>
>> For additional commands, e-mail:
>> [hidden email]<mailto:[hidden email]>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail:
>> [hidden email]<mailto:[hidden email]>
>> For additional commands, e-mail:
>> [hidden email]<mailto:[hidden email]>
>>
>>
>>
>
> --
> Sent from my mobile device
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>

--
Sent from my mobile device

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

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

RE: Need support for Dynamic procedure invocation

Nicky Jha
Hi Jeff,

we wanted to switch to myIbatis as we started this project, but we are  using ibatis Spring integration in our project and I think new version of Ibatis is not completely supported by Spring as of now.For example SqlMapExecutor executor etc. and we want to run Ibatis wit Spring :(.Can you help us as when can we expect Spring support for new version of Ibatis.

So we are helpless to use Ibatis 2.X.So no way for this problem then with Ibatis 2.x?

Thanks
Nicky



-----Original Message-----
From: Jeff Butler [mailto:[hidden email]]
Sent: Tuesday, May 25, 2010 7:14 PM
To: [hidden email]
Subject: Re: Need support for Dynamic procedure invocation

This would be a great time to switch to mybatis and usa a
@SelectProvider to write the query exactly as you need it. I think
that the dynamic SQL tags won't work in this case.

Jeff Butler


On 5/25/10, Nicky Jha <[hidden email]> wrote:

> Hi
>
> Is there a way I can modify {call $procedureName$ <iterate property="params"
> open="(" close=")"
>  conjunction=",">#params[]#</iterate> } to include jdbc types also, so that
> I can pass null value to database?
>
> Thanks
> Nicky
>
>
>
> -----Original Message-----
> From: Nicky Jha
> Sent: Tuesday, May 25, 2010 11:12 AM
> To: '[hidden email]'
> Subject: RE: Need support for Dynamic procedure invocation
>
> Joe/Jeff
> Please help.
>
> this class is not part of framework, this is approach, we have used in case
> our procedure changes at runtime, so will its parameter.For that we using
>
> public class DynamicProcedureParams {
>>>   private String procedureName;
>>>   private List<Object> params = new ArrayList<Object>();
>>>
>>>   // getters and setters here
>>> }
>>>
>>> <procedure id="executeCopyProcs"
>>> parameterClass="path.to.DynamicProcedureParams">
>>> {call $procedureName$ <iterate property="params" open="(" close=")"
>>> conjunction=",">#params[]#</iterate> }
>>> </procedure>
>>>
>
> Thanks
> Nicky
>
>
>
>
> -----Original Message-----
> From: Clinton Begin [mailto:[hidden email]]
> Sent: Tuesday, May 25, 2010 11:07 AM
> To: [hidden email]
> Subject: Re: Need support for Dynamic procedure invocation
>
> I'm not familiar with that class, as it's not part of the framework
> (maybe post it here).  Short story is that JDBC requires nullable
> column types to be specified.  Ibatis allows for this in the parameter
> map (crack open the user guide and search for "jdbcType" for more).
>
> Cheers,
> Clinton
>
>
>
> On 2010-05-24, Nicky Jha <[hidden email]> wrote:
>> Hi
>>
>> I am using parameter class DynamicProcedureParams(as sugessted by Joe
>> Gooch
>> ), as my requirement was to build paramaters at run time, so with this
>> approach where can I set jdbcType?
>>
>> Nicky
>>
>> From: Clinton Begin [mailto:[hidden email]]
>> Sent: Tuesday, May 25, 2010 10:39 AM
>> To: [hidden email]
>> Subject: Re: Need support for Dynamic procedure invocation
>>
>> Are you setting the jdbcType in your parameter map for all nullable
>> columns?
>>
>> Clinton
>> On Mon, May 24, 2010 at 11:03 PM, Nicky Jha
>> <[hidden email]<mailto:[hidden email]>> wrote:
>>
>> Hi
>>
>> with approach mentioned below(DynamicProcedureParams) , I want to also
>> pass
>> some of parameter as null((private List<Object> params = new
>> ArrayList<Object>(); params.add(null))), but when I pass string value as
>> null , it executes parametes procedure as
>>
>> call procname(?,?,?,?,?)
>> Parameters: [#IMNT_RISK_SENSITIVITY, IMNT_RISK_SENSITIVITY, null, null, 0]
>> Types: [java.lang.String, java.lang.String, null, null, java.lang.Byte]
>>
>> I want type to be java.lang.String instead of null(as with null, I am
>> getting Unsupported SQL type 0 )
>>
>> If I pass blank string "" in place of null in (private List<Object> params
>> =
>> new ArrayList<Object>(); params.add("")), Types become java.lang.String,
>> but
>> then I think it no more considers it as null.
>>
>> Please help
>>
>> Thanks
>> Nicky
>>
>>
>>
>> -----Original Message-----
>> From: Nicky Jha
>> Sent: Friday, May 14, 2010 8:46 PM
>> To: '[hidden email]<mailto:[hidden email]>'
>> Subject: RE: Need support for Dynamic procedure invocation
>>
>> Joe/Jeff
>>
>> This worked with your suggestion.Thank you so much!!!
>>
>> Nicky
>>
>> -----Original Message-----
>> From: Jeff Butler
>> [mailto:[hidden email]<mailto:[hidden email]>]
>> Sent: Friday, May 14, 2010 6:31 PM
>> To: [hidden email]<mailto:[hidden email]>
>> Subject: Re: Need support for Dynamic procedure invocation
>>
>> Yes - this the best approach.
>>
>> Jeff Butler
>>
>>
>> On 5/14/10, Joe Gooch
>> <[hidden email]<mailto:[hidden email]>> wrote:
>>> I suggest going with a dynamic SQL approach in the mapped statement.
>>>
>>> public class DynamicProcedureParams {
>>>   private String procedureName;
>>>   private List<Object> params = new ArrayList<Object>();
>>>
>>>   // getters and setters here
>>> }
>>>
>>> <procedure id="executeCopyProcs"
>>> parameterClass="path.to.DynamicProcedureParams">
>>> {call $procedureName$ <iterate property="params" open="(" close=")"
>>> conjunction=",">#params[]#</iterate> }
>>> </procedure>
>>>
>>>
>>>
>>> Joe
>>>
>>> Confidentiality Notice:
>>> This e-mail transmission may contain confidential and legally privileged
>>> information that is intended only for the individual named in the e-mail
>>> address. If you are not the intended recipient, you are hereby notified
>>> that
>>> any disclosure, copying, distribution, or reliance upon the contents of
>>> this
>>> e-mail message is strictly prohibited. If you have received this e-mail
>>> transmission in error, please reply to the sender, so that proper
>>> delivery
>>> can be arranged, and please delete the message from your mail box.
>>>
>>>> -----Original Message-----
>>>> From: Nicky Jha
>>>> [mailto:[hidden email]<mailto:[hidden email]>]
>>>> Sent: Friday, May 14, 2010 7:38 AM
>>>> To: [hidden email]<mailto:[hidden email]>
>>>> Subject: RE: Need support for Dynamic procedure invocation
>>>>
>>>> Hi Jeff,
>>>>
>>>> Thanks for this, if I was to do as suggested and compose the entire
>>>> string with parameter values, how will the parameter types (e.g. date,
>>>> smallint etc) be handled?
>>>>
>>>> Nicky!
>>>>
>>>> -----Original Message-----
>>>> From: Jeff Butler
>>>> [mailto:[hidden email]<mailto:[hidden email]>]
>>>> Sent: Friday, May 14, 2010 4:53 PM
>>>> To: [hidden email]<mailto:[hidden email]>
>>>> Subject: Re: Need support for Dynamic procedure invocation
>>>>
>>>> This won't work (as you've discovered).  iBATIS 2.x does not reparse
>>>> the string for variables after string substitution.  You'll need to do
>>>> this with the dynamic tags, or you'll need to compose the entire
>>>> string (including parameter values - like {call myproc('fred')}).
>>>>
>>>> Jeff Butler
>>>>
>>>>
>>>> On Fri, May 14, 2010 at 4:12 AM, Nicky Jha
>>>> <[hidden email]<mailto:[hidden email]>>
>>>> wrote:
>>>> > Hi Team,
>>>> >
>>>> >
>>>> >
>>>> > I am having hard time resolving following issue.Please help.
>>>> >
>>>> > We are using Ibatis 2.X.
>>>> >
>>>> > In our application we want capability to invoke stored procedure by
>>>> reading
>>>> > it from property xml file.We provide procedure name parameters,
>>>> parameters
>>>> > type to  property xml file.
>>>> >
>>>> > Now from this xml file I have created one dynamic procedure string
>>>> like
>>>> >
>>>> procedureName(#stringVal1#,#stringVal2#,#stringVal3#,#intVal1#,#byteVal
>>>> 1#).
>>>> >
>>>> > Now I want to call this procedure from Ibatis SQL mapping XML like
>>>> this
>>>> >
>>>> >
>>>> >
>>>> > <procedure id="executeCopyProcs"
>>>> >
>>>> parameterClass="com.jpmorgan.pyramid.pyrsyst.configure.ProcParameterMap
>>>> per">
>>>> >
>>>> > {call $procedureName$ }
>>>> >
>>>> >
>>>> >
>>>> > </procedure>
>>>> >
>>>> >
>>>> >
>>>> > Please refer below for ProcParameterMapper class. Now as soon as
>>>> iBatis sees
>>>> >  $procedureName$, it replaces it with say
>>>> > (#stringVal1#,#stringVal2#,#stringVal3#,#intVal1#,#byteVal1#) but it
>>>> does
>>>> > not replace the placeHolder(#), it passes on this as it is.Which is a
>>>> issue.
>>>> >
>>>> >
>>>> >
>>>> > If we type
>>>> >
>>>> procedureName(#stringVal1#,#stringVal2#,#stringVal3#,#intVal1#,#byteVal
>>>> 1)
>>>> > directly into SQL mapping XML, it replaces place holder, but with
>>>> string
>>>> > substitution , it does not work.We can't type directly into mapping
>>>> xML, as
>>>> > this string is composed at run time.Also dynamic tags are not of
>>>> help, as
>>>> > logic to create
>>>> > (#stringVal1#,#stringVal2#,#stringVal3#,#intVal1#,#byteVal1#) complex
>>>> > procedure with different types of parameter can't be written in
>>>> mapping
>>>> > XML.At least I am unable to do.
>>>> >
>>>> >
>>>> >
>>>> > I am really struck.Please suggest us the best way to deal with it.
>>>> >
>>>> >
>>>> >
>>>> >
>>>> >
>>>> >
>>>> >
>>>> >
>>>> >
>>>> >
>>>> >
>>>> >
>>>> >
>>>> > package com.jpmorgan.pyramid.pyrsyst.configure;
>>>> >
>>>> >
>>>> >
>>>> > import java.lang.reflect.Field;
>>>> >
>>>> >
>>>> >
>>>> > public class ProcParameterMapper {
>>>> >
>>>> >
>>>> >
>>>> >       StringBuffer procedureName = new StringBuffer("");
>>>> >
>>>> >       boolean firstParam = true;
>>>> >
>>>> >       boolean lastParam = false;
>>>> >
>>>> >
>>>> >
>>>> >       String stringVal1;
>>>> >
>>>> >       String stringVal2;
>>>> >
>>>> >       String stringVal3;
>>>> >
>>>> >       String stringVal4;
>>>> >
>>>> >       String stringVal5;
>>>> >
>>>> >       String stringVal6;
>>>> >
>>>> >       String stringVal7;
>>>> >
>>>> >       String stringVal8;
>>>> >
>>>> >       String stringVal9;
>>>> >
>>>> >       String stringVal10;
>>>> >
>>>> >
>>>> >
>>>> >       int intVal1;
>>>> >
>>>> >       int intVal2;
>>>> >
>>>> >       int intVal3;
>>>> >
>>>> >       int intVal4;
>>>> >
>>>> >       int intVal5;
>>>> >
>>>> >       int intVal6;
>>>> >
>>>> >       int intVal7;
>>>> >
>>>> >       int intVal8;
>>>> >
>>>> >       int intVal9;
>>>> >
>>>> >       int intVal10;
>>>> >
>>>> >
>>>> >
>>>> >       byte byteVal1;
>>>> >
>>>> >       byte byteVal2;
>>>> >
>>>> >       byte byteVal3;
>>>> >
>>>> >       byte byteVal4;
>>>> >
>>>> >       byte byteVal5;
>>>> >
>>>> >
>>>> >
>>>> >       public void setStringVal(String value, int count,String
>>>> jdbcType)
>>>> > throws ConfigureException {
>>>> >
>>>> >
>>>> >
>>>> >             Field[] field =
>>>> ProcParameterMapper.class.getDeclaredFields();
>>>> >
>>>> >
>>>> >
>>>> >             for (int i = 0; i < field.length; i++) {
>>>> >
>>>> >                   try {
>>>> >
>>>> >                         if
>>>> > (field[i].getName().endsWith(String.valueOf(count))
>>>> >
>>>> >                                     && ("String")
>>>> >
>>>> >
>>>> > .equals(field[i].getType().getSimpleName())) {
>>>> >
>>>> >                               field[i].set(this, value);
>>>> >
>>>> >                               if (firstParam) {
>>>> >
>>>> >                                     procedureName.append("(#" +
>>>> > field[i].getName()+"#");
>>>> >
>>>> >                                     firstParam = false;
>>>> >
>>>> >                               } else if (lastParam) {
>>>> >
>>>> >                                     procedureName.append(",#" +
>>>> > field[i].getName() +"#)");
>>>> >
>>>> >                               } else {
>>>> >
>>>> >                                     procedureName.append(",#" +
>>>> > field[i].getName()+"#");
>>>> >
>>>> >                               }
>>>> >
>>>> >                               break;
>>>> >
>>>> >
>>>> >
>>>> >                         }
>>>> >
>>>> >                   } catch (Exception e) {
>>>> >
>>>> >                         throw new ConfigureException(
>>>> >
>>>> >                                     "Exception setting String value
>>>> in
>>>> > paramMapper"
>>>> >
>>>> >                                                 + e.getStackTrace());
>>>> >
>>>> >                   }
>>>> >
>>>> >
>>>> >
>>>> >             }
>>>> >
>>>> >
>>>> >
>>>> >       }
>>>> >
>>>> >
>>>> >
>>>> >       public void setIntVal(int value, int count,String jdbcType)
>>>> throws
>>>> > ConfigureException {
>>>> >
>>>> >
>>>> >
>>>> >             Field[] field =
>>>> ProcParameterMapper.class.getDeclaredFields();
>>>> >
>>>> >
>>>> >
>>>> >             for (int i = 0; i < field.length; i++) {
>>>> >
>>>> >                   try {
>>>> >
>>>> >                         if
>>>> > (field[i].getName().endsWith(String.valueOf(count))
>>>> >
>>>> >                                     &&
>>>> > ("int").equals(field[i].getType().getSimpleName())) {
>>>> >
>>>> >
>>>> >
>>>> >                               field[i].set(this, value);
>>>> >
>>>> >                               if (firstParam) {
>>>> >
>>>> >                                     procedureName.append("(#" +
>>>> > field[i].getName()+"#");
>>>> >
>>>> >                                     firstParam = false;
>>>> >
>>>> >                               } else if (lastParam) {
>>>> >
>>>> >                                     procedureName.append(",#" +
>>>> > field[i].getName() +"#)");
>>>> >
>>>> >                               } else {
>>>> >
>>>> >                                     procedureName.append(",#" +
>>>> > field[i].getName()+"#");
>>>> >
>>>> >                               }
>>>> >
>>>> >                               break;
>>>> >
>>>> >                         }
>>>> >
>>>> >                   } catch (Exception e) {
>>>> >
>>>> >                         throw new ConfigureException(
>>>> >
>>>> >                                     "Exception setting int value in
>>>> > paramMapper"
>>>> >
>>>> >                                                 + e.getStackTrace());
>>>> >
>>>> >                   }
>>>> >
>>>> >
>>>> >
>>>> >             }
>>>> >
>>>> >
>>>> >
>>>> >       }
>>>> >
>>>> >
>>>> >
>>>> >       public void setByteVal(Byte value, int count,String jdbcType)
>>>> throws
>>>> > ConfigureException {
>>>> >
>>>> >
>>>> >
>>>> >             Field[] field =
>>>> ProcParameterMapper.class.getDeclaredFields();
>>>> >
>>>> >
>>>> >
>>>> >             for (int i = 0; i < field.length; i++) {
>>>> >
>>>> >                   try {
>>>> >
>>>> >                         if
>>>> > (field[i].getName().endsWith(String.valueOf(count))
>>>> >
>>>> >                                     &&
>>>> > ("byte").equals(field[i].getType().getSimpleName())) {
>>>> >
>>>> >
>>>> >
>>>> >                               field[i].set(this, value);
>>>> >
>>>> >                               if (firstParam) {
>>>> >
>>>> >                                     procedureName.append("(#" +
>>>> > field[i].getName()+"#");
>>>> >
>>>> >                                     firstParam = false;
>>>> >
>>>> >                               } else if (lastParam) {
>>>> >
>>>> >                                     procedureName.append(",#" +
>>>> > field[i].getName() + "#)");
>>>> >
>>>> >                               } else {
>>>> >
>>>> >                                     procedureName.append(",#" +
>>>> > field[i].getName()+"#");
>>>> >
>>>> >                               }
>>>> >
>>>> >                               break;
>>>> >
>>>> >                         }
>>>> >
>>>> >                   } catch (Exception e) {
>>>> >
>>>> >                         throw new ConfigureException(
>>>> >
>>>> >                                     "Exception setting byte value in
>>>> > paramMapper"
>>>> >
>>>> >                                                 + e.getStackTrace());
>>>> >
>>>> >                   }
>>>> >
>>>> >
>>>> >
>>>> >             }
>>>> >
>>>> >
>>>> >
>>>> >       }
>>>> >
>>>> >
>>>> >
>>>> >       public String toString() {
>>>> >
>>>> >
>>>> >
>>>> >             return new String("stringVal1 is" + stringVal1 +
>>>> "stringVal2 is"
>>>> >
>>>> >                         + stringVal2 + "stringVal3 is" + stringVal3 +
>>>> > "intVal1 is"
>>>> >
>>>> >                         + intVal1 + "intVal2 is" + intVal2 + "intVal3
>>>> is" +
>>>> > intVal3);
>>>> >
>>>> >
>>>> >
>>>> >       }
>>>> >
>>>> >
>>>> >
>>>> >       public void setProcedure(String procName) {
>>>> >
>>>> >             procedureName.append(procName);
>>>> >
>>>> >
>>>> >
>>>> >       }
>>>> >
>>>> >
>>>> >
>>>> >       public String getProcedure(){
>>>> >
>>>> >             return procedureName.toString();
>>>> >
>>>> >       }
>>>> >
>>>> >
>>>> >
>>>> > }
>>>> >
>>>> >
>>>> >
>>>> > May thanks in advance
>>>> >
>>>> > Waiting for reply
>>>> >
>>>> > Nicky Jha
>>>> >
>>>> >
>>>> >
>>>> > This communication is for informational purposes only. It is not
>>>> intended as
>>>> > an offer or solicitation for the purchase or sale of any financial
>>>> > instrument or as an official confirmation of any transaction. All
>>>> market
>>>> > prices, data and other information are not warranted as to
>>>> completeness or
>>>> > accuracy and are subject to change without notice. Any comments or
>>>> > statements made herein do not necessarily reflect those of JPMorgan
>>>> Chase &
>>>> > Co., its subsidiaries and affiliates. This transmission may contain
>>>> > information that is privileged, confidential, legally privileged,
>>>> and/or
>>>> > exempt from disclosure under applicable law. If you are not the
>>>> intended
>>>> > recipient, you are hereby notified that any disclosure, copying,
>>>> > distribution, or use of the information contained herein (including
>>>> any
>>>> > reliance thereon) is STRICTLY PROHIBITED. Although this transmission
>>>> and any
>>>> > attachments are believed to be free of any virus or other defect that
>>>> might
>>>> > affect any computer system into which it is received and opened, it
>>>> is the
>>>> > responsibility of the recipient to ensure that it is virus free and
>>>> no
>>>> > responsibility is accepted by JPMorgan Chase & Co., its subsidiaries
>>>> and
>>>> > affiliates, as applicable, for any loss or damage arising in any way
>>>> from
>>>> > its use. If you received this transmission in error, please
>>>> immediately
>>>> > contact the sender and destroy the material in its entirety, whether
>>>> in
>>>> > electronic or hard copy format. Thank you. Please refer to
>>>> > http://www.jpmorgan.com/pages/disclosures for disclosures relating to
>>>> > European legal entities.
>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail:
>>>> [hidden email]<mailto:[hidden email]>
>>>> For additional commands, e-mail:
>>>> [hidden email]<mailto:[hidden email]>
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail:
>>> [hidden email]<mailto:[hidden email]>
>>> For additional commands, e-mail:
>>> [hidden email]<mailto:[hidden email]>
>>>
>>>
>>
>> --
>> Sent from my mobile device
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail:
>> [hidden email]<mailto:[hidden email]>
>> For additional commands, e-mail:
>> [hidden email]<mailto:[hidden email]>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail:
>> [hidden email]<mailto:[hidden email]>
>> For additional commands, e-mail:
>> [hidden email]<mailto:[hidden email]>
>>
>>
>>
>
> --
> Sent from my mobile device
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>

--
Sent from my mobile device

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

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

Re: Need support for Dynamic procedure invocation

Clinton Begin
Nicky,

Even if you're using iBATIS, you'll want to switch to the MyBatis user groups.  This mailing list will disappear eventually.

That said, I think the solution to your problem might be simple:

<procedure id="executeCopyProcs" parameterClass="path.to.DynamicProcedureParams">
 {call $procedureName$ <iterate property="params" open="(" close=")" conjunction=",">#params[]:VARCHAR#</iterate> }
</procedure>

Hopefully the red text shows up for you, but otherwise, look for the :VARCHAR in the parameter.

This is in the user guide on page 30.

Cheers,
Clinton

On Tue, May 25, 2010 at 7:49 AM, Nicky Jha <[hidden email]> wrote:
Hi Jeff,

we wanted to switch to myIbatis as we started this project, but we are  using ibatis Spring integration in our project and I think new version of Ibatis is not completely supported by Spring as of now.For example SqlMapExecutor executor etc. and we want to run Ibatis wit Spring :(.Can you help us as when can we expect Spring support for new version of Ibatis.

So we are helpless to use Ibatis 2.X.So no way for this problem then with Ibatis 2.x?

Thanks
Nicky



-----Original Message-----
From: Jeff Butler [mailto:[hidden email]]
Sent: Tuesday, May 25, 2010 7:14 PM
To: [hidden email]
Subject: Re: Need support for Dynamic procedure invocation

This would be a great time to switch to mybatis and usa a
@SelectProvider to write the query exactly as you need it. I think
that the dynamic SQL tags won't work in this case.

Jeff Butler


On 5/25/10, Nicky Jha <[hidden email]> wrote:
> Hi
>
> Is there a way I can modify {call $procedureName$ <iterate property="params"
> open="(" close=")"
>  conjunction=",">#params[]#</iterate> } to include jdbc types also, so that
> I can pass null value to database?
>
> Thanks
> Nicky
>
>
>
> -----Original Message-----
> From: Nicky Jha
> Sent: Tuesday, May 25, 2010 11:12 AM
> To: '[hidden email]'
> Subject: RE: Need support for Dynamic procedure invocation
>
> Joe/Jeff
> Please help.
>
> this class is not part of framework, this is approach, we have used in case
> our procedure changes at runtime, so will its parameter.For that we using
>
> public class DynamicProcedureParams {
>>>   private String procedureName;
>>>   private List<Object> params = new ArrayList<Object>();
>>>
>>>   // getters and setters here
>>> }
>>>
>>> <procedure id="executeCopyProcs"
>>> parameterClass="path.to.DynamicProcedureParams">
>>> {call $procedureName$ <iterate property="params" open="(" close=")"
>>> conjunction=",">#params[]#</iterate> }
>>> </procedure>
>>>
>
> Thanks
> Nicky
>
>
>
>
> -----Original Message-----
> From: Clinton Begin [mailto:[hidden email]]
> Sent: Tuesday, May 25, 2010 11:07 AM
> To: [hidden email]
> Subject: Re: Need support for Dynamic procedure invocation
>
> I'm not familiar with that class, as it's not part of the framework
> (maybe post it here).  Short story is that JDBC requires nullable
> column types to be specified.  Ibatis allows for this in the parameter
> map (crack open the user guide and search for "jdbcType" for more).
>
> Cheers,
> Clinton
>
>
>
> On 2010-05-24, Nicky Jha <[hidden email]> wrote:
>> Hi
>>
>> I am using parameter class DynamicProcedureParams(as sugessted by Joe
>> Gooch
>> ), as my requirement was to build paramaters at run time, so with this
>> approach where can I set jdbcType?
>>
>> Nicky
>>
>> From: Clinton Begin [mailto:[hidden email]]
>> Sent: Tuesday, May 25, 2010 10:39 AM
>> To: [hidden email]
>> Subject: Re: Need support for Dynamic procedure invocation
>>
>> Are you setting the jdbcType in your parameter map for all nullable
>> columns?
>>
>> Clinton
>> On Mon, May 24, 2010 at 11:03 PM, Nicky Jha
>> <[hidden email]<mailto:[hidden email]>> wrote:
>>
>> Hi
>>
>> with approach mentioned below(DynamicProcedureParams) , I want to also
>> pass
>> some of parameter as null((private List<Object> params = new
>> ArrayList<Object>(); params.add(null))), but when I pass string value as
>> null , it executes parametes procedure as
>>
>> call procname(?,?,?,?,?)
>> Parameters: [#IMNT_RISK_SENSITIVITY, IMNT_RISK_SENSITIVITY, null, null, 0]
>> Types: [java.lang.String, java.lang.String, null, null, java.lang.Byte]
>>
>> I want type to be java.lang.String instead of null(as with null, I am
>> getting Unsupported SQL type 0 )
>>
>> If I pass blank string "" in place of null in (private List<Object> params
>> =
>> new ArrayList<Object>(); params.add("")), Types become java.lang.String,
>> but
>> then I think it no more considers it as null.
>>
>> Please help
>>
>> Thanks
>> Nicky
>>
>>
>>
>> -----Original Message-----
>> From: Nicky Jha
>> Sent: Friday, May 14, 2010 8:46 PM
>> To: '[hidden email]<mailto:[hidden email]>'
>> Subject: RE: Need support for Dynamic procedure invocation
>>
>> Joe/Jeff
>>
>> This worked with your suggestion.Thank you so much!!!
>>
>> Nicky
>>
>> -----Original Message-----
>> From: Jeff Butler
>> [mailto:[hidden email]<mailto:[hidden email]>]
>> Sent: Friday, May 14, 2010 6:31 PM
>> To: [hidden email]<mailto:[hidden email]>
>> Subject: Re: Need support for Dynamic procedure invocation
>>
>> Yes - this the best approach.
>>
>> Jeff Butler
>>
>>
>> On 5/14/10, Joe Gooch
>> <[hidden email]<mailto:[hidden email]>> wrote:
>>> I suggest going with a dynamic SQL approach in the mapped statement.
>>>
>>> public class DynamicProcedureParams {
>>>   private String procedureName;
>>>   private List<Object> params = new ArrayList<Object>();
>>>
>>>   // getters and setters here
>>> }
>>>
>>> <procedure id="executeCopyProcs"
>>> parameterClass="path.to.DynamicProcedureParams">
>>> {call $procedureName$ <iterate property="params" open="(" close=")"
>>> conjunction=",">#params[]#</iterate> }
>>> </procedure>
>>>
>>>
>>>
>>> Joe
>>>
>>> Confidentiality Notice:
>>> This e-mail transmission may contain confidential and legally privileged
>>> information that is intended only for the individual named in the e-mail
>>> address. If you are not the intended recipient, you are hereby notified
>>> that
>>> any disclosure, copying, distribution, or reliance upon the contents of
>>> this
>>> e-mail message is strictly prohibited. If you have received this e-mail
>>> transmission in error, please reply to the sender, so that proper
>>> delivery
>>> can be arranged, and please delete the message from your mail box.
>>>
>>>> -----Original Message-----
>>>> From: Nicky Jha
>>>> [mailto:[hidden email]<mailto:[hidden email]>]
>>>> Sent: Friday, May 14, 2010 7:38 AM
>>>> To: [hidden email]<mailto:[hidden email]>
>>>> Subject: RE: Need support for Dynamic procedure invocation
>>>>
>>>> Hi Jeff,
>>>>
>>>> Thanks for this, if I was to do as suggested and compose the entire
>>>> string with parameter values, how will the parameter types (e.g. date,
>>>> smallint etc) be handled?
>>>>
>>>> Nicky!
>>>>
>>>> -----Original Message-----
>>>> From: Jeff Butler
>>>> [mailto:[hidden email]<mailto:[hidden email]>]
>>>> Sent: Friday, May 14, 2010 4:53 PM
>>>> To: [hidden email]<mailto:[hidden email]>
>>>> Subject: Re: Need support for Dynamic procedure invocation
>>>>
>>>> This won't work (as you've discovered).  iBATIS 2.x does not reparse
>>>> the string for variables after string substitution.  You'll need to do
>>>> this with the dynamic tags, or you'll need to compose the entire
>>>> string (including parameter values - like {call myproc('fred')}).
>>>>
>>>> Jeff Butler
>>>>
>>>>
>>>> On Fri, May 14, 2010 at 4:12 AM, Nicky Jha
>>>> <[hidden email]<mailto:[hidden email]>>
>>>> wrote:
>>>> > Hi Team,
>>>> >
>>>> >
>>>> >
>>>> > I am having hard time resolving following issue.Please help.
>>>> >
>>>> > We are using Ibatis 2.X.
>>>> >
>>>> > In our application we want capability to invoke stored procedure by
>>>> reading
>>>> > it from property xml file.We provide procedure name parameters,
>>>> parameters
>>>> > type to  property xml file.
>>>> >
>>>> > Now from this xml file I have created one dynamic procedure string
>>>> like
>>>> >
>>>> procedureName(#stringVal1#,#stringVal2#,#stringVal3#,#intVal1#,#byteVal
>>>> 1#).
>>>> >
>>>> > Now I want to call this procedure from Ibatis SQL mapping XML like
>>>> this
>>>> >
>>>> >
>>>> >
>>>> > <procedure id="executeCopyProcs"
>>>> >
>>>> parameterClass="com.jpmorgan.pyramid.pyrsyst.configure.ProcParameterMap
>>>> per">
>>>> >
>>>> > {call $procedureName$ }
>>>> >
>>>> >
>>>> >
>>>> > </procedure>
>>>> >
>>>> >
>>>> >
>>>> > Please refer below for ProcParameterMapper class. Now as soon as
>>>> iBatis sees
>>>> >  $procedureName$, it replaces it with say
>>>> > (#stringVal1#,#stringVal2#,#stringVal3#,#intVal1#,#byteVal1#) but it
>>>> does
>>>> > not replace the placeHolder(#), it passes on this as it is.Which is a
>>>> issue.
>>>> >
>>>> >
>>>> >
>>>> > If we type
>>>> >
>>>> procedureName(#stringVal1#,#stringVal2#,#stringVal3#,#intVal1#,#byteVal
>>>> 1)
>>>> > directly into SQL mapping XML, it replaces place holder, but with
>>>> string
>>>> > substitution , it does not work.We can't type directly into mapping
>>>> xML, as
>>>> > this string is composed at run time.Also dynamic tags are not of
>>>> help, as
>>>> > logic to create
>>>> > (#stringVal1#,#stringVal2#,#stringVal3#,#intVal1#,#byteVal1#) complex
>>>> > procedure with different types of parameter can't be written in
>>>> mapping
>>>> > XML.At least I am unable to do.
>>>> >
>>>> >
>>>> >
>>>> > I am really struck.Please suggest us the best way to deal with it.
>>>> >
>>>> >
>>>> >
>>>> >
>>>> >
>>>> >
>>>> >
>>>> >
>>>> >
>>>> >
>>>> >
>>>> >
>>>> >
>>>> > package com.jpmorgan.pyramid.pyrsyst.configure;
>>>> >
>>>> >
>>>> >
>>>> > import java.lang.reflect.Field;
>>>> >
>>>> >
>>>> >
>>>> > public class ProcParameterMapper {
>>>> >
>>>> >
>>>> >
>>>> >       StringBuffer procedureName = new StringBuffer("");
>>>> >
>>>> >       boolean firstParam = true;
>>>> >
>>>> >       boolean lastParam = false;
>>>> >
>>>> >
>>>> >
>>>> >       String stringVal1;
>>>> >
>>>> >       String stringVal2;
>>>> >
>>>> >       String stringVal3;
>>>> >
>>>> >       String stringVal4;
>>>> >
>>>> >       String stringVal5;
>>>> >
>>>> >       String stringVal6;
>>>> >
>>>> >       String stringVal7;
>>>> >
>>>> >       String stringVal8;
>>>> >
>>>> >       String stringVal9;
>>>> >
>>>> >       String stringVal10;
>>>> >
>>>> >
>>>> >
>>>> >       int intVal1;
>>>> >
>>>> >       int intVal2;
>>>> >
>>>> >       int intVal3;
>>>> >
>>>> >       int intVal4;
>>>> >
>>>> >       int intVal5;
>>>> >
>>>> >       int intVal6;
>>>> >
>>>> >       int intVal7;
>>>> >
>>>> >       int intVal8;
>>>> >
>>>> >       int intVal9;
>>>> >
>>>> >       int intVal10;
>>>> >
>>>> >
>>>> >
>>>> >       byte byteVal1;
>>>> >
>>>> >       byte byteVal2;
>>>> >
>>>> >       byte byteVal3;
>>>> >
>>>> >       byte byteVal4;
>>>> >
>>>> >       byte byteVal5;
>>>> >
>>>> >
>>>> >
>>>> >       public void setStringVal(String value, int count,String
>>>> jdbcType)
>>>> > throws ConfigureException {
>>>> >
>>>> >
>>>> >
>>>> >             Field[] field =
>>>> ProcParameterMapper.class.getDeclaredFields();
>>>> >
>>>> >
>>>> >
>>>> >             for (int i = 0; i < field.length; i++) {
>>>> >
>>>> >                   try {
>>>> >
>>>> >                         if
>>>> > (field[i].getName().endsWith(String.valueOf(count))
>>>> >
>>>> >                                     && ("String")
>>>> >
>>>> >
>>>> > .equals(field[i].getType().getSimpleName())) {
>>>> >
>>>> >                               field[i].set(this, value);
>>>> >
>>>> >                               if (firstParam) {
>>>> >
>>>> >                                     procedureName.append("(#" +
>>>> > field[i].getName()+"#");
>>>> >
>>>> >                                     firstParam = false;
>>>> >
>>>> >                               } else if (lastParam) {
>>>> >
>>>> >                                     procedureName.append(",#" +
>>>> > field[i].getName() +"#)");
>>>> >
>>>> >                               } else {
>>>> >
>>>> >                                     procedureName.append(",#" +
>>>> > field[i].getName()+"#");
>>>> >
>>>> >                               }
>>>> >
>>>> >                               break;
>>>> >
>>>> >
>>>> >
>>>> >                         }
>>>> >
>>>> >                   } catch (Exception e) {
>>>> >
>>>> >                         throw new ConfigureException(
>>>> >
>>>> >                                     "Exception setting String value
>>>> in
>>>> > paramMapper"
>>>> >
>>>> >                                                 + e.getStackTrace());
>>>> >
>>>> >                   }
>>>> >
>>>> >
>>>> >
>>>> >             }
>>>> >
>>>> >
>>>> >
>>>> >       }
>>>> >
>>>> >
>>>> >
>>>> >       public void setIntVal(int value, int count,String jdbcType)
>>>> throws
>>>> > ConfigureException {
>>>> >
>>>> >
>>>> >
>>>> >             Field[] field =
>>>> ProcParameterMapper.class.getDeclaredFields();
>>>> >
>>>> >
>>>> >
>>>> >             for (int i = 0; i < field.length; i++) {
>>>> >
>>>> >                   try {
>>>> >
>>>> >                         if
>>>> > (field[i].getName().endsWith(String.valueOf(count))
>>>> >
>>>> >                                     &&
>>>> > ("int").equals(field[i].getType().getSimpleName())) {
>>>> >
>>>> >
>>>> >
>>>> >                               field[i].set(this, value);
>>>> >
>>>> >                               if (firstParam) {
>>>> >
>>>> >                                     procedureName.append("(#" +
>>>> > field[i].getName()+"#");
>>>> >
>>>> >                                     firstParam = false;
>>>> >
>>>> >                               } else if (lastParam) {
>>>> >
>>>> >                                     procedureName.append(",#" +
>>>> > field[i].getName() +"#)");
>>>> >
>>>> >                               } else {
>>>> >
>>>> >                                     procedureName.append(",#" +
>>>> > field[i].getName()+"#");
>>>> >
>>>> >                               }
>>>> >
>>>> >                               break;
>>>> >
>>>> >                         }
>>>> >
>>>> >                   } catch (Exception e) {
>>>> >
>>>> >                         throw new ConfigureException(
>>>> >
>>>> >                                     "Exception setting int value in
>>>> > paramMapper"
>>>> >
>>>> >                                                 + e.getStackTrace());
>>>> >
>>>> >                   }
>>>> >
>>>> >
>>>> >
>>>> >             }
>>>> >
>>>> >
>>>> >
>>>> >       }
>>>> >
>>>> >
>>>> >
>>>> >       public void setByteVal(Byte value, int count,String jdbcType)
>>>> throws
>>>> > ConfigureException {
>>>> >
>>>> >
>>>> >
>>>> >             Field[] field =
>>>> ProcParameterMapper.class.getDeclaredFields();
>>>> >
>>>> >
>>>> >
>>>> >             for (int i = 0; i < field.length; i++) {
>>>> >
>>>> >                   try {
>>>> >
>>>> >                         if
>>>> > (field[i].getName().endsWith(String.valueOf(count))
>>>> >
>>>> >                                     &&
>>>> > ("byte").equals(field[i].getType().getSimpleName())) {
>>>> >
>>>> >
>>>> >
>>>> >                               field[i].set(this, value);
>>>> >
>>>> >                               if (firstParam) {
>>>> >
>>>> >                                     procedureName.append("(#" +
>>>> > field[i].getName()+"#");
>>>> >
>>>> >                                     firstParam = false;
>>>> >
>>>> >                               } else if (lastParam) {
>>>> >
>>>> >                                     procedureName.append(",#" +
>>>> > field[i].getName() + "#)");
>>>> >
>>>> >                               } else {
>>>> >
>>>> >                                     procedureName.append(",#" +
>>>> > field[i].getName()+"#");
>>>> >
>>>> >                               }
>>>> >
>>>> >                               break;
>>>> >
>>>> >                         }
>>>> >
>>>> >                   } catch (Exception e) {
>>>> >
>>>> >                         throw new ConfigureException(
>>>> >
>>>> >                                     "Exception setting byte value in
>>>> > paramMapper"
>>>> >
>>>> >                                                 + e.getStackTrace());
>>>> >
>>>> >                   }
>>>> >
>>>> >
>>>> >
>>>> >             }
>>>> >
>>>> >
>>>> >
>>>> >       }
>>>> >
>>>> >
>>>> >
>>>> >       public String toString() {
>>>> >
>>>> >
>>>> >
>>>> >             return new String("stringVal1 is" + stringVal1 +
>>>> "stringVal2 is"
>>>> >
>>>> >                         + stringVal2 + "stringVal3 is" + stringVal3 +
>>>> > "intVal1 is"
>>>> >
>>>> >                         + intVal1 + "intVal2 is" + intVal2 + "intVal3
>>>> is" +
>>>> > intVal3);
>>>> >
>>>> >
>>>> >
>>>> >       }
>>>> >
>>>> >
>>>> >
>>>> >       public void setProcedure(String procName) {
>>>> >
>>>> >             procedureName.append(procName);
>>>> >
>>>> >
>>>> >
>>>> >       }
>>>> >
>>>> >
>>>> >
>>>> >       public String getProcedure(){
>>>> >
>>>> >             return procedureName.toString();
>>>> >
>>>> >       }
>>>> >
>>>> >
>>>> >
>>>> > }
>>>> >
>>>> >
>>>> >
>>>> > May thanks in advance
>>>> >
>>>> > Waiting for reply
>>>> >
>>>> > Nicky Jha
>>>> >
>>>> >
>>>> >
>>>> > This communication is for informational purposes only. It is not
>>>> intended as
>>>> > an offer or solicitation for the purchase or sale of any financial
>>>> > instrument or as an official confirmation of any transaction. All
>>>> market
>>>> > prices, data and other information are not warranted as to
>>>> completeness or
>>>> > accuracy and are subject to change without notice. Any comments or
>>>> > statements made herein do not necessarily reflect those of JPMorgan
>>>> Chase &
>>>> > Co., its subsidiaries and affiliates. This transmission may contain
>>>> > information that is privileged, confidential, legally privileged,
>>>> and/or
>>>> > exempt from disclosure under applicable law. If you are not the
>>>> intended
>>>> > recipient, you are hereby notified that any disclosure, copying,
>>>> > distribution, or use of the information contained herein (including
>>>> any
>>>> > reliance thereon) is STRICTLY PROHIBITED. Although this transmission
>>>> and any
>>>> > attachments are believed to be free of any virus or other defect that
>>>> might
>>>> > affect any computer system into which it is received and opened, it
>>>> is the
>>>> > responsibility of the recipient to ensure that it is virus free and
>>>> no
>>>> > responsibility is accepted by JPMorgan Chase & Co., its subsidiaries
>>>> and
>>>> > affiliates, as applicable, for any loss or damage arising in any way
>>>> from
>>>> > its use. If you received this transmission in error, please
>>>> immediately
>>>> > contact the sender and destroy the material in its entirety, whether
>>>> in
>>>> > electronic or hard copy format. Thank you. Please refer to
>>>> > http://www.jpmorgan.com/pages/disclosures for disclosures relating to
>>>> > European legal entities.
>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail:
>>>> [hidden email]<mailto:[hidden email]>
>>>> For additional commands, e-mail:
>>>> [hidden email]<mailto:[hidden email]>
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail:
>>> [hidden email]<mailto:[hidden email]>
>>> For additional commands, e-mail:
>>> [hidden email]<mailto:[hidden email]>
>>>
>>>
>>
>> --
>> Sent from my mobile device
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail:
>> [hidden email]<mailto:[hidden email]>
>> For additional commands, e-mail:
>> [hidden email]<mailto:[hidden email]>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail:
>> [hidden email]<mailto:[hidden email]>
>> For additional commands, e-mail:
>> [hidden email]<mailto:[hidden email]>
>>
>>
>>
>
> --
> Sent from my mobile device
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>

--
Sent from my mobile device

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]


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

RE: Need support for Dynamic procedure invocation

Joe Gooch
In reply to this post by Nicky Jha
Sure, but it'll be uglier.

To me, your whole approach/rationale seems odd here... Procedures changing at runtime like that seems like poor DB practice except for very specific scenarios, and/or single threaded db usage.... Then again, I don't know exactly what you're trying to accomplish.  So I'll try to suspend judgement.  But it seems to me at this point, if you're not mapping a result set, that IBatis isn't really gaining you much, since you can easily fire up a StringBuffer and build the statement yourself.  Probably easier than you could with the IBatis XML.

Beans/Defn:

public class MyParam {
  public final static int VARCHAR=1;
  public final static int INTEGER=2;
  public final static int DECIMAL=3;
  private int type;
  private Object value;

  // TODO: getters/setters/constructor
}

public class DynamicProcedureParams {
  private String procedureName;
  private List<MyParam> params = new ArrayList<MyParam>();

  // TODO: getters/setters/constructor
}

{call $procedureName$ <iterate> property="params" open="(" close=")" conjunction=",">
  <isEqual property="params[].type" compareValue="1">#params[].value:VARCHAR#</isEqual>
  <isEqual property="params[].type" compareValue="2">#params[].value:INTEGER#</isEqual>
  <isEqual property="params[].type" compareValue="3">#params[].value:DECIMAL#</isEqual>
</iterate> }



However, in JDBC you're looking like this:
  DynamicProcedureParams dpp = // your stuff;
  Connection con = // your connection;

  StringBuffer sb = new StringBuffer(1000).append("{call ").append(dpp.getProcedureName()).append(" (");
  for(int i=0; i<dpp.getParams().size(); i++) {
    if (i>0) sb.append(",");
    sb.append("?");
  }
  sb.append(")}");
  PreparedStatement st = con.prepareStatement(sb.toString());
  // You could now cache this prepared statement until/unless your dpp values change
  for(int i=0; i<dpp.getParams().size(); i++) {
    switch(dpp.getParams().get(i).getType()) {
      case MyParam.VARCHAR: st.setString(i+1, dpp.getParams().get(i).getValue()); break;
      case MyParam.INTEGER: st.setInt(i+1, dpp.getParams().get(i).getValue()); break;
      case MyParam.DECIMAL: st.setDouble(i+1, dpp.getParams().get(i).getValue()); break;
    }
  }
  ResultSet rs = st.executeQuery();  // or st.execute();


If you really need to do this type of thing, it seems to me like you're going to be better off creating a class and beans to hold the procedure name and parameter types, and when that changes, recompile the prepared statement.  And then have a separate class that holds parameter values for a single execution.  Then the stringbuffer bits above can be split from individual queries, and only need to happen when the underlying schema changes. (Of course you'll need synchronization)

Similarly, you could do as Jeff suggested and use a SelectProvider to build the SQL, effectively doing the caching bits above.

I guess at this point it would help to know what IBatis is really buying you.

Joe

Confidentiality Notice:
This e-mail transmission may contain confidential and legally privileged information that is intended only for the individual named in the e-mail address. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or reliance upon the contents of this e-mail message is strictly prohibited. If you have received this e-mail transmission in error, please reply to the sender, so that proper delivery can be arranged, and please delete the message from your mail box.


> -----Original Message-----
> From: Nicky Jha [mailto:[hidden email]]
> Sent: Tuesday, May 25, 2010 3:59 AM
> To: [hidden email]
> Subject: RE: Need support for Dynamic procedure invocation
>
> Hi
>
> Is there a way I can modify {call $procedureName$ <iterate
> property="params" open="(" close=")"
>  conjunction=",">#params[]#</iterate> } to include jdbc types also, so
> that I can pass null value to database?
>
> Thanks
> Nicky
>
>
>
> -----Original Message-----
> From: Nicky Jha
> Sent: Tuesday, May 25, 2010 11:12 AM
> To: '[hidden email]'
> Subject: RE: Need support for Dynamic procedure invocation
>
> Joe/Jeff
> Please help.
>
> this class is not part of framework, this is approach, we have used in
> case our procedure changes at runtime, so will its parameter.For that
> we using
>
> public class DynamicProcedureParams {
> >>   private String procedureName;
> >>   private List<Object> params = new ArrayList<Object>();
> >>
> >>   // getters and setters here
> >> }
> >>
> >> <procedure id="executeCopyProcs"
> >> parameterClass="path.to.DynamicProcedureParams">
> >> {call $procedureName$ <iterate property="params" open="(" close=")"
> >> conjunction=",">#params[]#</iterate> }
> >> </procedure>
> >>
>
> Thanks
> Nicky
>
>
>
>
> -----Original Message-----
> From: Clinton Begin [mailto:[hidden email]]
> Sent: Tuesday, May 25, 2010 11:07 AM
> To: [hidden email]
> Subject: Re: Need support for Dynamic procedure invocation
>
> I'm not familiar with that class, as it's not part of the framework
> (maybe post it here).  Short story is that JDBC requires nullable
> column types to be specified.  Ibatis allows for this in the parameter
> map (crack open the user guide and search for "jdbcType" for more).
>
> Cheers,
> Clinton
>
>
>
> On 2010-05-24, Nicky Jha <[hidden email]> wrote:
> > Hi
> >
> > I am using parameter class DynamicProcedureParams(as sugessted by Joe
> Gooch
> > ), as my requirement was to build paramaters at run time, so with
> this
> > approach where can I set jdbcType?
> >
> > Nicky
> >
> > From: Clinton Begin [mailto:[hidden email]]
> > Sent: Tuesday, May 25, 2010 10:39 AM
> > To: [hidden email]
> > Subject: Re: Need support for Dynamic procedure invocation
> >
> > Are you setting the jdbcType in your parameter map for all nullable
> > columns?
> >
> > Clinton
> > On Mon, May 24, 2010 at 11:03 PM, Nicky Jha
> > <[hidden email]<mailto:[hidden email]>> wrote:
> >
> > Hi
> >
> > with approach mentioned below(DynamicProcedureParams) , I want to
> also pass
> > some of parameter as null((private List<Object> params = new
> > ArrayList<Object>(); params.add(null))), but when I pass string value
> as
> > null , it executes parametes procedure as
> >
> > call procname(?,?,?,?,?)
> > Parameters: [#IMNT_RISK_SENSITIVITY, IMNT_RISK_SENSITIVITY, null,
> null, 0]
> > Types: [java.lang.String, java.lang.String, null, null,
> java.lang.Byte]
> >
> > I want type to be java.lang.String instead of null(as with null, I am
> > getting Unsupported SQL type 0 )
> >
> > If I pass blank string "" in place of null in (private List<Object>
> params =
> > new ArrayList<Object>(); params.add("")), Types become
> java.lang.String, but
> > then I think it no more considers it as null.
> >
> > Please help
> >
> > Thanks
> > Nicky
> >
> >
> >
> > -----Original Message-----
> > From: Nicky Jha
> > Sent: Friday, May 14, 2010 8:46 PM
> > To: '[hidden email]<mailto:[hidden email]>'
> > Subject: RE: Need support for Dynamic procedure invocation
> >
> > Joe/Jeff
> >
> > This worked with your suggestion.Thank you so much!!!
> >
> > Nicky
> >
> > -----Original Message-----
> > From: Jeff Butler
> > [mailto:[hidden email]<mailto:[hidden email]>]
> > Sent: Friday, May 14, 2010 6:31 PM
> > To: [hidden email]<mailto:[hidden email]>
> > Subject: Re: Need support for Dynamic procedure invocation
> >
> > Yes - this the best approach.
> >
> > Jeff Butler
> >
> >
> > On 5/14/10, Joe Gooch
> > <[hidden email]<mailto:[hidden email]>> wrote:
> >> I suggest going with a dynamic SQL approach in the mapped statement.
> >>
> >> public class DynamicProcedureParams {
> >>   private String procedureName;
> >>   private List<Object> params = new ArrayList<Object>();
> >>
> >>   // getters and setters here
> >> }
> >>
> >> <procedure id="executeCopyProcs"
> >> parameterClass="path.to.DynamicProcedureParams">
> >> {call $procedureName$ <iterate property="params" open="(" close=")"
> >> conjunction=",">#params[]#</iterate> }
> >> </procedure>
> >>
> >>
> >>
> >> Joe
> >>
> >> Confidentiality Notice:
> >> This e-mail transmission may contain confidential and legally
> privileged
> >> information that is intended only for the individual named in the e-
> mail
> >> address. If you are not the intended recipient, you are hereby
> notified
> >> that
> >> any disclosure, copying, distribution, or reliance upon the contents
> of
> >> this
> >> e-mail message is strictly prohibited. If you have received this e-
> mail
> >> transmission in error, please reply to the sender, so that proper
> >> delivery
> >> can be arranged, and please delete the message from your mail box.
> >>
> >>> -----Original Message-----
> >>> From: Nicky Jha
> >>> [mailto:[hidden email]<mailto:[hidden email]>]
> >>> Sent: Friday, May 14, 2010 7:38 AM
> >>> To: [hidden email]<mailto:[hidden email]>
> >>> Subject: RE: Need support for Dynamic procedure invocation
> >>>
> >>> Hi Jeff,
> >>>
> >>> Thanks for this, if I was to do as suggested and compose the entire
> >>> string with parameter values, how will the parameter types (e.g.
> date,
> >>> smallint etc) be handled?
> >>>
> >>> Nicky!
> >>>
> >>> -----Original Message-----
> >>> From: Jeff Butler
> >>> [mailto:[hidden email]<mailto:[hidden email]>]
> >>> Sent: Friday, May 14, 2010 4:53 PM
> >>> To: [hidden email]<mailto:[hidden email]>
> >>> Subject: Re: Need support for Dynamic procedure invocation
> >>>
> >>> This won't work (as you've discovered).  iBATIS 2.x does not
> reparse
> >>> the string for variables after string substitution.  You'll need to
> do
> >>> this with the dynamic tags, or you'll need to compose the entire
> >>> string (including parameter values - like {call myproc('fred')}).
> >>>
> >>> Jeff Butler
> >>>
> >>>
> >>> On Fri, May 14, 2010 at 4:12 AM, Nicky Jha
> >>> <[hidden email]<mailto:[hidden email]>>
> >>> wrote:
> >>> > Hi Team,
> >>> >
> >>> >
> >>> >
> >>> > I am having hard time resolving following issue.Please help.
> >>> >
> >>> > We are using Ibatis 2.X.
> >>> >
> >>> > In our application we want capability to invoke stored procedure
> by
> >>> reading
> >>> > it from property xml file.We provide procedure name parameters,
> >>> parameters
> >>> > type to  property xml file.
> >>> >
> >>> > Now from this xml file I have created one dynamic procedure
> string
> >>> like
> >>> >
> >>>
> procedureName(#stringVal1#,#stringVal2#,#stringVal3#,#intVal1#,#byteVal
> >>> 1#).
> >>> >
> >>> > Now I want to call this procedure from Ibatis SQL mapping XML
> like
> >>> this
> >>> >
> >>> >
> >>> >
> >>> > <procedure id="executeCopyProcs"
> >>> >
> >>>
> parameterClass="com.jpmorgan.pyramid.pyrsyst.configure.ProcParameterMap
> >>> per">
> >>> >
> >>> > {call $procedureName$ }
> >>> >
> >>> >
> >>> >
> >>> > </procedure>
> >>> >
> >>> >
> >>> >
> >>> > Please refer below for ProcParameterMapper class. Now as soon as
> >>> iBatis sees
> >>> >  $procedureName$, it replaces it with say
> >>> > (#stringVal1#,#stringVal2#,#stringVal3#,#intVal1#,#byteVal1#) but
> it
> >>> does
> >>> > not replace the placeHolder(#), it passes on this as it is.Which
> is a
> >>> issue.
> >>> >
> >>> >
> >>> >
> >>> > If we type
> >>> >
> >>>
> procedureName(#stringVal1#,#stringVal2#,#stringVal3#,#intVal1#,#byteVal
> >>> 1)
> >>> > directly into SQL mapping XML, it replaces place holder, but with
> >>> string
> >>> > substitution , it does not work.We can't type directly into
> mapping
> >>> xML, as
> >>> > this string is composed at run time.Also dynamic tags are not of
> >>> help, as
> >>> > logic to create
> >>> > (#stringVal1#,#stringVal2#,#stringVal3#,#intVal1#,#byteVal1#)
> complex
> >>> > procedure with different types of parameter can't be written in
> >>> mapping
> >>> > XML.At least I am unable to do.
> >>> >
> >>> >
> >>> >
> >>> > I am really struck.Please suggest us the best way to deal with
> it.
> >>> >
> >>> >
> >>> >
> >>> >
> >>> >
> >>> >
> >>> >
> >>> >
> >>> >
> >>> >
> >>> >
> >>> >
> >>> >
> >>> > package com.jpmorgan.pyramid.pyrsyst.configure;
> >>> >
> >>> >
> >>> >
> >>> > import java.lang.reflect.Field;
> >>> >
> >>> >
> >>> >
> >>> > public class ProcParameterMapper {
> >>> >
> >>> >
> >>> >
> >>> >       StringBuffer procedureName = new StringBuffer("");
> >>> >
> >>> >       boolean firstParam = true;
> >>> >
> >>> >       boolean lastParam = false;
> >>> >
> >>> >
> >>> >
> >>> >       String stringVal1;
> >>> >
> >>> >       String stringVal2;
> >>> >
> >>> >       String stringVal3;
> >>> >
> >>> >       String stringVal4;
> >>> >
> >>> >       String stringVal5;
> >>> >
> >>> >       String stringVal6;
> >>> >
> >>> >       String stringVal7;
> >>> >
> >>> >       String stringVal8;
> >>> >
> >>> >       String stringVal9;
> >>> >
> >>> >       String stringVal10;
> >>> >
> >>> >
> >>> >
> >>> >       int intVal1;
> >>> >
> >>> >       int intVal2;
> >>> >
> >>> >       int intVal3;
> >>> >
> >>> >       int intVal4;
> >>> >
> >>> >       int intVal5;
> >>> >
> >>> >       int intVal6;
> >>> >
> >>> >       int intVal7;
> >>> >
> >>> >       int intVal8;
> >>> >
> >>> >       int intVal9;
> >>> >
> >>> >       int intVal10;
> >>> >
> >>> >
> >>> >
> >>> >       byte byteVal1;
> >>> >
> >>> >       byte byteVal2;
> >>> >
> >>> >       byte byteVal3;
> >>> >
> >>> >       byte byteVal4;
> >>> >
> >>> >       byte byteVal5;
> >>> >
> >>> >
> >>> >
> >>> >       public void setStringVal(String value, int count,String
> >>> jdbcType)
> >>> > throws ConfigureException {
> >>> >
> >>> >
> >>> >
> >>> >             Field[] field =
> >>> ProcParameterMapper.class.getDeclaredFields();
> >>> >
> >>> >
> >>> >
> >>> >             for (int i = 0; i < field.length; i++) {
> >>> >
> >>> >                   try {
> >>> >
> >>> >                         if
> >>> > (field[i].getName().endsWith(String.valueOf(count))
> >>> >
> >>> >                                     && ("String")
> >>> >
> >>> >
> >>> > .equals(field[i].getType().getSimpleName())) {
> >>> >
> >>> >                               field[i].set(this, value);
> >>> >
> >>> >                               if (firstParam) {
> >>> >
> >>> >                                     procedureName.append("(#" +
> >>> > field[i].getName()+"#");
> >>> >
> >>> >                                     firstParam = false;
> >>> >
> >>> >                               } else if (lastParam) {
> >>> >
> >>> >                                     procedureName.append(",#" +
> >>> > field[i].getName() +"#)");
> >>> >
> >>> >                               } else {
> >>> >
> >>> >                                     procedureName.append(",#" +
> >>> > field[i].getName()+"#");
> >>> >
> >>> >                               }
> >>> >
> >>> >                               break;
> >>> >
> >>> >
> >>> >
> >>> >                         }
> >>> >
> >>> >                   } catch (Exception e) {
> >>> >
> >>> >                         throw new ConfigureException(
> >>> >
> >>> >                                     "Exception setting String
> value
> >>> in
> >>> > paramMapper"
> >>> >
> >>> >                                                 +
> e.getStackTrace());
> >>> >
> >>> >                   }
> >>> >
> >>> >
> >>> >
> >>> >             }
> >>> >
> >>> >
> >>> >
> >>> >       }
> >>> >
> >>> >
> >>> >
> >>> >       public void setIntVal(int value, int count,String jdbcType)
> >>> throws
> >>> > ConfigureException {
> >>> >
> >>> >
> >>> >
> >>> >             Field[] field =
> >>> ProcParameterMapper.class.getDeclaredFields();
> >>> >
> >>> >
> >>> >
> >>> >             for (int i = 0; i < field.length; i++) {
> >>> >
> >>> >                   try {
> >>> >
> >>> >                         if
> >>> > (field[i].getName().endsWith(String.valueOf(count))
> >>> >
> >>> >                                     &&
> >>> > ("int").equals(field[i].getType().getSimpleName())) {
> >>> >
> >>> >
> >>> >
> >>> >                               field[i].set(this, value);
> >>> >
> >>> >                               if (firstParam) {
> >>> >
> >>> >                                     procedureName.append("(#" +
> >>> > field[i].getName()+"#");
> >>> >
> >>> >                                     firstParam = false;
> >>> >
> >>> >                               } else if (lastParam) {
> >>> >
> >>> >                                     procedureName.append(",#" +
> >>> > field[i].getName() +"#)");
> >>> >
> >>> >                               } else {
> >>> >
> >>> >                                     procedureName.append(",#" +
> >>> > field[i].getName()+"#");
> >>> >
> >>> >                               }
> >>> >
> >>> >                               break;
> >>> >
> >>> >                         }
> >>> >
> >>> >                   } catch (Exception e) {
> >>> >
> >>> >                         throw new ConfigureException(
> >>> >
> >>> >                                     "Exception setting int value
> in
> >>> > paramMapper"
> >>> >
> >>> >                                                 +
> e.getStackTrace());
> >>> >
> >>> >                   }
> >>> >
> >>> >
> >>> >
> >>> >             }
> >>> >
> >>> >
> >>> >
> >>> >       }
> >>> >
> >>> >
> >>> >
> >>> >       public void setByteVal(Byte value, int count,String
> jdbcType)
> >>> throws
> >>> > ConfigureException {
> >>> >
> >>> >
> >>> >
> >>> >             Field[] field =
> >>> ProcParameterMapper.class.getDeclaredFields();
> >>> >
> >>> >
> >>> >
> >>> >             for (int i = 0; i < field.length; i++) {
> >>> >
> >>> >                   try {
> >>> >
> >>> >                         if
> >>> > (field[i].getName().endsWith(String.valueOf(count))
> >>> >
> >>> >                                     &&
> >>> > ("byte").equals(field[i].getType().getSimpleName())) {
> >>> >
> >>> >
> >>> >
> >>> >                               field[i].set(this, value);
> >>> >
> >>> >                               if (firstParam) {
> >>> >
> >>> >                                     procedureName.append("(#" +
> >>> > field[i].getName()+"#");
> >>> >
> >>> >                                     firstParam = false;
> >>> >
> >>> >                               } else if (lastParam) {
> >>> >
> >>> >                                     procedureName.append(",#" +
> >>> > field[i].getName() + "#)");
> >>> >
> >>> >                               } else {
> >>> >
> >>> >                                     procedureName.append(",#" +
> >>> > field[i].getName()+"#");
> >>> >
> >>> >                               }
> >>> >
> >>> >                               break;
> >>> >
> >>> >                         }
> >>> >
> >>> >                   } catch (Exception e) {
> >>> >
> >>> >                         throw new ConfigureException(
> >>> >
> >>> >                                     "Exception setting byte value
> in
> >>> > paramMapper"
> >>> >
> >>> >                                                 +
> e.getStackTrace());
> >>> >
> >>> >                   }
> >>> >
> >>> >
> >>> >
> >>> >             }
> >>> >
> >>> >
> >>> >
> >>> >       }
> >>> >
> >>> >
> >>> >
> >>> >       public String toString() {
> >>> >
> >>> >
> >>> >
> >>> >             return new String("stringVal1 is" + stringVal1 +
> >>> "stringVal2 is"
> >>> >
> >>> >                         + stringVal2 + "stringVal3 is" +
> stringVal3 +
> >>> > "intVal1 is"
> >>> >
> >>> >                         + intVal1 + "intVal2 is" + intVal2 +
> "intVal3
> >>> is" +
> >>> > intVal3);
> >>> >
> >>> >
> >>> >
> >>> >       }
> >>> >
> >>> >
> >>> >
> >>> >       public void setProcedure(String procName) {
> >>> >
> >>> >             procedureName.append(procName);
> >>> >
> >>> >
> >>> >
> >>> >       }
> >>> >
> >>> >
> >>> >
> >>> >       public String getProcedure(){
> >>> >
> >>> >             return procedureName.toString();
> >>> >
> >>> >       }
> >>> >
> >>> >
> >>> >
> >>> > }
> >>> >
> >>> >
> >>> >
> >>> > May thanks in advance
> >>> >
> >>> > Waiting for reply
> >>> >
> >>> > Nicky Jha
> >>> >
> >>> >
> >>> >
> >>> > This communication is for informational purposes only. It is not
> >>> intended as
> >>> > an offer or solicitation for the purchase or sale of any
> financial
> >>> > instrument or as an official confirmation of any transaction. All
> >>> market
> >>> > prices, data and other information are not warranted as to
> >>> completeness or
> >>> > accuracy and are subject to change without notice. Any comments
> or
> >>> > statements made herein do not necessarily reflect those of
> JPMorgan
> >>> Chase &
> >>> > Co., its subsidiaries and affiliates. This transmission may
> contain
> >>> > information that is privileged, confidential, legally privileged,
> >>> and/or
> >>> > exempt from disclosure under applicable law. If you are not the
> >>> intended
> >>> > recipient, you are hereby notified that any disclosure, copying,
> >>> > distribution, or use of the information contained herein
> (including
> >>> any
> >>> > reliance thereon) is STRICTLY PROHIBITED. Although this
> transmission
> >>> and any
> >>> > attachments are believed to be free of any virus or other defect
> that
> >>> might
> >>> > affect any computer system into which it is received and opened,
> it
> >>> is the
> >>> > responsibility of the recipient to ensure that it is virus free
> and
> >>> no
> >>> > responsibility is accepted by JPMorgan Chase & Co., its
> subsidiaries
> >>> and
> >>> > affiliates, as applicable, for any loss or damage arising in any
> way
> >>> from
> >>> > its use. If you received this transmission in error, please
> >>> immediately
> >>> > contact the sender and destroy the material in its entirety,
> whether
> >>> in
> >>> > electronic or hard copy format. Thank you. Please refer to
> >>> > http://www.jpmorgan.com/pages/disclosures for disclosures
> relating to
> >>> > European legal entities.
> >>>
> >>> -------------------------------------------------------------------
> --
> >>> To unsubscribe, e-mail:
> >>> [hidden email]<mailto:user-java-
> [hidden email]>
> >>> For additional commands, e-mail:
> >>> [hidden email]<mailto:user-java-
> [hidden email]>
> >>
> >>
> >> --------------------------------------------------------------------
> -
> >> To unsubscribe, e-mail:
> >> [hidden email]<mailto:user-java-
> [hidden email]>
> >> For additional commands, e-mail:
> >> [hidden email]<mailto:user-java-
> [hidden email]>
> >>
> >>
> >
> > --
> > Sent from my mobile device
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail:
> > [hidden email]<mailto:user-java-
> [hidden email]>
> > For additional commands, e-mail:
> > [hidden email]<mailto:user-java-
> [hidden email]>
> >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail:
> > [hidden email]<mailto:user-java-
> [hidden email]>
> > For additional commands, e-mail:
> > [hidden email]<mailto:user-java-
> [hidden email]>
> >
> >
> >
>
> --
> Sent from my mobile device
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

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

Re: Need support for Dynamic procedure invocation

Jeff Butler-2
Joe - this is exactly what I was going to write next - good call!

Nicky - you might try Clinton's suggestion, but it will depend on
whether your driver allows setting a null VARCHAR for a DECIMAL
parameter.  Some will, some won't.

The @SelectProvider route is the best route if things really need to
be this dynamic (do they REALLY need to be this dynamic?)  And writing
a spring object factory for mybatis isn't that hard to do.

Jeff Butler



On Tue, May 25, 2010 at 10:04 AM, Joe Gooch <[hidden email]> wrote:

> Sure, but it'll be uglier.
>
> To me, your whole approach/rationale seems odd here... Procedures changing at runtime like that seems like poor DB practice except for very specific scenarios, and/or single threaded db usage.... Then again, I don't know exactly what you're trying to accomplish.  So I'll try to suspend judgement.  But it seems to me at this point, if you're not mapping a result set, that IBatis isn't really gaining you much, since you can easily fire up a StringBuffer and build the statement yourself.  Probably easier than you could with the IBatis XML.
>
> Beans/Defn:
>
> public class MyParam {
>  public final static int VARCHAR=1;
>  public final static int INTEGER=2;
>  public final static int DECIMAL=3;
>  private int type;
>  private Object value;
>
>  // TODO: getters/setters/constructor
> }
>
> public class DynamicProcedureParams {
>  private String procedureName;
>  private List<MyParam> params = new ArrayList<MyParam>();
>
>  // TODO: getters/setters/constructor
> }
>
> {call $procedureName$ <iterate> property="params" open="(" close=")" conjunction=",">
>  <isEqual property="params[].type" compareValue="1">#params[].value:VARCHAR#</isEqual>
>  <isEqual property="params[].type" compareValue="2">#params[].value:INTEGER#</isEqual>
>  <isEqual property="params[].type" compareValue="3">#params[].value:DECIMAL#</isEqual>
> </iterate> }
>
>
>
> However, in JDBC you're looking like this:
>  DynamicProcedureParams dpp = // your stuff;
>  Connection con = // your connection;
>
>  StringBuffer sb = new StringBuffer(1000).append("{call ").append(dpp.getProcedureName()).append(" (");
>  for(int i=0; i<dpp.getParams().size(); i++) {
>    if (i>0) sb.append(",");
>    sb.append("?");
>  }
>  sb.append(")}");
>  PreparedStatement st = con.prepareStatement(sb.toString());
>  // You could now cache this prepared statement until/unless your dpp values change
>  for(int i=0; i<dpp.getParams().size(); i++) {
>    switch(dpp.getParams().get(i).getType()) {
>      case MyParam.VARCHAR: st.setString(i+1, dpp.getParams().get(i).getValue()); break;
>      case MyParam.INTEGER: st.setInt(i+1, dpp.getParams().get(i).getValue()); break;
>      case MyParam.DECIMAL: st.setDouble(i+1, dpp.getParams().get(i).getValue()); break;
>    }
>  }
>  ResultSet rs = st.executeQuery();  // or st.execute();
>
>
> If you really need to do this type of thing, it seems to me like you're going to be better off creating a class and beans to hold the procedure name and parameter types, and when that changes, recompile the prepared statement.  And then have a separate class that holds parameter values for a single execution.  Then the stringbuffer bits above can be split from individual queries, and only need to happen when the underlying schema changes. (Of course you'll need synchronization)
>
> Similarly, you could do as Jeff suggested and use a SelectProvider to build the SQL, effectively doing the caching bits above.
>
> I guess at this point it would help to know what IBatis is really buying you.
>
> Joe
>
> Confidentiality Notice:
> This e-mail transmission may contain confidential and legally privileged information that is intended only for the individual named in the e-mail address. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or reliance upon the contents of this e-mail message is strictly prohibited. If you have received this e-mail transmission in error, please reply to the sender, so that proper delivery can be arranged, and please delete the message from your mail box.
>
>
>> -----Original Message-----
>> From: Nicky Jha [mailto:[hidden email]]
>> Sent: Tuesday, May 25, 2010 3:59 AM
>> To: [hidden email]
>> Subject: RE: Need support for Dynamic procedure invocation
>>
>> Hi
>>
>> Is there a way I can modify {call $procedureName$ <iterate
>> property="params" open="(" close=")"
>>  conjunction=",">#params[]#</iterate> } to include jdbc types also, so
>> that I can pass null value to database?
>>
>> Thanks
>> Nicky
>>
>>
>>
>> -----Original Message-----
>> From: Nicky Jha
>> Sent: Tuesday, May 25, 2010 11:12 AM
>> To: '[hidden email]'
>> Subject: RE: Need support for Dynamic procedure invocation
>>
>> Joe/Jeff
>> Please help.
>>
>> this class is not part of framework, this is approach, we have used in
>> case our procedure changes at runtime, so will its parameter.For that
>> we using
>>
>> public class DynamicProcedureParams {
>> >>   private String procedureName;
>> >>   private List<Object> params = new ArrayList<Object>();
>> >>
>> >>   // getters and setters here
>> >> }
>> >>
>> >> <procedure id="executeCopyProcs"
>> >> parameterClass="path.to.DynamicProcedureParams">
>> >> {call $procedureName$ <iterate property="params" open="(" close=")"
>> >> conjunction=",">#params[]#</iterate> }
>> >> </procedure>
>> >>
>>
>> Thanks
>> Nicky
>>
>>
>>
>>
>> -----Original Message-----
>> From: Clinton Begin [mailto:[hidden email]]
>> Sent: Tuesday, May 25, 2010 11:07 AM
>> To: [hidden email]
>> Subject: Re: Need support for Dynamic procedure invocation
>>
>> I'm not familiar with that class, as it's not part of the framework
>> (maybe post it here).  Short story is that JDBC requires nullable
>> column types to be specified.  Ibatis allows for this in the parameter
>> map (crack open the user guide and search for "jdbcType" for more).
>>
>> Cheers,
>> Clinton
>>
>>
>>
>> On 2010-05-24, Nicky Jha <[hidden email]> wrote:
>> > Hi
>> >
>> > I am using parameter class DynamicProcedureParams(as sugessted by Joe
>> Gooch
>> > ), as my requirement was to build paramaters at run time, so with
>> this
>> > approach where can I set jdbcType?
>> >
>> > Nicky
>> >
>> > From: Clinton Begin [mailto:[hidden email]]
>> > Sent: Tuesday, May 25, 2010 10:39 AM
>> > To: [hidden email]
>> > Subject: Re: Need support for Dynamic procedure invocation
>> >
>> > Are you setting the jdbcType in your parameter map for all nullable
>> > columns?
>> >
>> > Clinton
>> > On Mon, May 24, 2010 at 11:03 PM, Nicky Jha
>> > <[hidden email]<mailto:[hidden email]>> wrote:
>> >
>> > Hi
>> >
>> > with approach mentioned below(DynamicProcedureParams) , I want to
>> also pass
>> > some of parameter as null((private List<Object> params = new
>> > ArrayList<Object>(); params.add(null))), but when I pass string value
>> as
>> > null , it executes parametes procedure as
>> >
>> > call procname(?,?,?,?,?)
>> > Parameters: [#IMNT_RISK_SENSITIVITY, IMNT_RISK_SENSITIVITY, null,
>> null, 0]
>> > Types: [java.lang.String, java.lang.String, null, null,
>> java.lang.Byte]
>> >
>> > I want type to be java.lang.String instead of null(as with null, I am
>> > getting Unsupported SQL type 0 )
>> >
>> > If I pass blank string "" in place of null in (private List<Object>
>> params =
>> > new ArrayList<Object>(); params.add("")), Types become
>> java.lang.String, but
>> > then I think it no more considers it as null.
>> >
>> > Please help
>> >
>> > Thanks
>> > Nicky
>> >
>> >
>> >
>> > -----Original Message-----
>> > From: Nicky Jha
>> > Sent: Friday, May 14, 2010 8:46 PM
>> > To: '[hidden email]<mailto:[hidden email]>'
>> > Subject: RE: Need support for Dynamic procedure invocation
>> >
>> > Joe/Jeff
>> >
>> > This worked with your suggestion.Thank you so much!!!
>> >
>> > Nicky
>> >
>> > -----Original Message-----
>> > From: Jeff Butler
>> > [mailto:[hidden email]<mailto:[hidden email]>]
>> > Sent: Friday, May 14, 2010 6:31 PM
>> > To: [hidden email]<mailto:[hidden email]>
>> > Subject: Re: Need support for Dynamic procedure invocation
>> >
>> > Yes - this the best approach.
>> >
>> > Jeff Butler
>> >
>> >
>> > On 5/14/10, Joe Gooch
>> > <[hidden email]<mailto:[hidden email]>> wrote:
>> >> I suggest going with a dynamic SQL approach in the mapped statement.
>> >>
>> >> public class DynamicProcedureParams {
>> >>   private String procedureName;
>> >>   private List<Object> params = new ArrayList<Object>();
>> >>
>> >>   // getters and setters here
>> >> }
>> >>
>> >> <procedure id="executeCopyProcs"
>> >> parameterClass="path.to.DynamicProcedureParams">
>> >> {call $procedureName$ <iterate property="params" open="(" close=")"
>> >> conjunction=",">#params[]#</iterate> }
>> >> </procedure>
>> >>
>> >>
>> >>
>> >> Joe
>> >>
>> >> Confidentiality Notice:
>> >> This e-mail transmission may contain confidential and legally
>> privileged
>> >> information that is intended only for the individual named in the e-
>> mail
>> >> address. If you are not the intended recipient, you are hereby
>> notified
>> >> that
>> >> any disclosure, copying, distribution, or reliance upon the contents
>> of
>> >> this
>> >> e-mail message is strictly prohibited. If you have received this e-
>> mail
>> >> transmission in error, please reply to the sender, so that proper
>> >> delivery
>> >> can be arranged, and please delete the message from your mail box.
>> >>
>> >>> -----Original Message-----
>> >>> From: Nicky Jha
>> >>> [mailto:[hidden email]<mailto:[hidden email]>]
>> >>> Sent: Friday, May 14, 2010 7:38 AM
>> >>> To: [hidden email]<mailto:[hidden email]>
>> >>> Subject: RE: Need support for Dynamic procedure invocation
>> >>>
>> >>> Hi Jeff,
>> >>>
>> >>> Thanks for this, if I was to do as suggested and compose the entire
>> >>> string with parameter values, how will the parameter types (e.g.
>> date,
>> >>> smallint etc) be handled?
>> >>>
>> >>> Nicky!
>> >>>
>> >>> -----Original Message-----
>> >>> From: Jeff Butler
>> >>> [mailto:[hidden email]<mailto:[hidden email]>]
>> >>> Sent: Friday, May 14, 2010 4:53 PM
>> >>> To: [hidden email]<mailto:[hidden email]>
>> >>> Subject: Re: Need support for Dynamic procedure invocation
>> >>>
>> >>> This won't work (as you've discovered).  iBATIS 2.x does not
>> reparse
>> >>> the string for variables after string substitution.  You'll need to
>> do
>> >>> this with the dynamic tags, or you'll need to compose the entire
>> >>> string (including parameter values - like {call myproc('fred')}).
>> >>>
>> >>> Jeff Butler
>> >>>
>> >>>
>> >>> On Fri, May 14, 2010 at 4:12 AM, Nicky Jha
>> >>> <[hidden email]<mailto:[hidden email]>>
>> >>> wrote:
>> >>> > Hi Team,
>> >>> >
>> >>> >
>> >>> >
>> >>> > I am having hard time resolving following issue.Please help.
>> >>> >
>> >>> > We are using Ibatis 2.X.
>> >>> >
>> >>> > In our application we want capability to invoke stored procedure
>> by
>> >>> reading
>> >>> > it from property xml file.We provide procedure name parameters,
>> >>> parameters
>> >>> > type to  property xml file.
>> >>> >
>> >>> > Now from this xml file I have created one dynamic procedure
>> string
>> >>> like
>> >>> >
>> >>>
>> procedureName(#stringVal1#,#stringVal2#,#stringVal3#,#intVal1#,#byteVal
>> >>> 1#).
>> >>> >
>> >>> > Now I want to call this procedure from Ibatis SQL mapping XML
>> like
>> >>> this
>> >>> >
>> >>> >
>> >>> >
>> >>> > <procedure id="executeCopyProcs"
>> >>> >
>> >>>
>> parameterClass="com.jpmorgan.pyramid.pyrsyst.configure.ProcParameterMap
>> >>> per">
>> >>> >
>> >>> > {call $procedureName$ }
>> >>> >
>> >>> >
>> >>> >
>> >>> > </procedure>
>> >>> >
>> >>> >
>> >>> >
>> >>> > Please refer below for ProcParameterMapper class. Now as soon as
>> >>> iBatis sees
>> >>> >  $procedureName$, it replaces it with say
>> >>> > (#stringVal1#,#stringVal2#,#stringVal3#,#intVal1#,#byteVal1#) but
>> it
>> >>> does
>> >>> > not replace the placeHolder(#), it passes on this as it is.Which
>> is a
>> >>> issue.
>> >>> >
>> >>> >
>> >>> >
>> >>> > If we type
>> >>> >
>> >>>
>> procedureName(#stringVal1#,#stringVal2#,#stringVal3#,#intVal1#,#byteVal
>> >>> 1)
>> >>> > directly into SQL mapping XML, it replaces place holder, but with
>> >>> string
>> >>> > substitution , it does not work.We can't type directly into
>> mapping
>> >>> xML, as
>> >>> > this string is composed at run time.Also dynamic tags are not of
>> >>> help, as
>> >>> > logic to create
>> >>> > (#stringVal1#,#stringVal2#,#stringVal3#,#intVal1#,#byteVal1#)
>> complex
>> >>> > procedure with different types of parameter can't be written in
>> >>> mapping
>> >>> > XML.At least I am unable to do.
>> >>> >
>> >>> >
>> >>> >
>> >>> > I am really struck.Please suggest us the best way to deal with
>> it.
>> >>> >
>> >>> >
>> >>> >
>> >>> >
>> >>> >
>> >>> >
>> >>> >
>> >>> >
>> >>> >
>> >>> >
>> >>> >
>> >>> >
>> >>> >
>> >>> > package com.jpmorgan.pyramid.pyrsyst.configure;
>> >>> >
>> >>> >
>> >>> >
>> >>> > import java.lang.reflect.Field;
>> >>> >
>> >>> >
>> >>> >
>> >>> > public class ProcParameterMapper {
>> >>> >
>> >>> >
>> >>> >
>> >>> >       StringBuffer procedureName = new StringBuffer("");
>> >>> >
>> >>> >       boolean firstParam = true;
>> >>> >
>> >>> >       boolean lastParam = false;
>> >>> >
>> >>> >
>> >>> >
>> >>> >       String stringVal1;
>> >>> >
>> >>> >       String stringVal2;
>> >>> >
>> >>> >       String stringVal3;
>> >>> >
>> >>> >       String stringVal4;
>> >>> >
>> >>> >       String stringVal5;
>> >>> >
>> >>> >       String stringVal6;
>> >>> >
>> >>> >       String stringVal7;
>> >>> >
>> >>> >       String stringVal8;
>> >>> >
>> >>> >       String stringVal9;
>> >>> >
>> >>> >       String stringVal10;
>> >>> >
>> >>> >
>> >>> >
>> >>> >       int intVal1;
>> >>> >
>> >>> >       int intVal2;
>> >>> >
>> >>> >       int intVal3;
>> >>> >
>> >>> >       int intVal4;
>> >>> >
>> >>> >       int intVal5;
>> >>> >
>> >>> >       int intVal6;
>> >>> >
>> >>> >       int intVal7;
>> >>> >
>> >>> >       int intVal8;
>> >>> >
>> >>> >       int intVal9;
>> >>> >
>> >>> >       int intVal10;
>> >>> >
>> >>> >
>> >>> >
>> >>> >       byte byteVal1;
>> >>> >
>> >>> >       byte byteVal2;
>> >>> >
>> >>> >       byte byteVal3;
>> >>> >
>> >>> >       byte byteVal4;
>> >>> >
>> >>> >       byte byteVal5;
>> >>> >
>> >>> >
>> >>> >
>> >>> >       public void setStringVal(String value, int count,String
>> >>> jdbcType)
>> >>> > throws ConfigureException {
>> >>> >
>> >>> >
>> >>> >
>> >>> >             Field[] field =
>> >>> ProcParameterMapper.class.getDeclaredFields();
>> >>> >
>> >>> >
>> >>> >
>> >>> >             for (int i = 0; i < field.length; i++) {
>> >>> >
>> >>> >                   try {
>> >>> >
>> >>> >                         if
>> >>> > (field[i].getName().endsWith(String.valueOf(count))
>> >>> >
>> >>> >                                     && ("String")
>> >>> >
>> >>> >
>> >>> > .equals(field[i].getType().getSimpleName())) {
>> >>> >
>> >>> >                               field[i].set(this, value);
>> >>> >
>> >>> >                               if (firstParam) {
>> >>> >
>> >>> >                                     procedureName.append("(#" +
>> >>> > field[i].getName()+"#");
>> >>> >
>> >>> >                                     firstParam = false;
>> >>> >
>> >>> >                               } else if (lastParam) {
>> >>> >
>> >>> >                                     procedureName.append(",#" +
>> >>> > field[i].getName() +"#)");
>> >>> >
>> >>> >                               } else {
>> >>> >
>> >>> >                                     procedureName.append(",#" +
>> >>> > field[i].getName()+"#");
>> >>> >
>> >>> >                               }
>> >>> >
>> >>> >                               break;
>> >>> >
>> >>> >
>> >>> >
>> >>> >                         }
>> >>> >
>> >>> >                   } catch (Exception e) {
>> >>> >
>> >>> >                         throw new ConfigureException(
>> >>> >
>> >>> >                                     "Exception setting String
>> value
>> >>> in
>> >>> > paramMapper"
>> >>> >
>> >>> >                                                 +
>> e.getStackTrace());
>> >>> >
>> >>> >                   }
>> >>> >
>> >>> >
>> >>> >
>> >>> >             }
>> >>> >
>> >>> >
>> >>> >
>> >>> >       }
>> >>> >
>> >>> >
>> >>> >
>> >>> >       public void setIntVal(int value, int count,String jdbcType)
>> >>> throws
>> >>> > ConfigureException {
>> >>> >
>> >>> >
>> >>> >
>> >>> >             Field[] field =
>> >>> ProcParameterMapper.class.getDeclaredFields();
>> >>> >
>> >>> >
>> >>> >
>> >>> >             for (int i = 0; i < field.length; i++) {
>> >>> >
>> >>> >                   try {
>> >>> >
>> >>> >                         if
>> >>> > (field[i].getName().endsWith(String.valueOf(count))
>> >>> >
>> >>> >                                     &&
>> >>> > ("int").equals(field[i].getType().getSimpleName())) {
>> >>> >
>> >>> >
>> >>> >
>> >>> >                               field[i].set(this, value);
>> >>> >
>> >>> >                               if (firstParam) {
>> >>> >
>> >>> >                                     procedureName.append("(#" +
>> >>> > field[i].getName()+"#");
>> >>> >
>> >>> >                                     firstParam = false;
>> >>> >
>> >>> >                               } else if (lastParam) {
>> >>> >
>> >>> >                                     procedureName.append(",#" +
>> >>> > field[i].getName() +"#)");
>> >>> >
>> >>> >                               } else {
>> >>> >
>> >>> >                                     procedureName.append(",#" +
>> >>> > field[i].getName()+"#");
>> >>> >
>> >>> >                               }
>> >>> >
>> >>> >                               break;
>> >>> >
>> >>> >                         }
>> >>> >
>> >>> >                   } catch (Exception e) {
>> >>> >
>> >>> >                         throw new ConfigureException(
>> >>> >
>> >>> >                                     "Exception setting int value
>> in
>> >>> > paramMapper"
>> >>> >
>> >>> >                                                 +
>> e.getStackTrace());
>> >>> >
>> >>> >                   }
>> >>> >
>> >>> >
>> >>> >
>> >>> >             }
>> >>> >
>> >>> >
>> >>> >
>> >>> >       }
>> >>> >
>> >>> >
>> >>> >
>> >>> >       public void setByteVal(Byte value, int count,String
>> jdbcType)
>> >>> throws
>> >>> > ConfigureException {
>> >>> >
>> >>> >
>> >>> >
>> >>> >             Field[] field =
>> >>> ProcParameterMapper.class.getDeclaredFields();
>> >>> >
>> >>> >
>> >>> >
>> >>> >             for (int i = 0; i < field.length; i++) {
>> >>> >
>> >>> >                   try {
>> >>> >
>> >>> >                         if
>> >>> > (field[i].getName().endsWith(String.valueOf(count))
>> >>> >
>> >>> >                                     &&
>> >>> > ("byte").equals(field[i].getType().getSimpleName())) {
>> >>> >
>> >>> >
>> >>> >
>> >>> >                               field[i].set(this, value);
>> >>> >
>> >>> >                               if (firstParam) {
>> >>> >
>> >>> >                                     procedureName.append("(#" +
>> >>> > field[i].getName()+"#");
>> >>> >
>> >>> >                                     firstParam = false;
>> >>> >
>> >>> >                               } else if (lastParam) {
>> >>> >
>> >>> >                                     procedureName.append(",#" +
>> >>> > field[i].getName() + "#)");
>> >>> >
>> >>> >                               } else {
>> >>> >
>> >>> >                                     procedureName.append(",#" +
>> >>> > field[i].getName()+"#");
>> >>> >
>> >>> >                               }
>> >>> >
>> >>> >                               break;
>> >>> >
>> >>> >                         }
>> >>> >
>> >>> >                   } catch (Exception e) {
>> >>> >
>> >>> >                         throw new ConfigureException(
>> >>> >
>> >>> >                                     "Exception setting byte value
>> in
>> >>> > paramMapper"
>> >>> >
>> >>> >                                                 +
>> e.getStackTrace());
>> >>> >
>> >>> >                   }
>> >>> >
>> >>> >
>> >>> >
>> >>> >             }
>> >>> >
>> >>> >
>> >>> >
>> >>> >       }
>> >>> >
>> >>> >
>> >>> >
>> >>> >       public String toString() {
>> >>> >
>> >>> >
>> >>> >
>> >>> >             return new String("stringVal1 is" + stringVal1 +
>> >>> "stringVal2 is"
>> >>> >
>> >>> >                         + stringVal2 + "stringVal3 is" +
>> stringVal3 +
>> >>> > "intVal1 is"
>> >>> >
>> >>> >                         + intVal1 + "intVal2 is" + intVal2 +
>> "intVal3
>> >>> is" +
>> >>> > intVal3);
>> >>> >
>> >>> >
>> >>> >
>> >>> >       }
>> >>> >
>> >>> >
>> >>> >
>> >>> >       public void setProcedure(String procName) {
>> >>> >
>> >>> >             procedureName.append(procName);
>> >>> >
>> >>> >
>> >>> >
>> >>> >       }
>> >>> >
>> >>> >
>> >>> >
>> >>> >       public String getProcedure(){
>> >>> >
>> >>> >             return procedureName.toString();
>> >>> >
>> >>> >       }
>> >>> >
>> >>> >
>> >>> >
>> >>> > }
>> >>> >
>> >>> >
>> >>> >
>> >>> > May thanks in advance
>> >>> >
>> >>> > Waiting for reply
>> >>> >
>> >>> > Nicky Jha
>> >>> >
>> >>> >
>> >>> >
>> >>> > This communication is for informational purposes only. It is not
>> >>> intended as
>> >>> > an offer or solicitation for the purchase or sale of any
>> financial
>> >>> > instrument or as an official confirmation of any transaction. All
>> >>> market
>> >>> > prices, data and other information are not warranted as to
>> >>> completeness or
>> >>> > accuracy and are subject to change without notice. Any comments
>> or
>> >>> > statements made herein do not necessarily reflect those of
>> JPMorgan
>> >>> Chase &
>> >>> > Co., its subsidiaries and affiliates. This transmission may
>> contain
>> >>> > information that is privileged, confidential, legally privileged,
>> >>> and/or
>> >>> > exempt from disclosure under applicable law. If you are not the
>> >>> intended
>> >>> > recipient, you are hereby notified that any disclosure, copying,
>> >>> > distribution, or use of the information contained herein
>> (including
>> >>> any
>> >>> > reliance thereon) is STRICTLY PROHIBITED. Although this
>> transmission
>> >>> and any
>> >>> > attachments are believed to be free of any virus or other defect
>> that
>> >>> might
>> >>> > affect any computer system into which it is received and opened,
>> it
>> >>> is the
>> >>> > responsibility of the recipient to ensure that it is virus free
>> and
>> >>> no
>> >>> > responsibility is accepted by JPMorgan Chase & Co., its
>> subsidiaries
>> >>> and
>> >>> > affiliates, as applicable, for any loss or damage arising in any
>> way
>> >>> from
>> >>> > its use. If you received this transmission in error, please
>> >>> immediately
>> >>> > contact the sender and destroy the material in its entirety,
>> whether
>> >>> in
>> >>> > electronic or hard copy format. Thank you. Please refer to
>> >>> > http://www.jpmorgan.com/pages/disclosures for disclosures
>> relating to
>> >>> > European legal entities.
>> >>>
>> >>> -------------------------------------------------------------------
>> --
>> >>> To unsubscribe, e-mail:
>> >>> [hidden email]<mailto:user-java-
>> [hidden email]>
>> >>> For additional commands, e-mail:
>> >>> [hidden email]<mailto:user-java-
>> [hidden email]>
>> >>
>> >>
>> >> --------------------------------------------------------------------
>> -
>> >> To unsubscribe, e-mail:
>> >> [hidden email]<mailto:user-java-
>> [hidden email]>
>> >> For additional commands, e-mail:
>> >> [hidden email]<mailto:user-java-
>> [hidden email]>
>> >>
>> >>
>> >
>> > --
>> > Sent from my mobile device
>> >
>> > ---------------------------------------------------------------------
>> > To unsubscribe, e-mail:
>> > [hidden email]<mailto:user-java-
>> [hidden email]>
>> > For additional commands, e-mail:
>> > [hidden email]<mailto:user-java-
>> [hidden email]>
>> >
>> >
>> > ---------------------------------------------------------------------
>> > To unsubscribe, e-mail:
>> > [hidden email]<mailto:user-java-
>> [hidden email]>
>> > For additional commands, e-mail:
>> > [hidden email]<mailto:user-java-
>> [hidden email]>
>> >
>> >
>> >
>>
>> --
>> Sent from my mobile device
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [hidden email]
>> For additional commands, e-mail: [hidden email]
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [hidden email]
>> For additional commands, e-mail: [hidden email]
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

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

RE: Need support for Dynamic procedure invocation

Nicky Jha
In reply to this post by Nicky Jha
Joe/Jeff/Clinton

It worked for me.Thanks again for all the support you are providing.

Nicky

-----Original Message-----
From: Nicky Jha
Sent: Wednesday, May 26, 2010 11:24 AM
To: Amar X Hussain
Subject: FW: Need support for Dynamic procedure invocation

Hi Amar,

Please see below, may be this needs discussion with you :-)

Nicky

-----Original Message-----
From: Jeff Butler [mailto:[hidden email]]
Sent: Tuesday, May 25, 2010 8:39 PM
To: [hidden email]
Subject: Re: Need support for Dynamic procedure invocation

Joe - this is exactly what I was going to write next - good call!

Nicky - you might try Clinton's suggestion, but it will depend on
whether your driver allows setting a null VARCHAR for a DECIMAL
parameter.  Some will, some won't.

The @SelectProvider route is the best route if things really need to
be this dynamic (do they REALLY need to be this dynamic?)  And writing
a spring object factory for mybatis isn't that hard to do.

Jeff Butler



On Tue, May 25, 2010 at 10:04 AM, Joe Gooch <[hidden email]> wrote:

> Sure, but it'll be uglier.
>
> To me, your whole approach/rationale seems odd here... Procedures changing at runtime like that seems like poor DB practice except for very specific scenarios, and/or single threaded db usage.... Then again, I don't know exactly what you're trying to accomplish.  So I'll try to suspend judgement.  But it seems to me at this point, if you're not mapping a result set, that IBatis isn't really gaining you much, since you can easily fire up a StringBuffer and build the statement yourself.  Probably easier than you could with the IBatis XML.
>
> Beans/Defn:
>
> public class MyParam {
>  public final static int VARCHAR=1;
>  public final static int INTEGER=2;
>  public final static int DECIMAL=3;
>  private int type;
>  private Object value;
>
>  // TODO: getters/setters/constructor
> }
>
> public class DynamicProcedureParams {
>  private String procedureName;
>  private List<MyParam> params = new ArrayList<MyParam>();
>
>  // TODO: getters/setters/constructor
> }
>
> {call $procedureName$ <iterate> property="params" open="(" close=")" conjunction=",">
>  <isEqual property="params[].type" compareValue="1">#params[].value:VARCHAR#</isEqual>
>  <isEqual property="params[].type" compareValue="2">#params[].value:INTEGER#</isEqual>
>  <isEqual property="params[].type" compareValue="3">#params[].value:DECIMAL#</isEqual>
> </iterate> }
>
>
>
> However, in JDBC you're looking like this:
>  DynamicProcedureParams dpp = // your stuff;
>  Connection con = // your connection;
>
>  StringBuffer sb = new StringBuffer(1000).append("{call ").append(dpp.getProcedureName()).append(" (");
>  for(int i=0; i<dpp.getParams().size(); i++) {
>    if (i>0) sb.append(",");
>    sb.append("?");
>  }
>  sb.append(")}");
>  PreparedStatement st = con.prepareStatement(sb.toString());
>  // You could now cache this prepared statement until/unless your dpp values change
>  for(int i=0; i<dpp.getParams().size(); i++) {
>    switch(dpp.getParams().get(i).getType()) {
>      case MyParam.VARCHAR: st.setString(i+1, dpp.getParams().get(i).getValue()); break;
>      case MyParam.INTEGER: st.setInt(i+1, dpp.getParams().get(i).getValue()); break;
>      case MyParam.DECIMAL: st.setDouble(i+1, dpp.getParams().get(i).getValue()); break;
>    }
>  }
>  ResultSet rs = st.executeQuery();  // or st.execute();
>
>
> If you really need to do this type of thing, it seems to me like you're going to be better off creating a class and beans to hold the procedure name and parameter types, and when that changes, recompile the prepared statement.  And then have a separate class that holds parameter values for a single execution.  Then the stringbuffer bits above can be split from individual queries, and only need to happen when the underlying schema changes. (Of course you'll need synchronization)
>
> Similarly, you could do as Jeff suggested and use a SelectProvider to build the SQL, effectively doing the caching bits above.
>
> I guess at this point it would help to know what IBatis is really buying you.
>
> Joe
>
> Confidentiality Notice:
> This e-mail transmission may contain confidential and legally privileged information that is intended only for the individual named in the e-mail address. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or reliance upon the contents of this e-mail message is strictly prohibited. If you have received this e-mail transmission in error, please reply to the sender, so that proper delivery can be arranged, and please delete the message from your mail box.
>
>
>> -----Original Message-----
>> From: Nicky Jha [mailto:[hidden email]]
>> Sent: Tuesday, May 25, 2010 3:59 AM
>> To: [hidden email]
>> Subject: RE: Need support for Dynamic procedure invocation
>>
>> Hi
>>
>> Is there a way I can modify {call $procedureName$ <iterate
>> property="params" open="(" close=")"
>>  conjunction=",">#params[]#</iterate> } to include jdbc types also, so
>> that I can pass null value to database?
>>
>> Thanks
>> Nicky
>>
>>
>>
>> -----Original Message-----
>> From: Nicky Jha
>> Sent: Tuesday, May 25, 2010 11:12 AM
>> To: '[hidden email]'
>> Subject: RE: Need support for Dynamic procedure invocation
>>
>> Joe/Jeff
>> Please help.
>>
>> this class is not part of framework, this is approach, we have used in
>> case our procedure changes at runtime, so will its parameter.For that
>> we using
>>
>> public class DynamicProcedureParams {
>> >>   private String procedureName;
>> >>   private List<Object> params = new ArrayList<Object>();
>> >>
>> >>   // getters and setters here
>> >> }
>> >>
>> >> <procedure id="executeCopyProcs"
>> >> parameterClass="path.to.DynamicProcedureParams">
>> >> {call $procedureName$ <iterate property="params" open="(" close=")"
>> >> conjunction=",">#params[]#</iterate> }
>> >> </procedure>
>> >>
>>
>> Thanks
>> Nicky
>>
>>
>>
>>
>> -----Original Message-----
>> From: Clinton Begin [mailto:[hidden email]]
>> Sent: Tuesday, May 25, 2010 11:07 AM
>> To: [hidden email]
>> Subject: Re: Need support for Dynamic procedure invocation
>>
>> I'm not familiar with that class, as it's not part of the framework
>> (maybe post it here).  Short story is that JDBC requires nullable
>> column types to be specified.  Ibatis allows for this in the parameter
>> map (crack open the user guide and search for "jdbcType" for more).
>>
>> Cheers,
>> Clinton
>>
>>
>>
>> On 2010-05-24, Nicky Jha <[hidden email]> wrote:
>> > Hi
>> >
>> > I am using parameter class DynamicProcedureParams(as sugessted by Joe
>> Gooch
>> > ), as my requirement was to build paramaters at run time, so with
>> this
>> > approach where can I set jdbcType?
>> >
>> > Nicky
>> >
>> > From: Clinton Begin [mailto:[hidden email]]
>> > Sent: Tuesday, May 25, 2010 10:39 AM
>> > To: [hidden email]
>> > Subject: Re: Need support for Dynamic procedure invocation
>> >
>> > Are you setting the jdbcType in your parameter map for all nullable
>> > columns?
>> >
>> > Clinton
>> > On Mon, May 24, 2010 at 11:03 PM, Nicky Jha
>> > <[hidden email]<mailto:[hidden email]>> wrote:
>> >
>> > Hi
>> >
>> > with approach mentioned below(DynamicProcedureParams) , I want to
>> also pass
>> > some of parameter as null((private List<Object> params = new
>> > ArrayList<Object>(); params.add(null))), but when I pass string value
>> as
>> > null , it executes parametes procedure as
>> >
>> > call procname(?,?,?,?,?)
>> > Parameters: [#IMNT_RISK_SENSITIVITY, IMNT_RISK_SENSITIVITY, null,
>> null, 0]
>> > Types: [java.lang.String, java.lang.String, null, null,
>> java.lang.Byte]
>> >
>> > I want type to be java.lang.String instead of null(as with null, I am
>> > getting Unsupported SQL type 0 )
>> >
>> > If I pass blank string "" in place of null in (private List<Object>
>> params =
>> > new ArrayList<Object>(); params.add("")), Types become
>> java.lang.String, but
>> > then I think it no more considers it as null.
>> >
>> > Please help
>> >
>> > Thanks
>> > Nicky
>> >
>> >
>> >
>> > -----Original Message-----
>> > From: Nicky Jha
>> > Sent: Friday, May 14, 2010 8:46 PM
>> > To: '[hidden email]<mailto:[hidden email]>'
>> > Subject: RE: Need support for Dynamic procedure invocation
>> >
>> > Joe/Jeff
>> >
>> > This worked with your suggestion.Thank you so much!!!
>> >
>> > Nicky
>> >
>> > -----Original Message-----
>> > From: Jeff Butler
>> > [mailto:[hidden email]<mailto:[hidden email]>]
>> > Sent: Friday, May 14, 2010 6:31 PM
>> > To: [hidden email]<mailto:[hidden email]>
>> > Subject: Re: Need support for Dynamic procedure invocation
>> >
>> > Yes - this the best approach.
>> >
>> > Jeff Butler
>> >
>> >
>> > On 5/14/10, Joe Gooch
>> > <[hidden email]<mailto:[hidden email]>> wrote:
>> >> I suggest going with a dynamic SQL approach in the mapped statement.
>> >>
>> >> public class DynamicProcedureParams {
>> >>   private String procedureName;
>> >>   private List<Object> params = new ArrayList<Object>();
>> >>
>> >>   // getters and setters here
>> >> }
>> >>
>> >> <procedure id="executeCopyProcs"
>> >> parameterClass="path.to.DynamicProcedureParams">
>> >> {call $procedureName$ <iterate property="params" open="(" close=")"
>> >> conjunction=",">#params[]#</iterate> }
>> >> </procedure>
>> >>
>> >>
>> >>
>> >> Joe
>> >>
>> >> Confidentiality Notice:
>> >> This e-mail transmission may contain confidential and legally
>> privileged
>> >> information that is intended only for the individual named in the e-
>> mail
>> >> address. If you are not the intended recipient, you are hereby
>> notified
>> >> that
>> >> any disclosure, copying, distribution, or reliance upon the contents
>> of
>> >> this
>> >> e-mail message is strictly prohibited. If you have received this e-
>> mail
>> >> transmission in error, please reply to the sender, so that proper
>> >> delivery
>> >> can be arranged, and please delete the message from your mail box.
>> >>
>> >>> -----Original Message-----
>> >>> From: Nicky Jha
>> >>> [mailto:[hidden email]<mailto:[hidden email]>]
>> >>> Sent: Friday, May 14, 2010 7:38 AM
>> >>> To: [hidden email]<mailto:[hidden email]>
>> >>> Subject: RE: Need support for Dynamic procedure invocation
>> >>>
>> >>> Hi Jeff,
>> >>>
>> >>> Thanks for this, if I was to do as suggested and compose the entire
>> >>> string with parameter values, how will the parameter types (e.g.
>> date,
>> >>> smallint etc) be handled?
>> >>>
>> >>> Nicky!
>> >>>
>> >>> -----Original Message-----
>> >>> From: Jeff Butler
>> >>> [mailto:[hidden email]<mailto:[hidden email]>]
>> >>> Sent: Friday, May 14, 2010 4:53 PM
>> >>> To: [hidden email]<mailto:[hidden email]>
>> >>> Subject: Re: Need support for Dynamic procedure invocation
>> >>>
>> >>> This won't work (as you've discovered).  iBATIS 2.x does not
>> reparse
>> >>> the string for variables after string substitution.  You'll need to
>> do
>> >>> this with the dynamic tags, or you'll need to compose the entire
>> >>> string (including parameter values - like {call myproc('fred')}).
>> >>>
>> >>> Jeff Butler
>> >>>
>> >>>
>> >>> On Fri, May 14, 2010 at 4:12 AM, Nicky Jha
>> >>> <[hidden email]<mailto:[hidden email]>>
>> >>> wrote:
>> >>> > Hi Team,
>> >>> >
>> >>> >
>> >>> >
>> >>> > I am having hard time resolving following issue.Please help.
>> >>> >
>> >>> > We are using Ibatis 2.X.
>> >>> >
>> >>> > In our application we want capability to invoke stored procedure
>> by
>> >>> reading
>> >>> > it from property xml file.We provide procedure name parameters,
>> >>> parameters
>> >>> > type to  property xml file.
>> >>> >
>> >>> > Now from this xml file I have created one dynamic procedure
>> string
>> >>> like
>> >>> >
>> >>>
>> procedureName(#stringVal1#,#stringVal2#,#stringVal3#,#intVal1#,#byteVal
>> >>> 1#).
>> >>> >
>> >>> > Now I want to call this procedure from Ibatis SQL mapping XML
>> like
>> >>> this
>> >>> >
>> >>> >
>> >>> >
>> >>> > <procedure id="executeCopyProcs"
>> >>> >
>> >>>
>> parameterClass="com.jpmorgan.pyramid.pyrsyst.configure.ProcParameterMap
>> >>> per">
>> >>> >
>> >>> > {call $procedureName$ }
>> >>> >
>> >>> >
>> >>> >
>> >>> > </procedure>
>> >>> >
>> >>> >
>> >>> >
>> >>> > Please refer below for ProcParameterMapper class. Now as soon as
>> >>> iBatis sees
>> >>> >  $procedureName$, it replaces it with say
>> >>> > (#stringVal1#,#stringVal2#,#stringVal3#,#intVal1#,#byteVal1#) but
>> it
>> >>> does
>> >>> > not replace the placeHolder(#), it passes on this as it is.Which
>> is a
>> >>> issue.
>> >>> >
>> >>> >
>> >>> >
>> >>> > If we type
>> >>> >
>> >>>
>> procedureName(#stringVal1#,#stringVal2#,#stringVal3#,#intVal1#,#byteVal
>> >>> 1)
>> >>> > directly into SQL mapping XML, it replaces place holder, but with
>> >>> string
>> >>> > substitution , it does not work.We can't type directly into
>> mapping
>> >>> xML, as
>> >>> > this string is composed at run time.Also dynamic tags are not of
>> >>> help, as
>> >>> > logic to create
>> >>> > (#stringVal1#,#stringVal2#,#stringVal3#,#intVal1#,#byteVal1#)
>> complex
>> >>> > procedure with different types of parameter can't be written in
>> >>> mapping
>> >>> > XML.At least I am unable to do.
>> >>> >
>> >>> >
>> >>> >
>> >>> > I am really struck.Please suggest us the best way to deal with
>> it.
>> >>> >
>> >>> >
>> >>> >
>> >>> >
>> >>> >
>> >>> >
>> >>> >
>> >>> >
>> >>> >
>> >>> >
>> >>> >
>> >>> >
>> >>> >
>> >>> > package com.jpmorgan.pyramid.pyrsyst.configure;
>> >>> >
>> >>> >
>> >>> >
>> >>> > import java.lang.reflect.Field;
>> >>> >
>> >>> >
>> >>> >
>> >>> > public class ProcParameterMapper {
>> >>> >
>> >>> >
>> >>> >
>> >>> >       StringBuffer procedureName = new StringBuffer("");
>> >>> >
>> >>> >       boolean firstParam = true;
>> >>> >
>> >>> >       boolean lastParam = false;
>> >>> >
>> >>> >
>> >>> >
>> >>> >       String stringVal1;
>> >>> >
>> >>> >       String stringVal2;
>> >>> >
>> >>> >       String stringVal3;
>> >>> >
>> >>> >       String stringVal4;
>> >>> >
>> >>> >       String stringVal5;
>> >>> >
>> >>> >       String stringVal6;
>> >>> >
>> >>> >       String stringVal7;
>> >>> >
>> >>> >       String stringVal8;
>> >>> >
>> >>> >       String stringVal9;
>> >>> >
>> >>> >       String stringVal10;
>> >>> >
>> >>> >
>> >>> >
>> >>> >       int intVal1;
>> >>> >
>> >>> >       int intVal2;
>> >>> >
>> >>> >       int intVal3;
>> >>> >
>> >>> >       int intVal4;
>> >>> >
>> >>> >       int intVal5;
>> >>> >
>> >>> >       int intVal6;
>> >>> >
>> >>> >       int intVal7;
>> >>> >
>> >>> >       int intVal8;
>> >>> >
>> >>> >       int intVal9;
>> >>> >
>> >>> >       int intVal10;
>> >>> >
>> >>> >
>> >>> >
>> >>> >       byte byteVal1;
>> >>> >
>> >>> >       byte byteVal2;
>> >>> >
>> >>> >       byte byteVal3;
>> >>> >
>> >>> >       byte byteVal4;
>> >>> >
>> >>> >       byte byteVal5;
>> >>> >
>> >>> >
>> >>> >
>> >>> >       public void setStringVal(String value, int count,String
>> >>> jdbcType)
>> >>> > throws ConfigureException {
>> >>> >
>> >>> >
>> >>> >
>> >>> >             Field[] field =
>> >>> ProcParameterMapper.class.getDeclaredFields();
>> >>> >
>> >>> >
>> >>> >
>> >>> >             for (int i = 0; i < field.length; i++) {
>> >>> >
>> >>> >                   try {
>> >>> >
>> >>> >                         if
>> >>> > (field[i].getName().endsWith(String.valueOf(count))
>> >>> >
>> >>> >                                     && ("String")
>> >>> >
>> >>> >
>> >>> > .equals(field[i].getType().getSimpleName())) {
>> >>> >
>> >>> >                               field[i].set(this, value);
>> >>> >
>> >>> >                               if (firstParam) {
>> >>> >
>> >>> >                                     procedureName.append("(#" +
>> >>> > field[i].getName()+"#");
>> >>> >
>> >>> >                                     firstParam = false;
>> >>> >
>> >>> >                               } else if (lastParam) {
>> >>> >
>> >>> >                                     procedureName.append(",#" +
>> >>> > field[i].getName() +"#)");
>> >>> >
>> >>> >                               } else {
>> >>> >
>> >>> >                                     procedureName.append(",#" +
>> >>> > field[i].getName()+"#");
>> >>> >
>> >>> >                               }
>> >>> >
>> >>> >                               break;
>> >>> >
>> >>> >
>> >>> >
>> >>> >                         }
>> >>> >
>> >>> >                   } catch (Exception e) {
>> >>> >
>> >>> >                         throw new ConfigureException(
>> >>> >
>> >>> >                                     "Exception setting String
>> value
>> >>> in
>> >>> > paramMapper"
>> >>> >
>> >>> >                                                 +
>> e.getStackTrace());
>> >>> >
>> >>> >                   }
>> >>> >
>> >>> >
>> >>> >
>> >>> >             }
>> >>> >
>> >>> >
>> >>> >
>> >>> >       }
>> >>> >
>> >>> >
>> >>> >
>> >>> >       public void setIntVal(int value, int count,String jdbcType)
>> >>> throws
>> >>> > ConfigureException {
>> >>> >
>> >>> >
>> >>> >
>> >>> >             Field[] field =
>> >>> ProcParameterMapper.class.getDeclaredFields();
>> >>> >
>> >>> >
>> >>> >
>> >>> >             for (int i = 0; i < field.length; i++) {
>> >>> >
>> >>> >                   try {
>> >>> >
>> >>> >                         if
>> >>> > (field[i].getName().endsWith(String.valueOf(count))
>> >>> >
>> >>> >                                     &&
>> >>> > ("int").equals(field[i].getType().getSimpleName())) {
>> >>> >
>> >>> >
>> >>> >
>> >>> >                               field[i].set(this, value);
>> >>> >
>> >>> >                               if (firstParam) {
>> >>> >
>> >>> >                                     procedureName.append("(#" +
>> >>> > field[i].getName()+"#");
>> >>> >
>> >>> >                                     firstParam = false;
>> >>> >
>> >>> >                               } else if (lastParam) {
>> >>> >
>> >>> >                                     procedureName.append(",#" +
>> >>> > field[i].getName() +"#)");
>> >>> >
>> >>> >                               } else {
>> >>> >
>> >>> >                                     procedureName.append(",#" +
>> >>> > field[i].getName()+"#");
>> >>> >
>> >>> >                               }
>> >>> >
>> >>> >                               break;
>> >>> >
>> >>> >                         }
>> >>> >
>> >>> >                   } catch (Exception e) {
>> >>> >
>> >>> >                         throw new ConfigureException(
>> >>> >
>> >>> >                                     "Exception setting int value
>> in
>> >>> > paramMapper"
>> >>> >
>> >>> >                                                 +
>> e.getStackTrace());
>> >>> >
>> >>> >                   }
>> >>> >
>> >>> >
>> >>> >
>> >>> >             }
>> >>> >
>> >>> >
>> >>> >
>> >>> >       }
>> >>> >
>> >>> >
>> >>> >
>> >>> >       public void setByteVal(Byte value, int count,String
>> jdbcType)
>> >>> throws
>> >>> > ConfigureException {
>> >>> >
>> >>> >
>> >>> >
>> >>> >             Field[] field =
>> >>> ProcParameterMapper.class.getDeclaredFields();
>> >>> >
>> >>> >
>> >>> >
>> >>> >             for (int i = 0; i < field.length; i++) {
>> >>> >
>> >>> >                   try {
>> >>> >
>> >>> >                         if
>> >>> > (field[i].getName().endsWith(String.valueOf(count))
>> >>> >
>> >>> >                                     &&
>> >>> > ("byte").equals(field[i].getType().getSimpleName())) {
>> >>> >
>> >>> >
>> >>> >
>> >>> >                               field[i].set(this, value);
>> >>> >
>> >>> >                               if (firstParam) {
>> >>> >
>> >>> >                                     procedureName.append("(#" +
>> >>> > field[i].getName()+"#");
>> >>> >
>> >>> >                                     firstParam = false;
>> >>> >
>> >>> >                               } else if (lastParam) {
>> >>> >
>> >>> >                                     procedureName.append(",#" +
>> >>> > field[i].getName() + "#)");
>> >>> >
>> >>> >                               } else {
>> >>> >
>> >>> >                                     procedureName.append(",#" +
>> >>> > field[i].getName()+"#");
>> >>> >
>> >>> >                               }
>> >>> >
>> >>> >                               break;
>> >>> >
>> >>> >                         }
>> >>> >
>> >>> >                   } catch (Exception e) {
>> >>> >
>> >>> >                         throw new ConfigureException(
>> >>> >
>> >>> >                                     "Exception setting byte value
>> in
>> >>> > paramMapper"
>> >>> >
>> >>> >                                                 +
>> e.getStackTrace());
>> >>> >
>> >>> >                   }
>> >>> >
>> >>> >
>> >>> >
>> >>> >             }
>> >>> >
>> >>> >
>> >>> >
>> >>> >       }
>> >>> >
>> >>> >
>> >>> >
>> >>> >       public String toString() {
>> >>> >
>> >>> >
>> >>> >
>> >>> >             return new String("stringVal1 is" + stringVal1 +
>> >>> "stringVal2 is"
>> >>> >
>> >>> >                         + stringVal2 + "stringVal3 is" +
>> stringVal3 +
>> >>> > "intVal1 is"
>> >>> >
>> >>> >                         + intVal1 + "intVal2 is" + intVal2 +
>> "intVal3
>> >>> is" +
>> >>> > intVal3);
>> >>> >
>> >>> >
>> >>> >
>> >>> >       }
>> >>> >
>> >>> >
>> >>> >
>> >>> >       public void setProcedure(String procName) {
>> >>> >
>> >>> >             procedureName.append(procName);
>> >>> >
>> >>> >
>> >>> >
>> >>> >       }
>> >>> >
>> >>> >
>> >>> >
>> >>> >       public String getProcedure(){
>> >>> >
>> >>> >             return procedureName.toString();
>> >>> >
>> >>> >       }
>> >>> >
>> >>> >
>> >>> >
>> >>> > }
>> >>> >
>> >>> >
>> >>> >
>> >>> > May thanks in advance
>> >>> >
>> >>> > Waiting for reply
>> >>> >
>> >>> > Nicky Jha
>> >>> >
>> >>> >
>> >>> >
>> >>> > This communication is for informational purposes only. It is not
>> >>> intended as
>> >>> > an offer or solicitation for the purchase or sale of any
>> financial
>> >>> > instrument or as an official confirmation of any transaction. All
>> >>> market
>> >>> > prices, data and other information are not warranted as to
>> >>> completeness or
>> >>> > accuracy and are subject to change without notice. Any comments
>> or
>> >>> > statements made herein do not necessarily reflect those of
>> JPMorgan
>> >>> Chase &
>> >>> > Co., its subsidiaries and affiliates. This transmission may
>> contain
>> >>> > information that is privileged, confidential, legally privileged,
>> >>> and/or
>> >>> > exempt from disclosure under applicable law. If you are not the
>> >>> intended
>> >>> > recipient, you are hereby notified that any disclosure, copying,
>> >>> > distribution, or use of the information contained herein
>> (including
>> >>> any
>> >>> > reliance thereon) is STRICTLY PROHIBITED. Although this
>> transmission
>> >>> and any
>> >>> > attachments are believed to be free of any virus or other defect
>> that
>> >>> might
>> >>> > affect any computer system into which it is received and opened,
>> it
>> >>> is the
>> >>> > responsibility of the recipient to ensure that it is virus free
>> and
>> >>> no
>> >>> > responsibility is accepted by JPMorgan Chase & Co., its
>> subsidiaries
>> >>> and
>> >>> > affiliates, as applicable, for any loss or damage arising in any
>> way
>> >>> from
>> >>> > its use. If you received this transmission in error, please
>> >>> immediately
>> >>> > contact the sender and destroy the material in its entirety,
>> whether
>> >>> in
>> >>> > electronic or hard copy format. Thank you. Please refer to
>> >>> > http://www.jpmorgan.com/pages/disclosures for disclosures
>> relating to
>> >>> > European legal entities.
>> >>>
>> >>> -------------------------------------------------------------------
>> --
>> >>> To unsubscribe, e-mail:
>> >>> [hidden email]<mailto:user-java-
>> [hidden email]>
>> >>> For additional commands, e-mail:
>> >>> [hidden email]<mailto:user-java-
>> [hidden email]>
>> >>
>> >>
>> >> --------------------------------------------------------------------
>> -
>> >> To unsubscribe, e-mail:
>> >> [hidden email]<mailto:user-java-
>> [hidden email]>
>> >> For additional commands, e-mail:
>> >> [hidden email]<mailto:user-java-
>> [hidden email]>
>> >>
>> >>
>> >
>> > --
>> > Sent from my mobile device
>> >
>> > ---------------------------------------------------------------------
>> > To unsubscribe, e-mail:
>> > [hidden email]<mailto:user-java-
>> [hidden email]>
>> > For additional commands, e-mail:
>> > [hidden email]<mailto:user-java-
>> [hidden email]>
>> >
>> >
>> > ---------------------------------------------------------------------
>> > To unsubscribe, e-mail:
>> > [hidden email]<mailto:user-java-
>> [hidden email]>
>> > For additional commands, e-mail:
>> > [hidden email]<mailto:user-java-
>> [hidden email]>
>> >
>> >
>> >
>>
>> --
>> Sent from my mobile device
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [hidden email]
>> For additional commands, e-mail: [hidden email]
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [hidden email]
>> For additional commands, e-mail: [hidden email]
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Loading...