>
> CAST(? AS INTERVAL(18) DAY)
>
I meant CAST(? AS INTERVAL DAY(18))
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
Hello.
1. You can't legally use expressions as the first parameter of this
function, H2 officially supports only datetime fields:
https://h2database.com/html/functions.html#datediff
The valid syntax is TIMESTAMPDIFF(HOUR, arg1, arg2).
H2 accepts character string literals too for some limited
There is no such data type in the SQL Standard and such data type is quite
exotic even for general purpose programming languages. I don't see any good
reasons to have it in H2.
These numbers are usually stored in two columns or as a character string
value. You can also use JAVA_OBJECT data
Hello.
BACKUP command of H2 works only with persistent databases. You can't use it
with in-memory database.
You can try to use the SCRIPT command, it works with both persistent and
in-memory databases, but it doesn't ensure consistency if some concurrent
transaction commits its data.
--
You
Hello.
It's a long story. Apache Ignite used released versions of H2 internally.
Because it needed more than H2 can provide, some new functionality were
contributed to H2, usually without taking own needs of H2 into account.
Some of this functionality wasn't used by H2 itself and other
Hello.
H2 currently can use index-sorted optimization only when there is a
compatible index with the same order. But you can create indexes with
different order on the same columns.
CREATE TABLE TEST(A INT, B INT);
CREATE INDEX IDX1 ON TEST(A, B);
CREATE INDEX IDX2 ON TEST(A, B DESC);
EXPLAIN
This function is not intended to be called by regular users. Even more, you
should never give ADMIN privileges to users, especially in H2 where users
with such privileges have access to the server and may interact with
operating system. H2 is not a secure container.
DBAs should see what
I don't see any good reasons for such modifications in H2. Result of this
function can be ignored by application. JDBC doesn't require you to read
result of a query.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this
No, it can't. But you can filter out all rows from its result.
SELECT * FROM LINK_SCHEMA('PUBLIC', '', 'jdbc:h2:mem:2', 'sa', '', 'PUBLIC')
WHERE FALSE;
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop
Hello.
H2 never had real support of Oracle-style TRUNC function. H2 has only own
function and its optional second argument is a number of digits.
Recent releases of H2 are more pedantic and they correctly reject the
second argument when it isn't supported due to type of the first argument,
I
Hello.
You really should use an identity column instead of sequence with such
trigger in both Oracle and H2 instead of all these complications with
triggers.
But if you need to execute some more complicated actions in the trigger,
you need to write an own class with implementation of
Hello.
In old versions of H2 there was only one setting for case of identifiers.
With DATABASE_TO_UPPER=FALSE unquoted identifiers aren't converted to upper
case and all identifiers are not case sensitive.
In new versions of H2 there are three separate settings. DATABASE_TO_UPPER,
Hello.
Users of application server and users of database are not related in any
way.
If you use H2 Console servlet in production server, you must add a security
constraint for it, otherwise you can create a security hole in your system,
especially if you use some old version of H2. It looks
Hello.
There is no default username and there is no default password. You need to
specify some by yourself when you create a new database. If you opened the
H2 Console, you need to specify JDBC URL, some username and password, these
username and password will be used to create a user with
Hello.
1. MVStore is also released separately.
https://search.maven.org/artifact/com.h2database/h2-mvstore
2. There are some plans to stop its separate releases.
https://github.com/h2database/h2database/issues/2742
--
You received this message because you are subscribed to the Google Groups
Hello.
If you don't use DB_CLOSE_DELAY setting all these objects can be garbage
collected, but it's a bad practice anyway.
If you use it, it may prevent deletion of database when all connections
were closed or garbage collected, so it can create a memory leak.
--
You received this message
build.cmd jar (or ./build.sh jar on Linux, OS X, etc.) in h2 subdirectory
produces jar file with exactly the same content as included into official
releases in bin/h2-***.jar. It has H2 Console and command-line Shell tool,
so I don't understand what do you mean by tools.
>
>
You need Java 8, 9,
Hello.
It looks like you're trying to execute multiple commands at once and you
have a table value constructor after some data definition command:
try (Connection c = DriverManager.getConnection("jdbc:h2:mem:1")) {
c.createStatement().execute("CREATE TABLE TEST(); VALUES 1");
}
H2 returns
No, you can't rely on such behavior. For example, it will be broken in
you'll add a smaller index with symbol and side columns.
I missed, however, the WHERE clause in your query. With such clause when
only one value is allowed in symbol column and only one value is allowed
in side column your
Hello.
You need an index on (orderingpx, serial) columns. Index on (symbol, side,
orderingpx, serial) columns can't be used by order by orderingpx, serial,
because such index is ordered by symbol, side, ….
--
You received this message because you are subscribed to the Google Groups "H2
Hello.
Yes, there are known issues and most likely they will not be fixed,
development of legacy PageStore backend was stopped many years ago, only
few changes were applied since then.
Versions before 1.4.197 use incorrect indexes for referential constraints
in some cases, this issue was
Hello.
Subqueries with exactly one row can be used as expressions.
INSERT INTO MYTABLE VALUES ((SELECT REMARKS FROM … WHERE TABLE_NAME = … AND
COLUMN_NAME = 'A'), (SELECT … WHERE …));
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To
You must specify a TCP password to use the shutdownTcpServer() due to
security reasons.
https://h2database.com/html/tutorial.html#using_server
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving
Hello.
Why you're using -tcpAllowOthers? Do you really need to connect to your
testing system from other hosts? Combination of this setting
with -ifNotExists effectively creates a remote security hole on your
system, -ifNotExists should not be normally used even for local
connections, local
You can parse JSON with GSON, FasterXML/jackson, javax.json, or some other
library.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to
You need JSON_TABLE for it, but it isn't implemented yet and AFAIK nobody
works on its implementation.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to
Hello.
If you simply want to read a JSON from a file, use
CAST(FILE_READ('/path/to/file.json') AS VARBINARY) FORMAT JSON
or something like it. You can't convert a BLOB to JSON directly, but you
can use VARBINARY in the middle.
If you want to inspect structure of JSON, you need to use some
1. In H2 (and many others) database and schema are different things.
Database may have multiple schemas.
2. Each database has own connection URL. You can't use multiple URLs in one
connection pool and can't use multiple databases in the one pool. Each
database must have own connection pool.
What exactly do you want? Use the *same* database it all connections? Then
choose some name for your in-memory database and pass JDBC URL with this
name to your pool. Don't use jdbc:h2:mem:; use jdbc:h2:mem:something;
And don't use DATABASE_TO_UPPER=FALSE parameter if you need some
Then you need to generate some name for it and pass this name to all
connections. Use some counter, for example.
Don't forget about lifecycle of your in-memory databases. By default, they
are removed when all connections to the specific database were closed. But
when you're using
If your JDBC URL is
jdbc:h2:mem:
the database will be private to the connection, if this URL will be
specified in another connection, this another connection will have another
private database not related with the first one.
If your JDBC URL is
jdbc:h2:mem:someDatabaseName
the database will be
Hello.
Please, read mentioned by you on GitHub section of documentation again,
especially after words
> Sometimes multiple connections to the same in-memory database are required
https://h2database.com/html/features.html#in_memory_databases
--
You received this message because you are
Hello.
It looks like you use some outdated version of H2, because MVCC setting
doesn't exist in recent releases.
PostgreSQL doesn't have the NUMBER data type, and this type is not a native
type of H2 too, why you use it? It is from Oracle, H2 accepts it, but only
for compatibility with
Hello.
H2 doesn't have any built-in JSON decomposition functions yet, you can only
write an own user-defined function for such purpose that can use some
third-party JSON library.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe
Hello.
Queries like SELECT * FROM SOME_TABLE WHERE COUNTRY = something AND STATE =
something and similar will be able to use a normal multi-column index if
*first* columns of this index are COUNTRY and STATE. There is no reason for
additional weird indexed computed combined column, such column
1. Databases usually need more space than CSV. You need to use a normal
file-based database (jdbc:h2:/path/to/database) instead of in-memory one.
2. Built-in CSVREAD() function in recent versions of H2 reads all data into
memory too. If I remember it well, its on-disk buffering was removed due
There is a Max rows combobox on the top.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the
Create a new database... command is not designed to create an in-memory
database, it is for persistent databases only. Actually you can create an
in-memory database with it, but it is removed immediately, because there
are no active connections. You can use mem:testdb;DB_CLOSE_DELAY=-1 to
Hello.
You are not connected to H2 Console, this port is used by some other
application.
You should have .h2.server.properties file in the home directory of your
user. Open it with some text editor, find the line webPort=8082 and change
the port number to a some free port. Then restart the H2
Hello.
It looks like you already have a database with the name *test* in your home
directory and this database has a user with some name and password; you
need to specify them.
If you don't know them, you can try to use the recovery tool, but if you
don't need data in this database, you can
By “current H2” I meant current sources of H2 on GitHub.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To view this
Hello.
SCRIPT command has many flaws, but system sequences are going to be
reimplemented together with identity columns when I or somebody else will
find time to write a better implementation.
https://github.com/h2database/h2database/issues/2339
This command, however, is not designed for such
Hello.
Indexes may be shared between constraints.
In the SQL Standard referential constraints may reference only columns with
already existing unique constraint. It isn't ensured by released versions
of H2, they allow referential constraints without unique constraints and
reuse their indexes.
Hello.
I guess only few people really use it.
If some SET commands fail, you can add some dummy implementations for them
here:
https://github.com/h2database/h2database/blob/93f51bf336de1ce7af598f890a6557052c1d6dc4/h2/src/main/org/h2/command/Parser.java#L8371
But you may run into some much more
H2 doesn't need a lot of memory for plain queries without aggregate and
window functions, large results are stored on the disk automatically. But
queries with aggregate or window functions currently need to load the whole
result into the memory; the only exclusion is the mentioned optimization
Hello.
If you don't have an index on GROUP BY column, you need a lot of memory for
such queries in H2.
You can use the EXPLAIN command to check whether optimization is used or
not.
create table result(id bigint, name varchar, phone int);
-- Without optimization
explain select id, sum(phone)
Don't rely on that undocumented feature, it is a hidden internal part of
archaic implementation of identity columns, most likely it will be removed
in the future. You really should fix your command instead, it currently
abuses an old bug of MySQL.
--
You received this message because you are
Hello.
Your command is obliviously invalid. You can't insert a NULL into column
with NOT NULL constraint. You need to use DEFAULT
INSERT INTO test_table(column1, update_time) VALUES ('test', DEFAULT);
or you can simply remove this column from the list of INSERT columns.
INSERT INTO
Hello.
H2 needs to know some information about the linked database to work with
its tables. Each JDBC driver must implement these basic methods. You need
to report your problem as an issue with Apache Hive it its bugtracker:
https://issues.apache.org/jira/projects/HIVE/issues
There is nothing
Hello.
In depends on version of H2. You should use
DatabaseMetaData.getJDBCMajorVersion()
and DatabaseMetaData.getJDBCMinorVersion() to detect its announced version.
For example, H2 1.4.200 returns 4.1, in the current sources of H2 the
returned version is 4.2.
But this version has a very low
>
> I´m using H2 DB with a JDBC Driver to Oracle, and It´s working now.
No, you aren't. It only means that you use some entirely outdated version
of H2, such as 1.4.197 or older. H2 Console in these versions doesn't throw
any error about incorrect driver in that case, but silently uses the
Hello.
You can use Oracle's driver only when you work with Oracle Database and you
can use H2's driver only when you work with H2 Database. You can't use
driver for one database with another.
Both drivers are available on Maven Central and in other places:
Hello.
You need to post a complete stack trace, first few lines don't contain
enough information about your problem.
You can try to use a more recent version of H2 or even try to build H2 from
its current sources, current H2 is more reliable than 1.4.197. You can also
try to use the legacy
Hello.
No, it isn't possible. H2 Console is a tool that works only with databases.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to
Hello.
Use the recommended procedure with SCRIPT TO 'filename.sql' in your old
version of H2, creation of a new database in the new version, and RUNSCIPT
FROM 'filename.sql' in it.
https://h2database.com/html/tutorial.html#upgrade_backup_restore
Because there is a huge difference between
Hello.
Scan index may return all rows from the find() method (but it should also
return true from isFindUsingFullTableScan()).
Order of elements in the index is not used in this case if index is not
returned from Table.getIndexes().
If you can efficiently lookup rows by values in some
Hello.
When database reads a row from a table, the whole row is constructed and
all its values are read, with exception for large CLOB and BLOB objects. It
means that query like
SELECT id FROM route2 WHERE name LIKE '%something%'
will read a lot of unnecessary data from waypoints column.
Not
Hello.
DDL (data definition language) commands in H2 don't support any parameters.
The SQL Standard also does not specify such support in any way.
You can construct and execute a non-query commands dynamically:
https://h2database.com/html/commands.html#execute_immediate
EXECUTE IMMEDIATE
You can enable the compression only for the whole database, but it can
reduce overall performance.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to
Hello.
It sounds like your database is not normalized properly. I suggest you to
read a some book about database design or at least some articles about
database normalization.
For your specific use case you need to extract all possible values for each
such column into own table that has a
There is a UNION between two queries. One of them is a simple lookup, it
returns a row only if it exists.
Another one is a query from a data change delta table from the results of
inner MERGE command that tries to merge a temporary table with the one row
(VALUES ?) into the target table.
Try
INSERT INTO Questions VALUES (
?,
?,
(WITH S(NAME) AS (VALUES ?)
SELECT id FROM QuestionTypes JOIN S ON QuestionTypes.name = S.NAME
UNION SELECT id FROM FINAL TABLE (
MERGE INTO QuestionTypes T USING S ON T.name = S.NAME WHEN NOT
MATCHED THEN INSERT
Hello.
What exactly do you want? Do you know the id of QuestionBanks? Or you know
only its name and want to lookup the id for the known name of insert a new
row with such name?
If so, you can execute something like
WITH S(NAME) AS (VALUES ?)
SELECT id FROM QuestionBanks JOIN S ON
On Thursday, 13 February 2020 22:33:48 UTC+8, Rinse Lemstra wrote:
>
> Although the csv file does containt column names, they are not respected.
>
Hello.
The documentation of this function describes how to use custom names or use
column names from the file:
Hello.
CSVREAD needs many other changes and I don't have time for them right now.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to
Hello.
You can create user-defined functions with such names by using the CREATE
ALIAS command:
https://h2database.com/html/commands.html#create_alias
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop
Hello.
Building instructions are here:
https://h2database.com/html/build.html#building
Use the jar target.
You need Java 8, 9, 10, 11, or 12. You can set the JAVA_HOME environment
variable to a path to installation of appropriate JDK.
Java 13 and later versions can't be currently used by the
Hello.
Just launch the jar and you'll get a browser window where you'll be able to
login into an existing or a new database.
https://h2database.com/html/tutorial.html#tutorial_starting_h2_console
--
You received this message because you are subscribed to the Google Groups "H2
Database"
Hello.
I guess you meant 1.4.197.
This function with such parameters was never supported by H2. But H2
silently ignores the value of the second parameter if the first parameter
is a DATE, TIMESTAMP, or TIMESTAMP WITH TIME ZONE.
The code for these data types is the same in both versions:
Hello.
The BACKUP command most likely is not related (if it isn't invoked by
another process with different version of H2). But there are three
suspicious options in your connection URL.
1. If you use the retry: subsystem, I assume that you interrupt your
threads that perform calls into
You can also create a user-defined function with name
pg_get_serial_sequence and execute the inner query in it, if you wish.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send
Hello.
In general case, if you want to use multiple databases, you should use only
standard SQL features. However, the SQL Standard does not provide any way
to fetch the current value of the sequence. Such operation has very low
meaning. There are other ways to fetch the generated value during
Hello.
Windows distribution of H2 comes with a completely outdated 32-bit service
wrapper, it needs a 32-bit JDK (not available for recent versions of Java).
You can use some other third-party service wrapper, such as Procrun:
https://commons.apache.org/proper/commons-daemon/procrun.html
It has
Hello.
No, they don't use any wrappers in latest versions of H2, they all are
handled by own implementations in H2.
Actually legacy java.util.* and java.sql.* data types have different bugs
in Java; don't use them if you can.
Documentation of H2 also recommends JSR-310 data types:
Issue with your original query was fixed. If you need the fix right now,
you can build H2 from its current sources.
https://github.com/h2database/h2database
Use the jar target as described here:
https://h2database.com/html/build.html#building
The current sources require JDK 8, 9, 10, 11, or 12.
Hello.
Yes, it looks like a bug. IN predicate may have a row value expressions in
its right side, but this case is not currently supported. Older version
works only because it incorrectly parses them as arrays.
Use can use something like
SELECT col1 FROM x WHERE (col1, col2) IN (VALUES ('a1',
H2 has org.h2.tools.SimpleResultSet for such purpose.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To view this
SqlServerDataTable is not a part of H2 and it cannot be used with H2. You
can use it only with JDBC driver of MS SQL Server when your database is MS
SQL Server.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and
Hello.
Please, describe what exactly do you want?
If you want to create a linked table to read some data from the SQL Server
in H2, you need to add its JDBC driver to the classpath of H2's Java
process. Distribution of H2 does not ship with drivers of other databases.
--
You received this
Hello.
No, there is no such feature in H2. Only some databases have different own
callbacks and that functionality is not standardized, AFAIK the SQL
Standard does not have such features.
You can try to write some own code in the trigger to send some messages to
your clients using the
Unfortunately, you need to recompile H2 with your version of Lucene, or use
Lucene 5.5.5.
1.4.200 was source compatible, but binary incompatible with more recent
versions.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from
jdbc:h2:tcp://localhost//C:/Users/… is obliviously invalid, did you
try jdbc:h2:tcp://localhost/C:/Users/… with one slash after localhost?
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails
Your issue should be fixed in the current master branch. Please, fetch the
latest sources and re-test it.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to
Hello.
It looks like a bug. Thanks for the report!
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To view this
CAST from one NUMERIC/DECIMAL data type to another one with the same scale
is cheap. But if you don't mind that minor difference in precision you can
avoid it.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and
Not exactly, your assumption doesn't work for operands with different scale.
Anyway, the result of addition has larger precision for additional possible
digit, so in some use cases an explicit cast is still necessary.
--
You received this message because you are subscribed to the Google Groups
> If this needs to be a security thing, then it should be a command-line
argument to control it on a per server basis
There is an unsafe command-line argument that allows remote database
creation as it was in older versions, do you really read the
whole “Creating New Databases” section? Just
Hello.
1. Unsupported type 17 is usually thrown when multiple versions of H2 were
used with the same database file (versions of TCP clients don't matter,
only versions of servers and embedded connections should match). For
example, IDEA users are affected, because IDEA uses 1.4.196 it its
Hello.
Security fixes were applied and H2 does not allow remote creation of
database by default any more because it actually creates a remote security
hole on your server.
Unfortunately, a weird error message is generated by the version that you
use. 1.4.200 throws an error with better
Hello.
No, there is no such feature in H2 (and in the SQL Standard too).
You can add a new column at position that you need, copy data to it with
UPDATE, recreate indexes and constraints (if old column was referenced by
something), drop old column, and finally rename a new one.
--
You
The issue with your query was fixed.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web
Hello.
CTEs are experimental in H2 and have known issues, you can find some on
GitHub.
Support of data change delta tables is a relatively new functionality in H2
and I think nobody tested them in combination with CTE earlier.
--
You received this message because you are subscribed to the
You really need to decrease cache sizes of your databases. You have too
many of them for the default setting.
And if you want to see the real memory use, you need to compute the
retained sizes, but on large dumps they will be computed very slowly.
--
You received this message because you are
Hello.
When you open many databases at once you need to decrease the cache size in
all of them or at least in the most of them to a some reasonable size for
your use case:
https://h2database.com/html/commands.html#set_cache_size
--
You received this message because you are subscribed to the
Issue with H2 Console was fixed.
https://github.com/h2database/h2database
https://h2database.com/html/build.html#building
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an
This is the same issue. H2 Console reads the formatted value with
JavaScript where spaces were already removed by the browser; you can
add white-space: pre style to cells (if your browser has development tools)
as a workaround.
The database is not affected.
try (Connection c =
If you want to assign row numbers to a some column, you can use the
following command in both SQL Server and H2:
CREATE TABLE TEST(ID INT PRIMARY KEY, NUMBER INT);
INSERT INTO TEST(ID) VALUES (1), (2), (5), (6), (7), (10);
WITH T(ID, RN) AS (SELECT ID, ROW_NUMBER() OVER(ORDER BY (SELECT 1))
Hello.
They aren't removed by H2. Web browser ignores them when it displays a
table.
Perhaps H2 Console should use some style such as white-space: pre to make
them visible.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from
Hello.
I tried to execute such command in SQL Server 2012, 2017, and 2019 on
db<>fiddle and I must say that it leads to undefined results in this
database system. It can set column to 1 in all rows, in can set column to 1
in some rows and to some other number in other rows depending on content
It was already fixed, you can build H2 from its current sources available
on GitHub, if you wish.
https://github.com/h2database/h2database
https://h2database.com/html/build.html#building
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To
201 - 300 of 537 matches
Mail list logo
