Solved

HSQLDB

Posted on 2016-07-31
6
39 Views
Last Modified: 2016-08-07
I am reading some tutorials and in one of them there is code how to use JSP to connect to DB directly (I know it's bad practice even tutorials mention it). Code works, but what puzzles me is WHERE does it store newly created table "COUNTER"?
There was no special setup for HSQLDB - I've just downloaded it, unpacked it and run runServer.bat
which has in it:
cd ..\data
@java -classpath ../lib/hsqldb.jar org.hsqldb.server.Server %1 %2 %3 %4 %5 %6 %7 %8 %9

Open in new window

Then CMD message informed me that server is up and running:
C:\hsqldb\bin>cd ..\data
[Server@4517d9a3]: [Thread[main,5,main]]: checkRunning(false) entered
[Server@4517d9a3]: [Thread[main,5,main]]: checkRunning(false) exited
[Server@4517d9a3]: Startup sequence initiated from main() method
[Server@4517d9a3]: Could not load properties from file
[Server@4517d9a3]: Using cli/default properties only
[Server@4517d9a3]: Initiating startup sequence...
[Server@4517d9a3]: Server socket opened successfully in 6 ms.
[Server@4517d9a3]: Database [index=0, id=0, db=file:test, alias=] opened successfully in 261 ms.
[Server@4517d9a3]: Startup sequence completed in 269 ms.
[Server@4517d9a3]: 2016-07-31 13:32:02.444 HSQLDB server 2.3.4 is online on port 9001
[Server@4517d9a3]: To close normally, connect and execute SHUTDOWN SQL
[Server@4517d9a3]: From command line, use [Ctrl]+[C] to abort abruptly

Open in new window


Then I executed JSP code:
<%--
  Run this file before using the other JSP pages in this directory to
  set up your database tables.
--%>

<%@ taglib prefix="sql" uri="http://java.sun.com/jstl/sql" %>

<%-- Sets up 'counter' table --%>
<sql:update>
  create table counter (
    counter integer
  )
</sql:update>
<sql:update>
  insert into counter(counter) values(0)
</sql:update>

Open in new window


and another JSP page reads from DB:
<%@ taglib prefix="c" uri="http://java.sun.com/jstl/core" %>
<%@ taglib prefix="sql" uri="http://java.sun.com/jstl/sql" %>

<sql:transaction>

  <sql:update>
    update counter set counter = counter + 1
  </sql:update>
  <sql:query var="result">
    select * from counter
  </sql:query>
  <c:set var="count" value="${result.rows[0].counter}" />

</sql:transaction>

<%-- Display the count --%>
Number of visitors to this page:  <c:out value="${count}"/>

Open in new window


But when I try to find table COUNTER there is no that table?
Not in INFORMATION_SCHEMA, not in PUBLIC, not in SYSTEM_LOBS?
Where could it go?
Screen-Shot-07-31-16-at-01.45-PM.PNG
0
Comment
Question by:KPax
  • 4
  • 2
6 Comments
 
LVL 15

Assisted Solution

by:gurpsbassi
gurpsbassi earned 500 total points
ID: 41736865
Is it stored in a file called test.data?
1
 

Author Comment

by:KPax
ID: 41737918
No, I've searched whole hard disk and there is no such file. But I found on desktop (?) folder: sample.tmp which is empty and sample.lck (looks like small binary content there) and  sample.script with following content:

SET DATABASE UNIQUE NAME HSQLDB563D1A6A43
SET DATABASE GC 0
SET DATABASE DEFAULT RESULT MEMORY ROWS 0
SET DATABASE EVENT LOG LEVEL 0
SET DATABASE TRANSACTION CONTROL LOCKS
SET DATABASE DEFAULT ISOLATION LEVEL READ COMMITTED
SET DATABASE TRANSACTION ROLLBACK ON CONFLICT TRUE
SET DATABASE TEXT TABLE DEFAULTS ''
SET DATABASE SQL NAMES FALSE
SET DATABASE SQL REFERENCES FALSE
SET DATABASE SQL SIZE TRUE
SET DATABASE SQL TYPES FALSE
SET DATABASE SQL TDC DELETE TRUE
SET DATABASE SQL TDC UPDATE TRUE
SET DATABASE SQL TRANSLATE TTI TYPES TRUE
SET DATABASE SQL TRANSLATE TTI TYPES TRUE
SET DATABASE SQL CONCAT NULLS TRUE
SET DATABASE SQL UNIQUE NULLS TRUE
SET DATABASE SQL CONVERT TRUNCATE TRUE
SET DATABASE SQL AVG SCALE 0
SET DATABASE SQL DOUBLE NAN TRUE
SET FILES WRITE DELAY 500 MILLIS
SET FILES BACKUP INCREMENT TRUE
SET FILES CACHE SIZE 10000
SET FILES CACHE ROWS 50000
SET FILES SCALE 32
SET FILES LOB SCALE 32
SET FILES DEFRAG 0
SET FILES NIO TRUE
SET FILES NIO SIZE 256
SET FILES LOG TRUE
SET FILES LOG SIZE 50
CREATE USER SA PASSWORD DIGEST 'd41d8cd98f00b204e9800998ecf8427e'
ALTER USER SA SET LOCAL TRUE
CREATE SCHEMA PUBLIC AUTHORIZATION DBA
SET SCHEMA PUBLIC
CREATE MEMORY TABLE PUBLIC.COUNTER(COUNTER INTEGER)
CREATE MEMORY TABLE PUBLIC.EMPLOYEES(ID INTEGER NOT NULL,AGE INTEGER NOT NULL,FIRST VARCHAR(255),LAST VARCHAR(255))
CREATE MEMORY TABLE PUBLIC.USERS(NAME VARCHAR(255),IQ INTEGER)
ALTER SEQUENCE SYSTEM_LOBS.LOB_ID RESTART WITH 1
SET DATABASE DEFAULT INITIAL SCHEMA PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.SQL_IDENTIFIER TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.YES_OR_NO TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.TIME_STAMP TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.CARDINAL_NUMBER TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.CHARACTER_DATA TO PUBLIC
GRANT DBA TO SA
SET SCHEMA SYSTEM_LOBS
INSERT INTO BLOCKS VALUES(0,2147483647,0)
SET SCHEMA PUBLIC
INSERT INTO COUNTER VALUES(8)
INSERT INTO EMPLOYEES VALUES(100,18,'Zara','Ali')
INSERT INTO EMPLOYEES VALUES(101,25,'Mahnaz','Fatma')
INSERT INTO EMPLOYEES VALUES(102,30,'Zaid','Khan')
INSERT INTO EMPLOYEES VALUES(103,28,'Sumit','Mittal')
INSERT INTO USERS VALUES(NULL,132)
INSERT INTO USERS VALUES(NULL,121)
INSERT INTO USERS VALUES(NULL,140)
INSERT INTO USERS VALUES(NULL,162)
INSERT INTO USERS VALUES(NULL,149)
INSERT INTO USERS VALUES('Richard',NULL)
INSERT INTO USERS VALUES('Jonathan',NULL)
INSERT INTO USERS VALUES('Liz',NULL)
INSERT INTO USERS VALUES('Michael',NULL)
INSERT INTO USERS VALUES('Rachel',NULL)

Open in new window


Tables COUNTER, EMPLOYEES and USERS are created by code from JSP/JSTL tutorial.
But still when I look at schema PUBLIC there is nothing there and if I try to run query
select * from public.counter
or
select * from counter
there is:
user lacks privilege or object not found: COUNTER / Error Code: -5501 / State: 42501
There is just one user SA without password.
The weirdest thing is that code works OK. It created tables, inserted data, reads and updates but I can' find where?
0
 
LVL 15

Assisted Solution

by:gurpsbassi
gurpsbassi earned 500 total points
ID: 41738979
HSQLDB runs in RAM or File system mode. The reason I was looking for the test.data file was because your log file suggests its running in file mode so should be storing its data in a file called test.data according to your log output.
1
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:KPax
ID: 41739494
There is also folder: C:\hsqldb\data with content:
test.tmp <-- folder
test.lck
test.log
test.properties
test

It is storing data on file system --> I see files on my desktop:
sample.log
sample.properties
sample.script
sample.lck
BTW how can I setup DBMS to store those files somewhere else instead on my desktop?

sample.script looks like:

SET DATABASE UNIQUE NAME HSQLDB564C54350A
SET DATABASE GC 0
SET DATABASE DEFAULT RESULT MEMORY ROWS 0
SET DATABASE EVENT LOG LEVEL 0
SET DATABASE TRANSACTION CONTROL LOCKS
SET DATABASE DEFAULT ISOLATION LEVEL READ COMMITTED
SET DATABASE TRANSACTION ROLLBACK ON CONFLICT TRUE
SET DATABASE TEXT TABLE DEFAULTS ''
SET DATABASE SQL NAMES FALSE
SET DATABASE SQL REFERENCES FALSE
SET DATABASE SQL SIZE TRUE
SET DATABASE SQL TYPES FALSE
SET DATABASE SQL TDC DELETE TRUE
SET DATABASE SQL TDC UPDATE TRUE
SET DATABASE SQL TRANSLATE TTI TYPES TRUE
SET DATABASE SQL TRANSLATE TTI TYPES TRUE
SET DATABASE SQL CONCAT NULLS TRUE
SET DATABASE SQL UNIQUE NULLS TRUE
SET DATABASE SQL CONVERT TRUNCATE TRUE
SET DATABASE SQL AVG SCALE 0
SET DATABASE SQL DOUBLE NAN TRUE
SET FILES WRITE DELAY 500 MILLIS
SET FILES BACKUP INCREMENT TRUE
SET FILES CACHE SIZE 10000
SET FILES CACHE ROWS 50000
SET FILES SCALE 32
SET FILES LOB SCALE 32
SET FILES DEFRAG 0
SET FILES NIO TRUE
SET FILES NIO SIZE 256
SET FILES LOG TRUE
SET FILES LOG SIZE 50
CREATE USER SA PASSWORD DIGEST 'd41d8cd98f00b204e9800998ecf8427e'
ALTER USER SA SET LOCAL TRUE
CREATE SCHEMA PUBLIC AUTHORIZATION DBA
ALTER SEQUENCE SYSTEM_LOBS.LOB_ID RESTART WITH 1
SET DATABASE DEFAULT INITIAL SCHEMA PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.SQL_IDENTIFIER TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.YES_OR_NO TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.TIME_STAMP TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.CARDINAL_NUMBER TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.CHARACTER_DATA TO PUBLIC
GRANT DBA TO SA
SET SCHEMA SYSTEM_LOBS
INSERT INTO BLOCKS VALUES(0,2147483647,0)

Open in new window


I don't understand how comes that JSP/JSTL can create tables, write and read data even without credentials while none of DB Clients I even tried can't even "see" those tables, no matter if I try "memory" or "file system" mode? At least I was expecting that it's own tool HSQL Data Base Manager can "see" it?
0
 

Accepted Solution

by:
KPax earned 0 total points
ID: 41739795
I finally found it:
web.xml
    <!-- By default, we use an embedded HSQLDB as our data source. -->
    <context-param>
        <param-name>javax.servlet.jsp.jstl.sql.dataSource</param-name>
        <param-value>jdbc:hsqldb:sample,org.hsqldb.jdbcDriver,sa</param-value>
    </context-param>

Open in new window


But this path: jdbc:hsqldb:sample
as there was no given drive letter and letter path somehow ended up on desktop, so final path in HSQL Database Manager is:

jdbc:hsqldb:C:\Users\<MyWindowsUsername>\Desktop\sample

and type ip HSQL Database Engine Standalone

jdbc:hsqldb:C:\Users\nenad\Desktop\sample

log says:
[Server@4517d9a3]: 2016-08-02 22:40:05.622 HSQLDB server 2.3.4 is online on port 9001
and server is started with @java -classpath ../lib/hsqldb.jar org.hsqldb.server.Server %1 %2 %3 %4 %5 %6 %7 %8 %9
so I was expecting that DB/schema would be accessible on port 9001 but it isn't, I can get to it only through direct file access.
0
 

Author Closing Comment

by:KPax
ID: 41746092
Because my solution is final solution to this problem.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
HashMap Vs TreeMap 12 49
oracle query help 29 64
one-way data "masking" MD5 sql 26 104
eclipse package explorer vs project explorer view 2 34
Creating and Managing Databases with phpMyAdmin in cPanel.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
The viewer will learn how to implement Singleton Design Pattern in Java.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now