ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Brandon Goodin <brandon.goo...@gmail.com>
Subject Re: Calling all Store Procedure Gurus
Date Tue, 03 May 2005 05:14:04 GMT
Thanks jeff! I appreciate your guy contriburtion. We'll add this one
to the wiki. If you have opportunity to provide a more "correct"
stored procedure that would be great. Again, these kind of resources
are great to have. I appreciate all your guys efforts in getting this
to me.

iBatis has the best community!

Brandon

On 5/2/05, Jeff Butler <dhscn06@cstone.dhs.state.il.us> wrote:
> Well, its late and I'm running out of time to get something to you.  So,
> I just modified Sven's stuff to make it work on DB2.  Still has the same
> SQL injection exposure, and is not the way we'd do it in a live
> environment, but it does illustrate the concepts.  This is also an
> example of generating dynamic SQL in a stored procedure.  I'm not a huge
> fan of this approach, but it works for now.
> 
> I do get the desired results when calling from JDBC as Sven illustrated
> earlier.
> 
> Thanks to Sven for the kick start!
> 
> Jeff Butler
> 
> -- create the table
> create table db2admin.category (categoryid  char(2), name varchar(255),
> title varchar(255), description varchar(255), sequence integer);
> 
> -- insert some test data
> insert into db2admin.category(categoryid, name, title, description,
> sequence) values('AA', 'AA name', 'Title AA', 'Description AA', 1);
> insert into db2admin.category(categoryid, name, title, description,
> sequence) values('AA', 'AB name', 'Title AB', 'Description AB', 1);
> insert into db2admin.category(categoryid, name, title, description,
> sequence) values('BB', 'AA name', 'Title BB', 'Description BB', 1);
> insert into db2admin.category(categoryid, name, title, description,
> sequence) values('CC', 'CC name', 'Title CC', 'Description CC', 1);
> insert into db2admin.category(categoryid, name, title, description,
> sequence) values('AA', 'DD name', 'Title DD', 'Description DD', 1);
> 
> -- create the stored procedure
> CREATE PROCEDURE DB2ADMIn.get_category ( IN categoryids VARCHAR(255),
>                                          IN p_name VARCHAR(255) )
>     DYNAMIC RESULT SETS 1
> P1: BEGIN
>    declare sqltext varchar(1000);
>    declare first char(1) default 'Y';
>    declare myStmt statement;
> 
>    set sqltext = 'select * from db2admin.category';
> 
>    if (categoryids is not null)  then
>       if (first = 'Y')  then
>          set sqltext = sqltext || ' where categoryid in (' ||
> categoryids || ') ';
>          set first = 'N';
>       end if;
>    end if;
> 
>    if (p_name is not null)  then
>       if (first = 'Y')  then
>          set sqltext = sqltext || ' where ';
>       else
>          set sqltext = sqltext || ' and ';
>       end if;
> 
>       set sqltext = sqltext || 'name like ''' || p_name || '%''';
>       set first = 'N';
>    end if;
> 
>    prepare myStmt from sqltext;
> 
>    begin
>       declare res cursor with return to caller for myStmt;
>       open res;
>    end;
> 
> END P1
> 
> >>> brandon.goodin@gmail.com 05/02/05 12:42 PM >>>
> I am interested in the latter. I am trying to show a comparison
> between coding iBatis
> dynamic SQL and coding an equivalent function in a stored proc. I am
> more interested in functional equivalence than i am in syntactical
> equivalence. I would have preformed this myself. But, i wanted a
> chance to get my bias towards iBatis out of the way and see what
> others have found for dealing with mildly complex situations like the
> one i presented. If you can get it to me sooner that would be better.
> I am running on a short deadline to complete this chapter and my hope
> was to have it in sometime late tonight. But, I'll take what i can get
> and if you are willing... i'll have to wait.
> 
> If anyone else would like to make a contribution it would be a great
> addition to the wiki. I think this kind of information will help us to
> understand when and why to use iBatis.
> 
> Thanks much,
> Brandon
> 
> On 5/2/05, Jeff Butler <dhscn06@cstone.dhs.state.il.us> wrote:
> > I'll bite...
> >
> > What are you trying to show in this comparison?
> >
> > If you are trying to show that iBatis is better than stored procs for
> > composing dynamic SQL, then the comparison, to me, is pretty
> > uninteresting.  IMHO iBatis (or almost any other client), is better
> than
> > trying to compose dynamic SQL in a stored procedure.  To me, composing
> > dynamic SQL in a stored procedure is a waste of time.  You get few of
> > the benefits of a stored proc, and a lot more headache.  Security and
> > some kind of schema hiding are the only possible benefits I can think
> > of.  Performance and complexity would likely be worse.
> >
> > If, however, you are trying to show a comparison between coding iBatis
> > dynamic SQL and coding an equivalent function in a stored proc, that
> > would be interesting to me.  In that case, I would not write a stored
> > proc that composed dynamic sql.  For example, rather than using the
> > "categoryId IN" syntax , I might insert the values into a temp table
> and
> > join the table.  Stored procs also have some limitations in your
> example
> > because many (ALL??) databases do not support variable argument lists
> > for stored procs.  You might have to resort to some kind of delimited
> > string to pass in indeterminate arguments, and then parse the string
> in
> > the proc.  The benefits of writing the proc this way would be that the
> > SQL could be prepared when the proc is created and the perfomance
> could
> > improve substantially - most of the reason to write a proc in the
> first
> > place.
> >
> > So this kind of comparison MIGHT show:
> >
> > - iBatis, using dynamic SQL, has a more understandable syntax
> > - the stored proc is more complex to code, and somewhat more difficult
> > to call (because of the delimited string)
> > - the stored proc has better performance and is more secure
> >
> > This would have to be a "your milage may vary" type of thing.
> >
> > If you're interested in the second kind of comparison, I could mock up
> > a DB2 example for you.  Probably not until tomorrow.
> >
> > Jeff Butler
> >
> > >>> brandon.goodin@gmail.com 5/2/2005 4:55:50 AM >>>
> > Hey all,
> >
> > I am putting together a comparison of using iBatis dynamic SQL versus
> > dynamic SQL in a stored procedure. I want to solicit some assistance
> > on this. If you are willing i would like for you to write a stored
> > procedure that can accomplish the following requirements.
> >
> > The stored procedure must use the following SQL statement:
> >
> > SELECT
> > categoryId,
> > title,
> > description,
> > sequence
> > FROM Category
> > WHERE
> > categoryId IN (?,?,?,?,...) AND
> > name LIKE ('?%')
> >
> > - The 'categoryId IN' should be completely omitted/ignored if no
> > values are passed in for it and the 'AND' should be removed.
> > - The 'categoryId IN' statement should be able to accommodate  a
> > dynamic number of ids.
> > - The 'name LIKE' statement should be ignored if no LIKE value is
> > passed in and the 'AND' should be removed..
> > - The value of the 'name LIKE' should be an alpha character that has
> > the '%' wildcard appended to it inside the stored procedure.
> >
> > Please provide straight JDBC code that calls the stored procedure.
> >
> > I wanted to get a few examples from various databases if possible.
> > But, one will do as much as several. Whoever delivers the best usable
> > example will receive credit for it in the an upcoming iBatis book.
> >
> > I hope a few of you are up for the challenge!
> > Brandon
> >
> > P.S. I need this today :)
> >
> 
>

Mime
View raw message