ALTER VIEW PUBLIC."View1" AS SELECT ....

25 views
Skip to first unread message

prrvchr

unread,
Jul 5, 2022, 5:36:04 AMJul 5
to H2 Database
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

unread,
Jul 5, 2022, 5:38:52 AMJul 5
to h2-da...@googlegroups.com
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


Best regards
Andreas

Evgenij Ryazanov

unread,
Jul 5, 2022, 5:46:38 AMJul 5
to H2 Database
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 …

prrvchr

unread,
Jul 5, 2022, 5:50:20 AMJul 5
to H2 Database
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

unread,
Jul 5, 2022, 5:56:50 AMJul 5
to h2-da...@googlegroups.com
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

unread,
Jul 5, 2022, 5:58:45 AMJul 5
to H2 Database
Hi Evgenij,

Thank you for answering me. I will use:

CREATE OR REPLACE VIEW viewName AS ..

prrvchr

unread,
Jul 5, 2022, 7:09:03 AMJul 5
to H2 Database

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.
Reply all
Reply to author
Forward
0 new messages