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]
