Ask Your Question
0

JDBC: Connection to HSQLDB database does not permit update [closed]

asked 2018-01-12 18:23:00 +0100

Spun69 gravatar image

updated 2018-01-12 18:28:45 +0100

I was able to migrate an existing LO Base embedded database to a standalone HSQLDB instance with server (per http://hsqldb.org/doc/2.0/guide/guide..., Appendix C) on Windows 10. I can access the standalone database using JDBC just fine, and query all the tables/views using username "SA". Note server tracing is enabled, so console reports all submitted commands to confirm remote client transactions. However, the returned data seems to be read-only as LO will not permit any updates. I have verified the database properties have readonly=false. I suspect the issue may be that JDBC connections are read-only by default. Anyone know if this is true, and how to change it such that JDBC connections are updateable?

Note before migration, the same database when embedded permitted updates just fine. Relevant property file content below.

LO version: 5.3.5.2
JRE version: 1.8.0_152

=== server.properties ===
server.database.0=file:c:/temp/BOARS/database
server.dbname.0=database
server.root=c:/temp/BOARS/server
server.silent=false
server.trace=false
server.address=192.168.1.2
server.port=9001
server.daemon=false
server.no_system_exit=true

=== database.properties ===
#HSQL Database Engine 1.8.0.10
#Thu Jan 11 19:18:38 CST 2018
hsqldb.cache_file_scale=1
hsqldb.cache_scale=13
hsqldb.cache_size_scale=8
hsqldb.cache_version=1.7.0
hsqldb.compatible_version=1.8.0
hsqldb.default_table_type=cached
hsqldb.lock_file=true
hsqldb.log_size=10
hsqldb.nio_data_file=false
hsqldb.original_version=1.8.0
hsqldb.script_format=0
modified=yes
readonly=false
runtime.gc_interval=0
sql.enforce_strict_size=true
version=1.8.0
edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Spun69
close date 2018-01-13 20:10:17.834586

Comments

Hello,

Can't seem to duplicate with v1.8.0 or v2.3.2. What JDBC driver class name are you using? Just curious as to why you are not using a newer HSQLDB version? The guide you point to is v2.4!

Ratslinger gravatar imageRatslinger ( 2018-01-12 21:42:00 +0100 )edit

Hey, thanks for the response. I am using the HSQLDB release bundled with the LO distribution. The JAR archive is found at "C:\Program Files\LibreOffice 5\program\classes\hsqldb.jar", which was added to the JRE classpath. I will try a newer version of HSQLDB to see if the issue is resolved using it.

Spun69 gravatar imageSpun69 ( 2018-01-12 22:58:02 +0100 )edit

@Spun69 Please REMOVE the classpath from Options->Advanced as this will cause major problems in using new versions of HSQLDB server. Want to avoid this setting for HSQL. Other DB's are OK such as MySQL. Checking on some other potential issues also which may affect you.

Ratslinger gravatar imageRatslinger ( 2018-01-12 23:03:50 +0100 )edit

@Ratslinger After upgrading the database to use HQSL 2.4, the original issue seems to have gone away. I can now update tables via JDBC connection. Thanks for the suggestion. This ticket can be closed. BTW I did set the JRE classpath within LO for my most recent testing (before I saw your response). I am curious how the newer JAR file will be found within LO if there is no explicit path given...I would think it would fallback to using the old one bundled with LO version. Please clarify.

Spun69 gravatar imageSpun69 ( 2018-01-13 00:02:53 +0100 )edit

As stated, the setting of classpath will cause problems with embedded files. It will make them inoperable after the first open. To avoid this, remove the HSQL classpath. Change the internal setting of the .odb for the classpath with a macro found in the LO docs (here) - Appendix on page 26.

Ratslinger gravatar imageRatslinger ( 2018-01-13 02:37:37 +0100 )edit

I finally found the relevant doc (BaseHandbook v5 Appendix pdf), and implemented the specified macro. Retested and no issues found. Seems like I am good to go at this point. Thanks again for the prompt help.

Spun69 gravatar imageSpun69 ( 2018-01-13 20:09:19 +0100 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2018-01-13 00:05:17 +0100

Spun69 gravatar image

updated 2018-01-13 00:06:35 +0100

Upgrading the HSQLDB installed version to 2.4, and migrating database to new version, seems to have corrected the issue. Can update database via JDBC (using newer hsqldb.jar) from within LO.

edit flag offensive delete link more

Comments

@Spun69 Just to be clear, are you all set? Everything working? NO class path set? If all OK, please respond & then close question.

Ratslinger gravatar imageRatslinger ( 2018-01-13 00:20:01 +0100 )edit

Issue is now resolved WITHOUT using LO classpath.

Spun69 gravatar imageSpun69 ( 2018-01-13 20:07:48 +0100 )edit

Question Tools

1 follower

Stats

Asked: 2018-01-12 18:23:00 +0100

Seen: 19 times

Last updated: Jan 13