Retrieving metadata of the result of a TQuery

In our application a user can enter SQL code to retrieve data from a NexusDB database. We want to determine the metadata of the result of such a query without actually executing the query. We use the metadata to i.e. determine the number of fields and the datatype of each resulting field.

[addition] We use this functionality as part of a user-defined conversion process. The actual conversion is done later on in a batch-like process, but we need the meta data beforehand, so the user can specify in the conversion additional field characteristics or modifications like applying plugins, creating paren开发者_开发问答t-child relations, etc. [/addition]

Until now we do this by basically adding WHERE FALSE to the SQL query or replacing all already present WHERE statements by WHERE FALSE AND. But of course we have to parse the whole SQL to determine the exact location of the correct where clause to adjust, taking comments, nested SELECTS, JOINs and other clauses into account. Its getting quite complicated this way :-(

And another downside of this is, that most of the time the execution of the query can still take a long time, even though we know beforehand that the resultset will be empty.

We were wondering if there is another way of achieving this.

i.e. The TQuery object must have a parser of its own, splitting the SQL statement in its different clauses. If we could change the where clause just before execution, we wouldn't have to do the parsing ourselves. But we are a bit anxious of diving into the internals of the TQuery object, just to find out there is no way to use it the way we want to.

Anyone have any advise on this?

---

A variant of what you already do is to put your original SQL statment in a nested select instead of modifying the where clause. If select * from MyTable is your complicated query you can embed it like this to get no result.

select *
from
(
  select *
  from MyTable
) as xx
where 0=1

I have only tested this in SQL Server, not in NexusDB

---

Diving into the client-side TnxQuery component wouldn't help. It is only a wrapper around a statement and cursor handle. The only way to retrieve the metadata you are after on the client side is from the cursor handle. A cursor handle is only being produced when you execute the query.

For now the only way to minimize the work that is being performed when executing the query is what you are already doing.

If you file a feature request in our issue tracker ( http://www.nexusdb.com/mantis/view_all_bug_page.php ) we can research the possibility of specifying some flag to tell the engine to "never process any data" without the need of messing around with the query itself.

---

You can also try to link a TClientDataset with a TDatasetProvider to the original TQuery (Or any TDataset descendant). Then set the PacketRecords property of the TClientDataset to 0, and open it. It will retrieve the fields in the TClientDataSet, without any data.