0

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 parent-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?

Bascy
  • 2,017
  • 1
  • 21
  • 46
  • 3
    You're trying to do something very atypical: Get the list of returned fields without getting the actual fields. The only thing that might help is the `Prepare` method, but in my tests it doesn't set up `Fields`. The fact you're using BDE and going through a minimum of two indirections before getting to the database can't help. I'd try to simply work around the problem but not trying to get the list of fields before hand. Parsing the SQL can be a daunting task, especially when faced with complex queries involving unions and joins; – Cosmin Prund Mar 14 '11 at 09:41
  • About delving into the depths of `TQuery`: Don't bother, the answer's not there. Only the Database knows the answer, TQuery is just an intermediary. Think about this query: `SELECT * FROM MYTABLE`. It's obvious TQuery can't know what's in `MYTABLE`, only the Database knows! – Cosmin Prund Mar 14 '11 at 10:01
  • @Cosmin See additions in original question, we're not using BDE by the way. – Bascy Mar 14 '11 at 12:16

3 Answers3

3

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

Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • I just tried that query with Firebird 2.5. It works (as in: It returns 0 records) but looking at the query plan analyzer it did read everything from "MyTable" - in essence processing the whole query. Does SQL Server have a similar facility? Are you sure it isn't running the full query and then simply not returning anything? – Cosmin Prund Mar 14 '11 at 11:11
  • @Cosmin SQL Server does not run the full query. I have tested with timing the query and by looking at the query plan. – Mikael Eriksson Mar 14 '11 at 11:24
  • This will work, but if we're dealing with a nested select statement or a union of selects it will only disregard the data in the last moment (a least in NexusDB). We now replace ALL occurrences of where by where false and this will never produce any data (only time to come to this conclusion) – Bascy Mar 14 '11 at 13:20
1

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.

Thorsten Engler
  • 2,333
  • 12
  • 13
  • @Thorsten Engler: I gather from your respons that you are from NexusDB. We have posted a request earlier to add such a thing, and your (nexsudb) answer was: "use WHERE FALSE". If such a request would have more chance of actually getting researched, i'm more than happy to post it again – Bascy Mar 14 '11 at 12:11
  • Yes I am. Did you post it to he issue tracker? what's the Issue #? – Thorsten Engler Mar 14 '11 at 12:15
  • @Thorsten Engler: My collegue Hans Hasenack posted it 5 years ago (sep 12, 2006) in newsgroup nexusdb.public.support.sql, thread title "Returning an empty dataset", back in the days of NexusDB V1 or V2. He suggested something in the line of "SELECT NONE" – Bascy Mar 14 '11 at 12:24
  • Thanks, now that it is in the issue tracker, it wont get lost again. – Thorsten Engler Mar 14 '11 at 13:08
  • Marked as Accepted answer ... assuming the feature request will result into something usefull – Bascy Mar 14 '11 at 13:35
0

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.

Toto
  • 1,360
  • 1
  • 16
  • 18