I'm fairly new to the database world but I had to use an hsqldb-2.0.0 database for a netflow collector software. As the software shows no output even if the client is collecting the flows I investigated the database and it seems that after committing some changes it rolls back to the previous state (the empty one). This is the sql log : 2016-03-07 09:30:51.217 0 SET DATABASE TRANSACTION CONTROL LOCKS 2016-03-07 09:30:51.217 0 COMMIT 2016-03-07 09:30:51.217 0 SET DATABASE DEFAULT ISOLATION LEVEL READ COMMITTED 2016-03-07 09:30:51.217 0 COMMIT 2016-03-07 09:30:51.217 0 SET DATABASE TRANSACTION ROLLBACK ON CONFLICT TRUE 2016-03-07 09:30:51.217 0 COMMIT 2016-03-07 09:30:51.217 0 SET DATABASE TEXT TABLE DEFAULTS '' 2016-03-07 09:30:51.217 0 COMMIT 2016-03-07 09:30:51.217 0 SET DATABASE SQL NAMES FALSE 2016-03-07 09:30:51.217 0 COMMIT 2016-03-07 09:30:51.217 0 SET DATABASE SQL REFERENCES FALSE 2016-03-07 09:30:51.217 0 COMMIT 2016-03-07 09:30:51.217 0 SET DATABASE SQL SIZE TRUE 2016-03-07 09:30:51.217 0 COMMIT 2016-03-07 09:30:51.217 0 SET DATABASE SQL TYPES FALSE 2016-03-07 09:30:51.217 0 COMMIT 2016-03-07 09:30:51.217 0 SET DATABASE SQL TDC DELETE TRUE 2016-03-07 09:30:51.217 0 COMMIT 2016-03-07 09:30:51.217 0 SET DATABASE SQL TDC UPDATE TRUE 2016-03-07 09:30:51.217 0 COMMIT 2016-03-07 09:30:51.217 0 SET DATABASE SQL TRANSLATE TTI TYPES TRUE 2016-03-07 09:30:51.217 0 COMMIT 2016-03-07 09:30:51.217 0 SET DATABASE SQL CONCAT NULLS TRUE 2016-03-07 09:30:51.217 0 COMMIT 2016-03-07 09:30:51.217 0 SET DATABASE SQL UNIQUE NULLS TRUE 2016-03-07 09:30:51.217 0 COMMIT 2016-03-07 09:30:51.217 0 SET DATABASE SQL CONVERT TRUNCATE TRUE 2016-03-07 09:30:51.217 0 COMMIT 2016-03-07 09:30:51.217 0 SET DATABASE SQL AVG SCALE 0 2016-03-07 09:30:51.217 0 COMMIT 2016-03-07 09:30:51.217 0 SET DATABASE SQL DOUBLE NAN TRUE 2016-03-07 09:30:51.217 0 COMMIT 2016-03-07 09:30:51.217 0 SET FILES WRITE DELAY 500 MILLIS 2016-03-07 09:30:51.217 0 SET FILES BACKUP INCREMENT TRUE 2016-03-07 09:30:51.217 0 COMMIT 2016-03-07 09:30:51.217 0 SET FILES CACHE SIZE 10000 2016-03-07 09:30:51.217 0 COMMIT 2016-03-07 09:30:51.217 0 SET FILES CACHE ROWS 50000 2016-03-07 09:30:51.217 0 COMMIT 2016-03-07 09:30:51.217 0 SET FILES SCALE 32 2016-03-07 09:30:51.217 0 COMMIT 2016-03-07 09:30:51.217 0 SET FILES LOB SCALE 32 2016-03-07 09:30:51.217 0 COMMIT 2016-03-07 09:30:51.217 0 SET FILES DEFRAG 0 2016-03-07 09:30:51.217 0 COMMIT 2016-03-07 09:30:51.217 0 SET FILES NIO TRUE 2016-03-07 09:30:51.217 0 COMMIT 2016-03-07 09:30:51.217 0 SET FILES NIO SIZE 256 2016-03-07 09:30:51.217 0 COMMIT 2016-03-07 09:30:51.217 0 SET FILES LOG TRUE 2016-03-07 09:30:51.217 0 COMMIT 2016-03-07 09:30:51.217 0 SET FILES LOG SIZE 50 2016-03-07 09:30:51.217 0 COMMIT 2016-03-07 09:30:51.217 0 CREATE USER SA PASSWORD DIGEST 'd41d8cd98f00b204e9800998ecf8427e' 2016-03-07 09:30:51.217 0 COMMIT 2016-03-07 09:30:51.217 0 ALTER USER SA SET LOCAL TRUE 2016-03-07 09:30:51.217 0 CREATE SCHEMA PUBLIC AUTHORIZATION DBA 2016-03-07 09:30:51.217 0 COMMIT 2016-03-07 09:30:51.217 0 ALTER SEQUENCE SYSTEM_LOBS.LOB_ID RESTART WITH 1 2016-03-07 09:30:51.217 0 COMMIT 2016-03-07 09:30:51.217 0 SET DATABASE DEFAULT INITIAL SCHEMA PUBLIC 2016-03-07 09:30:51.217 0 COMMIT 2016-03-07 09:30:51.217 0 GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.SQL_IDENTIFIER TO PUBLIC 2016-03-07 09:30:51.217 0 COMMIT 2016-03-07 09:30:51.217 0 GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.YES_OR_NO TO PUBLIC 2016-03-07 09:30:51.217 0 COMMIT 2016-03-07 09:30:51.217 0 GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.TIME_STAMP TO PUBLIC 2016-03-07 09:30:51.217 0 COMMIT 2016-03-07 09:30:51.217 0 GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.CARDINAL_NUMBER TO PUBLIC 2016-03-07 09:30:51.217 0 COMMIT 2016-03-07 09:30:51.217 0 GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.CHARACTER_DATA TO PUBLIC 2016-03-07 09:30:51.217 0 COMMIT 2016-03-07 09:30:51.217 0 GRANT DBA TO SA 2016-03-07 09:30:51.217 0 COMMIT 2016-03-07 09:30:51.232 2 COMMIT 2016-03-07 09:30:51.232 2 ROLLBACK 2016-03-07 09:30:57.207 3 CREATE TABLE IpSegments ( IpSeg VARCHAR(15) NOT NULL,SegName VARCHAR(15) NOT NULL) 2016-03-07 09:30:57.207 3 COMMIT 2016-03-07 09:30:57.223 3 CREATE TABLE Raw_V1 ( RouterIP VARCHAR(15) NOT NULL, SysUptime BIGINT, Secs BIGINT, Nsecs BIGINT, SrcAddr VARCHAR(15) NOT NULL, DstAddr VARCHAR(15) NOT NULL, NextHop VARCHAR(15) NOT NULL, Input INT NOT NULL, Output INT NOT NULL, dPkts BIGINT, dOctets BIGINT, aFirst BIGINT, aLast BIGINT, SrcPort INT NOT NULL, DstPort INT NOT NULL, Prot INT NOT NULL, TOS INT NOT NULL, Flags INT, Stamp VARCHAR(15) NULL ) 2016-03-07 09:30:57.223 3 COMMIT 2016-03-07 09:30:57.238 3 CREATE TABLE Raw_V5 ( RouterIP VARCHAR(15) NOT NULL, SysUptime BIGINT, Secs BIGINT, Nsecs BIGINT, Flow_Sequence BIGINT NOT NULL, Engine_Type INT NOT NULL, Engine_ID INT NOT NULL, SrcAddr VARCHAR(15) NOT NULL, DstAddr VARCHAR(15) NOT NULL, NextHop VARCHAR(15) NOT NULL, Input INT NOT NULL, Output INT NOT NULL, dPkts BIGINT, dOctets BIGINT, aFirst BIGINT, aLast BIGINT, SrcPort INT NOT NULL, DstPort INT NOT NULL, Tcp_Flags INT NOT NULL, Prot INT NOT NULL, TOS INT NOT NULL, Src_As INT NOT NULL, Dst_As INT NOT NULL, Src_Mask INT NOT NULL, Dst_Mask INT NOT NULL, Stamp VARCHAR(15) NULL ) 2016-03-07 09:30:57.238 3 COMMIT 2016-03-07 09:30:57.238 3 CREATE TABLE Raw_V7 ( RouterIP VARCHAR(15) NOT NULL, SysUptime BIGINT, Secs BIGINT, Nsecs BIGINT, Flow_Sequence BIGINT NOT NULL, SrcAddr VARCHAR(15) NOT NULL, DstAddr VARCHAR(15) NOT NULL, NextHop VARCHAR(15) NOT NULL, Input INT NOT NULL, Output INT NOT NULL, dPkts BIGINT, dOctets BIGINT, aFirst BIGINT, aLast BIGINT, SrcPort INT NOT NULL, DstPort INT NOT NULL, Flags INT NOT NULL, Tcp_Flags INT NOT NULL, Prot INT NOT NULL, TOS INT NOT NULL, Src_As INT NOT NULL, Dst_As INT NOT NULL, Src_Mask INT NOT NULL, Dst_Mask INT NOT NULL, Flags1 INT NOT NULL, Router_Sc VARCHAR(15) NOT NULL, Stamp VARCHAR(15) NULL ) 2016-03-07 09:30:57.238 3 COMMIT 2016-03-07 09:30:57.238 3 CREATE TABLE Raw_V8_AS ( RouterIP VARCHAR(15) NOT NULL, SysUptime BIGINT, Secs BIGINT, Nsecs BIGINT, Flow_Sequence BIGINT NOT NULL, Engine_Type CHAR(1) NOT NULL, Engine_ID CHAR(1) NOT NULL, Flows BIGINT NOT NULL, dPkts BIGINT NOT NULL, dOctets BIGINT NOT NULL, aFirst BIGINT NOT NULL, aLast BIGINT NOT NULL, Src_As INT NOT NULL, Dst_As INT NOT NULL, Input INT NOT NULL, Output INT NOT NULL, Stamp VARCHAR(15) NULL ) 2016-03-07 09:30:57.238 3 COMMIT 2016-03-07 09:30:57.254 3 CREATE TABLE Raw_V8_ProtoPort ( RouterIP VARCHAR(15) NOT NULL, SysUptime BIGINT, Secs BIGINT, Nsecs BIGINT, Flow_Sequence BIGINT NOT NULL, Engine_Type CHAR(1) NOT NULL, Engine_ID CHAR(1) NOT NULL, Flows BIGINT NOT NULL, dPkts BIGINT NOT NULL, dOctets BIGINT NOT NULL, aFirst BIGINT NOT NULL, aLast BIGINT NOT NULL, Prot INT NOT NULL, SrcPort INT NOT NULL, DstPort INT NOT NULL, Stamp VARCHAR(15) NULL ) 2016-03-07 09:30:57.254 3 COMMIT 2016-03-07 09:30:57.254 3 CREATE TABLE Raw_V8_DstPrefix ( RouterIP VARCHAR(15) NOT NULL, SysUptime BIGINT, Secs BIGINT, Nsecs BIGINT, Flow_Sequence BIGINT NOT NULL, Engine_Type CHAR(1) NOT NULL, Engine_ID CHAR(1) NOT NULL, Flows BIGINT NOT NULL, dPkts BIGINT NOT NULL, dOctets BIGINT NOT NULL, aFirst BIGINT NOT NULL, aLast BIGINT NOT NULL, Dst_Prefix VARCHAR(15) NOT NULL, Dst_Mask INT NOT NULL, Dst_As INT NOT NULL, Output INT NOT NULL, Stamp VARCHAR(15) NULL ) 2016-03-07 09:30:57.254 3 COMMIT 2016-03-07 09:30:57.254 3 CREATE TABLE Raw_V8_SrcPrefix ( RouterIP VARCHAR(15) NOT NULL, SysUptime BIGINT, Secs BIGINT, Nsecs BIGINT, Flow_Sequence BIGINT NOT NULL, Engine_Type CHAR(1) NOT NULL, Engine_ID CHAR(1) NOT NULL, Flows BIGINT NOT NULL, dPkts BIGINT NOT NULL, dOctets BIGINT NOT NULL, aFirst BIGINT NOT NULL, aLast BIGINT NOT NULL, Src_Prefix VARCHAR(15) NOT NULL, Src_Mask INT NOT NULL, Src_As INT NOT NULL, Input INT NOT NULL, Stamp VARCHAR(15) NULL ) 2016-03-07 09:30:57.254 3 COMMIT 2016-03-07 09:30:57.254 3 CREATE TABLE Raw_V8_Prefix ( RouterIP VARCHAR(15) NOT NULL, SysUptime BIGINT, Secs BIGINT, Nsecs BIGINT, Flow_Sequence BIGINT NOT NULL, Engine_Type CHAR(1) NOT NULL, Engine_ID CHAR(1) NOT NULL, Flows BIGINT NOT NULL, dPkts BIGINT NOT NULL, dOctets BIGINT NOT NULL, aFirst BIGINT NOT NULL, aLast BIGINT NOT NULL, Src_Prefix VARCHAR(15) NOT NULL, Dst_Prefix VARCHAR(15) NOT NULL, Src_Mask INT NOT NULL, Dst_Mask INT NOT NULL, Src_As INT NOT NULL, Dst_As INT NOT NULL, Input INT NOT NULL, Output INT NOT NULL, Stamp VARCHAR(15) NULL ) 2016-03-07 09:30:57.254 3 COMMIT 2016-03-07 09:30:57.254 3 CREATE TABLE Raw_V9 ( RouterIP VARCHAR(15) NOT NULL, SysUptime BIGINT, Secs BIGINT, PackageSequence BIGINT NOT NULL, SourceID BIGINT NOT NULL, SrcAddr VARCHAR(15) NULL, DstAddr VARCHAR(15) NULL, NextHop VARCHAR(15) NULL, Input INT NULL, Output INT NULL, dPkts BIGINT, dOctets BIGINT, aFirst BIGINT, aLast BIGINT, SrcPort INT NULL, DstPort INT NULL, Tcp_Flags INT NULL, Prot INT NULL, TOS INT NULL, Src_As INT NULL, Dst_As INT NULL, Src_Mask INT NULL, Dst_Mask INT NULL, Stamp VARCHAR(15) NULL ) 2016-03-07 09:30:57.254 3 COMMIT 2016-03-07 09:30:57.254 3 CREATE TABLE OptionsTable ( RouterIP VARCHAR(15) NOT NULL, SysUptime BIGINT, Secs BIGINT, PackageSequence BIGINT NOT NULL, SourceID BIGINT NOT NULL, IsScope VARCHAR(1) NOT NULL, Stamp VARCHAR(15) NOT NULL, TemplateID INT NOT NULL, OptionType INT NOT NULL, OptionValue BIGINT NOT NULL ) 2016-03-07 09:30:57.254 3 COMMIT 2016-03-07 09:30:57.254 3 CREATE TABLE SrcAS ( StartDate DATE NOT NULL, StartTime TIME NOT NULL, EndDate DATE NOT NULL, EndTime TIME NOT NULL, RouterIP VARCHAR(15) NOT NULL, Flows BIGINT NOT NULL, Missed BIGINT NOT NULL, dPkts BIGINT NOT NULL, dOctets BIGINT NOT NULL, Src_As VARCHAR(64) NOT NULL, Stamp VARCHAR(15) NULL ) 2016-03-07 09:30:57.254 3 COMMIT 2016-03-07 09:30:57.254 3 CREATE TABLE DstAS ( StartDate DATE NOT NULL, StartTime TIME NOT NULL, EndDate DATE NOT NULL, EndTime TIME NOT NULL, RouterIP VARCHAR(15) NOT NULL, Flows BIGINT NOT NULL, Missed BIGINT NOT NULL, dPkts BIGINT NOT NULL, dOctets BIGINT NOT NULL, Dst_As VARCHAR(64) NOT NULL, Stamp VARCHAR(15) NULL ) 2016-03-07 09:30:57.254 3 COMMIT 2016-03-07 09:30:57.269 3 CREATE TABLE ASMatrix ( StartDate DATE NOT NULL, StartTime TIME NOT NULL, EndDate DATE NOT NULL, EndTime TIME NOT NULL, RouterIP VARCHAR(15) NOT NULL, Flows BIGINT NOT NULL, Missed BIGINT NOT NULL, dPkts BIGINT NOT NULL, dOctets BIGINT NOT NULL, Src_As VARCHAR(64) NOT NULL, Dst_As VARCHAR(64) NOT NULL, Stamp VARCHAR(15) NULL ) 2016-03-07 09:30:57.269 3 COMMIT 2016-03-07 09:30:57.269 3 CREATE TABLE SrcNode ( StartDate DATE NOT NULL, StartTime TIME NOT NULL, EndDate DATE NOT NULL, EndTime TIME NOT NULL, RouterIP VARCHAR(15) NOT NULL, Flows BIGINT NOT NULL, Missed BIGINT NOT NULL, dPkts BIGINT NOT NULL, dOctets BIGINT NOT NULL, SrcNode VARCHAR(15) NOT NULL, Stamp VARCHAR(15) NULL ) 2016-03-07 09:30:57.269 3 COMMIT 2016-03-07 09:30:57.269 3 CREATE TABLE DstNode ( StartDate DATE NOT NULL, StartTime TIME NOT NULL, EndDate DATE NOT NULL, EndTime TIME NOT NULL, RouterIP VARCHAR(15) NOT NULL, Flows BIGINT NOT NULL, Missed BIGINT NOT NULL, dPkts BIGINT NOT NULL, dOctets BIGINT NOT NULL, DstNode VARCHAR(15) NOT NULL, Stamp VARCHAR(15) NULL ) 2016-03-07 09:30:57.269 3 COMMIT 2016-03-07 09:30:57.269 3 CREATE TABLE HostMatrix ( StartDate DATE NOT NULL, StartTime TIME NOT NULL, EndDate DATE NOT NULL, EndTime TIME NOT NULL, RouterIP VARCHAR(15) NOT NULL, Flows BIGINT NOT NULL, Missed BIGINT NOT NULL, dPkts BIGINT NOT NULL, dOctets BIGINT NOT NULL, SrcNode VARCHAR(15) NOT NULL, DstNode VARCHAR(15) NOT NULL, Stamp VARCHAR(15) NULL ) 2016-03-07 09:30:57.269 3 COMMIT 2016-03-07 09:30:57.269 3 CREATE TABLE SrcInt ( StartDate DATE NOT NULL, StartTime TIME NOT NULL, EndDate DATE NOT NULL, EndTime TIME NOT NULL, RouterIP VARCHAR(15) NOT NULL, Flows BIGINT NOT NULL, Missed BIGINT NOT NULL, dPkts BIGINT NOT NULL, dOctets BIGINT NOT NULL, SrcInt INT NOT NULL, Stamp VARCHAR(15) NULL ) 2016-03-07 09:30:57.269 3 COMMIT 2016-03-07 09:30:57.269 3 CREATE TABLE DstInt ( StartDate DATE NOT NULL, StartTime TIME NOT NULL, EndDate DATE NOT NULL, EndTime TIME NOT NULL, RouterIP VARCHAR(15) NOT NULL, Flows BIGINT NOT NULL, Missed BIGINT NOT NULL, dPkts BIGINT NOT NULL, dOctets BIGINT NOT NULL, DstInt INT NOT NULL, Stamp VARCHAR(15) NULL ) 2016-03-07 09:30:57.269 3 COMMIT 2016-03-07 09:30:57.269 3 CREATE TABLE IntMatrix ( StartDate DATE NOT NULL, StartTime TIME NOT NULL, EndDate DATE NOT NULL, EndTime TIME NOT NULL, RouterIP VARCHAR(15) NOT NULL, Flows BIGINT NOT NULL, Missed BIGINT NOT NULL, dPkts BIGINT NOT NULL, dOctets BIGINT NOT NULL, SrcInt INT NOT NULL, DstInt INT NOT NULL, Stamp VARCHAR(15) NULL ) 2016-03-07 09:30:57.269 3 COMMIT 2016-03-07 09:30:57.269 3 CREATE TABLE SrcPrefix ( StartDate DATE NOT NULL, StartTime TIME NOT NULL, EndDate DATE NOT NULL, EndTime TIME NOT NULL, RouterIP VARCHAR(15) NOT NULL, Flows BIGINT NOT NULL, Missed BIGINT NOT NULL, dPkts BIGINT NOT NULL, dOctets BIGINT NOT NULL, SrcPrefix VARCHAR(15) NOT NULL, SrcMask INT NOT NULL, SrcAS INT NOT NULL, Input INT NOT NULL, Stamp VARCHAR(15) NULL ) 2016-03-07 09:30:57.269 3 COMMIT 2016-03-07 09:30:57.285 3 CREATE TABLE DstPrefix ( StartDate DATE NOT NULL, StartTime TIME NOT NULL, EndDate DATE NOT NULL, EndTime TIME NOT NULL, RouterIP VARCHAR(15) NOT NULL, Flows BIGINT NOT NULL, Missed BIGINT NOT NULL, dPkts BIGINT NOT NULL, dOctets BIGINT NOT NULL, DstPrefix VARCHAR(15) NOT NULL, DstMask INT NOT NULL, DstAS INT NOT NULL, Output INT NOT NULL, Stamp VARCHAR(15) NULL ) 2016-03-07 09:30:57.285 3 COMMIT 2016-03-07 09:30:57.285 3 CREATE TABLE PrefixMatrix ( StartDate DATE NOT NULL, StartTime TIME NOT NULL, EndDate DATE NOT NULL, EndTime TIME NOT NULL, RouterIP VARCHAR(15) NOT NULL, Flows BIGINT NOT NULL, Missed BIGINT NOT NULL, dPkts BIGINT NOT NULL, dOctets BIGINT NOT NULL, SrcPrefix VARCHAR(15) NOT NULL, SrcMask INT NOT NULL, SrcAS INT NOT NULL, Input INT NOT NULL, DstPrefix VARCHAR(15) NOT NULL, DstMask INT NOT NULL, DstAS INT NOT NULL, Output INT NOT NULL, Stamp VARCHAR(15) NULL ) 2016-03-07 09:30:57.285 3 COMMIT 2016-03-07 09:30:57.285 3 CREATE TABLE Protocol ( StartDate DATE NOT NULL, StartTime TIME NOT NULL, EndDate DATE NOT NULL, EndTime TIME NOT NULL, RouterIP VARCHAR(15) NOT NULL, Flows BIGINT NOT NULL, Missed BIGINT NOT NULL, dPkts BIGINT NOT NULL, dOctets BIGINT NOT NULL, Protocol VARCHAR(32) NOT NULL, Stamp VARCHAR(15) NULL ) 2016-03-07 09:30:57.285 3 COMMIT 2016-03-07 09:30:57.285 3 ROLLBACK 2016-03-07 09:30:57.285 5 ROLLBACK 2016-03-07 09:30:57.285 8 ROLLBACK 2016-03-07 09:30:57.285 7 ROLLBACK 2016-03-07 09:30:57.285 6 ROLLBACK 2016-03-07 09:30:57.285 4 ROLLBACK 2016-03-07 09:30:57.285 9 ROLLBACK 2016-03-07 09:30:57.285 10 ROLLBACK 2016-03-07 09:30:57.285 11 ROLLBACK 2016-03-07 09:30:57.285 12 ROLLBACK 2016-03-07 09:31:02.371 13 select * from IpSegments 2016-03-07 09:31:02.371 13 COMMIT 2016-03-07 09:32:02.026 14 INSERT INTO Raw_V9 ( RouterIP, SysUptime, Secs, PackageSequence, SourceID, SrcAddr, DstAddr, NextHop, Input, Output, dPkts, dOctets, aFirst, aLast, SrcPort, DstPort, Tcp_Flags, Prot, TOS, Src_As, Dst_As, Src_Mask, Dst_Mask, Stamp ) VALUES( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ('192.168.10.23',888693100,1457339958,30207,0,'178.182.11.16','158.47.120.3','192.168.20.12',1,13,7,1981,888621984,888681988,1660,2405,16,17,0,0,0,24,24,'20160307093202') 2016-03-07 09:32:02.026 14 COMMIT 2016-03-07 09:32:02.026 14 INSERT INTO Raw_V9 ( RouterIP, SysUptime, Secs, PackageSequence, SourceID, SrcAddr, DstAddr, NextHop, Input, Output, dPkts, dOctets, aFirst, aLast, SrcPort, DstPort, Tcp_Flags, Prot, TOS, Src_As, Dst_As, Src_Mask, Dst_Mask, Stamp ) VALUES( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ('192.168.10.23',888693100,1457339958,30207,0,'178.182.11.4','192.168.11.4','192.168.11.4',1,13,2,110,888669256,888669260,49642,2404,24,6,0,0,0,24,24,'20160307093202') 2016-03-07 09:32:02.026 14 COMMIT 2016-03-07 09:32:02.026 14 INSERT INTO Raw_V9 ( RouterIP, SysUptime, Secs, PackageSequence, SourceID, SrcAddr, DstAddr, NextHop, Input, Output, dPkts, dOctets, aFirst, aLast, SrcPort, DstPort, Tcp_Flags, Prot, TOS, Src_As, Dst_As, Src_Mask, Dst_Mask, Stamp ) VALUES( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ('192.168.10.23',888693100,1457339958,30207,0,'192.168.11.4','178.182.11.4','178.182.11.4',13,1,1,58,888669260,888669260,2404,49642,24,6,0,0,0,24,24,'20160307093202') 2016-03-07 09:32:02.026 14 COMMIT 2016-03-07 09:32:32.165 13 ROLLBACK 2016-03-07 09:32:32.181 16 ROLLBACK 2016-03-07 09:32:32.197 14 ROLLBACK 2016-03-07 09:32:32.197 22 ROLLBACK 2016-03-07 09:32:32.197 20 ROLLBACK 2016-03-07 09:32:32.197 15 ROLLBACK 2016-03-07 09:32:32.197 17 ROLLBACK 2016-03-07 09:32:32.197 19 ROLLBACK 2016-03-07 09:32:32.197 21 ROLLBACK 2016-03-07 09:32:32.197 18 ROLLBACK What could be the cause for this behaviour ? Feel free to ask for more info as I'm new and I have surely omitted some important details

Lately we have been tasked with porting some existing DB infrastructure from PostgreSQL to HSQLDB. Yes, I know what you're thinking (why on Earth would anybody do such a thing?). But unfortunately for reasons I can't explain here this is our task and there's no way around it. We don't need to port any data, just the queries: we need to plug an HSQLDB layer into our software to replace the PostgreSQL layer. There are thousands of hardcoded PostgreSQL queries -- and while I thought initially that HSQLDB syntax is similar (somewhat compatible) with PostgreSQL syntax, I was quite surprised. For instance, statements including - SERIAL, - RETURNING, - and other more complex queries involving foreign keys, are not supported in HSQLDB. Note that we are using the latest HSQLDB driver (2.5.1), but if any other version is more compatible that's fine as well. I can of course go through each and every query and ''translate'' them into HSQLDB syntax, but that is not very efficient. Any ideas?

This is a follow up to this previously posted question. I'm using LibreOffice Base to work with some membership data. Further expansion might be possible in the future, but for now, the embedded database is fine, and it provides portability (I could send the file to someone and they can see the data, without haing to connect to an SQL server). The downside: LibreOffice Base uses HSQLDB version 1.8 (from circa 2008). Hence, the available functions are limited. I have several tables and forms and queries to add and retrieve data from my database, including payments. Among the payments, my culprit are the membership renewal. There are several types of membership, each with different prices, and in some cases, different expiration periods. Members can purchase membership for 1 year or 3 years, and they could do it at any given point in time. Each membership expire on December 31st of the calendar year. I have a table (actually a View produced by a filtering Query) called "membershipPayment" where I have memberID paymentID paymentDate expirePeriod 0 0 2012-02-02 1 0 10 2013-02-15 3 0 30 2014-01-10 1 0 60 2015-08-15 3 1 5 2012-03-12 1 1 15 2013-04-01 1 1 20 2013-10-01 3 1 35 2014-02-01 1 The expirePeriod is the number of years of membership associated with that payment. Notice that in the example, in some cases members are renewing membership when their membership is still active due to a previous payment. I need to query this table/view to summarize these payments and have an expiration date and membership status, with one line per member memberID expirationDate memberStatus 0 2019-12-31 active 1 2017-12-31 inactive The memberStatus column should be computed from the expirationDate and CURRENT_DATE. No problems here. My issue is how to compute the expirationDate. I would like to use only a Query, if possible. I don't know how to loop (or if it's even possible to do such thing) along the rows of the "membershipPayment" table to increment the expirationDate based on the value of expirePeriod, or to keep track of a value from a previous row while checking another. I managed to compute the total number of membership purchased on a sigle year using SELECT, SUM() and GROUP BY, according to the answer of the previously cited post. But keeping track of the expirationDate, still no clue. If macros are required, any help or advice is appreciated. Thanks for any advice or comment, even if it's to point that my question is trivial,

I'm looking for a query to get the next IDENTITY value of a HSQL table. Like if I were to insert a new row into a table that has an auto id column, what value would that turn into. I did some research and couldn't find anything sadly. Example: Suppose I have a table like this: CREATE TABLE IF NOT EXISTS Names ( id INTEGER IDENTITY PRIMARY KEY, name VARCHAR(16) ) Now I can insert names without having to specify the id. Instead it gets added automatically and increments itself with each insert. Now I'd like to get whatever value will be next (if I don't manually set it). *Edit:* I think I found a solution: SELECT IDENTITY_START FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'NAMES' AND COLUMN_NAME = 'ID'

I want to group the following data by an user defined period:

+------------+--------+
|    DATE    | Amount |
+------------+--------+
| 2019-03-12 |   300  |
| 2019-03-15 |  1500  |
| 2019-03-25 |  2500  |
| 2019-03-25 |  3000  |
| 2019-04-04 |  5000  |
| 2019-04-27 | 10000  |
+------------+--------+

However, the start and end of the period does not have to align with the start and end of a calendar month, e.g.: User A: period start first of month, period end last of month User B: period start 15th of the month, period end 14th of the following month User C: period start 7th of the month, period end 6th of the following month So: User A:

+--------------+--------+
| Period Start | Amount |
+--------------+--------+
|  2019-01-01  |    0   |
|  2019-02-01  |    0   |
|  2019-03-01  |  7300  |
|  2019-04-01  | 15000  | 
|  2019-05-01  |    0   |
|  2019-06-01  |    0   |
|     ...      |   ...  |
+--------------+--------+

User B:

+--------------+--------+
| Period Start | Amount |
+--------------+--------+
|  2019-01-15  |    0   |
|  2019-02-15  |   300  |
|  2019-03-15  | 12000  |
|  2019-04-15  | 10000  | 
|  2019-05-15  |    0   |
|  2019-06-15  |    0   |
|     ...      |   ...  |
+--------------+--------+

User C:

+--------------+--------+
| Period Start | Amount |
+--------------+--------+
|  2019-01-07  |    0   |
|  2019-02-07  |    0   |
|  2019-03-07  | 12300  |
|  2019-04-07  | 10000  | 
|  2019-05-07  |    0   |
|  2019-06-07  |    0   |
|     ...      |   ...  |
+--------------+--------+

Can this be done in a vendor-agnostic way? If not please show me how it can be done in PostgreSQL 10.x and - if possible - in HSQLDB 2.4.x.

I'm learning how to handle databases, so this is a trivial question, and quite likely has been answered already. I'm just not familiar to the lingo and have no idea how to search for it (although I tried). My data is on LibreOffice, which runs HSQLDB v1.8 (very old engine version). For the moment, I have to stick to it. Using a more up-to-date SQL engine is not possible. I have a payment table with the following structure member_id pay_year pay_unit 0 2016 1 0 2017 2 0 2017 1 0 2017 1 0 2018 2 1 2015 1 1 2018 2 I need to sumarize this table into something like member_id pay_year pay_unit_year 0 2016 1 0 2017 4 0 2018 2 1 2017 1 1 2018 2 The pay_unit_year is the number of years of membership purchased. So eventually I need to operate with the years and units, and getting a single line per member member_id expire_year 0 2023 1 2020 I'm using SELECT DISTINCT member_id, pay_year, pay_unit FROM payments and I'm getting member_id pay_year pay_unit 0 2016 1 0 2017 2 0 2017 1 0 2018 2 1 2017 1 1 2018 2 Is there any way to perform an operation on a subset of pay_unit that has the other columns the same information? I'm aware that SQL has many available date operations, but HSQLDB v1.8 has not them implemented. Thanks for any help, and for your patience

I have some flat files with the following columns; 3 integers, 3 reals, and 1 varchar(20). For querying I need an index that contains both 1 of the integer columns and the varchar column. Each file is around 1.8GB in size with around 38 million rows. Currently I am using a HSQL(Standalone) database to load a file for processing; one database per file. It is very slow to load (120+ min) the file and results in a 4.7GB database file when the database is created with the following options. "Properties" -> { "check_props" -> "true", "shutdown" -> "true", "hsqldb.default_table_type" -> "cached", "sql.syntax_mss" -> "true", "hsqldb.log_data" -> "false", "hsqldb.inc_backup" -> "false" } The file read in batches of 100k records. The read is very fast (almost instant) so I do not think it is the read that is slowing things down. It also takes a very long time to close the connection to the database. I have the option to use Derby, H2, or SQLite. Will any of these result in faster load time and/or smaller database file size in this scenario? If so what are the connection string options that should be used to achieve this? Alternatively, are there different connection string options I can use with HSQL(Standalone) that will reduce the load time and/or database file size? Driver information added. JDBCDriver[ "Name" -> "HSQL(Standalone)", "Driver" -> "org.hsqldb.jdbcDriver", "Protocol" -> "jdbc:hsqldb:file:", "Version" -> 3.1, "Description" -> "HSQL Database Engine (In-Process Mode) - Version 2.3.3 - This ...", "Location" -> "C:\... "] Driver information for the other options available to me. Derby JDBCDriver[ "Name" -> "Derby(Embedded)", "Driver" -> "org.apache.derby.jdbc.EmbeddedDriver", "Protocol" -> "jdbc:derby:", "Version" -> 3.1, "Description" -> "Derby Database Engine (Embedded Mode) - Version 10.12.1.1 - This...", "Location" -> "C:\... "] H2 JDBCDriver[ "Name" -> "H2(Embedded)", "Driver" -> "org.h2.Driver", "Protocol" -> "jdbc:h2:", "Version" -> 3.1, "Description" -> "H2 Database Engine (Embedded Mode) - Version 1.3.176 - This...", "Location" -> "C:\... "] SQLite JDBCDriver[ "Name" -> "SQLite", "Driver" -> "org.sqlite.JDBC", "Protocol" -> "jdbc:sqlite:", "Version" -> 3.1, "Description" -> "SQLite using Zentus-derived JDBC Driver - Version 3.8.11.2", "Location" -> "C:\..."] Additional variants include the below. However, I need it all to run on the client's computer. I believe this excludes server and webserver modes. {"Derby(Embedded)", "Derby(Server)", "H2(Embedded)", "H2(Memory)", "H2(Server)", "HSQL(Memory)", "HSQL(Server)", "HSQL(Standalone)", "SQLite", "SQLite(Memory)"}

I have an application whose HSQL database grows awfully large for some users, but not others. When I received one example where this problem happenned, I discovered that the database is nearly empty - it's almost 16GB of zeroes and only a couple tens of megabytes of real data. Upon performing CHECKPOINT DEFRAG, it shrinks to its real, small size. Unfortunately, I'm not very familiar with this database engine and my online searches have produced little useful information. One interesting thing was that there is an option to automatically perform the defragmentation upon reaching a certain size, which I didn't use. However, I would expect the DB to reuse dead rows and not keep them around, and definitely not zeroed out as this must have a bad effect on performance. Why does the database keep producing so much empty space, which is only interrupted by data here and there? How can I find out what's happening?

I have Hyper SQL database and I don't know what is current character set. Is it set for whole DB or it can be different for every table? How to evaluate encoding in my base?