Tutorial :ANSI Sql query to force return 0 records



Question:

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.


Solution: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.


Solution: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. :-)


Solution:3

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

SET FMTONLY ON SELECT * FROM tablename SET FMTONLY OFF  


Solution:4

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


Solution: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).


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »