H2 Database Engine
1 of 76
Table of Contents
H2 Database Engine................................................................................................................................................................................. 1
Quickstart............................................................................................................................................................................................... 6
The H2 Console Application................................................................................................................................................................. 6
Step-by-Step................................................................................................................................................................................ 6
Installation............................................................................................................................................................................................ 10
Requirements................................................................................................................................................................................... 10
Installing the Software...................................................................................................................................................................... 10
Directory Structure........................................................................................................................................................................... 10
Tutorial................................................................................................................................................................................................. 11
Starting and Using the H2 Console..................................................................................................................................................... 11
Firewall...................................................................................................................................................................................... 11
Native Version............................................................................................................................................................................ 11
Testing Java............................................................................................................................................................................... 12
Error Message 'Port is in use'....................................................................................................................................................... 12
Starting Successfully................................................................................................................................................................... 12
Connecting to the Server using a Browser..................................................................................................................................... 12
Multiple Concurrent Sessions....................................................................................................................................................... 12
Application Properties................................................................................................................................................................. 12
Login......................................................................................................................................................................................... 12
Error Messages........................................................................................................................................................................... 12
Adding Database Drivers............................................................................................................................................................. 13
Using the Application.................................................................................................................................................................. 13
Inserting Table Names or Column Names..................................................................................................................................... 13
Disconnecting and Stopping the Application.................................................................................................................................. 13
Features................................................................................................................................................................................................ 14
Comparison to Other Database Engines.............................................................................................................................................. 14
Supported Platforms......................................................................................................................................................................... 15
Why Java......................................................................................................................................................................................... 15
Connection Modes............................................................................................................................................................................ 15
Security Features............................................................................................................................................................................. 15
Connecting to a Database using JDBC................................................................................................................................................ 16
Creating New Databases............................................................................................................................................................. 16
Database URL Overview.................................................................................................................................................................... 16
Connecting to an Embedded (Local) Database............................................................................................................................... 16
Memory-Only Databases............................................................................................................................................................. 17
Passwords: Using Char Arrays instead of Strings........................................................................................................................... 17
Using Secure Passwords.............................................................................................................................................................. 17
Connecting to a Database with File Encryption.............................................................................................................................. 18
Database File Locking................................................................................................................................................................. 18
Opening a Database Only if it Already Exists................................................................................................................................. 18
Passing the User Name and/or Password in the URL...................................................................................................................... 18
Changing Other Settings when Opening a Connection.................................................................................................................... 18
Multiple Connections......................................................................................................................................................................... 19
Opening Multiple Databases at the Same Time.............................................................................................................................. 19
Multiple Connections to the Same Database: Client/Server............................................................................................................. 19
Multithreading Support................................................................................................................................................................ 19
Locking, Lock-Timeout, Deadlocks................................................................................................................................................ 19
Database File Layout........................................................................................................................................................................ 20
Moving and Renaming Database Files........................................................................................................................................... 20
Backup...................................................................................................................................................................................... 20
Logging and Recovery...................................................................................................................................................................... 20
Compatibility Modes.......................................................................................................................................................................... 21
Using the Trace Options.................................................................................................................................................................... 21
Trace Options............................................................................................................................................................................. 21
Setting the Maximum Size of the Trace File................................................................................................................................... 21
Java Code Generation................................................................................................................................................................. 22
Enabling the Trace Option at Runtime by Manually Creating a File.................................................................................................. 22
Read Only Databases........................................................................................................................................................................ 22
Binary and Text Storage Formats....................................................................................................................................................... 22
Graceful Handling of Low Disk Space Situations.................................................................................................................................. 23
Advanced Topics.................................................................................................................................................................................... 24
Performance Tuning......................................................................................................................................................................... 24
Virus Scanners........................................................................................................................................................................... 24
Index Usage............................................................................................................................................................................... 24
Optimizer................................................................................................................................................................................... 24
Expression Optimization.............................................................................................................................................................. 24
COUNT(*) Optimization............................................................................................................................................................... 24
Result Sets...................................................................................................................................................................................... 24
Limiting the Number of Rows....................................................................................................................................................... 24
Large Result Sets and External Sorting......................................................................................................................................... 24
Large Objects................................................................................................................................................................................... 25
Storing and Reading Large Objects.............................................................................................................................................. 25
Transaction Isolation........................................................................................................................................................................ 25
2 of 76
Table Level Locking.......................................................................................................................................................................... 25
Lock Timeout............................................................................................................................................................................. 25
Clustering / High Availability.............................................................................................................................................................. 25
Using the CreateCluster Tool....................................................................................................................................................... 26
Two Phase Commit........................................................................................................................................................................... 26
Compatibility.................................................................................................................................................................................... 26
Transaction Commit when Autocommit is On................................................................................................................................ 26
Keywords / Reserved Words........................................................................................................................................................ 27
ODBC Driver.................................................................................................................................................................................... 27
ODBC Installation....................................................................................................................................................................... 27
Log Option................................................................................................................................................................................. 27
Security Considerations............................................................................................................................................................... 27
Uninstalling................................................................................................................................................................................ 27
ACID............................................................................................................................................................................................... 28
Atomicity................................................................................................................................................................................... 28
Consistency................................................................................................................................................................................ 28
Isolation.................................................................................................................................................................................... 28
Durability................................................................................................................................................................................... 28
Running the Durability Test......................................................................................................................................................... 29
Using the Recover Tool..................................................................................................................................................................... 29
File Locking Protocols....................................................................................................................................................................... 29
File Locking Method 'File'............................................................................................................................................................. 29
File Locking Method 'Socket'........................................................................................................................................................ 30
Security Protocols............................................................................................................................................................................. 30
User Password Encryption........................................................................................................................................................... 30
File Encryption............................................................................................................................................................................ 30
SSL/TLS Connections.................................................................................................................................................................. 31
HTTPS Connections.................................................................................................................................................................... 31
Glossary and Links............................................................................................................................................................................ 31
SQL Grammar........................................................................................................................................................................................ 32
Commands (Data Manipulation)......................................................................................................................................................... 32
Commands (Data Definition)............................................................................................................................................................. 32
Commands (Other)........................................................................................................................................................................... 33
Other Grammar................................................................................................................................................................................ 33
SELECT...................................................................................................................................................................................... 34
INSERT...................................................................................................................................................................................... 34
UPDATE..................................................................................................................................................................................... 34
DELETE..................................................................................................................................................................................... 34
CALL......................................................................................................................................................................................... 35
RUNSCRIPT................................................................................................................................................................................ 35
SCRIPT...................................................................................................................................................................................... 35
EXPLAIN.................................................................................................................................................................................... 35
CREATE TABLE........................................................................................................................................................................... 36
CREATE LINKED TABLE............................................................................................................................................................... 36
CREATE SCHEMA........................................................................................................................................................................ 36
CREATE SEQUENCE.................................................................................................................................................................... 36
CREATE USER............................................................................................................................................................................ 37
CREATE INDEX........................................................................................................................................................................... 37
CREATE TRIGGER....................................................................................................................................................................... 37
CREATE ROLE............................................................................................................................................................................ 37
CREATE VIEW............................................................................................................................................................................ 38
ALTER INDEX RENAME................................................................................................................................................................ 38
ALTER SEQUENCE...................................................................................................................................................................... 38
ALTER TABLE ADD...................................................................................................................................................................... 38
ALTER TABLE ADD CONSTRAINT................................................................................................................................................. 38
ALTER TABLE ALTER COLUMN RENAME........................................................................................................................................ 39
ALTER TABLE ALTER COLUMN SET NULL...................................................................................................................................... 39
ALTER TABLE ALTER COLUMN SET NOT NULL.............................................................................................................................. 39
ALTER TABLE ALTER COLUMN SET DEFAULT................................................................................................................................ 39
ALTER TABLE ALTER COLUMN RESTART...................................................................................................................................... 40
ALTER TABLE ALTER COLUMN..................................................................................................................................................... 40
ALTER TABLE DROP CONSTRAINT............................................................................................................................................... 40
ALTER TABLE RENAME................................................................................................................................................................ 40
ALTER USER SET PASSWORD...................................................................................................................................................... 41
ALTER USER ADMIN................................................................................................................................................................... 41
ALTER USER RENAME................................................................................................................................................................. 41
CREATE ALIAS............................................................................................................................................................................ 41
DROP ALIAS............................................................................................................................................................................... 42
DROP TABLE.............................................................................................................................................................................. 42
DROP INDEX.............................................................................................................................................................................. 42
DROP SCHEMA........................................................................................................................................................................... 42
DROP USER............................................................................................................................................................................... 42
DROP ROLE............................................................................................................................................................................... 43
DROP VIEW............................................................................................................................................................................... 43
HELP......................................................................................................................................................................................... 43
COMMIT.................................................................................................................................................................................... 43
CHECKPOINT............................................................................................................................................................................. 43
CHECKPOINT SYNC.................................................................................................................................................................... 44
3 of 76
SAVEPOINT................................................................................................................................................................................ 44
SET AUTOCOMMIT..................................................................................................................................................................... 44
ROLLBACK................................................................................................................................................................................. 44
SHUTDOWN............................................................................................................................................................................... 44
GRANT RIGHT............................................................................................................................................................................ 45
GRANT ROLE.............................................................................................................................................................................. 45
REVOKE RIGHT.......................................................................................................................................................................... 45
REVOKE ROLE............................................................................................................................................................................ 45
SET CACHE_SIZE........................................................................................................................................................................ 45
SET MAX_MEMORY_ROWS.......................................................................................................................................................... 46
SET CLUSTER............................................................................................................................................................................. 46
COMMIT TRANSACTION.............................................................................................................................................................. 46
ROLLBACK TRANSACTION........................................................................................................................................................... 46
PREPARE COMMIT...................................................................................................................................................................... 47
SET COLLATION......................................................................................................................................................................... 47
SET DEFAULT_LOCK_TIMEOUT................................................................................................................................................... 47
SET DEFAULT_TABLE_TYPE........................................................................................................................................................ 47
SET DATABASE_EVENT_LISTENER............................................................................................................................................... 48
SET LOCK_TIMEOUT................................................................................................................................................................... 48
SET MAX_LOG_SIZE................................................................................................................................................................... 48
SET MODE................................................................................................................................................................................. 48
SET PASSWORD......................................................................................................................................................................... 49
SET SALT HASH.......................................................................................................................................................................... 49
SET TRACE_LEVEL...................................................................................................................................................................... 49
SET TRACE_MAX_FILE_SIZE........................................................................................................................................................ 49
SET WRITE_DELAY..................................................................................................................................................................... 50
constraint.................................................................................................................................................................................. 50
referentialConstraint................................................................................................................................................................... 50
tableExpression.......................................................................................................................................................................... 50
order......................................................................................................................................................................................... 51
expression................................................................................................................................................................................. 51
andCondition.............................................................................................................................................................................. 51
condition.................................................................................................................................................................................... 51
compare.................................................................................................................................................................................... 51
concat....................................................................................................................................................................................... 52
sum........................................................................................................................................................................................... 52
factor........................................................................................................................................................................................ 52
term.......................................................................................................................................................................................... 52
aggregate.................................................................................................................................................................................. 53
cipher........................................................................................................................................................................................ 53
dataType................................................................................................................................................................................... 53
selectExpression......................................................................................................................................................................... 53
Functions.............................................................................................................................................................................................. 54
Numeric Functions............................................................................................................................................................................ 54
String Functions............................................................................................................................................................................... 54
Time and Date Functions.................................................................................................................................................................. 54
System Functions............................................................................................................................................................................. 55
ABS........................................................................................................................................................................................... 55
ACOS, ASIN, ATAN, COS, COT, SIN, TAN...................................................................................................................................... 55
ATAN2....................................................................................................................................................................................... 55
BITAND, BITOR, BITXOR, MOD................................................................................................................................................... 55
CEILING, DEGREES, EXP, FLOOR, LOG, LOG10, RADIANS, SQRT.................................................................................................... 56
PI.............................................................................................................................................................................................. 56
POWER...................................................................................................................................................................................... 56
RAND........................................................................................................................................................................................ 56
ROUND...................................................................................................................................................................................... 56
ROUNDMAGIC............................................................................................................................................................................ 57
SECURE_RAND........................................................................................................................................................................... 57
ENCRYPT................................................................................................................................................................................... 57
DECRYPT................................................................................................................................................................................... 57
HASH........................................................................................................................................................................................ 57
SIGN......................................................................................................................................................................................... 58
TRUNCATE................................................................................................................................................................................. 58
COMPRESS................................................................................................................................................................................. 58
EXPAND..................................................................................................................................................................................... 58
ASCII........................................................................................................................................................................................ 58
BIT_LENGTH.............................................................................................................................................................................. 59
LENGTH, CHAR_LENGTH, CHARACTER_LENGTH........................................................................................................................... 59
OCTET_LENGTH......................................................................................................................................................................... 59
CHAR........................................................................................................................................................................................ 59
CONCAT.................................................................................................................................................................................... 59
DIFFERENCE.............................................................................................................................................................................. 60
HEXTORAW, RAWTOHEX............................................................................................................................................................ 60
INSERT...................................................................................................................................................................................... 60
LCASE, LOWER, UCASE, UPPER................................................................................................................................................... 60
LEFT, RIGHT.............................................................................................................................................................................. 60
LOCATE..................................................................................................................................................................................... 61
POSITION.................................................................................................................................................................................. 61
4 of 76
LTRIM, RTRIM............................................................................................................................................................................ 61
TRIM......................................................................................................................................................................................... 61
REPEAT..................................................................................................................................................................................... 61
REPLACE.................................................................................................................................................................................... 62
SOUNDEX.................................................................................................................................................................................. 62
SPACE....................................................................................................................................................................................... 62
STRINGDECODE......................................................................................................................................................................... 62
STRINGENCODE......................................................................................................................................................................... 62
STRINGTOUTF8.......................................................................................................................................................................... 63
SUBSTR, SUBSTRING.................................................................................................................................................................. 63
UTF8TOSTRING.......................................................................................................................................................................... 63
DATABASE................................................................................................................................................................................. 63
USER, CURRENT_USER............................................................................................................................................................... 63
IDENTITY.................................................................................................................................................................................. 64
AUTOCOMMIT............................................................................................................................................................................ 64
READONLY................................................................................................................................................................................. 64
IFNULL...................................................................................................................................................................................... 64
CASEWHEN................................................................................................................................................................................ 64
CONVERT.................................................................................................................................................................................. 65
CAST......................................................................................................................................................................................... 65
COALESCE................................................................................................................................................................................. 65
NULLIF...................................................................................................................................................................................... 65
CURDATE, CURRENT_DATE, SYSDATE, TODAY............................................................................................................................. 65
CURTIME, CURRENT_TIME.......................................................................................................................................................... 66
NOW, CURRENT_TIMESTAMP...................................................................................................................................................... 66
DATEDIFF.................................................................................................................................................................................. 66
DAYNAME, MONTHNAME............................................................................................................................................................ 66
DAYOFMONTH, DAYOFWEEK, DAYOFYEAR................................................................................................................................... 66
YEAR, QUARTER, MONTH, WEEK................................................................................................................................................. 67
HOUR, MINUTE, SECOND............................................................................................................................................................ 67
Data Types............................................................................................................................................................................................ 68
BOOLEAN.................................................................................................................................................................................. 68
TINYINT.................................................................................................................................................................................... 68
SMALLINT.................................................................................................................................................................................. 68
INTEGER................................................................................................................................................................................... 68
DOUBLE..................................................................................................................................................................................... 68
IDENTITY.................................................................................................................................................................................. 68
DECIMAL................................................................................................................................................................................... 69
VARCHAR................................................................................................................................................................................... 69
OTHER...................................................................................................................................................................................... 69
VARBINARY................................................................................................................................................................................ 69
CLOB......................................................................................................................................................................................... 69
BLOB......................................................................................................................................................................................... 69
DATE......................................................................................................................................................................................... 69
TIME......................................................................................................................................................................................... 69
TIMESTAMP............................................................................................................................................................................... 70
Build..................................................................................................................................................................................................... 71
Portability........................................................................................................................................................................................ 71
Environment.................................................................................................................................................................................... 71
Building the Software........................................................................................................................................................................ 71
History.................................................................................................................................................................................................. 72
History of this Database Engine......................................................................................................................................................... 72
Change Log..................................................................................................................................................................................... 72
Frequently Asked Questions.................................................................................................................................................................... 75
Is this Database Engine Open Source?............................................................................................................................................... 75
Is it Reliable?................................................................................................................................................................................... 75
How to Create a New Database?........................................................................................................................................................ 75
How to Connect to a Database?......................................................................................................................................................... 75
Where are the Database Files Stored?................................................................................................................................................ 75
License.................................................................................................................................................................................................. 76
Overview......................................................................................................................................................................................... 76
Copyright......................................................................................................................................................................................... 76
License Terms.................................................................................................................................................................................. 76
5 of 76
