Activity for HyperSQL Database Engine (HSQLDB)
-
prrvchr posted a comment on ticket #1735
Hi Fred, Thank you for taking care of this issue. For me, it's no longer a problem since I can easily patch the ResultSets using javax.sql.rowset.CachedRowSet. That's what I'm doing, in fact. But it's true that it might be preferable to correct it to comply with the JDBC specification.
-
Fred Toussi modified ticket #1735
DatabaseMetaData.getTablePrivileges dont follow JDBC
-
Thanks. Will fix for next release.
-
JavaSystem should not throw NumberFormatException during normal operation
-
Thanks. Will apply to the next release.
-
Ticket moved from /p/hsqldb/feature-requests/375/
-
Julian Hyde created ticket #375
JavaSystem should not throw NumberFormatException during normal operation
-
DatabaseMetaData.getTablePrivileges dont follow JDBC
-
build updates
-
documentation updates
-
java doc revision updates
-
JVM-level deadlock
-
Issue found by static code analysis. Fixed and committed to SVN for the next release.
-
core code updates - fix for bug #1730 deadlock
-
core code updates - fix for bug #1712 support getMetaData to return generated keys meta in DML PreparedStatement for DML
-
PreparedStatement.getMetadata() returns null for generated keys mode
-
Support added for PreparedStatement.getMetaData() for next release.
-
core code updates - fix for bug #1712 support getMetaData to return generated keys meta in DML PreparedStatement for DML
-
PreparedStatement.setTimestamp ignores Calendar timezone
-
Fixed and committed to SVN for the next release.
-
core code updates - fix for bug #1729 timestamps
-
Default UUID values corrupt script file
-
Thanks for reporting. Fixed and committed to SVN for the next release.
-
core code updates - fix for bug #1732 Default UUID values
-
Support for date - integer in PostgreSQL mode
-
Thanks for reporting. Support for this has been committed to SVN for the next release.
-
added support for adding / subtracting numbers to / from dates in PostgreSQL compatibility mode
-
The issue here is that HSQLDB does not support queries with joined tables as updatable. Another issue is that the FOR UPDATE clause is accepted and ignored even when the query is not updatable. The following types of query are updatable select * from books b where book_name = 'A BOOK' for update select * from books b where id in (select book_fk from book_tags) for update In the second query, the table books has a write lock and the table book_tags has a read lock.
-
The FOR UPDATE clause is actually ignored in a joined query. The query is read-only, with read locks on the two tables. In my second example query, the query is updatable and there is also a read lock on book_tags. In the default READ COMMITTED isolation mode these read locks are immediately removed after each execution. But in SERIALIZABLE (and REPEATABLE READ) isolation mode the read locks are retained until commit or rollback. So a DELETE statement will be blocked.
-
The FOR UPDATE clause is actually ignored in a joined query. The query is read-only, with read locks on the two tables. In my second example query, the query is updatable and there is also a read lock on book_tabs. In the default READ COMMITTED isolation mode these read locks are immediately removed after each execution. But in SERIALIZABLE (and REPEATABLE READ) isolation mode the read locks are retained until commit or rollback. So a DELETE statement will be blocked.
-
Steve Ebersole posted a comment on ticket #1734
So you are saying that given that joined for-update query, even delete from books won't be blocked?
-
The issue here is that HSQLDB does not support queries with joined tables as updatable. Another issue is that the FOR UPDATE clause is accepted and ignored even when the query is not updatable. The following types of query are updatable select * from books b where book_name = 'A BOOK' for update select * from books b where id in (select book_fk from book_tags) for update In the second query, the table books has a write lock and the table book_fk has a read lock.
-
Vasiliy Gagin created ticket #374
Support for date - integer in PostgreSQL mode
-
I will check the FOR UPDATE behavior next week and will also provide more information on the limitations. Re explicit locking, see the same chapter of the guide under < lock table statement >. It goes like LOCK TABLE books WRITE, book_tags READ For current isolation level use the java.sql.Connection method, getTransactionIsolation() For database transaction control, use the query below: select property_value from information_schema.system_properties where property_name = 'hsqldb.tx' Thanks Steve...
-
Since it is not specified, afaik the default (2PL/LOCKS) should apply - https://hsqldb.org/doc/guide/sessions-chapt.html#snc_tx_tx_cc However, I did not see anything obvious there about the impact, if any, of for-update. Can I assume then that with LOCKS for-update only acquires shared locks? Assuming so, another option would possibly be something mentioned in that above doc - HyperSQL also supports explicit locking of a group of tables for the duration of the current transaction. Use of this command...
-
General locking behavior depends on the transaction control setting for the database (LOCKS or MVCC). Please state which one is being used. I will check the issue in detail in a couple of days.
-
joins and for-update
-
The deleteOnExit hook is used for the .lck file. For your use case, you need to ensure the HSQLDB file names are always distinct from one another. In this situation there is no need for the .lck file, whose function is to prevent two different Java processes from opening the same file-based database. Create the databases with hsqldb.lock_file=false on the database URL. See http://www.hsqldb.org/doc/2.0/guide/dbproperties-chapt.html
-
The deleteOnExit hook is used for the .lck file. For your use case, you need to ensure the HSQLDB file names are always distinct from one another. In this situation there is no need for the .lck file, whose function is to prevent to different Java processes to open the same file-based database. Create the databases with hsqldb.lock_file=false on the database URL. See http://www.hsqldb.org/doc/2.0/guide/dbproperties-chapt.html
-
The deleteOnExit hook is used for the .lk file. For your use case, you need to ensure the HSQLDB file names are always distinct from one another. In this situation there is no need for the .lck file, whose function is to prevent to different Java processes to open the same file-based database. Create the databases with hsqldb.lock_file=false on the database URL. See http://www.hsqldb.org/doc/2.0/guide/dbproperties-chapt.html
-
Try changing the configuration of your connection pool to keep a connection to the database open and to avoid having to close and open the last connection to the database.
-
Sandeep Kadam posted a comment on discussion Help
HSQLD is randomly throwing org.hsqldb.persist.LockFile$LockHeldExternallyException error. 20250501 01:41:50,840 WARN BasicResourcePool - com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@7345b06d -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception: java.sql.SQLException: Database lock acquisition failure:...
-
Paul Reicherzer created ticket #223
remove deleteOnExit-Hooks
-
Tom Eugelink modified a comment on ticket #1733
Ah, that helped solve the problem! What was I thinking doubting HSQLDB? 😄
-
Ah, that helped solved the problem! What was I thinking doubting HSQLDB? 😄
-
table named 'group' is causing problems, quoting does not help
-
Server and in-memory mode behave exactly the same in case conversion and retention. If you create a table with a name that is not double quoted, the name is converted to all uppercase. It looks like your various methods of creating tables are not doing exactly the same thing.
-
Okay. I have not renamed yet, because the actual problem is that there is a difference in behavior between HSQL in server and in-memory mode (as the link to stackoverflow explains). Renaming was a possible solution. It turns out in server mode I get all the tables in uppercase, in in-memory mode I get them in lower case. The create scripts are in lowercase. That probably explains why I'm having problems with my tests.
-
First you need to find out exactly what the table name is. You can do this with a SELECT from the INFORMATION_SCHEMA.TABLES. Supposing the actual name of the table is GROUP (all uppercase), you should be able to use alter table "GROUP" rename to groups
-
table named 'group' is causing problems, quoting does not help
-
Peter Winckles created ticket #1732
Default UUID values corrupt script file
-
The type DOUBLE is for floating point values which are stored in an approximate fashion. Use a DECIMAL type such as DECIMAL(16,2) to store exact monetary values. The DECIMAL(16,2) can be used for currencies such as the Dollar or Euro for values over many trillion units.
-
Compairing 0's values not working as expacted!
-
The type DOUBLE is for floating point values which are stored in an approximate fasion. Use a DECIMAL type such as DECIMAL(16,2) to store exact monetary values. The DECIMAL(16,2) can be used for currencies such as the Dollar or Euro for values over many trillion units.
-
Abid Maqbool modified a comment on ticket #1731
some others solutions are also working e.g. AND CAST(amount AS DECIMAL) = 0 or ABS(amount) < 0.000001 or CAST(amount AS INT) = 0 etc. but these are not looking like clean one, why it's not working? amount = 0
-
some others solutions are also working e.g. AND CAST(amount AS DECIMAL) = 0 or ABS(amount) < 0.000001 or CAST(amount AS INT) = 0 etc. but these are not looking like clean one, why it's not working amount = 0
-
Compairing 0's values not working as expacted!
-
Thanks for reporting. I will investigate this later this year. You can eliminate this by turning off the auto checkpoint which is done by the HSQLDB Timer thread. Set hsqldb.log_size=0 as a connection property.
-
Mitchell Herrijgers created ticket #1730
JVM-level deadlock
-
Please note "org.hsqldb.jdbc.JDBCDriver" is the JDBC driver class, while "org.hsqldb.jdbcDriver" is the old class name. The old class name is still supported for backward compatibility.
-
Dick Hildreth posted a comment on discussion Open Discussion
Thanks, Fred. I discovered that the problem was related to my inattentiveness to character case. For years, I had registered org.hsqldb.JDBCDriver but with the updates to openJDK 22+ and/or Tomcat 10+, it now requires org.hsqldb.jdbcDriver I wasted an awful lot of my time on this and I apologize that I wasted some of yours. Dick On Tue, Jan 28, 2025 at 3:34 AM Fred Toussi fredt@users.sourceforge.net wrote: Hi Dick, I don't know anything about this issue. It would be better to ask in a Tomcat forum....
-
Ideally, conversions should be symmetrical. I will include this issue in a future review of JDBC compatibility.
-
Hi Dick, I don't know anything about this issue. It would be better to ask in a Tomcat forum. I know Tomee's latest release has an hsqldb 2.7.4 dependency (https://tomee.apache.org/10.0.0/release-notes.html) so you could also check there.
-
The most recent post on this topic is almost 7 years old, so I don't feel terribly guilty posting this. I've been a fan of hSQLdb since v1.8 (about 2000?). I'm running two databases on the server at jdbc:hsqldb:hsql://localhost:9001 with the server.properties of: server.silent=false server.trace=true server.database.0=C:/hsqldb/data/precincts server.dbname.0=precincts server.database.1=C:/hsqldb/data/people server.dbname.1=people I have no problem starting the hSQLdb server as a Windows service as...
-
Bas de Bakker posted a comment on ticket #1729
Why should the conversions to/from character be different? The same timezone is explicitly specified in both setTimestamp and getTimestamp. Shouldn't those conversions to/from character be using those timezones? (Also, it works as expected in PostgreSQL, SQL Server, Oracle, SAP HANA, MariaDB and MySQL.)
-
This is probably working fine. The "SELECT ? FROM table1" has a parameter with undefined data type, which is defaulted to CHARACTER. After that, you cannot expect retrieved values to match inserted values, as different conversions to / from character is performed. If you want to explore this further, try this "SELECT CAST(? AS TIMESTAMP WITH TIME ZONE) FROM table1" instead.
-
Pijus Krisiukenas created ticket #1729
PreparedStatement.setTimestamp ignores Calendar timezone
-
Ralph Espinola modified a comment on discussion Help
The ERRORs are intermittent, but they appear as early as log4j 2.18.0.
-
The ERRORs are intermittent, but they appear as early as log4j 2.19.0.
-
We have not tested with recent releases of log4j and have not modified access to log4j in 2.7.4. Please find out from which version of log4j the error appears.
-
After upgrading to hsqldb 2.7.4-jdk8 and log4j2 2.24.3 we get the following error: ERROR Unable to create Lookup for bundle java.lang.ClassCastException: class org.apache.logging.log4j.core.lookup.ResourceBundleLookup. at java.lang.Class.asSubclass(Class.java:3404) at org.apache.logging.log4j.core.lookup.Interpolator.<init>(Interpolator.java:8Z) at org.apache.logging.log4j.core.lookup.Interpolator.<init>(Interpolator.java:109 at org.apache.logging.log4j.core.config.AbstractConfiguration.<init>(AbstractConfiguration.iava=136)...
-
After upgrading to hsqldb 2.7.4-jdk8 and log4j2 2.24.3 we get the following error: ERROR Unable to create Lookup for bundle java.lang.ClassCastException: class org.apache.logging.log4j.core.lookup.ResourceBundleLookup. The same ERROR appears for the following Lookups: ctx, date, env, eventjava, log4j, lower, main map, marker, sd, sys, and upper.
-
After upgrading to hsqldb 2.7.4-jdk8 and log4j2 2.24.3 we get the following error: ERROR Unable to create Lookup for bundle java.lang.ClassCastException: class org.apache.logging.log4j.core.lookup.ResourceBundleLookup.
-
Union of DECIMAL and INTEGER in subqueries throws general error
-
Thanks for reporting. You need to cast one or both of the column references for this type of query to work. This may be fixed in a future version.
-
Union of DECIMAL and INTEGER in subqueries throws general error
-
core code updates - move HsqlException class to org.hsqldb.error package
-
I noticed that t is empty and joining it with the VALUES(1) results in an empty result regardless of ON FALSE or ON TRUE. But there is a row in VALUES(1) that is not joined with t and it looks like WHEN NOT MATCHED means this row should be inserted. I need to check the Standard text again before deciding.
-
Lukas Eder posted a comment on ticket #1727
As per my follow up comment, the error is in my thinking. When running the MERGE statement for the first time, there's no match simply because the target table contains no rows.
-
Thanks for reporting. I need to check the expected behaviour according to SQL:2023 before commenting.
-
Egh, stupid 😅 t is empty in the beginning, so there was no match in the inner join
-
Hmm, I must be misunderstanding something. It seems that most (all?) RDBMS behave this way?
-
MERGE statement behaves incorrectly when ON clause contains TRUE or FALSE literals
-
core code updates - move HsqlException class to org.hsqldb.error package
-
Please provide the complete query and the complete error message.
-
rmpbklyn posted a comment on discussion Help
receving error column is unkown for both of these statments CASEWHEN( [vEmpWide].[STATEORPROVINCE] IN ('AL') , 'SOUTH', '') CASEWHEN ( "DEPARTMENT" ='IT' OR "DEPARTMENT" ='Corp HQ", 'n', 'y')
-
Support FUNCTION RETURNS ... ARRAY for Java functions
-
Thanks for reporting. Fixed and committed to SVN. A usage example is in SVN /base/trunk/src/org/hsqldb/test/TestRoutines.java Please build the jar and test and report the result.
-
test updates - fix for bug #1726
-
core code updates - fix for bug #1726
-
Ondrej Zizka created ticket #1726
Support FUNCTION RETURNS ... ARRAY for Java functions
-
test code updates
-
core code updates - move HsqlException class to org.hsqldb.error package
-
core code updates - move HsqlException class to org.hsqldb.error package
-
core code updates - move HsqlException class to org.hsqldb.error package
-
You can indicate the intended type using a CAST: USING (select ? AS ID, CAST(? AS DATE) AS DATE1, CAST(? AS TIMESTAMP) AS TIMESTAMP1, We may support setting the date and timestamp without a parameter cast in the next release.
-
Accessing generated values, where non exists throws exception on second try.
-
Thanks again. Fixed and committed to SVN /base/trunk/
-
General error when comparing VARCHAR value with CLOB value
-
Fixed and committed.
