Hi Ray, > HSQLDB 1.8.0.7 already supports SQL named parameter. This support is > inherent in HSQLDB and independent of OO Base GUI.
Yes. > Apparently OO Base > 2.1.0 also supports access to SQL named parameter implemented in > whatever database engines, based on the following info.: > > * > documentation.openoffice.org/HOW_TO/data_source/link_tablehtml/link.html > #6.Enabling Use of Named Parameters for the Data Source|outline That's a little bit misleading. Base always supported named parameteres, the point is some databases don't (e.g. HSQL prior to 1.8.0.7). To nonetheless allow Base's user to use named parameters, Base "downgraded" them to unnamed ones before sending them to the database. For instances, a statement like SELECT * FROM "customers" WHERE "ID" = :cid would be translated to SELECT * FROM "customers" WHERE "ID" = ? The latter is understood by far more (all?) databases than the former. Since this downgrading could have side effects, it was done only when a data source was set up so - and *this* is what the macro is good for. It tells Base to use Patamater Name Substitution for one particular data source. This option was available for data source types which could address more than one particular backend, namely for ODBC and JDBC. All others would have ignored this option if set. HSQL never had this option, since we internally always used the substitution, since the backend was under our control and we knew there was no side effect. > I could not figure out how to turn on the named parameter for HSQLDB > data sources in OO Base through OO Base GUI, without using the OO Basic > script, as suggested in OO Issue 2794. The resolution "fixed" in OO > Issue 2794 implies that OO Base already has the UI code to collaborate > with database engine (no UI) for the named parameters in queries/views > stored on the engine side. This issue requested a user interface for enabling or disabling the substitution. As above, the option is only available for ODBC and JDBC (plus some more, see "Data Source Properties dialog" in http://dba.openoffice.org/specifications/index.html#application). > In addition, is there a way to set the default through OO Base GUI for > the named parameter option for "all" data sources in OO Base? No. > Please note an implementation subtlety in OO Base stored query. OO Base > 2.1.0 GUI currently contains query capability that partially duplicates > the SQL queries and SQL views stored on the database engine side. Let > us call them the GUI query and SQL query for the sake of brevity. > Although their usage seems initially similar to casual users, they are > very different for database development and deployment purposes. GUI > query is stored on the OO GUI specific side (XML part of zip), and SQL > query in the HSQLDB specific side (database\* of zip), or SQL query in a > separate file for an external HSQLDB (not embedded in OO Base), or SQL > views in yet another file for linked tables (virtual tables, etc.). The > naturally conform to syntax rules as mandated by DB engines (HSQLDB, > MySQL, Oracle, etc.) and GUI that might differ and that will continue to > evolve in competition. The revision and update strategy will be > different for a server and client configuration in business environment. > > There is also potential impact on the execution of multi-tier SQL > statements as well as handling hierarchical data in RDBMS. > > Thanks for advice. I'm actually not sure which kind of advice you seek :-\. What you describe above is true (one could add that also the "server side capabilities" are subject to evolution). Which of the two "kinds" of queries the user chooses depends on various factors. If you're perfectly okay with typing mere SQL statements, you might prefer views (except that currently, you cannot edit them - sigh). If you need graphical assitance, you're often bound to the GUI queries. For a lot of other constraints, you might decide for one or the other ... 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]
