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: '[email protected]'
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:[email protected]]
Sent: Friday, May 14, 2010 6:31 PM
To: [email protected]
Subject: Re: Need support for Dynamic procedure invocation
Yes - this the best approach.
Jeff Butler
On 5/14/10, Joe Gooch <[email protected]> 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:[email protected]]
>> Sent: Friday, May 14, 2010 7:38 AM
>> To: [email protected]
>> 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:[email protected]]
>> Sent: Friday, May 14, 2010 4:53 PM
>> To: [email protected]
>> 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 <[email protected]>
>> 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: [email protected]
>> For additional commands, e-mail: [email protected]
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [email protected]
> For additional commands, e-mail: [email protected]
>
>
--
Sent from my mobile device
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
