resultset headers

[tvrulesme]

I have a query that could potentially return millions of results.

Is it possible to retrieve metadata without running the query which could take minutes to complete.

I am particularly interested in getting the following info:

rs.getMetaData().getColumnType(0);
rs.getMetaData().getColumnLabel(0);

Would also be very useful to get a count of how many results would be returned from running the query but am guessing that the query would need to be run to do that.

Currently doing something like this:

exdb.prepareStatement(sql);
Statement dbs2 = exdb.createStatement();

ResultSet rs2 = dbs2.executeQuery(sql);

String headers = "";

while (rs2.next()) {
int columns = rs2.getMetaData().getColumnCount();

for (int i = 1; i < columns + 1; i++) {
headers += rs2.getMetaData().getColumnLabel(i);
headers += ",";
rs2.getMetaData().getColumnType(i);
}
break;

}

[Tolls]

As you say, you can't get a count of results without the query actualy executing. The only difference a "select count(*)..." makes compared to "select <list of columns>..." is the data returned.

As for the meta data, I take it that sql could be anything, and is not something you control?

[tvrulesme]

Thanks Tolls,

the count is a minor requirement. For the metadata, you are correct. i have no control over the query.

I was thinking of creating a substring, for example replace "select * from...." with "select top 1 * from...." but it doesn't seem like a very safe way to me.

[Tolls]

Why do you need the meta data without the actual results?

[tvrulesme]

Within the tool I am developing you are able to reconcile two databases against each other. You have the ability to exclude certain columns from the reconciliation, apply certain tolerances and perform mapping of one column against another.

In order to do this it's important for the user to see which columns are within the report and what the data type of those columns is before running a report.

[Tolls]

OK.
Is a report simply a query then?
Or do you have access to the columns and tables that form the report?
If so then you can use the DatabaseMetaData possibly, from the Connection.
There's a whole slew of methods on there for getting at columns and tables.

[tvrulesme]

Thanks Tolls,

The problem with using DatabaseMetaData is that the query could potentially e a join or union from many tables.

What I need to know is given an sql query, for each column returned what is the type of data and what is the column label.

There are no methods (as far as I can see) within DatabaseMetaData which take an sql query as an argument, so while the info returned is useful, it would still require control over the sql going in.

[Tolls]

Yep, complex queries would be a pain, and it would involve you decoding the sql to find the actual fields involved.

I do wonder if there's some library out there that would do the work for you? Maybe something in Apache? Then again, how would it handle subqueries and calculated fields in a subquery, or in the query itself?

The more I think about it the worse it gets. Our reporting here is done on a field selection basis, rather than raw SQL. If you need to do the sort of work you're suggesting I would argue that that is the route you (possibly) need to go. Otherwise you will need to either run the query, or write a SQL interpreter of some description.