Results 1 to 8 of 8
  1. #1
    tvrulesme is offline Member
    Join Date
    Nov 2010
    Posts
    4
    Rep Power
    0

    Default resultset headers

    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;

    }

  2. #2
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,097
    Rep Power
    20

    Default

    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?

  3. #3
    tvrulesme is offline Member
    Join Date
    Nov 2010
    Posts
    4
    Rep Power
    0

    Default

    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.

  4. #4
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,097
    Rep Power
    20

    Default

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

  5. #5
    tvrulesme is offline Member
    Join Date
    Nov 2010
    Posts
    4
    Rep Power
    0

    Default

    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.

  6. #6
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,097
    Rep Power
    20

    Default

    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.

  7. #7
    tvrulesme is offline Member
    Join Date
    Nov 2010
    Posts
    4
    Rep Power
    0

    Default

    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.

  8. #8
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,097
    Rep Power
    20

    Default

    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.

Similar Threads

  1. Linked list with Headers & Trailers
    By Debonairj in forum New To Java
    Replies: 2
    Last Post: 08-05-2010, 02:59 AM
  2. How to span column headers over several columns
    By thayalan in forum AWT / Swing
    Replies: 0
    Last Post: 06-02-2009, 04:33 PM
  3. Scrolling and Headers in a scroll pane
    By glhansen in forum AWT / Swing
    Replies: 10
    Last Post: 04-03-2009, 02:06 AM
  4. HttpClient invalid headers
    By Nicole in forum Advanced Java
    Replies: 6
    Last Post: 03-31-2009, 12:36 AM
  5. tooltips for JTable column headers
    By fossildoc in forum AWT / Swing
    Replies: 2
    Last Post: 12-18-2008, 11:42 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •