Hi Marc, > I can, however, set the query editor > to native SQL mode, then the SQL code is passed as is to the database > backend and no error is output. But with native SQL mode, I loose the > possibility to have OpenOffice ask the user for parameters. I can only > use calls without parameters. But most stored procedurs actually > require paramates.
Indeed, native queries do not allow for parameters. This is caused by the fact that for recognizing the parameters, Base would need to parse the statement, which contradicts the "do-not-parse" restricting imposed by the "native" flag. Some of the underlying APIs (JDBC, for instance) support retrieving parameter information from the database/driver, but that's not used in Base currently. Issue 66559 would probably fix this, at least for all JDBC based connections (the issue talks about HSQL only, but that wouldn't matter much). Another option - which I am not sure an issue exists for - would be to let the creator of the native query define which parameters to ask the user for - would be the responsibility of the creator then to ensure consistency. > There is a second (and maybe unrelated) problem: OpenOffice Base seems > for each query to send a generated query to the database server to check > if that table in the query exists. It does something like > > SELECT COUNT(*) FROM <tablename> WHERE 0 = 1 > > This will of course never return any data, but it will raise an error if > <tablename> does not exist, so I assume this is done to check for the > presence of a table. But again, this does not work when a query instead > of table specifies a stored procedure in a SELECT. For historic reasons, the "WHERE 0 = 1" thing is done for retrieving column information, with a fallback to ask the meta data of the prepared statement. I suppose the latter didn't work properly formerly, for a certain set of database/drivers, so "WHERE 0 = 1" had been invented (well, that's not really an invention of Base), since the meta data of a result set (which is forced to be empty) were more reliable than the meta data of the prepared statement. If you're saying this happens even for native queries, then please submit an issue for it - it should be done for non-native queries only, IMO. Ciao Frank -- - Frank Schönheit, Software Engineer frank.schoenh...@sun.com - - Sun Microsystems http://www.sun.com/staroffice - - OpenOffice.org Base http://dba.openoffice.org - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - --------------------------------------------------------------------- To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org For additional commands, e-mail: dev-h...@dba.openoffice.org
