Frank, >> 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.
Maybe a third mode could help here: Semi-Native SQL, where base would only scan for parameter definitions using the :<name> scheme. > 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. What a hack ;) > > 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. No, it only happens for parsed queries. - Marc > > Ciao > Frank --------------------------------------------------------------------- To unsubscribe, e-mail: dev-unsubscr...@dba.openoffice.org For additional commands, e-mail: dev-h...@dba.openoffice.org
