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
