Drew Jensen wrote:
Problem 2
Using the same query, I wanted to substitute MONTH(Filing_Date1) by a
parameter, e.g. :Mois_Choisi, or ?Mois_Choisi and allow the user to
input a value from 1 to 12 representing the 12 months of the year.
However, it doesn't seem to matter which way I formulate the
parameter, ie. whether using the OOo way with a colon, or using the
mysql way with a question mark, I always get an error message that
either there is an error in my SQL statement, or that the "parameter
variable was not given". OOo didn't even ask me for the parameter in
the first place, but perhaps that is because it only does so when in
OOo SQL parsing or Query Design mode and not in SQL Direct mode. I
also tried switching the option ParamaterModeSubstitution on and off
in the driver settings.
So that leads me to my second question : is there a problem with
parameter substitution in OOo ?
Well, the problem is that parameter substitution only works with
Escape Processing turned on.
Given the problem with datediff under MySQL requires that Escape
Processing be turned off this negates your ability to use this feature.
Now, could that query be re-written in a way to allow escape
processing on the client (OO.o) side?
OK - well you can do that. Won't be as efficient (perhaps) but it works
of course.
Create 2 queries.
First query is just for that datediff column and of course must also
return your key value.
Set escape processing off (Run SQL Direct) for this query.
Now create a second query the joins your table and the first query on
the key.
Here in this second query you can have Escape Processing set to true -
so you can use the parameter substitution for the month as you desire.
HTH,
Drew
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
