SELECT COUNT (DISTINCT *) may not be legal, but in t-sql at least, SELECT
DISTINCT COUNT(*) is perfectly legal (I'm not sure if it's standard SQL-92
or not).
So you could first check for SELECT DISTINCT and if that exists, replace it
with SELECT DISTINCT COUNT(*) [as whatever]
Mike.
-----Original Message-----
From: David Shadovitz [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 03, 2001 9:35 PM
To: CF-Talk
Subject: Limit Query Results
I'm looking for suggestions:
My intranet app lets users construct a SQL query. I want to guard against
queries which would swamp the system by returning too much data. My plan is
to
cap the number of rows that a query can return. Sure, a row may contain
many
fields or few, but it's probably a good enough measure of a query's size.
Here's the methodology:
* Make a copy of the user's SQL statement
* In this copy, replace the SELECT clause with SELECT COUNT(*) AS NumRows
* Execute this copy
* If NumRows exceeds some threshold, tell the user that he must refine his
query. Otherwise, go ahead and execute it.
One problem I see is the DISTINCT keyword. If the user's query is SELECT
DISTINCT ..., I don't know how to get a count of the number of rows. SELECT
COUNT(DISTINCT *) AS NumRows is not valid syntax. Perhaps I'll just let
DISTINCT queries go without a limit.
Thanks.
-David
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
