File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Unable to Insert into temp db(HSQL) VIEW. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Unable to Insert into temp db(HSQL) VIEW." Watch "Unable to Insert into temp db(HSQL) VIEW." New topic
Author

Unable to Insert into temp db(HSQL) VIEW.

kapil shukla
Greenhorn

Joined: Jan 10, 2010
Posts: 4
Hi all,

I am facing this issue with HSQL DB. Even if i try to create a simple view, i am unable to insert into it. Following are the steps I am trying:

1. create table test_emp(emp_id int, emp_name varchar, emp_age varchar, emp_sal varchar);
2. insert into test_emp values (4, 'rakesh', 29, 35000);//inserted some values into the table
3. CREATE VIEW emp_view as select emp_id,emp_name, emp_age, emp_sal from test_emp;// creating a view displays the values which are available in table.
4. INSERT INTO EMP_VIEW (emp_id,emp_name, emp_age, emp_sal) VALUES (9, 'naresh', '41', '42000');//this line gives error
[Error Code: -55, SQL State: S0005] Not a Table: EMP_VIEW in statement [INSERT INTO EMP_VIEW]

It seems it will accept only a table name here.
I am using Temp DB so all the tables, view and even the db gets deleted once session is completed.

Can anybody help me out in resolving this issue?

Thanks,
Kapil
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1106

You can't insert into a view (usually), a view is a read only way of looking at the database.

A view can select from many tables and have a complicated where clause with outer joins and inner joins, unions and subselects. How can the database know what to insert where, how would it handle mandatory columns that were on the table but not in the view?

kapil shukla
Greenhorn

Joined: Jan 10, 2010
Posts: 4
we can insert in a DB VIEW, provided some conditions. You can go through this link for that http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_9014.htm#i2080505

probably I could use INSTEAD OF trigger, but it is also giving me error.


CREATE TRIGGER EMP_Trigger on EMP_VIEW
INSTEAD OF INSERT
AS
BEGIN
--Build an INSERT statement ignoring inserted.ID and
--inserted.ComputedCol.
INSERT INTO TEST_EMP
SELECT emp_id,emp_name, emp_age, emp_sal
FROM EMP_VIEW
END;

ERROR: Unexpected Token INSTEAD, requires ON in statement

is there something wrong with this?
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1106

are you using HSQLdb (hyper SQL) or oracle, as you are linking to the oracle database syntax not HSQL db syntax
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Unable to Insert into temp db(HSQL) VIEW.
 
Similar Threads
mapping parent child relationship
Hibernate primary Key/User defined Generator class
Oracle Cursor
hsqldb - how to create db?
Regular expression - Pattern and matcher