How do I access hidden columns from ActiveRecord (in informix)

Question

Many databases have a hidden primary key column. MySQL implements this as _rowid. In MySQL's case it is really a pointer to a previously defined primary key column. However in other databases (in my case, Informix), this column is independent of a deliberately defined primary key. The database which I'm coding for was designed mostly under the practice of depending on the Informix hidden column, rowid instead of defining a primary key. I'm connecting to Informix from JRuby on Rails using the Informix JDBC driver. All is good except that I cannot figure out a way to make the hidden rowid column show up as an ActiveRecord property. All of the other fields are there, just not rowid. If I query a model using Whatever.find_by_sql("SELECT rowid,* FROM whatevertable") it returns an array of "Whatever" objects but the rowid column is not there.

I've investigated tweaking the JDBC driver, activerecord or the database; nothing has borne fruit.

Any guidance would be appreciated.

Answer 1

First, note that if your table is fragmented, it won't have a ROWID - unless you created it using the WITH ROWIDS clause, in which case the ROWID becomes an indexed physical column instead of a virtual column.

Second, why don't you just use the declared primary key for the table instead of delving behind the scenes - especially when the delving behind the scenes version is not working.

I'm not clear why the explicit notation you are using is not doing as you request; that could be the JDBC driver poking its nose in where its nose does not belong. How many columns do you get back in the result set? If it isn't the number you select (the number of columns in the table plus one), then something is very fishy.

If I needed to see what the JDBC driver is sending to the server, I would enable SQLI debug tracing - either at the client side or, if the client side does not cooperate, on the server side. In normal (C-based) APIs, enabling SQLI debug is a matter of setting an environment variable:

SQLIDEBUG=2:sqli

This will create a file with a name starting 'sqli_' in the current directory of the process running the C API (ESQL/C, ODBC, etc). I assume that the same mechanism should work with the JDBC driver.

If SQLIDEBUG does not work with JDBC, then you have a much harder job - you need to enable SQLI debug on the server side.

Assuming you trap the SQLI (SQL Interface) output, you can then print it using 'sqliprint'. You'd look for the SQL sent to IDS. If it does not contain the ROWID, then you can be fairly sure the JDBC driver is playing silly games with you. However, it is not clear what you'd do to work around that. Maybe try a table alias (such as 't') and:

SELECT t.ROWID, t.* FROM WhateverTable t WHERE ...

If it turns out that JDBC is tweaking ROWID, we could try aliassing that too:

SELECT ROWID AS pk_column, t.* FROM WhateverTable AS t WHERE ...

(The AS is optional after the table name.)

Please keep us posted if you discover anything interesting or useful.

---

The database is already installed in multiple locations and has compiled applications (for which source is unavailable) which would break if the database schema changes, so I cannot add legit primary keys. These applications rely on rowid as a primary key.

OK; that is a design decision that should be reviewed at your next main upgrade if any of your customers have large enough volumes of data that fragmentation might be beneficial. Remember, fragmented tables do not have a virtual ROWID column; you can create them with a physical ROWID column by using the WITH ROWIDS clause in the CREATE TABLE or ALTER FRAGMENT statements.

Using log4jdbc I was able to confirm that the query being sent to Informix does include the request for rowid. The value is returned and the JDBC driver casts each column in the ResultSet; I can see that it is casting the rowid column (ResultSet.getLong()). The ActiveRecord object returned does not, however, include the rowid value. I believe that this is because when the JDBC driver is asked for the schema of the table, which is used to establish the available properties of the ActiveRecord class, the rowid is not returned. Any input is appreciated...

Well done on getting the information. Offhand, my knowledge of what the JDBC driver might be doing starts to run out - this might be something to take to IBM/Informix Technical Support. Various questions arise in my mind - and I'm not even sure whether the problem is in the JDBC driver, the JDBC design overall, or something sitting above JDBC; I am not by any stretch of my imagination an expert in the area. (I can spell Java - C; is that right?)

Have you tried fiddling the ROWID into the last column in the statement?

SELECT *, ROWID FROM WhereEver

What about aliassing that?

SELECT *, ROWID AS T_RowID FROM WhereEver

If the JDBC driver sees through both those, then it is working far too hard and doing everyone a disservice. Indeed, I'm not even sure I know how it works with views.

I have a table of elements:

CREATE TABLE elements
(
    atomic_number   INTEGER NOT NULL UNIQUE CONSTRAINT c1_elements
                    CHECK (atomic_number > 0 AND atomic_number < 120),
    symbol          CHAR(3) NOT NULL UNIQUE CONSTRAINT c2_elements,
    name            CHAR(20) NOT NULL UNIQUE CONSTRAINT c3_elements,
    atomic_weight   DECIMAL(8,4) NOT NULL,
    stable          CHAR(1) DEFAULT 'Y' NOT NULL
                    CHECK (stable IN ('Y', 'N'))
);

INSERT INTO elements VALUES(  1, 'H',   'Hydrogen',        1.0079, 'Y');

I was able to create a view on that table including the ROWID and then query it:

CREATE VIEW x_elements(atomic_number, symbol, name, x_rowid)
    AS SELECT atomic_number, symbol, name, ROWID AS x_rowid
         FROM elements;

SELECT * FROM x_elements WHERE x_rowid <  512;
SELECT * FROM x_elements WHERE x_rowid >= 512;

The two queries produced disjoint sets of data. You may be able to finagle your system to exploit this. If necessary, you'd rename the base tables (e.g. 'WhateverTable' becomes 'Base_Whatever') and then create the view 'WhateverTable' to select the data from Base_Whatever, along with the ROWID. I haven't formally tried this with a JDBC program, but it 'ought' to work (but since the original query also 'ought' to work, I am not sure how much reliance I would place on the 'ought to work' assertion).