Hi, Frank: Thanks for insight.
> Frank wrote: > In fact, parameters in views do not work at all in HSQL, neither named nor > unnamed ones. Admittedly, I'm not sure at the moment whether this is on HSQL > or on Base side, it might be a mix of both. It seems to me that it is straightforward to make SQL View with parameters work, as GUI Query with parameters is already working. A simple case of reusing the existing code. Please see the analysis below. past discussion of relevance: * sf.net/forum/message.php?msg_id=3933424 showed that HSQLDB engine has done all it could minus client UI, i.e. the run time dialog * Search in mail list [email protected] Type: subject line Keys: "SQL View Query parser" (inc. the quotes) (sorry, I do not know how better to provide a succinct URL) Some messages of interest * dba.openoffice.org/servlets/ReadMsg?list=users&msgNo=4008 (Andrew Jensen) * dba.openoffice.org/servlets/ReadMsg?list=users&msgNo=4012 (Frank Schönheit) * dba.openoffice.org/servlets/ReadMsg?list=users&msgNo=4020 (Frank Schönheit) * dba.openoffice.org/servlets/ReadMsg?list=users&msgNo=4022 (Frank Schönheit **) * dba.openoffice.org/servlets/ReadMsg?list=users&msgNo=4030 (Andrew Jensen) * dba.openoffice.org/servlets/ReadMsg?list=users&msgNo=4031 (Andrew Jensen) By design and by nature, parameters (named or unnamed) in SQL View require collaboration between DB engine (OO HSQLDB) and client UI (OO Base). DB engine parses, accepts, and stores the SQL View with parameters. Client UI (most often by GUI) retrieves the SQL View definition from DB engine, recognizes the existence of parameters, prompts for run time parameter substitution with user dialogs, sends the reconstituted query (SQL View) back to DB engine, and receives the result set from DB engine. HSQLDB 1.8.0.7 (no UI) already stores the parameters (named and unnamed) in SQL View definition. HSQLDB has done everything that it should. The client UI (OO Base GUI) seems to be the culprit of the problem at present. OO Base 2.1.0 does not run user dialogs upon seeing named parameters in SQL View. I do not know the details of the OO Base GUI code. A naïve logic flow is provided below: 1. Does it retrieve SQL View definition from DB engine? 2. Does it check for the presence of named parameters in SQL View definition? 3. Does it activate user dialogs for the parameters? 4. (subsequent processing) GUI Query in OO Base already does everything starting from step 2. SQL View can work simply by re-using the same code from step 2 and beyond. Is this analysis reasonable? Please note that java.sql.PreparedStatement is not mandated if we re-use the GUI Query code, as previously implied in dba.openoffice.org/servlets/ReadMsg?listName=users&msgNo=4028 ----- ----- abbreviation: * GUI - Graphical User Interface * CUI - Character User Interface > Ray wrote: > By "Currently you cannot edit view ..." do you refer to revising SQL View > through OO GUI? In OO Base 2.1.0, SQL View revision is easy by using SQL > statements through OO CUI (Tools - SQL). > Frank wrote: > Yes, but you cannot open the query designer - neither in graphical nor in SQL > view - for editing an existing view. The method of OO CUI (Tools - SQL) has nothing to do with either of the GUI approaches mentioned here. OO Base has supported this valuable CUI feature for a long time and I appreciate it very much. A few simple examples are provided below to illustrate how easy OO Base CUI has made the task of SQL code revision and maintenance. Apology if I seem pedantic in front of experts. Please feel free to move this part of discussion to a different thread if comments arise about whether straight SQL language approach (the CUI method) or wrapper of SQL language (the GUI method) is more preferable. -- initial definition of a SQL View -- on DB engine side (Tools - SQL) -- not on OO Base GUI side Create View "vv test" As Select tt01."your column 01", tt02."your column 02", etc. >From "your table 01" As tt01, "your table 02" As tt02, etc. Where tt01."column 12" Like :string_param_name and tt02."column 17" = :integer_param_name; -- revision 01 -- a trivial example of changing SQL View definition -- when no user SQL constraint is imposed on this SQL View Drop View "vv test"; Create View "vv test" As etc. -- the rest of new definition -- revision 02 -- when user SQL constraint exists for this SQL View Alter View "vv test" etc. -- many alteration options ----- ----- Regards, Ray --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
