[h2] Re: How to tell .db version without opening it

Hi! mv.db files have two identical 4096-byte blocks in the beginning. You can read one of them, discard trailing bytes 0x00, and convert previous bytes to a string. This string contains comma-separated properties in the name:value format. You can check value of the property format. 1 was used

[h2] Re: Can aliases made by CREATE ALIAS , be retrieved by SQL?

Hello! In H2 2.* you can read them from standard INFORMATION_SCHEMA.ROUTINES and INFORMATION_SCHEMA.PARAMETERS views. In historic versions of H2 you need non-standard INFORMATION_SCHEMA.FUNCTION_ALIASES and INFORMATION_SCHEMA.FUNCTION_COLUMNS tables. -- You received this message because you

Re: [h2] Re: Duplicate records when order by date?

> Are they really peers if there's a unique primary key? Yes, they are if this primary key isn't included into sort specification. If rows aren't distinct with respect to the sort specification, they are peers of each other. Columns aren't included into sort specification don't matter here.

Re: [h2] Re: Duplicate records when order by date?

On Tuesday 16 April 2024 at 17:18:41 UTC+8 Evgenij Ryazanov wrote: These rows are called *pears* in the SQL Standard. I meant *peers*, of course. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this grou

Re: [h2] Re: Duplicate records when order by date?

These rows are called *pears* in the SQL Standard. The SQL Standard doesn't require any exact ordering of peers and doesn't require any stable ordering rules for them. These things are implementation-dependent. If some particular DBMS doesn't have own documented ordering rules for pears (I

[h2] Re: Duplicate records when order by date?

Hello! Try the following query: SELECT RANK() OVER (ORDER BY start_date DESC NULLS FIRST) R, * FROM test ORDER BY 1; Some rows have the same rank: 149 130 2019-01-11 00:00:00-05 150 217 2019-01-02 00:00:00-05 150 218 2019-01-02 00:00:00-05 152 164 2019-01-01 00:00:00-05 152 166 2019-01-01

[h2] Re: H2 does not use SYSTEM TABLE in table types

H2 returns table types exactly as required by the SQL Standard. JDBC doesn't have any own requirements. -- 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] Re: H2 does not use SYSTEM TABLE in table types

Hi! > Why H2 does not use SYSTEM TABLE in the table types returned by the DatabaseMetaData.getTableTypes() method Because there is no such thing as system table in the SQL Standard. JDBC metadata is an entirely unreliable thing, you can't construct anything serious on top of it. There is a

[h2] Re: how much indexes are used for optimization

Hello! Currently only different primary queries can use different indexes, nested derived tables and subqueries can also use own indexes. SELECT * FROM TEST WHERE NAME = 'A' AND FIRSTNAME = 'B' can use only one index and it needs an index on (NAME, FIRSTNAME, …) or on (FIRSTNAME, NAME, …)

[h2] Re: java.lang.NoClassDefFoundError: org/h2/command/dml/SetClauseList

Hello! Make sure you have only one version of H2 in classpath of your application. If you have a standalone H2 Server process, check its classpath instead. If you use auto-server mode, check classpaths of all involved applications. -- You received this message because you are subscribed to

[h2] Re: Get user's roles and role's users

Hi! Take a look on GRANTEE and GRANTEDROLE columns in non-standard INFORMATION_SCHEMA.RIGHTS table where GRANTEETYPE = 'USER' and GRANTEDROLE IS NOT NULL. Please note that a role can be granted to another role. These records have GRANTEETYPE = 'ROLE' and GRANTEDROLE IS NOT NULL. Users have

Re: [h2] Re: Auto Increment statement is giving an error when passing null with H2DB 2.2.224 version

When you use Statement.RETURN_GENERATED_KEYS, driver may choose what it should return by itself, see JDBC™ 4.3 Specification, section 13.6. Modern versions of H2 return primary key columns, identity columns, and columns with non-constant default expression (including expressions inherited from

[h2] Re: Auto Increment statement is giving an error when passing null with H2DB 2.2.224 version

Hi! You shouldn't try to insert NULL into columns with NOT NULL constrains. Only non-null values or DEFAULT may be inserted into them. H2 allows such incorrect insertions into identity columns only in Legacy, HSLQDB, MariaDB, and MySQL compatibility modes. You can also enable this feature for

[h2] Re: Updating a current column with an old column using triggers without exception StackOverflowError

Hello! If you need to modify this row in your trigger, you must change values in the newRow array directly: newRow[/* 0-based column index */] = OffsetDateTime.now(); -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this

[h2] Re: create domain text[]

Hello! It isn't going to work, text[] is not an identifier and it cannot be used as domain name. PostgreSQL supports standard arrays for more than 20 years, just use a standard definition in both PostgreSQL and H2. -- You received this message because you are subscribed to the Google Groups

[h2] Re: concurrent queries

Hi! All 1.*.* versions of H2 are entirely outdated and aren't supported in any way. 1. H2 1.4.* has two different storage engines. 2. H2 1.4.199 and older versions have different synchronization models for every engine (1.4.200 uses multi-threaded mode for MVStore and single-threaded mode for

Re: [h2] Year alias in select statement

Hello! YEAR is a reserved word even in archaic SQL-92, so it was a bad idea to use it as unquoted identifier. BTW, DATE and TRANSACTION are reserved words too, but H2 allows their usage as identifiers. Also there is no YEAR function in the Standard, correct syntax is EXTRACT(YEAR FROM

[h2] Re: UNIQUE KEY constraint in H2-1.4.199 is not working with H2-2.2.224

Hi! Your table definition has non-portable MySQL-specific syntax. If this is not your intention, you should use standard CONSTRAINT mdrIdProeprtyName UNIQUE(mdr_id, property_name) instead. MySQL-specific features are only allowed in MySQL and MariaDB compatibility modes:

[h2] Re: Syntax error with ON CONFLICT

Hello! This PostgreSQL-specific clause is not supported by H2. You need to use the MERGE command from the SQL Standard: https://www.postgresql.org/docs/current/sql-merge.html https://h2database.com/html/commands.html#merge_using -- You received this message because you are subscribed to the

[h2] Re: Infinite looping when trigger updates same table

Hi! Is it your question? Anyway, take a look on answers: https://stackoverflow.com/questions/77754877/h2-triggers-behaving-in-infinite-loop -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails

[h2] Re: is there any command line interface can interact with h2 in shell

Hi! You can use the shell tool: https://h2database.com/html/tutorial.html#shell_tool -- 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] Re: Batch insert on key violation

Hello! When you use JDBC directly, H2 throws BatchUpdateException in case of failure, but only after execution of all commands in the batch (other drivers may stop after a first failure, but every driver must always continue processing or always stop processing). getUpdateCounts() and modern

[h2] Re: Lock file absent by default ?

Hello! 1. H2 uses file system locks by default for a very long time, so there is no additional file. In few special cases file systems (usually virtual or remote ones) don't support locks well. In these cases you can add ;FILE_LOCK=FILE to JDBC URL to use that old method instead of modern one.

[h2] Re: Unnesting multiple array columns

Hello! Yes, it is possible, but with valid SQL only. Subqueries must be enclosed in parentheses, all your queries are incorrect. Valid queries are select * from unnest((select bar from foo)); select * from unnest((select baz from foo)); select * from unnest((select bar from foo), (select baz

[h2] Re: Use of COMMIT TRANSACTION

Hello! These commands aren't going to work. You need to disable auto-commit, execute your commands and commit or rollback your transaction. In JDBC, you can use Connection.setAutoCommit(false), Connection.commit(), and Connection.rollback(). In SQL you can use SET AUTOCOMMIT OFF, COMMIT, and

[h2] Re: Support returning ID of inserted row right from the INSERT INTO statement

Hello! H2 supports standard data change delta tables: https://h2database.com/html/grammar.html#data_change_delta_table -- 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

[h2] Re: like 'test%' with escape '' not working in MODE=ORACLE

Hello! This issue cannot be resolved on H2 side. ESCAPE '' is not valid according to the SQL Standard: > If the length in characters of ECV is not equal to 1, then an exception condition is raised: > data exception — invalid escape character. Oracle throws an exception as expected: ORA-01425:

[h2] Re: Version 2.2.222 still not available in the Maven repository

H2 2.2.222 has a known regression: it doesn't perform automatic calculation of statistics. You need to run the ANALYZE command by yourself. In real-words applications it can be enough to call its once after insertion of enough amount of data, in unit tests you may need to call it in every test

[h2] Re: H2 mem db failing with LocalDateTime

Hello! You cannot use MySQL/MariaDB-specific features without MySQL or MariaDB compatibility mode: https://h2database.com/html/features.html#compatibility If you use H2 in unit tests you also need to disable their automatic configuration by Spring Boot, otherwise your custom JDBC URL with all

Re: [h2] Comparing NULL able columns --> IS DISTINCT FROM

Hello! SQL actually has three boolean values: TRUE, FALSE, and UNKNOWN (UNKNOWN in the same thing as NULL). Comparison operators can return all these values. NULL is neither equal nor not equal to NULL or any other value, results of all six comparison operators (=, <>, <, >, <=, >=) return

[h2] Re: Create Table Problem (Upgrading from 1.4.x)

Hello. In modern versions of the SQL Standard KEY is really a non-reserved word, but it was a mistake to exclude it from the list of reserved words, because in some contexts you still cannot use it as identifier, even the SQL Standard itself has at least one such documented exclusion appeared

[h2] Re: Is there a way to quote columns ?

With H2 version *2.1.214* and the JDBC URL *jdbc:h2:tcp://localhost/MY_DB;AUTO_SERVER=TRUE;IGNORECASE=TRUE;NON_KEYWORDS=INTERVAL,VALUES* I get this Exception: Caused by: org.h2.jdbc.JdbcSQLNonTransientConnectionException: Unsupported connection setting "NON_KEYWORDS" [90113-200] at

[h2] Re: Is there a way to quote columns ?

Hello! You need to quote column names in your application, there is nothing to do on H2 side. If you use Hibernate ORM, you can force quotation of all identifiers with hibernate.globally_quoted_identifiers setting. In the worst case you can add ;NON_KEYWORDS=INTERVAL to JDBC URL, but this

[h2] Re: When was REGEXP_SUBSTR added to H2?

Hello! It was added in H2 2.0.202. -- 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

Re: [h2] How to differentiate system tables from others

Do you still have an own special driver for LibreOffice? If so, you can assume that rows with TABLE_SCHEM = 'INFORMATION_SCHEMA' describe system tables. (Actually they aren't tables, but it doesn't matter.) -- You received this message because you are subscribed to the Google Groups "H2

Re: [h2] How to differentiate system tables from others

The SQL Standard describes 'BASE TABLE', 'VIEW', 'GLOBAL TEMPORARY', 'LOCAL TEMPORARY', and 'SYSTEM VERSIONED' table types. H2 doesn't support system versioned tables, but supports all other types. The JDBC Specification doesn't describe (and shouldn't describe) any table types. Javadoc of

Re: [h2] How to differentiate system tables from others

Hi! The latest version of H2 is 2.1.214, there is no such version as 2.2.219. You can detect types of tables with the following query in modern versions of H2: SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, STORAGE_TYPE, DB_OBJECT_SQL('TABLE', TABLE_SCHEMA, TABLE_NAME) IS NULL IS_SYSTEM FROM

[h2] Re: [Spring bot3 + h2 v.2.1.214] H2 failing to fetch Java Instant.

MySQL mentioned in your question on StackOverflow doesn't have the TIMESTAMP WITH TIME ZONE data type. It means your application uses different data types with different database systems. JDBC drivers by default return TIMESTAMP values as java.sql.Timestamp (R2DBC returns them as

[h2] Re: [Spring bot3 + h2 v.2.1.214] H2 failing to fetch Java Instant.

Hello! This problem is not related to H2 at all. H2 by itself can return TIMESTAMP WITH TIME ZONE and compatible values as java.time.Instant from its JDBC driver if it is explicitly requested. try (Connection c = DriverManager.getConnection("jdbc:h2:mem:")) { ResultSet rs =

Re: [h2] Alter table not working from code with prepared statements, but working from console (browser)

Hello! JDBC parameters can only be used to specify values (literals). They cannot be used to specify identifiers, identifiers (including names of columns) aren't parameterizable. Also your decision to create an own column for each user looks like a bad idea, usually you shouldn't store data

Re: [h2] Problems with DATEADD, DATEDIFF and daylight savings time

Hello! I have tests that use a H2 db, and the data set is populated with timestamps relative to the current timestamp, using expressions such as DATEADD(DAY, -3, CURRENT_TIMESTAMP). Let's say current timestamp is 2023-03-27 18:00:00 CURRENT_TIMESTAMP can't return this value, it returns a

Re: [h2] Re: Cannot find symbol NUMERIC

With JDBC, you can call your function either with PreparedStatement or with CallableStatement: try (Connection c = DriverManager.getConnection("jdbc:h2:mem:1")) { Statement s = c.createStatement(); s.execute("CREATE ALIAS my_stored_proc AS 'BigDecimal send() { return BigDecimal.ONE; }'"); //

[h2] Re: Cannot find symbol NUMERIC

Hello! The valid syntax is CREATE ALIAS MY_STORED_PROC AS ' BigDecimal envia() { return BigDecimal.ONE; } '; because SQL NUMERIC data type is mapped to BigDecimal data type in Java. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To

[h2] Re: Timestamp's value is different in different time zones

Take a look on file name extension, if it is .h2.db, this is a file from PageStore, if it is .mv.db a modern MVStore backend is used. H2 1.4.192 is too old and doesn't support JSR-310 data types yet, their initial support was added only in H2 1.4.193. If your file is in MVStore format, you can

[h2] Re: Timestamp's value is different in different time zones

> Unfortunately, I should be using an old version of jdbc driver If you use some old unsupported version of H2 with PageStore backend, you need to set the same JVM time zone as it was on system where database file was created. This backend had very problematic storage format for datetime

[h2] Re: Unique index or primary key violation

Hi! It doesn't look like an original error from H2, H2 produces messages like Unique index or primary key violation: "PRIMARY_KEY_6 ON PUBLIC.AD(ACCU_ID, TARGET_ID, REPLAY_ID) VALUES ( /* key:1 */ 2, 3, 4), where 1 is an internal row number (_ROWID_) and other values are values of primary key

[h2] Re: Timestamp's value is different in different time zones

Hi! There are two different timestamp data types in the SQL Standard: TIMESTAMP (TIMESTAMP WITHOUT TIME ZONE) and TIMESTAMP WITH TIME ZONE. TIMESTAMP [ WITHOUT TIME ZONE ] has YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND fields. TIMESTAMP WITH TIME ZONE additionally has TIMEZONE_HOUR and

[h2] Re: "SELECT .. WHERE ... IN ... OR ... IN" on large tables

Hello! H2 is unable to optimize such OR conditions when multiple columns are involved. To make your query faster, you need to rewrite it with a UNION between two inner queries, one with filtration by first column and second with filtration by second one. These inner queries should be able to

[h2] Re: Index not found error while using index hints

Hi! I think there are only two possible reasons for that failure. 1. Index belongs to another table (possibly with the same name, but in different schema). 2. Index has another name, for example, "my_table_index" (quoted identifiers are case sensitive by default). -- You received this message

[h2] Re: Asking for help

Hello! In H2 2.1.210 an old bug with incorrectly parsed join conditions was fixed. In earlier version of H2 in some cases they were applied to the wrong joins. Few applications use workarounds for this bug and these workarounds need to be removed from them during upgrade to H2 2.1. -- You

[h2] Re: Problem updating from 1.4.199 to 2.1.214

Hello! 2.5.5 is too old and it has hibernate-core 5.4.32.Final in its dependencies, this version doesn't support any new versions of H2. You need to check version of Hibernate ORM actually used by your application and upgrade it to 5.6.15.Final or 6.1.7.Final if necessary. You also need to

[h2] Re: Error converting value

Hello! In the SQL Standard all character strings with universal character set (H2 doesn't have any other character sets) are comparable with each other and all numbers are comparable with each other. Comparison operations between different groups of data types aren't described, so there is no

[h2] Re: Enquiry for New version release

Hello! What exactly do you mean? There are no known vulnerabilities in this version. CVE-2022-45868 / sonatype-2022-624. is a fake security report and there is nothing to fix on H2 side. If some code checking tools reports it to you, let it vendor know about that, usually such tools have

[h2] Re: Error on NaN literal in SQL statement

Hello! NaN is not a literal, it's just an identifier. In this case you can pass it as a character string literal: INSERT INTO "TABLE"(ID, DOUBLE_COLUMN) VALUES (10, 'NaN'); In more complex cases where data type cannot be determined automatically a cast is needed: CAST('NaN' AS DOUBLE

Re: [h2] Re: show hex value

Hello! There is a RAWTOHEX function, you can convert this integer number to a BINARY(4) data type and pass the result to this function: SELECT RAWTOHEX(CAST(1234567890 AS BINARY(4))); -- You received this message because you are subscribed

Re: [h2] index doesn't help

Hello! WHERE boolean and WHERE boolean = TRUE have no difference. The actual problem is usage of OR, H2 is unable to use indexes in such conditions. Take a look on execution plan produced by the EXPLAIN command: SELECT "SEQUENCE",

[h2] Re: questions about case sensitivity

Hello! These settings aren't related to each other. IGNORE_CASE is about *data types* and their *values*. It replaces CHARACTER VARYING (VARCHAR) columns in DDL commands with VARCHAR_IGNORECASE data type. This legacy setting should never be used, it is much better to specify some

[h2] Re: How to back up the data while the database and program is running

Hello! You can execute the SCRIPT command: https://h2database.com/html/commands.html#script -- 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] Re: RecoverTools creating scrip with tablenames 0_Number pattern? why

Scripts generated by this tool aren't really suitable for data migration. It will be better to use the SCRIPT command or the Script tool to produce an SQL without all this messy temporary tables and edit their resulting script as necessary.

[h2] Re: Hibernate 5.6.9 @Version annotation requires precision 9 for timestamp

Hello! Resolution of system timestamps in JVM depends on Java version and operating system. Old versions of Java (Java 8 and older) provide only 3 digits. Resolution was improved in Java 9, this version provides 7 digits on Windows, but only 6 digits on Linux, because it uses an old system

[h2] Re: show hex value

Hi! H2 Console already displays values of binary strings in hexadecimal format, if you need a similar feature in other tool you should ask its developers instead. There is also a RAWTOHEX function, you can use it to convert a binary

[h2] Re: RecoverTools creating scrip with tablenames 0_Number pattern? why

Hi! Table definition and table data are stored separately and they are recovered separately too. The recovery tool works in the following way (I skipped unrelated steps for simplicity): 1. It constructs a temporary table with LOB chunks from all tables first. 2. Then, it recovers data from

[h2] Re: Getting names of unique constraints via hibernate

Hello! H2 doesn't return names of constrains in these exceptions (maybe it will in future versions). If you need to find name of constraint, you need to check INFORMATION_SCHEMA.TABLE_CONSTRAINTS table. You need only rows where TABLE_SCHEMA and TABLE_NAME match schema and name of updated

[h2] Re: int not 100% continuous

Hi! If you don't care about exact ordering, the fastest way is UPDATE tableName SET columnName = ROWNUM(); If you want to preserve it, a slower command is needed: MERGE INTO tableName USING (SELECT columnName, ROW_NUMBER() OVER(ORDER BY columnName) FROM tableName) T(columnName, R) ON

Re: [h2] H2 PreparedStatement and Arrays

Your code actually constructs something like mstb_etypenum IN (ARRAY[2, 3, 4, 5]) instead of mstb_etypenum IN (2, 3, 4, 5). In SQL statements from JDBC (unlike in JPQL from JPA) you cannot pass parameters in that way. One parameter always creates one value. You can use mstb_etypenum = ANY(?)

[h2] Re: How to modify SELECT LOB_TIMEOUT

Yes, execution of any SQL command and also actions on toolbar (refresh, commit, rollback, command history, etc.) reset the timeout. -- 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

[h2] Re: How to modify SELECT LOB_TIMEOUT

Yes, this setting is not related to timeout of web sessions. You need java -Dh2.consoleTimeout=360 … -- 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] Re: How to modify SELECT LOB_TIMEOUT

You cannot specify any connection options in this file. You need to change JDBC URL in Login window of H2 Console itself. -- 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

[h2] Re: How to modify SELECT LOB_TIMEOUT

Hello. You can add ;LOB_TIMEOUT=your_value to JDBC URL. -- 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

[h2] Re: ERROR SqlExceptionHelper: Unknown data type: "unsigned"

Hello. CAST in MySQL and its forks is not compliant with cast specification from the SQL Standard and is not compatible with other database systems. H2 cannot emulate this deviation. You can replace it with something like case when sequence >= 0 then sequence else sequence +

Re: [h2] Can't insert SHA3-256 from runscript

H2 1.4.200 supports only SHA-256 and it needs to be specified as SHA256 (without dash) in this outdated version. Please note that passwords should normally be hashed with a random data (salt) to prevent some common attacks. -- You received this message because you are subscribed to the Google

[h2] Re: Can't insert SHA3-256 from runscript

Hello. SHA3-256 is only supported by H2 2.*.*. Error code 90008-200 was produced by H2 1.4.200, it doesn't support that algorithm. You need to upgrade H2 used by your application to a some recent version. -- You received this message because you are subscribed to the Google Groups "H2

[h2] Re: JDBC getIndexInfo for large table is very slow

Hello! You need to pass true as the last argument (approximate). It allows to return a fast approximation instead of exact number of rows in the index. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop

[h2] Re: Help needed for COLUMN option in SCRIPT

Hello! Unfortunately, this tool doesn't support all clauses of the SCRIPT command. You need to use the Shell tool or H2 Console or JDBC connection to the database. Valid SQL command will be SCRIPT COLUMNS TO 'backup.sql' https://h2database.com/html/commands.html#script -- You received this

[h2] Re: Cant get VARCHAR column to maximum size

Hello. Online documentation is actual only for the latest released version, currently it is 2.1.214. If you use some older version, you need to download its complete distribution in a ZIP archive, it includes documentation in HTML and PDF formats:

Re: [h2] Management of users, roles and privileges in LibreOffice Base

> Can you confirm that the names of users and roles only support uppercase characters without double quotes in all the commands where they appear. It is possible to use unquoted (NAME), quoted ("NAME"), or Unicode (U&"NAME") identifiers for names of users and roles in H2, but these names are

[h2] Re: SELECT command that returns a strange RESULSET

Hi! USER is not an identifier. It is a reserved word in the SQL Standard. It has exactly the same meaning as CURRENT_USER in the SQL Standard and in H2. https://h2database.com/html/functions.html#current_user -- You received this message because you are subscribed to the Google Groups "H2

Re: [h2] Management of users, roles and privileges in LibreOffice Base

Database administration is out of scope of the SQL Standard and there are no predefined roles is the Standard. In the Standard, every schema has an owner and this owner may perform DDL commands within this schema. Anything else is database-specific. In H2, the most of DDL commands require

Re: [h2] Management of users, roles and privileges in LibreOffice Base

Hi! On Friday, 2 September 2022 at 23:40:02 UTC+8 prrvchr wrote: > >- java.sql.DatabaseMetaData.getUserName() should return the user who >is logged in (current user). > > This method is implemented in H2 itself properly. You can also use standard CURRENT_USER expression in SQL. >

[h2] Re: Database hangs when the query contains multibyte space.

Hello! This issue was fixed, the fix will be included into the next version. If you need this fix right now, you can compile H2 from its current sources: https://github.com/h2database/h2database Building instructions are here: https://h2database.com/html/build.html#building -- You received

[h2] Re: A confusing point about INFORMATION_SCHEMA of H2Database 2.x

You need to download the complete distribution of the version you need in a ZIP archive: https://h2database.com/html/download-archive.html They contain documentation in PDF and HTML format. But INFORMATION_SCHEMA wasn't documented properly in old versions, so you cannot compare old and new

[h2] Re: A confusing point about INFORMATION_SCHEMA of H2Database 2.x

You can read data type name and other parameters from the COLUMNS table: https://h2database.com/html/systemtables.html#information_schema_columns DATA_TYPE column now contains the name as required by the SQL Standard. For ARRAY data types there is an additional table with definitions of

[h2] Re: A confusing point about INFORMATION_SCHEMA of H2Database 2.x

Hi! DatabaseMetaLocal is not related to INFORMATION_SCHEMA. It is a data source for implementation of java.sql.DatabaseMetaData. -- 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,

[h2] Re: getPlanSQL response

Hello. org.h2.command.Prepared is not a part of API and it should never be used directly by application. If you need to get execution plan of some query or DML command, you should use the EXPLAIN command: https://h2database.com/html/commands.html#explain -- You received this message because

[h2] Re: Question about async: filesystem

Web application with embedded database managed by this application must have a ServletContextListener. In its contextDestroyed() method it must shutdown the database properly. For example, application can use a org.h2.jdbcx.JdbcConnectionPool to allocate its connections. It can be constructed

[h2] Re: Question about async: filesystem

Hello! file: and nio: have no special meaning in modern versions of H2, jdbc:h2:file:SOME_PATH, jdbc:h2:nio:SOME_PATH, and plain jdbc:h2:SOME_PATH have exactly the same meaning. Unfortunately, invocation of Thread.interrupt() during disk I/O closes the underlying file channel, so it isn't safe

[h2] Re: question about to_char

Hello! Yes, it's a bug. Implementation of this Oracle compatibility function doesn't handle DECFLOAT arguments properly, but you can cast them to a some other numeric types as a workaround. TO_CHAR(CAST(2E+1 AS NUMERIC(6, 2)), 'D00') Approximate numeric literals (with exponent) have

[h2] Re: Revisiting manipulating CURRENT_TIMESTAMP

Hello! You can only add ;BUILTIN_ALIAS_OVERRIDE=TRUE to JDBC URL and create an own user-defined function with that name (CREATE ALIAS "CURRENT_TIMESTAMP" …): https://h2database.com/html/commands.html#create_alias But usually it is better to move that logic from database level to Java

[h2] Re: UNSIGNED and array problem in H2 query

Hello. Where did you find that syntax? There are no unsigned numeric data types in the SQL Standard and in the most of database systems including the H2. This is a feature of MySQL. H2 silently accepts UNSIGNED only in MySQL and MariaDB compatibility modes, but this declaration is ignored by

Re: [h2] Exception from PgServerThread.initDb

Hello. > Will the database files dbName.h2.db , dbName.lock.db, dbName.mv.db and dbName.track.db work in the latest version No, you need to export your databases to SQL scripts with old version of H2 and create new databases with the new version and execute these scripts (this procedure was

[h2] Re: WriteValue Function replacement

New versions of H2 don't have custom data types, they had significant design flaws, security problems, missing basic functionality, and it wasn't possible to maintain any stable API for them. New versions of H2 support standard ROW value data type and standard ARRAY data type. It is possible

[h2] Re: WriteValue Function replacement

Hello. No, there are no direct replacements. You cannot rely on internals of H2, they aren't expected to be used by applications. This method was used internally by removed PageStore backend. MVStore backend uses ValueDataType instead (and it also isn't a part of API). ValueDataType uses

[h2] Re: MV_Store

Hello. Legacy PageStore backend was removed from H2, so you cannot specify MV_STORE=FALSE any more. This backend is only available in old unsupported versions, it was introduced somewhere in 1.1.* releases and the last release with this backend is 1.4.200. -- You received this message

[h2] Re: LOWER function does not work as expected in v 2.1.214

Because when you use the LOWER or UPPER function, its result doesn't have CHARACTER(255) data type any more, it has CHARACTER VARYING data type. Character string literals also have CHARACTER VARYING data type in H2. So a comparison between two CHARACTER VARYING values is actually performed and

Re: [h2] ALTER VIEW PUBLIC."View1" AS SELECT ....

Hello! The SQL Standard doesn't have any commands for view modification, so the only portable way is to drop an old view and create a new one. In H2 you can use CREATE OR REPLACE VIEW viewName AS … https://h2database.com/html/commands.html#create_view -- You received this message because you

[h2] Re: LOWER function does not work as expected in v 2.1.214

CHARACTER is a fixed-width data type. Columns of CHARACTER data type always have exactly one character. Columns of CHARACTER(255) data type always have exactly 255 characters, shorted values are right-padded with spaces. If you need to store strings of different length, you should always use

[h2] Re: LOWER function does not work as expected in v 2.1.214

Hello! You need to provide a complete test case. Something like CREATE TABLE XXX(XYZ VARCHAR(100), …); INSERT INTO XXX(XYZ, …) VALUES (…); SELECT * FROM XXX WHERE LOWER(XYZ) = 'somevalue'; -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To

[h2] Re: No AUTO_INCREMENT in DatabaseMetaData.getTypeInfo()

Hello. Identity columns don't have any dedicated data types in the SQL Standard and it the most of database systems. When you declare an identity column you must specify both data type (BIGINT or INTEGER, for example) and identity clause (GENERATED BY DEFAULT AS IDENTITY etc.) Few database

Re: [h2] Re: No SYSTEM TABLE in TABLE_TYPE column returned by DatabaseMetaData.getTables()

I forgot to add TABLE_TYPE alias after CASE … END. -- 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

Re: [h2] Re: No SYSTEM TABLE in TABLE_TYPE column returned by DatabaseMetaData.getTables()

You can use something like that: SELECT TABLE_CATALOG TABLE_CAT, TABLE_SCHEMA TABLE_SCHEM, TABLE_NAME, CASE WHEN TABLE_SCHEMA = 'INFORMATION_SCHEMA' THEN 'SYSTEM TABLE' WHEN TABLE_TYPE = 'BASE TABLE' THEN 'TABLE' ELSE TABLE_TYPE END, REMARKS, CAST(NULL AS VARCHAR)

  1   2   3   4   5   6   >