[thelist] SQL Update CORRECTION

Ken Schaefer ken.schaefer at gmail.com
Tue Jul 13 03:36:51 CDT 2004


Hi David,

I'm not entirely sure I agree with your methodology:
a) Update SQL strings are much more difficult to secure than SQL
Server stored procedures. You have any number of issues with respect
to SQL Injection attacks. Stored procedures (sprocs) do not suffer
from injection attacks unless you dynamically build SQL inside your
sproc. Inline SQL statements are also slower than running sprocs in
most cases.

b) extracting schema information from an ADO Recordset object is an
expensive operation - by doing this, I don't think you're saving
anything if you're just looking at the difference between updating all
fields -vs- updating a subset of fields + extracting schema
information

c) I'm not exactly sure how you're lessened the server load - this is
the bit that is confusing me. I see that you're performing quite a few
more operations, each of which consumes more resources.

Cheers
Ken


On Tue, 6 Jul 2004 11:47:55 +0100, David Landy <davidlandy at clara.co.uk> wrote:
> > Writing lots of sprocs to cater for lots of different possible updates
> does
> > not sound like a good way to spend your time. The alternative is that you
> > somehow get the original values (either via another extraction from the
> > database, or by persiting the original value someonwhere), and compare
> those
> > to the new values, and update the record with the values that have
> changed.
> > Again, for a simple app, this seems more work than necessary.
> 
> Spot on, Ken.
> 
> In a recent app I adopted a strategy of keeping the original value in a
> hidden form field, then in the "gather" proc comparing each value to see if
> it's changed, and building an sql UPDATE string accordingly.
> 
> You can either hand-code these "gather" procs for each page using field
> names explicitly, which could be time-consuming (or better yet write your
> own piece of code to generate the aspx code for each table) or you could
> simply loop through the field names of the table using whatever meta-data is
> available for the table (in classic ASP you can get the field names from a
> recordset).
> 
> The advantage of this approach is that you reduce server load slighly, and
> it's more elegant to update only the data that's changed.
> 
> The biggest plus from my point of view is the audit trail: if you keep a log
> of all query strings run against your database, it's easy to see who changed
> what and when.
> 
> David.
> 
> 
> 
> ----- Original Message -----
> From: Ken Schaefer <ken at adOpenStatic.com>
> To: <thelist at lists.evolt.org>
> Sent: 05 July 2004 02:22
> Subject: Re: [thelist] SQL Update CORRECTION
> 
> > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> > From: "Jason Robbins" <evolt at whisky-fudge.org.uk>
> > Subject: Re: [thelist] SQL Update CORRECTION
> >
> >
> > : Ken Moore wrote:
> > : >> There is one exception, that is if you have two functions (for
> > : >> example, "scatter_tablename" and "gather_tablename") where one line
> > : >> loads all fields into variables and the other saves all variables
> into
> > : >> the table. Even then, in your calling procs, only update the data
> that
> > : >> has changed.
> > : The only trouble with that is it means writing ever different
> > : possibility as a stored procedure - unless someone knows a trick to
> > : solve this problem?
> > :
> > : Jas
> > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> >
> > What is the scale of this application? If it's just a two-tier
> application,
> > I would write just the one sproc, and update all the fields with the new
> > values (changed or not).
> >
> >
> > Obviously YYMV and you'll need to make a call based on your application's
> > requirements.
> >
> > Cheers
> > Ken
> >
> > --
> > * * Please support the community that supports you.  * *
> > http://evolt.org/help_support_evolt/
> >
> > For unsubscribe and other options, including the Tip Harvester
> > and archives of thelist go to: http://lists.evolt.org
> > Workers of the Web, evolt !
> >
> >
> 
> --
> * * Please support the community that supports you.  * *
> http://evolt.org/help_support_evolt/
> 
> For unsubscribe and other options, including the Tip Harvester
> and archives of thelist go to: http://lists.evolt.org
> Workers of the Web, evolt !
>


More information about the thelist mailing list