Replacing mysql specific functionality with HSQLDB

Question

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.

fredt · Accepted Answer · 2011-03-01 12:06:23Z

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'

answered Mar 1, 2011 at 12:06

fredt

24.5k3 gold badges43 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Erik Over a year ago

Word from the source as it were. Well, a trigger it is then. Thanx

Community · Accepted Answer · 2017-05-23 10:24:55Z

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?

edited May 23, 2017 at 10:24

CommunityBot

11 silver badge

answered Mar 1, 2011 at 10:15

fasseg

17.8k11 gold badges65 silver badges74 bronze badges

3 Comments

Erik Over a year ago

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 Over a year ago

INFORMATION_SCHEMA.TABLES do not seem to hold any usage information, sadly.

fasseg Over a year ago

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.

Collectives™ on Stack Overflow

Replacing mysql specific functionality with HSQLDB

2 Answers 2

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related