Hi Ray, > 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
citing from there: "Parameters in a VIEW definition are not supported, although currently no error is raised when they are used." In fact, you can *create* a view containing parameters, but they're completely ignored. In particular, if I programmatically create a statement "SELECT * FROM <view>" and ask for its parameter count, HSQL always returns 0. (I just tried this with 1.8.0.7.) So, there's something to be done on the HSQL side. > 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. That's not the way I would want to implement this. There is a dedicated API in JDBC (the PreparedStatement.getParameterMetaData) for asking for the parameters of a given statement. It's up to the engine then to deliver the right information (see above - HSQL currently doesn't for parametrized views). This way, you would not need to rely on OOo's parsing capabilities, but can use the full power of HSQL in views. Of course we *could* implement this the way you suggested, but this would be rather complicated in case the view is only a small part of a more complex statement. OOo would need to analyze all tables in the FROM clause, find out which ones are views, retrieve their statement, analyze the parameters, do the same recursively with the tables in all those views ... This will break as soon as one of the involved views is not parseabe, not to mention that it is not necessarily guaranteed that Base will get to the same results (with respect to parameter order) as HSQL will. > 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. Currently, for parameter recognition, Base relies *completely* on it's own parsing capabilities. That's why you can, for instance, not use parameters in "native" client-side queries - with turning "Run SQL directly" ON in the query design, you tell Base to not touch/parse the statement, which implies no parameter support. So, on OOo side, we would need to adjust our behaviour that we, where possible, rely on the DB engine when recognising parameters. I cannot judge at the moment how big a change this would be, but it's certainly possible. >> 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. I think we're on the same track here. Both approaches - CUI and GUI - are valueable, for different classes of users. There's a certain class which would not feel comfortable with executing your SQL examples via Tools/SQL, and there's a certain class which does. Both is perfectly okay. I originally said that views might be preferred by the second class, but in the same sentence mixed this with the first class. In fact I think there is an audience which wants to use views for various reasons, but wants to do this via GUI - some intermediate between the two classes above. That's why I think it might be good to have editable (per UI) views. Ciao Frank -- - Frank Schönheit, Software Engineer [EMAIL PROTECTED] - - Sun Microsystems http://www.sun.com/staroffice - - OpenOffice.org Base http://dba.openoffice.org - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
