Return-Path: Delivered-To: apmail-ibatis-user-java-archive@www.apache.org Received: (qmail 44413 invoked from network); 25 May 2010 15:04:48 -0000 Received: from unknown (HELO mail.apache.org) (140.211.11.3) by 140.211.11.9 with SMTP; 25 May 2010 15:04:48 -0000 Received: (qmail 7543 invoked by uid 500); 25 May 2010 15:04:47 -0000 Delivered-To: apmail-ibatis-user-java-archive@ibatis.apache.org Received: (qmail 7522 invoked by uid 500); 25 May 2010 15:04:47 -0000 Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user-java@ibatis.apache.org Delivered-To: mailing list user-java@ibatis.apache.org Received: (qmail 7515 invoked by uid 99); 25 May 2010 15:04:47 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 25 May 2010 15:04:47 +0000 X-ASF-Spam-Status: No, hits=-0.0 required=10.0 tests=SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: domain of mrwizard@k12system.com designates 208.177.111.18 as permitted sender) Received: from [208.177.111.18] (HELO dwalin.k12system.com) (208.177.111.18) by apache.org (qpsmtpd/0.29) with SMTP; Tue, 25 May 2010 15:04:41 +0000 Received: from localhost (localhost [127.0.0.1]) by dwalin.k12system.com (Postfix) with ESMTP id A18222CF4D for ; Tue, 25 May 2010 11:04:19 -0400 (EDT) X-Virus-Scanned: amavisd-new at k12system.com Received: from dwalin.k12system.com ([127.0.0.1]) by localhost (balin.k12system.com [127.0.0.1]) (amavisd-new, port 10025) with LMTP id qMU-tRCjlD6f for ; Tue, 25 May 2010 11:04:11 -0400 (EDT) Received: from BOROMIR.win.k12system.com (trusted.k12system.com [192.168.40.32]) by dwalin.k12system.com (Postfix) with ESMTP id A0DEA2CF4C for ; Tue, 25 May 2010 11:04:11 -0400 (EDT) Received: from BOROMIR.win.k12system.com ([::1]) by BOROMIR.win.k12system.com ([::1]) with mapi; Tue, 25 May 2010 11:04:11 -0400 From: Joe Gooch To: "user-java@ibatis.apache.org" Subject: RE: Need support for Dynamic procedure invocation Thread-Topic: Need support for Dynamic procedure invocation Thread-Index: AcrzZZFzCS1aFIfxSJ6tOVqT69pkLgAACBgQAATSKQACJ9R0YA== Date: Tue, 25 May 2010 15:04:10 +0000 Message-ID: <11FB0CD6E5A337448CBF1AB26C00057D2ED0E0@BOROMIR.win.k12system.com> References: <526CFDB395AB9D44B309022DF9BE86861B8E8ABFA1@EMAIC104VS01.exchad.jpmchase.net> <526CFDB395AB9D44B309022DF9BE86861B8E8ABFB0@EMAIC104VS01.exchad.jpmchase.net> <526CFDB395AB9D44B309022DF9BE86861B8E8AC034@EMAIC104VS01.exchad.jpmchase.net> In-Reply-To: <526CFDB395AB9D44B309022DF9BE86861B8E8AC034@EMAIC104VS01.exchad.jpmchase.net> Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable MIME-Version: 1.0 X-Virus-Checked: Checked by ClamAV on apache.org 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 s= cenarios, and/or single threaded db usage.... Then again, I don't know exac= tly what you're trying to accomplish. So I'll try to suspend judgement. B= ut it seems to me at this point, if you're not mapping a result set, that I= Batis isn't really gaining you much, since you can easily fire up a StringB= uffer and build the statement yourself. Probably easier than you could wit= h the IBatis XML. Beans/Defn: public class MyParam { public final static int VARCHAR=3D1; public final static int INTEGER=3D2; public final static int DECIMAL=3D3; private int type; private Object value; // TODO: getters/setters/constructor } public class DynamicProcedureParams { private String procedureName; private List params =3D new ArrayList(); // TODO: getters/setters/constructor } {call $procedureName$ property=3D"params" open=3D"(" close=3D")" = conjunction=3D","> #params[].value:VA= RCHAR# #params[].value:IN= TEGER# #params[].value:DE= CIMAL# } However, in JDBC you're looking like this: DynamicProcedureParams dpp =3D // your stuff; Connection con =3D // your connection; StringBuffer sb =3D new StringBuffer(1000).append("{call ").append(dpp.ge= tProcedureName()).append(" ("); for(int i=3D0; i0) sb.append(","); sb.append("?"); } sb.append(")}"); PreparedStatement st =3D con.prepareStatement(sb.toString()); // You could now cache this prepared statement until/unless your dpp valu= es change for(int i=3D0; i -----Original Message----- > From: Nicky Jha [mailto:nicky.jha@jpmchase.com] > Sent: Tuesday, May 25, 2010 3:59 AM > To: user-java@ibatis.apache.org > Subject: RE: Need support for Dynamic procedure invocation > > Hi > > Is there a way I can modify {call $procedureName$ property=3D"params" open=3D"(" close=3D")" > conjunction=3D",">#params[]# } 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 params =3D new ArrayList(); > >> > >> // getters and setters here > >> } > >> > >> >> parameterClass=3D"path.to.DynamicProcedureParams"> > >> {call $procedureName$ >> conjunction=3D",">#params[]# } > >> > >> > > Thanks > Nicky > > > > > -----Original Message----- > From: Clinton Begin [mailto:clinton.begin@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 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.begin@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 > > > wrote: > > > > Hi > > > > with approach mentioned below(DynamicProcedureParams) , I want to > also pass > > some of parameter as null((private List params =3D new > > ArrayList(); 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 > params =3D > > new ArrayList(); 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:jeffgbutler@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 > > > wrote: > >> I suggest going with a dynamic SQL approach in the mapped statement. > >> > >> public class DynamicProcedureParams { > >> private String procedureName; > >> private List params =3D new ArrayList(); > >> > >> // getters and setters here > >> } > >> > >> >> parameterClass=3D"path.to.DynamicProcedureParams"> > >> {call $procedureName$ >> conjunction=3D",">#params[]# } > >> > >> > >> > >> > >> 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.jha@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:jeffgbutler@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 > >>> > > >>> 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 > >>> > > >>> > > >>> > > >>> > >>> > > >>> > parameterClass=3D"com.jpmorgan.pyramid.pyrsyst.configure.ProcParameterMap > >>> per"> > >>> > > >>> > {call $procedureName$ } > >>> > > >>> > > >>> > > >>> > > >>> > > >>> > > >>> > > >>> > 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 =3D new StringBuffer(""); > >>> > > >>> > boolean firstParam =3D true; > >>> > > >>> > boolean lastParam =3D 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 =3D > >>> ProcParameterMapper.class.getDeclaredFields(); > >>> > > >>> > > >>> > > >>> > for (int i =3D 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 =3D 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 =3D > >>> ProcParameterMapper.class.getDeclaredFields(); > >>> > > >>> > > >>> > > >>> > for (int i =3D 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 =3D 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 =3D > >>> ProcParameterMapper.class.getDeclaredFields(); > >>> > > >>> > > >>> > > >>> > for (int i =3D 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 =3D 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-unsubscribe@ibatis.apache.org unsubscribe@ibatis.apache.org> > >>> For additional commands, e-mail: > >>> user-java-help@ibatis.apache.org help@ibatis.apache.org> > >> > >> > >> -------------------------------------------------------------------- > - > >> To unsubscribe, e-mail: > >> user-java-unsubscribe@ibatis.apache.org unsubscribe@ibatis.apache.org> > >> For additional commands, e-mail: > >> user-java-help@ibatis.apache.org help@ibatis.apache.org> > >> > >> > > > > -- > > Sent from my mobile device > > > > --------------------------------------------------------------------- > > To unsubscribe, e-mail: > > user-java-unsubscribe@ibatis.apache.org unsubscribe@ibatis.apache.org> > > For additional commands, e-mail: > > user-java-help@ibatis.apache.org help@ibatis.apache.org> > > > > > > --------------------------------------------------------------------- > > To unsubscribe, e-mail: > > user-java-unsubscribe@ibatis.apache.org unsubscribe@ibatis.apache.org> > > For additional commands, e-mail: > > user-java-help@ibatis.apache.org help@ibatis.apache.org> > > > > > > > > -- > Sent from my mobile device > > --------------------------------------------------------------------- > To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org > For additional commands, e-mail: user-java-help@ibatis.apache.org > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org > For additional commands, e-mail: user-java-help@ibatis.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org For additional commands, e-mail: user-java-help@ibatis.apache.org