hsqldb,optimistic-locking , HSQLDB & Optimistic Locking

HSQLDB & Optimistic Locking


Tag: hsqldb,optimistic-locking

In MySQL, it is possible to define a table column whose value is always updated by the database manager:

CREATE TABLE widgets (
    widget_id         INT NOT NULL,

Above, anytime a new [widgets] entity gets created, the current timestamp is used for its version column. And, anytime a write/update occurs to that entity, the system will update version with (again) the current timestamp.

Is there a way to do this in HSQLDB 2.3.x? My Google Fu and perusing of the HSQLDB docs turned up nadda.


I just spoke to Mitch Connor, founder and primary maintainer of HSQLDB, on IRC, and apparently HSQLDB was never built with the intention of handling optimistic locking.

Mitch recommended I try H2 instead.


HsqldbException: user lacks privilege or object not found: TABLE

I am trying to connect to a MS Access database using hsqldb in java. I added all the libraries I needed, but in the end I still get an exception: Exception in thread "main" java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: MESSAGES Caused by: org.hsqldb.HsqlException: user lacks privilege or...

Using BLOB in DBUnit

I have to test a class where we are retrieving data from Oracle, from a XMLTYPE column. We are using BLOB for the casting, because the system is prepared to run in MySQL too: BLOB salePlanXmlType = (BLOB) jdsLoad.getValueCell(0, "SALEPLAN"); In DBUnit, first we are creating the tables, and then...

HSQL + Hibernate Exception: Wrong column type: Found: double, expected: float

I am using in-memory HSQL (HSQLDB) with Hibernate for all my unit tests, because it is very fast. I have a table with a column defined as follows: float qw; When hibernate starts, I get the following error: org.hibernate.HibernateException: Wrong column type in MyTable for column qw. Found: double, expected:...

JDBC closing a database connection

I'm not sure how to close the database through this method of database connection, it's opens and connects successfully, I don't know if it closes when I close the GUI it's coded to import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; import javax.swing.JOptionPane; public class Database_link { Connection connect = null; Statement...

How to insert using a sequence with Liquibase

I would like to do a liquibase insert with the primary key being auto generated from the sequence defined in the database. The target database is HSQLDB. It works to do an insert specifying a value for the primary key <insert ...> <column name="TAG_ID" valueNumeric="2"/> I found this (admittedly older)...

Getting timestamp from Objecify entity?

Is it possible to obtain the timestamp that is used for optimistic concurrency control from an Objectify entity (or a lower-level part of the Google Datastore infrastructure) and if so, how?

Is it possible to use OracleDataSource for some other database except for Oracle?

What I want is to use OracleDataSource with HSQLDB database. Is it possible?

Truncate schema in HSQLDB

I'm using HSQLDB for my unit tests. The problem is that my project contains really many tests and dropping the whole DB and recreating it for each and every test takes a really significant amount of time. I found that new HSQLDB contains the possibility of removing data from schema...

Java: ucanaccess hsqldb java.lang.NoSuchMethodError:

I downloaded and installed the uCanAccess jars by following instructions from here: Manipulating an Access database from Java without ODBC I'm learning how to use the microsoft access db for java and this is the coding I used package Main.Net; import java.sql.*; public class DataBase { Connection con; Statement st;...

hsqldb 2.3.2 jdbc driver does not support ResultSet.first()?

I am playing with HSQLDB+JDBC driver using JDK 8. Using rs.next() looping results works fine, however, using rs.first() does not work: feature is not supported ?! Is is by design or a bug? I plan to access hsqldb using Spring jdbc template, and I am concerned that I may stuck...

Rails - Optimistic locking always fires StaleObjectError exception

I'm learning rails, And read about optimistic lock. I've added lock_version column of type integer into my articles table. But now whenever I try to update a record for the first time, I get StaleObjectError exception. Here's my migration: class AddLockVersionToArticle < ActiveRecord::Migration def change add_column :articles, :lock_version, :integer end...

orphanRemoval working in PostgreSQL but not in hsqldb

I have Academy entity: @Entity @Table(name = "academy") public class Academy { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; @Column(nullable = false) private String name; @Column(nullable = false) private String address; @OneToMany(fetch = FetchType.LAZY, orphanRemoval = true, mappedBy = "parent") private List<Institute> institutes; public Academy() {} public Academy(String name, String...

Error statment creation N tables DBUnit + HsqlDb (v. 2.3.2)

I'm using DBUnit + hsqlDB for database unit testing. Until now, we used hsqldb version <dependency> <groupId>org.hsqldb</groupId> <artifactId>hsqldb</artifactId> <version></version> <scope>test</scope> </dependency> We've some scripts to create the database schema (which create multiple tables). We load + execute these scripts through PreparedStatement. Something basicaly like that: @Test public void testMultipleTablesError()...

reset cursor position in hsqlDB, java

I'm using hsqlDB version 2.3.2 that I'm embedding into a small program. Everything worked fine so far, I've made a simple printer method that allows mi to print a resultset in the console: public static void printResultSet(ResultSet rs){ try { if(rs==null || rs.wasNull()) { System.out.println("#### empty result set ####"); return;...

HSQLDB + JUnit + Hibernate: java.sql.SQLException: invalid schema name

I want to run a test with JUnit and Hibernate an in-memory database HSQLDB, but when the method is trying to commit the changes to the DB, this exception appears: (No Spring framework) Caused by: java.sql.SQLException: invalid schema name: SRH in statement [select liquidacio0_.idLiquidacion as idLiquid1_180_0_, liquidacio0_.fkEstado as fkEstado180_0_, liquidacio0_.FECHA_LIQ...

Migration HSQLDB to MySQL using MySQL Workbench 6.2

I've to migrate HSQLDB to MYSQL but I don't know how to configure Workbench. I tried to follow this instruction http://dev.mysql.com/doc/workbench/en/wb-migration-overview-steps.html but I've failed. How to properely configure migration ?

optimistic lock with a “version” field vs snapshot isolation level

I was wondering what are the advantage/inconvenient for these two optimistic lock solutions : Use a "version" field and detect changes during updates (i.e. using hibernate @Version annotation) Use the Snapshot isolation level on transactions If I'm correct these 2 solutions have the same behavior : an error will be...

Hsqldb Stored Procedure

I am trying to setup a stored procedure in my in memory Hsqldb database for testing purposes. The stored proc I am working from is developed in MySql so I want to set it up with HSqlDb to fit in with my testing suite I am trying to create a...

Error in Script File If I Restart Identifyer Column After Insert

Is it possible to restart the ID column of an HSQLDB after rows were inserted? Can I even set it to restart at a value lower than existing IDs in the table? The Situation I have a simple Java program which connects to a HSQLDB like so: DriverManager.getConnection("jdbc:hsqldb:file:" + hsqldbPath,...

Stored Procedure in HSQLDB. Creation through SQLTool

I am trying to write a stored procedure. Creating it through the SQL browser goes well but when I save it in SQL file and load it through SQLTool it fails CREATE PROCEDURE SP_GETNEXTSEQUENCE(OUT out_new_seq_no DECIMAL, IN in_seq_id DECIMAL) MODIFIES SQL DATA BEGIN ATOMIC UPDATE SEQUENCE_GENERATOR SET seq_value=seq_value+1 where seq_id=in_seq_id;...

When I use hsqldb I have problems with GUI (JavaFx)

I can't understand how RDB can be linked with GUI but I've checked several times. I have osgi javafx application. when I install hsqldb and use database I have strange problems: Before that I used h2 and I didn't have anything like that. And the only thing was changed is...

HyperSQL (HSQLDB) explicit shared (read) lock syntax

According to HSQLDB documentation, there are two select-for-updating directives: FOR UPDATE FOR READ ONLY If the SQL qualifier, FOR UPDATE is used, then all the updatable columns of the result set become updatable. If a cursor is declared with FOR READ ONLY, then it is not updatable. In HSQLDB, if...

Check-and-set in Couchbase Java SDK 2.x?

In older version of Couchbase Java SDK there were several check-and-set (CAS) methods for implementing optimistic locking. But what is corresponding API in a newer version of SDK (>= 2.0)? Initial code: JsonDocument doc = bucket.get("myKey"); Long casValue = doc.cas(); // some method to set new value for "myKey" only...

Why do the concurrent DB connections see each others uncommitted changes though isolation is set to “read committed”?

I am trying to make a few tests to understand how transaction isolation levels can be used to address the various concurrency concerns. I've started with TRANSACTION_READ_COMMITED, but the simplest scenario doesn't behave the way I expect. Here's the code: try(Connection connection1 = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) { connection1.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); connection1.setAutoCommit(false); try(Connection...

Spring MVC: Validation, Post-Redirect-Get, Partial Updates, Optimistic Concurrency, Field Security

How do I update only a few fields of a model entity with forms? How do I use the Post-Redirect-Get pattern with Spring MVC, especially with form validation? How do I secure certain fields in my entities? How do I implement Optimistic Concurrency Control?...

Caused by: org.hsqldb.HsqlException: cardinality violation

I am using HSQLDB as a backend for my java application, when i am trying to call procedure in java with single record, i am able to get the resultset, but for multiple records, i am getting following exception "Caused by: org.hsqldb.HsqlException: cardinality violation" CREATE PROCEDURE get_Street(OUT c_str_col VARCHAR(50), OUT...

Cannot insert into hsql database with hibernate

My problem is,I trying add one record into HSQL Database and I can't. Really don't know what is wrong also how to fix this.All of this is really annyoing me,cuz it's simply example and I can't run this. At the begining I run HSQLDB server with this command : java...

Why throwing java.lang.IndexOutOfBoundsException when reading blob in the database hsqldb

Hi. I am using the following code to write blob to database at the server side. @Override public void write(TypeHandlerContext context, ResultSet resultSet, Object value) { int column = context.getColumn(); LargeObject lo = (LargeObject)value; if(lo == null) { try { resultSet.updateNull(column); } catch(SQLException e) { throw new RuntimeException(e); } }...

HSQLDB Database multiple connections

Is it possible to connect to a HSQL database over multiple connections? I have 2 connections using the same JDBC URL and the same hsqllib.jar and it appears I get a "new" database. I imagine each connection is initializing the database in its memory?...

How to change a column name in HSQLDB when the existing name is a reserved word?

I'm trying to upgrade our DB from HSQLDB 1.7 to the latest version. Unfortunately someone has named a column "Default". I can't seem to rename this column in SQL (this in 1.7 as the rename has to occur before the upgrade). I have tried escaping the column: stmt.executeUpdate("ALTER TABLE table_name...

How to change port that HSQLDB server listens on

I am starting a HSQLDB server programmatically, however I am getting port conflicts when I try an run the HSQLDB server in conjunction with a web application that I am developing. I get the following error on the webapp side java.util.concurrent.ExecutionException: java.net.BindException: Address already in use: JVM_Bind & I get...

Should the natural or surrogate key be returned in an API?

First time I think about it... Until now, I always used the natural key in my API. For example, a REST API allowing to deal with entities, the URL would be like /entities/{id} where id is a natural key known to the user (the ID is passed to the POST...