Actually, let me amend that last email. There is a big difference between the two. A view in HSQLDB and most ( many at least ) db engines is always read only. There are ways in many DMBSs to make it appear to be editable using triggers - but the fact is a simple view is usually read only.
With a table function or a prcoedure that returns a result set this could be updatable. Creating a query on a view in Base today could not be made updatable. On 9/18/06, Andrew Jensen <[EMAIL PROTECTED]> wrote:
Well, just did the same in the SQL window of Base. Created the table TEST2 and left it empty. Created a view V1_T2 with the :PART_NAME param. It took it just fine. Loaded data in to TEST2 Select * from V1_T2 Returns an empty result set. Dropping the view and running the create statement again, with data in the table, now generates the error for the unrecognized token! Curious - indeed I have no connection the HSQLDB team, except to have asked a few questions on their support mail list. I will ask another one regarding this conversation and report back any response to this list. As for advice. That depends on what you are looking for - if you have a particular use that you thought you would solve with a parametrized view then that is reasonably simple. Create your view with everything except the parameters. Create a query in Base that selects from the view and adds the parameters. That is no different in result as having used one of the mentioned table functions or a stored procedure in the database that takes parameters and returns a result set. Best regards Drew On 9/18/06, Jahn, Ray (R.) <[EMAIL PROTECTED]> wrote: > > > Andrew Jensen wrote: > > ... details of tests about HSQL data parameters ... > > ... test 1: HSQL 1.8.0.5 + OO 2.0.4 ... > > ... test 2: HSQL 1.8.0.5 + HSQL DatabaseManagerSwing ... > > Drew, I am much obliged to you on your detailed answer. > > Out of curiosity, I re-organized your test code. > I got different test results. > It is identical to my previous test result. > I am quite confused now. > > Do you have connection to the HSQL team? > Additional advice is appreciated. > > Ray > > ----- > > OS: MS Windows XP Professional, Service Pack 2 > DB: HSQL 1.8.0.5 > CUI: org.hsqldb.util.DatabaseManager > JVM: Sun 1.5.0-08 (not MS) > URL: jdbc:hsqldb:file:d:/test > driver: org.hsqldb.jdbcDriver > type: HSQL Database Engine Standalone > > java -version > java -cp d:/hsqldb/lib/hsqldb.jar org.hsqldb.util.DatabaseManager > > CREATE TABLE TEST1 ( ID IDENTITY, VAL VARCHAR( 50 ) ); > > -- test against empty table, result: no failure > CREATE VIEW V1 AS ( SELECT * FROM TEST1 WHERE VAL LIKE :PART_NAME ); > > -- add data now > INSERT INTO TEST1 VALUES ( NULL, 'Tom' ); > INSERT INTO TEST1 VALUES ( NULL, 'Dick' ); > INSERT INTO TEST1 VALUES ( NULL, 'Harry' ); > INSERT INTO TEST1 VALUES ( NULL, 'Thelma' ); > INSERT INTO TEST1 VALUES ( NULL, 'Donna' ); > INSERT INTO TEST1 VALUES ( NULL, 'Harriet' ); > > -- double check table, OK > Select * From test1; > > -- test against filled table, result: no failure > CREATE VIEW V2 AS ( SELECT * FROM TEST1 WHERE VAL LIKE :PART_NAME ); > > -- the normal view, no problem > -- see if HSQL quietly does something to the other views > CREATE VIEW V3 AS ( SELECT * FROM TEST1 WHERE VAL LIKE 'H%' ); > Select * From V3; > > -- now prepare the file for examination > commit; shutdown script; > > *** content of the final *.script file *** > *** notice the retention of all Views and the parameter *** > > CREATE SCHEMA PUBLIC AUTHORIZATION DBA > CREATE MEMORY TABLE TEST1(ID INTEGER GENERATED BY DEFAULT AS > IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,VAL VARCHAR(50)) > ALTER TABLE TEST1 ALTER COLUMN ID RESTART WITH 6 > CREATE VIEW V1 (ID,VAL) AS ( SELECT * FROM TEST1 WHERE VAL LIKE > :PART_NAME ) > CREATE VIEW V2 (ID,VAL) AS ( SELECT * FROM TEST1 WHERE VAL LIKE > :PART_NAME ) > CREATE VIEW V3 (ID,VAL) AS SELECT * FROM TEST1 WHERE VAL LIKE 'H%' > CREATE USER SA PASSWORD "" > GRANT DBA TO SA > SET WRITE_DELAY 20 > SET SCHEMA PUBLIC > INSERT INTO TEST1 VALUES(0,'Tom') > INSERT INTO TEST1 VALUES(1,'Dick') > INSERT INTO TEST1 VALUES(2,'Harry') > INSERT INTO TEST1 VALUES(3,'Thelma') > INSERT INTO TEST1 VALUES(4,'Donna') > INSERT INTO TEST1 VALUES(5,'Harriet') > > *** notes > > Retention of data parameters in HQL *.script > does not mean that HSQL CUI can handle them. > In fact, HSQL 1.8.0.5 CUI returns empty results > without any prompt for user input for the following > > Select * From V1; > Select * From V2; > > OO Base GUI is probably in a better position > to handle such data parameters when retrieved > from *.script. > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > >
