ANSI Sql query to force return 0 records

Question

Hi, I'm looking for an ANSI-SQL method to do a Select query without returning any record, but fill a TDataSet's Fields structure.

The method I found is by adding a "where 1=0" in any query, for example:

Select Id, name, province
from customers
where 1=0

This is a fairly trivial example, it turns a little more complicated when I have to work with queries entered by the user, then parse them, remove the where clause if it already has one, and replace by "1=0".

If the last clause in the user-entered query is the where clause, then there's no problem at all, but what about more complicated queries like this:

select
  c.lastname,
  sum(cs.amount)
from customersales cs
join customers c on c.idcustomer=cs.idcustomer
/* where 1=0 */
group by c.idcustomer, c.lastname

By using the "where 1=0" method, the only way to insert it in the previous example is by having a rather powerful SQL parser (remember the user can enter complex queries, including Subqueries, and all that), who can understand where to include this string.

Does anyone knows a better way to do this? I cannot use "limit 1" because it must be in an ANSI way.

Answer 1

What about adding your own SELECT around the user's SELECT?

SELECT * FROM (
select
  c.lastname,
  sum(cs.amount)
from customersales cs
join customers c on c.idcustomer=cs.idcustomer
/* where 1=0 */
group by c.idcustomer, c.lastname
) x
WHERE 0=1

EDIT: ORDER BY would not work with that solution, but since you get no rows, you could try to remove that from the query when necessary.

Answer 2

For future reference in case people end up here with a different goal: Note that making the WHERE-clause a contradiction can cause the optimizer to decide to not execute the sub-plan at all. So if you need some side-effects of the query (be it warm a cache, execute a procedure, whatever), be advised. :-)

Answer 3

if your using MSSQL Server, then you can wrap your query around SET FMTONLY

SET FMTONLY ON SELECT * FROM tablename SET FMTONLY OFF

Answer 4

In Firebird you may 'prepare' the statement instead of 'execute' it. Preparing simply parses the statement and returns the field list.

Answer 5

Or use

CustomerSQL='SELECT <Fields> FROM <Table>';
MySQL=Replace(CustomerSQL,'SELECT ','SELECT TOP 0 ');

(perhaps with some sanity checking, but you get the idea - a SELECT TOP 0 will return only the meta data containing the record layout and no record data).