I haven't run your code since I don't have Oracle running locally, but it looks like LabKey is attempting to read result set meta data after the underlying statement is closed; apparently the other four databases we support allow this. This comes up because LabKey caches result sets in many cases to free up JDBC connections. The solution is likely invasive… we probably need to cache the meta data as well in CachedResultSetImpl for Oracle.

As for the other things you mentioned in your message and code:

• You should probably try to get / parse the actual product version instead of using the driver version. JDBC drivers are typically backward (and often forward) compatible with different server versions; the dialect is most concerned with actual server version, since server version (not driver version) determines SQL syntax, capabilities supported, reserved words, etc.
• I believe the database name is used only for logging and error reporting… if a LabKey Server is connecting to many databases it's helpful to point admins to the problematic one. But not a critical thing right now.
• Oracle doesn't support LIMIT or OFFSET? That's terrible...

haha, yeah. been getting there slowly!

"The solution is likely invasive… we probably need to cache the meta data as well in CachedResultSetImpl for Oracle."

Oh right. Sounds a bit over my head. See if I can figure out what's going on.

"no LIMIT or OFFSET"

Yeah, I didn't even know such clauses existed until I used LabKey.

http://stackoverflow.com/questions/241622/paging-with-oracle

It might be possible to use this technique in your dialect limitRows implementation.

Adam

So aside from this needing more testing and addressing the above mentioned issues, what's the best way forward? It's probably impractical to be building this from source (i.e. adding the changes needed), in a prod scenario. I was reading the doco about finding a comitter and submitting a patch to them or something.I suppose other things to think about are version support. i.e. Im only having tested this with 11gR2, so I guess it would be best to limit to that release to start with.... (that is assuming this change would be wanted to be added into labkey)

I'll email you separately to work through the details.

Adam

I'm not certain on other databases implementation, but generally I use a NUMBER field on Oracle which is being generated in labkey as a Float. (which makes sense, as a NUMBER field can be a float in oracle - but I know this particular field will never be a float)..

Im thinking that the answer is in the metadata? But am having trouble getting it to work.

I have seen the xml doco: https://www.labkey.org/download/schema-docs/xml-schemas/schemas/tableInfo_xsd/elements/datatype.html

which says: The name of the SQL datatype of this column as would be specified in a CREATE TABLE statement.

but when I try specifying a different datatype it doesn't seem to make any difference. Possibly my syntax is way off?

<ns:tables xmlns:ns="http://labkey.org/data/xml">  <ns:table tableName="specimen" tableDbType="NOT_IN_DB">    <ns:columns>      <ns:column columnName="SPECIMEN_ID">        <ns:datatype>INT</ns:datatype>        <ns:description>dfgdgfgdfgdfgd</ns:description>      </ns:column>    </ns:columns>  </ns:table> </ns:tables>

see ColumnInfo.loadFromXml() and ColumnInfo.createFromDatabaseMetaData(). Also note the "false" parameter in the call to ColumnInfo.loadFromXml() from SchemaTableInfo.loadFromXml() at line 620.

If the field can never be a float, isn't there a way to set the type in the CREATE TABLE statement to enforce that, so that LabKey will know it is really an INT column?

Oh, right. No worries.

If the field can never be a float, isn't there a way to set the type in the CREATE TABLE statement to enforce that, so that LabKey will know it is really an INT column?

Well, the field can be float, in that it's theoretically possible. I'm saying that it would never be the case. In previous roles, I have always specified number columns as 'number' (data type) - whether they be floats or int's. I imagine its a common practice. I don't think its exactly a possibility for me to go changing the datatypes of tables.

OK. I think I saw somewhere that it loads in tables and views. So im going to try creating a view, casting number fields to int's and see if that solves the problem. (just gotta wait for that priv before i can test)

create or replace view
v_specimens
as
select cast(specimen_id as INTEGER) specimen_id, date_Drawn, participant_id, vial_type, quantity, measurement, visit_no, location, total_count, available_count
from specimen

desc v_specimens
Name Null Type

SPECIMEN_ID NUMBER(38)
DATE_DRAWN DATE
PARTICIPANT_ID NUMBER
VIAL_TYPE CHAR(5)
QUANTITY NUMBER
MEASUREMENT CHAR(2)
VISIT_NO NUMBER
LOCATION VARCHAR2(4000)
TOTAL_COUNT NUMBER
AVAILABLE_COUNT NUMBER

Specifying the database as Integer doesn't change anything. As the rdbms still just has the data type as NUMBER, with the precision,scale specified - so more of a convenience then specifying precisions/scale yourself.

SO, still comes through as Number (Double) in LabKey.

(off-topic: would be good to have a no format [like pre or code html tags] formatting option)

You can put a breakpoint there to see what the metadata is reporting.

In that function, i was attempting something like:

ResultSetMetaData md = rsCols.getMetaData(); md.getPrecision(1); md.getScale(1);

but it just returns 0 all the time =/

hmm.. im just thinking now, this may be because this function - rsCols is the column information and not the actual data. Still you (or I would) think it would work it out.

beyond that, my idea was then do:

if(dbmd.getDatabaseProductName().equalsIgnoreCase("Oracle")) {                //todo: doubles (re assign to INTEGER if no decimals.)            }

I figured out how to add function support into the datasource (from the Method class). SO I added in support for some basic string, numeric, and date function that I find most useful/common.

One of which is the to_char function. So, create a new query from the data source, adding a new field:

to_char(participant_id) participant_id_hidden

that returns the value without the '.0' at the end of it. Customize the view of this query to not display the new field and save as the default. In the metadata for the original field (participant_id), specify a format mask of # and in the URL (this also remove the '.0' from the display), and so that I can link to the participant view from this query (since the original format with .0 results in a no data found), have the URL like:

/labkey/study/externalData/test1/participant.view?participantId=${participant_id_hidden}