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.

[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

[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

[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.

[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

--
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/81a629f4-f62f-4f34-beba-d478f52ce688n%40googlegroups.com.

[prrvchr]

Hi Evgenij,

Thank you for answering me. I will use:

CREATE OR REPLACE VIEW viewName AS ..

[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.