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
>>> [EMAIL PROTECTED] 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 <[EMAIL PROTECTED]> 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
>
> >>> [EMAIL PROTECTED] 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 :)
>
