Access to Oracle query metadata such as column names

[ericwaldheim]

Hello,
I'd like access to the executeBaton->columnNames.
Any chance this gets into the API? Perhaps request-able via the execute options argument?
Or does generic Statement Handle Attribute access make more sense than just columnNames access?
Thanks,
Eric

[cjbj]

Would you ever want to do a "describe" to get table meta data without fetching rows?

PS in the short term look at the outFormat option OBJECT: that will give you column names.

[ericwaldheim]

We do get table meta data without fetching rows but not in this case.
The case I'm solving for is existing code where the (multi-table) query result dictates not only the data in a grid, but the column names for the grid as well. Without access to query columnNames, we will have to rewrite some code. (The code was migrated from a perl server where DBD::Oracle supports getting columnNames from a query.)

[ericwaldheim]

Thanks for the suggestion but if I understand the API doc correctly, it is does not solve our particular problem. My understanding is that outFormat OBJECT will give me column names as the keys to each returned row (object) in the result (array). I need two things that this does not provide: 1) column names in repeatable order 2) column names even when I get 0 rows returned.
Thanks again.

[cjbj]

@ericwaldheim We have metadata on the todo list. Thanks for the detail - it always helps to know exact use cases.

[ericwaldheim]

Excellent. Thank you!

[jeffm13]

+1 on this request. Actually, I'd like it to be read/write. In our API plug-ins, we transform result sets into Node objects, transforming column names into Node style camel case property names at the same time. I'd like to be able to (in priority order) 1. instruct the driver to translate column names to NodeJS conventions for me on an outFormat OBJECT, or 2. somehow set the property names that the driver will use on outFormat OBJECT results, 3) get access to the metadata for the query when executing or prior to executing so that I can do this efficiently.

[cjbj]

@jeffm13 can you expand a bit more on how you would use "write" properties?

We are likely to do your number 3, keeping the API simple but flexible.

[jeffm13]

The current driver design gives me a choice between a bloated array of values with redundant copies of column names (outFormat OBJECT), and a blind array of values without column names (outFormat ARRAY). I think there's a better way to provide the best of both worlds.

In my mind, the driver add-on should concentrate only on database interaction. The result object and its properties provide a view into that interaction: when the outFormat.ARRAY execute option is used, the rows property provides a partial view into that interaction (column names are missing). But the outFormat OBJECT execute option causes the driver to do more than just interact with the database. It adds decoration of the query results. So outFormat OBJECT provides a complete view, but in an inefficient way.

To provide the best of both worlds, simply add a result.columnNames property to the results object that contains the array of columnNames. You could also reimplement outFormat.OBJECT as a function on the result (called something like result.rowObjects) rather than as an alternate representation of the query results. That function would simply merge the result.rows array with the result.columnNames array to produce an array of objects. Then again, you could simply leave formatting of the results to the caller and skip the outFormat option altogether, which I believe would fit better with the spirit of simplicity that is one of the primary advantages of Node.

There are a number of benefits to this approach. First, it simplifies the execute function: there would be no need for the outFormat option. Second, it would allow the caller to modify the columnNames array prior to calling the result.rowObjects function (if you choose to reimplement outFormat.OBJECT). Third, it would provide a limited amount of metadata (column names) without requiring a separate call into the driver (as would be needed with Option 3).

This could be quickly implemented with a non-breaking change by simply adding the columnNames attribute to the results object.

[cjbj]

@jeffm13 we were thinking about making the metadata (names, types, sizes etc) available as an optional part of the execute() callback 'result' parameter, so there wouldn't need to be a separate call to get the values.

Do you think the current outFormat.OBJECT output format might be useful for someone, somewhere?

[jeffm13]

@cjbj, providing access to metada on the result would satisfy my use case. In terms of outFormat.OBJECT, I'm sure someone may find it useful, but I still believe it is unnecessary. When both the array of rows and an array of metadata are available in the response, it's a trivial exercise to massage them and produce an array of objects. Maybe something like this:

var _ = require('lodash');
...
// only necessary in my use case
result.metadata.columnNames = _.map(result.metadata.columnNames, _.camelCase);

// this does the equivalent of outputFormat.OBJECT
var rowObjects = _.map(result.rows, function(results, row) {
    return _.zipObject(result.metadata.columnNames, row);
});
...

[cjbj]

@jeffm13 are there any other features you'd like us to remove? :)

[cjbj]

@jeffm13 if you have comments on DATE handling (#20) or thoughts about how a result set streaming interface would work (via events?), let us know.

[jeffm13]

@cjbj, only those I don't need. Although that might impact marketing messages :) As far as date handling goes, I think you made a reasonable implementation choice. I'll respond in a bit on a streaming interface. I'm concentrating mainly on RESTful API implementation, so generally, I haven't needed to frequently deal with large data sets. But I'm starting to get into large-ish data sets now.