[h2] New LibreOffice driver for H2

[prrvchr]

Hi all,

I just finished implementing H2 integration in the new driver for 
LibreOffice/OpenOffice jdbcDriverOOo 
.

This driver must allow in Base:

   - The creation, deletion, modification, copy of table.
   - The creation, deletion, modification of view.
   - Editing the contents of tables and views if they are editable.
   - Creation, deletion of users and modification of the password.
   - Creation, modification, deletion of indexes.
   - Assigning user rights to database tables and views.


If there are people who are ready to test this first version, then I'm 
interested because it saves me a lot of time (because unfortunately I'm not 
paid ;-)

Here is the download link: jdbcDriverOOo 

 
and the documentation .

You must have Java version 11 minimum, and I recommend LibreOffice because 
the port to OpenOffice has not yet been done.

To have access to the extended mode of the driver you must validate the 
extended mode in: *Tools -> Options -> Base drivers -> JDBC Driver* and 
chose *com.sun.star.sdbcx.Driver* in *Driver UNO Service*. A restart of 
LibreOffice is needed.

This driver is not finished, it remains to manage the Indexes, the users 
and the rights on the tables...

Thank you for your help.

-- 
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/0eccfbb8-8fad-4e71-8be1-8d3ca47e0666n%40googlegroups.com.

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

[prrvchr]

CREATE OR REPLACE works like a charm...

If there are people who are ready to test this first version, then I'm 
interested because it saves me a lot of time (because unfortunately I'm not 
paid ;-)

Here is the download link: jdbcDriverOOo 
 
and the documentation .

You must have Java version 11 minimum, and I recommend LibreOffice because 
the port to OpenOffice has not yet been done.

To have access to the extended mode of the driver you must validate the 
extended mode in: *Tools -> Options -> Base drivers -> JDBC Driver* and 
chose *com.sun.star.sdbcx.Driver* in *Driver UNO Service*. A restart of 
LibreOffice is needed.

This driver is not finished, it remains to manage the Indexes, the users 
and the rights on the tables...

Thank you for your help.





Le mardi 5 juillet 2022 à 11:58:45 UTC+2, prrvchr a écrit :

> Hi Evgenij,
>
> Thank you for answering me. I will use:
>
> CREATE OR REPLACE VIEW viewName AS ..
>
> Le mardi 5 juillet 2022 à 11:50:20 UTC+2, prrvchr a écrit :
>
>> Hi Andreas,
>>
>> The UNO API also provides an interface com.sun.star.sdbcx.XRename 
>> 
>>  
>> to change the name of a view, but apparently this interface is not 
>> implemented in Base because it is never called.
>>
>> But I don't want to rename the view but change its command, and I can't 
>> find a command in H2 to do so.
>> Please advise me the best alternative.
>>
>>
>> Le mardi 5 juillet 2022 à 11:38:52 UTC+2, and...@manticore-projects.com 
>> a écrit :
>>
>>> Greetings!
>>>
>>> On Tue, 2022-07-05 at 02:36 -0700, prrvchr wrote:
>>>
>>> What is the best alternative with H2?
>>>
>>>
>>> H2 has a really excellent documentation: 
>>> https://www.h2database.com/html/commands.html
>>>
>>> Your are looking for: 
>>> https://www.h2database.com/html/commands.html#alter_view_rename
>>>
>>> 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/8023a814-b3fd-495b-ba12-8d639bac7d18n%40googlegroups.com.

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

[Evgenij Ryazanov]

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 this comparison returns FALSE in H2 when strings have different length.

Without this function a comparison between CHARACTER(255) and CHARACTER 
VARYING values is performed and this comparison ignores trailing spaces in 
H2.

LOWER and UPPER functions should return result of the same data type as 
their argument according to the SQL Standard, but H2 currently cannot 
satisfy that requirement, because H2 doesn't have warnings. Some strings in 
some locales have different lengths after conversion to upper or lower 
case. If this length is larger than length of original data type, 
standard-compliant database should truncate the result to the declared 
length and raise a warning. But H2 cannot warn you about truncation and it 
is a bad idea to perform this truncation silently, so it isn't performed at 
all. But to return values longer than argument H2 declares result of these 
functions as CHARACTER VARYING with the maximum length.

Take a look on length and columnDefinition attributes of @Column 
annotation. You can adjust SQL data type produced by JPA with them.

-- 
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/62d83651-b378-49e9-9aed-e0bf4b3bf52en%40googlegroups.com.

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

['Mehmet Cakir' via H2 Database]

That's completely right. The datastructure is not defined on my own.. It is 
Spring Boots hibernate / jpa which is defining the datastructure for my 
unit tests... BUT: I cannot understand why the same query is working 
without the LOWER() function. 

CREATE TABLE TEST (ID BIGINT, SOMEVALUE CHARACTER(255));
INSERT INTO TEST (ID, SOMEVALUE) VALUES(1, 'Y');
SELECT * FROM TEST WHERE SOMEVALUE = 'Y';

is working fine... The shorted value with one character is also 
right-padded with spaces but the query is working fine... The point is that 
I am using h2 as an in-memory database for my unit tests in a spring boot 
application. The tests are the same but after upgrading to h2 v2.. my unit 
tests are failing... I fixed my issue by giving the datatype a fixed length 
of 1 but I just wanted to know if this is an unseen issue or if this is a 
wanted feature. 

The padding point is completely fine but it is confusing me that just the 
LOWER() function is caring about the blanks..

Evgenij Ryazanov schrieb am Montag, 4. Juli 2022 um 15:01:28 UTC+2:

> 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 CHARACTER VARYING / VARCHAR data type instead. If you need to store 
> exactly one character, you can use CHARACTER or CHARACTER(1); but don't 
> use CHARACTER(255) for this purpose, such choice is obliviously wrong.
>
> More details are described here:
> https://github.com/h2database/h2database/issues/3385
>
>
>
>

-- 
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/2d6c6c55-9ff5-41b4-9d79-9e64778ad1cen%40googlegroups.com.

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

['Mehmet Cakir' via H2 Database]

That's completely right. The datastructure is not defined on my own.. It is 
Spring Boots hibernate / jpa which is defining the datastructure for my 
unit tests... BUT: I cannot understand why the same query is working 
without the LOWER() function. 

CREATE TABLE TEST (ID BIGINT, SOMEVALUE CHARACTER(255));
INSERT INTO TEST (ID, SOMEVALUE) VALUES(1, 'Y');
SELECT * FROM TEST WHERE SOMEVALUE = 'Y';

is working fine... The shorted value with one character is also 
right-padded with spaces but the query is working fine... The point is that 
I am using h2 as an in-memory database for my unit tests in a spring boot 
application. The tests are the same but after upgrading to h2 v2.. my unit 
tests are failing... I fixed my issue by giving the datatype a fixed length 
of 1 but I just wanted to know if this is an unseen issue or if this is a 
wanted feature

Evgenij Ryazanov schrieb am Montag, 4. Juli 2022 um 15:01:28 UTC+2:

> 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 CHARACTER VARYING / VARCHAR data type instead. If you need to store 
> exactly one character, you can use CHARACTER or CHARACTER(1); but don't 
> use CHARACTER(255) for this purpose, such choice is obliviously wrong.
>
> More details are described here:
> https://github.com/h2database/h2database/issues/3385
>
>
>
>

-- 
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/685ed104-0283-44c4-a23e-0b6ebf2ddf63n%40googlegroups.com.

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

[prrvchr]

Hi Evgenij,

Thank you for answering me. I will use:

CREATE OR REPLACE VIEW viewName AS ..

Le mardi 5 juillet 2022 à 11:50:20 UTC+2, prrvchr a écrit :

> Hi Andreas,
>
> The UNO API also provides an interface com.sun.star.sdbcx.XRename 
> 
>  
> to change the name of a view, but apparently this interface is not 
> implemented in Base because it is never called.
>
> But I don't want to rename the view but change its command, and I can't 
> find a command in H2 to do so.
> Please advise me the best alternative.
>
>
> Le mardi 5 juillet 2022 à 11:38:52 UTC+2, and...@manticore-projects.com a 
> écrit :
>
>> Greetings!
>>
>> On Tue, 2022-07-05 at 02:36 -0700, prrvchr wrote:
>>
>> What is the best alternative with H2?
>>
>>
>> H2 has a really excellent documentation: 
>> https://www.h2database.com/html/commands.html
>>
>> Your are looking for: 
>> https://www.h2database.com/html/commands.html#alter_view_rename
>>
>> 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/008fd9af-6859-4063-a806-d1c9595c82fbn%40googlegroups.com.

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

[Andreas Reichel]

Sorry, I seem to have misread your e-mail.

Now it would be:

DROP VIEW .. IF EXISTS
CREATE OR REPLACE VIEW .. IF NOT EXISTS

Best regards
Andreas

On Tue, 2022-07-05 at 02:50 -0700, prrvchr wrote:
> Hi Andreas,
> 
> The UNO API also provides an interface com.sun.star.sdbcx.XRename to
> change the name of a view, but apparently this interface is not
> implemented in Base because it is never called.
> 
> But I don't want to rename the view but change its command, and I
> can't find a command in H2 to do so.
> Please advise me the best alternative.
> 
> 
> Le mardi 5 juillet 2022 à 11:38:52 UTC+2, and...@manticore-
> projects.com a écrit :
> > Greetings!
> > 
> > On Tue, 2022-07-05 at 02:36 -0700, prrvchr wrote:
> > > What is the best alternative with H2?
> > 
> > 
> > H2 has a really excellent
> > documentation: https://www.h2database.com/html/commands.html
> > 
> > Your are looking
> > for: https://www.h2database.com/html/commands.html#alter_view_rename
> > 
> > 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/81a629f4-f62f-4f34-beba-d478f52ce688n%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/f5b765a084d7bb9add81e7cc78e7bf6ebdcc.camel%40manticore-projects.com.

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

[prrvchr]

Hi Andreas,

The UNO API also provides an interface com.sun.star.sdbcx.XRename 

 
to change the name of a view, but apparently this interface is not 
implemented in Base because it is never called.

But I don't want to rename the view but change its command, and I can't 
find a command in H2 to do so.
Please advise me the best alternative.


Le mardi 5 juillet 2022 à 11:38:52 UTC+2, and...@manticore-projects.com a 
écrit :

> Greetings!
>
> On Tue, 2022-07-05 at 02:36 -0700, prrvchr wrote:
>
> What is the best alternative with H2?
>
>
> H2 has a really excellent documentation: 
> https://www.h2database.com/html/commands.html
>
> Your are looking for: 
> https://www.h2database.com/html/commands.html#alter_view_rename
>
> 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/81a629f4-f62f-4f34-beba-d478f52ce688n%40googlegroups.com.

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

[Evgenij Ryazanov]

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 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/6b7abe0b-8ebd-485a-885a-8b780a226c39n%40googlegroups.com.

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

[Andreas Reichel]

Greetings!

On Tue, 2022-07-05 at 02:36 -0700, prrvchr wrote:
> What is the best alternative with H2?

H2 has a really excellent
documentation: https://www.h2database.com/html/commands.html

Your are looking
for: https://www.h2database.com/html/commands.html#alter_view_rename

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/3af602f1c44aa76d90aa0614a281b6493550c0e1.camel%40manticore-projects.com.

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

[prrvchr]

Hi all,

I come back to you for help.

Base offers the ability to change the command of a View with the 
com.sun.star.sdbcx.XAlterView 

 
interface.
With HsqlDB I just have to run the command:

`ALTER VIEW ViewName AS NewCommand`

What is the best alternative with H2?

Thanks.

-- 
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/2e77665b-291e-491b-9655-8c97cd8fe3ddn%40googlegroups.com.