[h2] Re: VARCHAR comparison and trailing spaces

2019-10-09 Thread Evgenij Ryazanov
In some other use cases such strength is required for normal operation and it is configurable in H2, but only in a new empty database, to change it later database needs to be exported to SQL, edited (with some stream editor) and re-created. -- You received this message because you are

[h2] Re: VARCHAR comparison and trailing spaces

2019-10-09 Thread Evgenij Ryazanov
Hello. The current version of the SQL Standard is SQL:2016 plus corrigenda and additional parts from 2019. Please, don't talk about SQL-92, it is completely outdated, there are six (!) versions that were published after it. I don't have the very recent version of ISO/IEC 9075-2, but in

[h2] Re: VARCHAR comparison and trailing spaces

2019-10-09 Thread Fawzib Rojas
Ok, found a post (ironically it was MY post), asking the same but about CHAR (not VARCHAR). The solution was using "SET COLLATION EN STRENGTH PRIMARY" After doing that before the creation of the table now the comparison between 'testing' and 'testing ' reports they are the same. My question

[h2] VARCHAR comparison and trailing spaces

2019-10-09 Thread Fawzib Rojas
I have been testing H2 and for some reason same strings with trailing spaces are considered different. For Example: select CASE WHEN 'testing'='testing ' THEN 'SAME' ELSE 'DIFFERENT' END H2 returns 'DIFFERENT' on that query, MSSQL does not, which it seems to be the right way. Looking for

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

2019-10-09 Thread Noel Grandin
Otherwise another trick you can try is manually sharding your query, something like SELECT ... WHERE docid % 10 == 0 SELECT ... WHERE docid % 10 == 1 ... SELECT ... WHERE docid % 10 == 9 -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To

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

2019-10-09 Thread Noel Grandin
On 2019/10/09 2:35 PM, Tim Fielder wrote: Is it just the case that any conditional SELECT will fail once a table reaches a certain size? Quite possibly that code was not smart enough to spool to disk when exceeding RAM size. You could try building from master and using that, Evgenij

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

2019-10-09 Thread Tim Fielder
On Wednesday, October 9, 2019 at 6:23:09 AM UTC-4, Tim Fielder wrote: > > My suspicion is that nesting a SELECT inside any other function is going > to cause these kinds of errors, and I will need to instead retrieve a > ResultSet and use a PreparedStatement to batch-INSERT into tables in chunks

Re: [h2] Re: Inserting text into a blob in H2

2019-10-09 Thread Philip Roc
I've found a solution INSERT INTO XXX ( ... "JSON_FILE" , ... ) VALUES ( ... FILE_READ('classpath:/j1.json') , ... ) ; See https://stackoverflow.com/questions/38353487/insert-blob-from-a-file-into-a-sql-script-to-embed-h2-database Le mercredi 9 octobre 2019 10:25:38

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

2019-10-09 Thread Tim Fielder
On Monday, October 7, 2019 at 3:55:17 PM UTC-4, Noel Grandin wrote: > > Also, looking at your schema, and your saying that you are indexing the > tables, you may want to consider doing > CREATE TABLE reference (docid varchar NOT NULL, > refid varchar NOT NULL ); > CREATE

Re: [h2] Re: Inserting text into a blob in H2

2019-10-09 Thread Philip Roc
Hi Eyvgenij, here's the situation. My colleagues and I are currently working on a Spring Boot project that uses Oracle. However, although one of the developers has managed to install Oracle 11 on his machine, the 4 others haven't, probably because of company-wide security policies (who knows).