At 01:20 AM 10/05/2003 -0700, you wrote:

> > >What would I set the SQL to?
> > >Would it be "execute procedure MyProc ('12345", 234, 'Hello')"
> > >Or, would it be "select retval1,retval2,retval3 from MyProc where MyParam1
> > >= '12345' and MyParam2 = 234 and MyParam3 = 'Hello'"
> > >or something else?
> >
> >If the purpose of the procedure is to return a dataset (even a one-row one)
> >for work on the client, or as a provider of parameters, then you need the
> >SELECT syntax. If the only purpose is to execute some DML on the server,
> >then use the EXECUTE syntax. The reason is that, if you are going to have
> >the user do updating, you want to keep the transaction open. Although you
> >can, *don't* mix and match, or you will be exposing users to performing
> >manipulation on "dirty" (uncommitted) data.
>
>The MAIN puropse of my SP is to execute some DML, but it does return a
>single value (one row, one column) that gives me the result of the
>operation. I'm not using "suspend" in my SP. In this case, would it be OK
>to use the EXECUTE syntax? It just seems cleaner/simpler to me.

Sure, if you aren't going to display the return values in a control.

> > >Then, do I call Open() or Execute()? Which is correct in this case?
> >
> >Call Open on an IB_Query. Call First on an IB_Cursor. Call Execute on an
> >executable (that has the EXECUTE PROCEDURE(...) syntax).
>
>What about an IB_Cursor using the EXECUTE PROCEDURE syntax?

Same.

> > >So, in my case above, I would set the SQL parameter to the correct
> > >statement, prepare, and execute/select.
> >
> >Prepare once, then execute/select iteratively. Just test "if not Prepared
> >then Prepare;" Take note that the Params[] array (a property) is not
> >available in an unprepared dataset. ParamByName() and Row.ByName() are
> >methods, which include this test, so you won't need it if you use either of
> >them.
>
>If I'm just calling the SP once, and putting all literals in the SQL, I
>don't have to worry about the test, or explicitly preparing,
>right? Setting the SQL property guarantees that the dataset is
>unprepared. Calling Open(), First(), or Execute() will automatically
>prepare it. Is this right?

Usually. The dataset methods should usually do so. Calling Execute might
not trigger the test so it's safer to make sure of it yourself and avoid an
exception.

>Even when I'm doing it in a loop with parameters, I don't see why I need
>the test either. If I set the SQL, I KNOW that I need to call Prepare()
>once (before the loop). Then, inside the loop, I KNOW that it's been
>prepared already, and it wouldn't get unprepared, right?

It's less elegant than having the one set of steps for every iteration of
the loop. It makes it less capable of being generic, too. What happens if
you rattle off the process, have it complete...then the user goes and does
something else and comes back and submits the same process again? If you
call Prepare explicitly it will be unecessary on the second and subsequent
instances of the task.

Still, we might be at cross-purposes here. If *every* use of the object
requires completely new, literal SQL, regardless, then the difference gets
down to a matter of style.

If this is your design approach and you potentially have "stub" SQL, don't
overlook the OnPrepareSQL event and SQLWhereItems. But it sounds as if you
might be talking about recycling the same data object for calling different
SPs. As I indicated before, it all depends on which particular requirement
you need to implement. Mostly, I want to discourage you from getting into
the mindset that there is only one "best way" to manage all SPs in your
app. Consider them all and work with any and all of them, case by case, to
get the best possible performance value.

Gotta stop now.

cheers,
Helen