David Warren wrote:
to change a long filter that syntax wise works with oo base macros.
I have managed to change a filter to run a report but oo Base has to
have double quotes around file and field names and single quotes around
variables (unlike straight sql).
This means that a long query can only be changed by adding something on
the end or replacing a string in the middle. Is it at all possible to
build up an sql query with select, from, where and order clauses as
separate components that can be replaced in a macro and then
concatenated together.
Thanks again for your patience but I need need to create sql
commands dynamically to update the database as well as filter reports,
forms and queries.
David
The answer is yes.
The OO.o API offers two services:
SingleSelectQueryAnalyzer and SingleSelectQueryComposer
Reference material is available at:
http://api.openoffice.org/docs/common/ref/com/sun/star/sdb/SingleSelectQueryAnalyzer.html
http://api.openoffice.org/docs/common/ref/com/sun/star/sdb/SingleSelectQueryComposer.html
http://wiki.services.openoffice.org/wiki/Documentation/DevGuide/Database/Database_Access
A very brief overview:
CAVEAT - most, if not all, of the below statements are valid only for
queries that can be run through the analyzer. Meaning that if the SQL
requires you to set EscapeProcessing = FALSE this turns access to these
services off also.
The services are usually instantiated from an open connection. ( For all
practical purposes in a Basic library this is always - but see beloew )
They abstract out things like quoting of identifiers versus strings
based on the particular database engine used at runtime.
You can also work with the parts of the query:
Tables used
Columns returned
Where Clause
Grouping Clause
Order By
Having Clause
add a simple filter to an existing query, which is a way of adding an
overall AND condition
update or add multiple filters for working with OR conditions
get a list of the replaceable parameters used, by name or position, to
that you can supply values for them at runtime
get the SELECT statement as it will be sent to the database engine. For
example if the original Query is using a Query-in-Query structure you
can ask to see the select statement after being expanded - in other
words get the sub-select SQL that will be sent to the server.
You can work with the services directly but more often you will be
working with other objects that expose some functions of the services.
For instance - a data entry form contains at least one dataform control.
The dataform control can be thought of as an aggregate object.
It has a collection of controls attached to a particular result. It
allows access to the ResultSet that supplies the data and to these two
services.
I hope that helps.
Drew
ps - I am also sending the original question and response to the
[email protected] mailing list.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
