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> 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'
> 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]
> Sent: Friday, May 14, 2010 6:31 PM
> To: 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> 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]
> >> Sent: Friday, May 14, 2010 7:38 AM
> >> To: 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]
> >> Sent: Friday, May 14, 2010 4:53 PM
> >> To: 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>
> >> 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
> >> 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
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-java-unsubscr...@ibatis.apache.org
> For additional commands, e-mail: user-java-h...@ibatis.apache.org
>
>
