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 <nicky....@jpmchase.com> 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: 'user-java@ibatis.apache.org'
> 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:clinton.be...@gmail.com]
> Sent: Tuesday, May 25, 2010 11:07 AM
> To: user-java@ibatis.apache.org
> 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 <nicky....@jpmchase.com> 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:clinton.be...@gmail.com]
>> Sent: Tuesday, May 25, 2010 10:39 AM
>> To: user-java@ibatis.apache.org
>> 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
>> <nicky....@jpmchase.com<mailto:nicky....@jpmchase.com>> 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: 'user-java@ibatis.apache.org<mailto:user-java@ibatis.apache.org>'
>> 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:jeffgbut...@gmail.com<mailto:jeffgbut...@gmail.com>]
>> Sent: Friday, May 14, 2010 6:31 PM
>> To: user-java@ibatis.apache.org<mailto:user-java@ibatis.apache.org>
>> Subject: Re: Need support for Dynamic procedure invocation
>>
>> Yes - this the best approach.
>>
>> Jeff Butler
>>
>>
>> On 5/14/10, Joe Gooch
>> <mrwiz...@k12system.com<mailto:mrwiz...@k12system.com>> 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:nicky....@jpmchase.com<mailto:nicky....@jpmchase.com>]
>>>> Sent: Friday, May 14, 2010 7:38 AM
>>>> To: user-java@ibatis.apache.org<mailto:user-java@ibatis.apache.org>
>>>> 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:jeffgbut...@gmail.com<mailto:jeffgbut...@gmail.com>]
>>>> Sent: Friday, May 14, 2010 4:53 PM
>>>> To: user-java@ibatis.apache.org<mailto:user-java@ibatis.apache.org>
>>>> 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
>>>> <nicky....@jpmchase.com<mailto:nicky....@jpmchase.com>>
>>>> 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:
>>>> user-java-unsubscr...@ibatis.apache.org<mailto:user-java-unsubscr...@ibatis.apache.org>
>>>> For additional commands, e-mail:
>>>> user-java-h...@ibatis.apache.org<mailto:user-java-h...@ibatis.apache.org>
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail:
>>> user-java-unsubscr...@ibatis.apache.org<mailto:user-java-unsubscr...@ibatis.apache.org>
>>> For additional commands, e-mail:
>>> user-java-h...@ibatis.apache.org<mailto:user-java-h...@ibatis.apache.org>
>>>
>>>
>>
>> --
>> Sent from my mobile device
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail:
>> user-java-unsubscr...@ibatis.apache.org<mailto:user-java-unsubscr...@ibatis.apache.org>
>> For additional commands, e-mail:
>> user-java-h...@ibatis.apache.org<mailto:user-java-h...@ibatis.apache.org>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail:
>> user-java-unsubscr...@ibatis.apache.org<mailto:user-java-unsubscr...@ibatis.apache.org>
>> For additional commands, e-mail:
>> user-java-h...@ibatis.apache.org<mailto:user-java-h...@ibatis.apache.org>
>>
>>
>>
>
> --
> Sent from my mobile device
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-java-unsubscr...@ibatis.apache.org
> For additional commands, e-mail: user-java-h...@ibatis.apache.org
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-java-unsubscr...@ibatis.apache.org
> For additional commands, e-mail: user-java-h...@ibatis.apache.org
>
>
--
Sent from my mobile device
---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscr...@ibatis.apache.org
For additional commands, e-mail: user-java-h...@ibatis.apache.org
