Second, books understandably have to limit themselves to what will sell the
book, so you're not going to find much about SQL *anything* outside of the
"advanced computing" section.

Third, both parties have valid points.  Both are right.  A stored procedure is
definitely a powerful tool whenever you can use them because (a) they are
centralized, stored on the server; and (b) to the extent that it is known that
all clients are using them, the nature and volume of the request load on the
server can be accurately predicted.  When queries come in over the transom,
the server can only do the best that it can.

Data-aware controls are frankly of limited usefulness in a true c/s situation
because the client needs to be very mindful of what he/she is requesting of
the server, and DAC's tend to request too much too often.

There ain't no such thing as a standard SQL... do the best ya can.  :-/

One technique that often works is to write a mock-up using queries and then
see how many of those can be moved to the server-side as a stored proc.

Be careful, from a personnel perspective, that both sides of the fence are
educating and working in tandem with one another at all times.  Both sides
have a lot to teach one another and both are working toward a common goal
using the best technological options available that seem to lend themselves to
that purpose.

/mr/

To all c/s developers,

This is a general question, posted to all with c/s development
experience.  It is somewhat related to Delphi, as the app we're
developing is in Delphi 2.  However, it has more to do with (generally)
arguments for/against stored procedures vs. queries initiated by the
client-side, and specifically in Delphi what is a better approach.

At our organization there seem to be two camps:  Those in favor of
stored procedures, and those not in favor of stored procedures.  It's
almost like a binary decision - there is no middle ground.  The two
camps simply cannot agree to use one in favor of the other.  It seems
like those from a "desktop" database background (Paradox, Access, dBase)
prefer to use client-generated queries (TTables & TQueries), while those
with enterprise-level backgrounds (Unix/Informix/Oracle/etc) recommend
stored procedures.  

The back-end database that we're using (currently) is SQL Anywhere, but
we will potentially up-size to Sybase, Oracle or Informix as our needs
grow, potentially (most likely) on a Unix server.

Stored Procedures Camp Says:
----------------------------
1. Access to databases is centrally controlled.
2. Stored procedures can handle their own transaction blocking, broken
down into "units of work".
3. Developers don't need to know about underlying table structure, as it
is hidden through the stored procedure mechanism.  One or two "dba"s can
worry about the structure, and write procedures as necessary.  Since we
have over 30 tables, each "simple" query typically consists of at least
three to four table joins.
4. Since the stored procedure is sent to the server, it is compiled &
optimized and ready to run on any "hit", unlike client-generated
queries.
5. Stored procedures are server independent - they are written in
ANSI-std SQL, so when we switch servers there is little to no coding
rework, we just load the stored procedures into the new database.

Client-Generated Queries (TQuery, TTable) Camp Says:
----------------------------------------------------
1. Developers write their own queries & tables without having to wait
for a "dba" to write the stored procedure for them.
2. With TQuery/TTable you can use the Delphi data aware controls to both
read and write via a datasource to the database.  With stored
procedures, if you get a result set, you can use data aware controls
only to read, but not to write back to a database (at least we've not
been able to figure out an easy way to do this).
3. None of the Delphi books out there talk about stored procedures in
any significant detail, compared to TQuery & TTable, so there must not
be many people out there using them!!
4. Queries used in TQuery are server independent.  The SQL code is ANSI
standard, so there is little to no coding rework when switching to a new
server. Note that this is the same argument that stored procedure folks
mention above.

***********************************************
* What is your opinion of the two mechanisms? *
***********************************************

Thanks,

- Jon

In article <326FA7BE.5...@carecentric.com>, Jon Czernel
<jon...@carecentric.com> writes

We take a pragmatic approach to this. You can make an analogy with a
traditional IT environment, where some tasks are 'obviously' on-line
(taking down a customer order over the phone) and some are 'obviously'
batch (generating several thousand invoices overnight).

We start off developing most apps with a 'fat client'. However, if a lot
of data is being transferred to a PC for some calculations, and the
results are being uploaded over the network back to the server ... it
makes more sense to do the whole job as a stored procedure on the
server. It also keeps an awful lot of traffic off your network (a factor
omitted from your list) - doing joins on a number of tables can lead to
a massive data explosion (e.g. where a code value is used to lookup a
large description and the whole lot is returned to the client).

I'd also recommend moving to your eventual target database as soon as
possible. All SQL servers are identical until you try and migrate. Even
if the server understands the same language, you may find that Delphi
will do things ok on one and not on another (stored procedures are a
very good example - especially those that return values).

Welcome to the world of client server.

John

+-------------------+--------------------------------------------------+
John P.McCreesh     | Net:   jp...@pines.demon.co.uk
Forbo-Lancaster Ltd.| X.400: c=GB,a=CWMAIL,p=FORBO,s=McCreesh
Lancaster, England  | Voice: +44 (0)1524 65222 Fax: +44 (0)1524 61638

Thanks for the comments, I agree that it does seem to be the case that
both sides of the fence will converge to some happy medium, weighing the
pros/cons in each case.

- Jon

In article F...@carecentric.com, Jon Czernel <jon...@carecentric.com> () writes:

It sounds like your DBA's want total control, which might be ok, if they
agree to take on this extra work.  This might impact the productivity of
the programmers. ;-(

If you use stored procedures, or even if you don't, programmers definately
NEED to be aware of the database issues involved.  Locking, index usage, etc
will all have an impact on performance, and if the programmers write lots
of silly SQL calls, it is going to clobber the database and network, no
matter how clever the DBA's are.

Now, back to writing stored procedures, as a programmer.  Maybe one of your
DBA's will do this for me? ;->

Good luck,
Mark McNulty
JYACC Consultant
mmc...@jyacc.com

Note- these views are my own and not those of JP Morgan or JYACC.

A couple of other thoughts.  I find it helps considerably with Paradox if you
store query text (QBE or SQL) in a -table- and have your application designed
to load the text, supply parameters (e.g. InfoPower), and run them.  The
queries are -not- imbedded in the app, and the table where they -are- imbedded
has a query-by-query *description* in a Memo field!

This will, at least, externalize and document the queries that your program
uses and where and how it uses them.  In fact, you can build on the idea such
that, if someday the query is replaced by a stored-proc, you can put *that*
info into the table as well and your code is that-much more generic.

Just an idea to be developed and built upon.

/mr/