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 :)
