2

I'm working at refactoring a lot of test code that uses a local host mysql. As can be imagined this is not optimal so I'm replacing mysql with hsqldb (for testing purposes, production still uses mysql). So far this is going (somewhat) smoothly as the code uses only standard sql. Now, however, I've run into a snag. The code polls a table to get its last update time and send it to all observers. The code uses the mysql specific (AFAIK) syntax show table status from someDb like tableName and then pulls the update_time column from the result set.
I need to implement the same thing in HSQLDB and I haven't been able to find anything. I've looked at the java.sql.DatabaseMetaData class to see if there is anything there that I can use to no avail.

I've been unable to format a google question narrow enough to be useful and so I turn to stackoverflow! So far I've haven't found anything about HSQLDB:s special tables either.

Erik
  • 2,013
  • 11
  • 17

2 Answers2

1

i dont know if im right but afaik there was an Infomration Scheme in HSQL:

check the Information Scheme section in the HSQL Manual (http://hsqldb.org/doc/2.0/guide/databaseobjects-chapt.html)

There ought to be some views in this scheme holding all the metadata. try if you can find a View named "TABLES" there, and check if you find the information you're seeking in some column of this view.

Update: found this question with a perfect answer:

How to see all the tables in an HSQLDB database?

Community
  • 1
  • 1
fasseg
  • 17,504
  • 8
  • 62
  • 73
  • Thanks for that! I've been able to get the metadata for the INFORMATION_SCHEMA.TABLES and I'm now looking for a 'updated' field. – Erik Mar 01 '11 at 10:37
  • INFORMATION_SCHEMA.TABLES do not seem to hold any usage information, sadly. – Erik Mar 01 '11 at 11:17
  • 1
    hmm yes it seems there's no functionality like that, at least i cant find anything :). you might have to add a new timestamp column to your test specific tables in order to mimick the mysql query. – fasseg Mar 01 '11 at 11:29
1

Up to version 2.1 RC4, HSQLDB does not have built-in functionality for this feature. You can define triggers in SQL on a given table which store the update time in another user-defined table. This example stores the last INSERT time. Other triggers (AFTER UPDATE or AFTER DELETE) should be defined to store the last UPDATE or DELETE time.

create table updatetime (table_name varchar(128) PRIMARY KEY, update_time timestamp );

create trigger trig after insert on sometable
update updatetime set update_time = current_timestamp 
where updatetime.table_name = 'SOMETABLE'
fredt
  • 24,044
  • 3
  • 40
  • 61