ROWID

[vsaji]

Hi,

Does H2 DB supports ROWID concepts similar like ORACLE ? You must be
aware of this concept, Basically the engine generates a unique
identifier for each row apart from the user specific unique ID,

This is kind of a hidden row only can access thru keyword ROWID.
Oracle generates something like this.

AAAr9dAAFAABawTAAA
AAAr9dAAFAABawTAAB
AAAr9dAAFAABawTAAC
AAAr9dAAFAABawTAAD

Thanks
Saji

[Thomas Mueller]

Hi,

> Does H2 DB supports ROWID concepts similar like ORACLE ?

No, at the moment not. It could be supported, but so far nobody
requested it. I will add a feature request (but it will be very low
priority until more people request it and until I understand the use
case).

Could you explain why you need it?

Regards,
Thomas

[vsaji]

Hi Thomas,

Thanks for your quick response.

I am developing a light weight ETL frame work for my company, as part
of the process, each request need to use some reference data from
different database.
To ease this task I have implemented a cache machanism where the
looked up data will be stored into the H2 memory db as the same
structure of reference DB.

i.e. say one of the reference table is currency which is in oracle.So
the the record structure would be something like this,

CUR_ID,CUR_CODE,NO_DECIMAL,COUNTRY,CREATED_DT,CREATED_BY,MOD_DT,MOD_BY
1 USD 2 US
05-08-2008 saji null null

The same structure (8 columns) would be cached to h2 memory db.
Note: The table may or may not have primary key, and I will not be
able to create a new column or modify the existing column as it will
give a wrong number of columns to the traget user.

Now I am planning to implement a LRU policy on the cache, so I want to
delete records which is least recently used.

In this scenario, If I have an option of getting the rowid (which is
not part of the user column) would be helpful in deleting the records
(uniquely) which is satisfying the LRU policy.


Regards
Saji

On Aug 27, 8:20 am, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:

[vsaji]

Hi Thomas,

Does the below Roadmap (Priority 2) point address my above issue ?
--> ROW_NUMBER (not the same as ROWNUM)

Also any consideration for my below suggestion :
Thread :http://groups.google.com/group/h2-database/browse_thread/
thread/601db912cb9eed1c/a8760b1fa25fc184?lnk=gst&q=columns
+Names#a8760b1fa25fc184
jdbc:h2:<url>;CASE_SENSITIVE_COLUMN={TRUE|FALSE} .


Just a query, What version of java will be used for implementing these
new features ?

Regards
Saji

[Thomas Mueller]

Hi,

> The table may or may not have primary key, and I will not be
> able to create a new column or modify the existing column as it will
> give a wrong number of columns to the traget user.

So basically you would like to have some kind of primary key in a
table where you didn't define a primary key. I don't think that's a
good solution. It will not work in other databases. If I was you, I
would define a primary key column and 'hide' that column from the
other users in some way if really required.

> Does the below Roadmap (Priority 2) point address my above issue ?
> --> ROW_NUMBER (not the same as ROWNUM)

No. ROW_NUMBER is something else. ROWID is what you want. It is also
on the roadmap now, but at the very end.

> Also any consideration for my below suggestion :
> Thread :http://groups.google.com/group/h2-database/browse_thread/
> thread/601db912cb9eed1c/a8760b1fa25fc184?lnk=gst&q=columns
> +Names#a8760b1fa25fc184
> jdbc:h2:<url>;CASE_SENSITIVE_COLUMN={TRUE|FALSE} .

No, it is not my plan to implement that, I don't see why at the
moment. To me, it sounds like the application should be fixed, and the
database is behaving correctly (according to how other databases
behave, and according to the SQL standard).

Regards,
Thomas