Inserting into View not supported

[Thotheolh]

Hi. I noticed that insertion of data into view is not supported while
using it via JDBC for a simple Java app.

JDBC: jdbc:h2:mem (memory mode without username or password)

Creating Table: CREATE TABLE IF NOT EXISTS DUMMY (UID INT PRIMARY KEY
AUTO_INCREMENT, UNAME VARCHAR(200));

Creating View: CREATE VIEW DVIEW AS SELECT * FROM DUMMY;"

Insertion SQL: PreparedStatement pstmt =
connection.prepareStatement("INSERT INTO DVIEW(UNAME) VALUES (?)");

org.h2.jdbc.JdbcSQLException: Feature not supported: "VIEW"; SQL
statement:
INSERT INTO DVIEW(UNAME) VALUES ('kelvin') [50100-143]

Would this feature be available somewhere soon ?

[tuc70899]

Greetings,

I was able to reproduce this..

stat.execute("create table test(id int primary key, name
varchar(255))");
stat.execute("insert into test values(1, 'Hello')");
stat.execute("insert into test values(2, 'World')");

stat.execute("CREATE VIEW hello_view AS SELECT name FROM test
");
stat.execute("INSERT INTO hello_view VALUES ('Test');");
.
.
Exception in thread "main" org.h2.jdbc.JdbcSQLException: Feature not
supported: "VIEW"; SQL statement:
INSERT INTO hello_view VALUES ('Test'); [50100-153]

Michael

[Thotheolh]

Thanks for the confirmation. Hopefully, inserting and maniuplating of
data could be done on a table view in near future.

[Thomas Mueller]

Hi,

It is possible to update views using "instead of" triggers. Please see this sample application:

http://code.google.com/p/h2database/source/browse/trunk/h2/src/test/org/h2/samples/UpdatableView.java

I currently don't plan to make views updatable 'automatically', but I will better document this existing feature.

Regards,
Thomas

[Martin Schmiedel]

Hi Thomas,

I'm bringing this old thread back to life, but it's still better than starting a new one with the same issue. I'm running into the same situation, but I really don't want to create a trigger in this way (with a Java Class). Is there a way to do the same thing without having to create Java code? I would like to be able to leave this to the Database 100%.

[Rami Ojares]

I feel your pain.
I would like to be rich.
You know like filthy rich.
But the banks simply refuse to give me the money!

- Rami

Ps. View updateability is a complex issue and currently not implemented
in a generic and complete fashion in ANY database in existence (afaik).
The nice thing with h2 is that it still allows you to do it case by
case if you really need it.
I do agree that view updateability is a powerful feature.

> --
> 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
> <mailto:h2-database...@googlegroups.com>.
> To post to this group, send email to h2-da...@googlegroups.com
> <mailto:h2-da...@googlegroups.com>.
> Visit this group at http://groups.google.com/group/h2-database.
> For more options, visit https://groups.google.com/d/optout.