[h2] Re: H2 in LibreOffice Base

[prrvchr]

Hi Evgenij Ryazanov,

Thank you for taking the time to respond.

LibreOffice/OpenOffice Base seems very limited in its compatibility with 
JDBC 3.0 and unfortunately does not know JDBC 4.X.

Here is for example a command that Base executes when creating a table with 
an autoincrement key:
CREATE TABLE "PUBLIC"."PUBLIC"."Table1" ("ID" INTEGER NOT NULL GENERATED BY 
DEFAULT AS IDENTITY, "Name" NVARCHAR(100), PRIMARY KEY  ("ID"))
It can be noted that the declaration of the keys can only be done by the 
instruction: PRIMARY KEY  ("ID", ...)
I don't think Base is able to retrieve the keys generated by the command: *rs 
= prep.getGeneratedKeys()*, but uses an *AutoRetrievingStatement* property 
with the value:* CALL IDENTITY() *

I know that LibreOffice wants to disengage from Java and won't make any 
effort to improve the integrated JDBC driver, that's why I had to write a 
new driver for my LibreOffice extensions 
 I need a cross-platform 
database (java) supporting the Temporal System-Versioned Tables (HsqlDB 
2.5x or 2.6x)
This driver gives me complete satisfaction at the level of the UNO API (use 
of the database by the smtpMailerOOo 
 extension written in Python for 
example) but it seems important to me to make the effort to make Base 
capable of supporting these databases.
I recently integrated into the driver the archives: hsqldb.jar, h2.jar, 
derby.jar and smallsql.jar in order to make debugging in Base easier and 
think that it is welcome to have access to all these databases in 
LibreOffice.
I have the possibility in my driver to modify the DatabaseMetaData and 
ResulsetMetaData in order to make Base work correctly, the problem is not 
to correct the malfunctions but to find them...
For exemple, to have H2 functional in Base I had to translate the name of 
the table types ("BASE TABLE" into "TABLE") in the DatabaseMetaData.
In fact, I'm interested in any information that can help me.



Le lundi 18 avril 2022 à 07:10:57 UTC-4, Evgenij Ryazanov a écrit :

> Hello!
>
> Recent versions of H2 fully support identity columns from the SQL Standard 
> and their retrieving functionality from the JDBC specification.
>
> What exactly doesn't work for you? How these columns are defined? How rows 
> are inserted? How exactly LibreOffice tries to read generated values with 
> your driver?
>
> For example, an application that uses own driver of H2 should execute 
> commands like these:
>
> Statement stat = connection.createStatement();
> stat.execute("CREATE TABLE TEST(ID BIGINT GENERATED BY DEFAULT AS IDENTITY 
> PRIMARY KEY, V INTEGER)");
> PreparedStatement prep = connection.prepareStatement("INSERT INTO TEST(V) 
> VALUES (?)",
> Statement.RETURN_GENERATED_KEYS);
> prep.setInt(1, 10);
> prep.executeUpdate();
> try (ResultSet rs = prep.getGeneratedKeys()) {
> rs.next();
> System.out.println(rs.getLong("ID"));
> }
>
> Actually you can run into some unrelated issue of LibreOffice that most 
> likely is not going to be fixed, because they don't really want to spend 
> time on additional database systems.
> For example, there is a problem with tables from JDBC metadata:
> https://bugs.documentfoundation.org/show_bug.cgi?id=146673
>
> I also don't understand why you need to create an alternative driver, but 
> you may have own reasons.
>

-- 
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 visit 
https://groups.google.com/d/msgid/h2-database/ee361b04-66a7-4ed4-8226-03eaa5c1624en%40googlegroups.com.

Re: [h2] Is it possible to flatten arrays when doing nested array aggregation?

[Adam R]

I guess I oversimplified the toy example.  In the real query I need 
grouping/aggregation for some things, but global aggregation for others.  
Thank you I will look into unnest.
-Adam

On Monday, April 25, 2022 at 11:38:18 PM UTC-6 
and...@manticore-projects.com wrote:

>
> https://www.h2database.com/html/functions.html#unnest
>
> This one.
>
>
> Sent from my Galaxy
>
>
>  Original message 
> From: Adam R  
> Date: 26/04/2022 05:59 (GMT+01:00) 
> To: H2 Database  
> Subject: [h2] Is it possible to flatten arrays when doing nested array 
> aggregation? 
>
> Here's a toy example:
>
> CREATE TABLE MY_TABLE (ID INT, NAME VARCHAR);
> INSERT INTO MY_TABLE VALUES(1, 'a');
> INSERT INTO MY_TABLE VALUES(2, 'a');
> INSERT INTO MY_TABLE VALUES(3, 'b');
> INSERT INTO MY_TABLE VALUES(4, 'b');
>
> SELECT ARRAY_AGG(IDS) AS IDS FROM
> (SELECT ARRAY_AGG(ID) AS IDS FROM MY_TABLE GROUP BY NAME)
>
> This will return [[1, 2], [3, 4]].  What I'd like is a way to flatten the 
> inner arrays so that I end up with a single array, like [1, 2, 3, 4].  Is 
> this possible in H2?  (In a way that would would work for an arbitrary 
> number of array elements).
>
> Thank you.
>
> -- 
> 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...@googlegroups.com.
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/h2-database/f9308cf3-2679-4652-94e7-ad48795f07c8n%40googlegroups.com
>  
> 
> .
>

-- 
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 visit 
https://groups.google.com/d/msgid/h2-database/b4bdefdf-8e3c-4ae2-bfbc-c66b46c30b3en%40googlegroups.com.

Re: [h2] UNLINK_SCHEMA as opposition of LINK_SCHEMA, LINK_SCHEMA RFE and questions

[Andreas Reichel]

Greetings.

On Mon, 2022-04-25 at 23:19 -0700, t603 wrote:
> Hello, may I ask You few questions about LINK_SCHEMA function? 
> 
> 1) Is LINK_SCHEMA on 10+ or 100+ tables and views far more faster
> that 10+ or 100+ times CREATE LINKED TABLE? In other words is there
> time improvement due connection establishment? Or does LINK_SCHEMA
> internally call of 10+ or 100+ CREATE LINKED TABLE and there is no
> performance improvement? 

I think, this will perform very similar, but may depend on the exact
circumstances.
As far as I understand it, the "LINKED" command only provide a
framework for querying via a different JDBC connection and fetching
records. Its an ETL alike mechanism.

So LINKED SCHEMA would only provide the JDBC connection URL for
accessing the schema's table -- same way as LINKED TABLE would do,.

> 
> 2) When I do not need linked schema anymore, is there something like
> UNLINK_SCHEMA? 

Would not that be a simple DROP SCHEMA?

Best regards
Andreas

-- 
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 visit 
https://groups.google.com/d/msgid/h2-database/304e641f868cb944cd660f6cb09fdd8dca9cd7b6.camel%40manticore-projects.com.

[h2] UNLINK_SCHEMA as opposition of LINK_SCHEMA, LINK_SCHEMA RFE and questions

[t603]

Hello, may I ask You few questions about LINK_SCHEMA function? 

1) Is LINK_SCHEMA on 10+ or 100+ tables and views far more faster that 10+ 
or 100+ times CREATE LINKED TABLE? In other words is there time improvement 
due connection establishment? Or does LINK_SCHEMA internally call of 10+ or 
100+ CREATE LINKED TABLE and there is no performance improvement? 

2) When I do not need linked schema anymore, is there something like 
UNLINK_SCHEMA? 

3) How can achieve, that linked table via LINK_SCHEMA are linked in read 
only mode similar to CREATE LINKED TABLE ... readlonly? 

Thank You in advance for answer, Stepan 

-- 
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 visit 
https://groups.google.com/d/msgid/h2-database/24bf98ae-12f8-4c25-84ea-0e301887bef1n%40googlegroups.com.