Hi Markus,
The JPA specification says the following about Trim in a footnote to
section 4.6.16.1.
[30] Note that not all databases support the use of a trim character
other than the space character; use of this argument may result in
queries that are not portable.
As a result, I think it will be ok if we do not provide an
implementation on SQLAnywhere and fix the test to detect this.
I think the option of providing some regular expression support in
expression operator is potentially a good idea, but likely quite
labor-intensive. Please feel free to enter an enhancement request.
TopLink, as a rule, does not create stored functions automatically, so
I would prefer to avoid this option.
-Tom
Markus KARG wrote:
>Tom,
>
>there is more more issue with the SQLAnywhere platform I am writing, and
>it would be great if you could give me some idea: SQLAnyhwere doesn't
>implement LeftTrim2, so I am looking for a workaround. In fact, I found two:
>
>(a) Rewrite by using regular expressions what actually worked well in a
>short interactive sql test on SQLAnyhwere: SUBSTR(a, PATINDEX(x,a))
>where a is the original string, and x is built by the pattern "%[^c]%",
>with "c" beeing the character to trim off. Actually I do not think that
>this is working in TopLink, due to two reasons: (1) ExpressionOperator
>is not able to repeat a parameter, what is needed to have "a" put twice
>into the sql output. (2) ExpressionOperator is not able to provide a
>value that is to be built from a pattern, what is needed to have
>"%[^c]%" be provided as one string parameter's value.
>
>(b) Make TopLink install a stored function into the database if it is
>not already there, e. g.:
>
>CREATE FUNCTION GlassFish_LeftTrim2(a LONG VARCHAR, c CHAR) BEGIN
>...
>END
>
>But I think that there is no entry point in TopLink's
>connect-to-database code that allows to check and install such a function.
>
>So it seems that I have to provide the stored function manually before
>running the tests, which is not nice, but works very well indeed.
>
>What do you suggest to do now? Shall we live with it (not smart) or
>shall I file a feature request?
>
>Thanks
>Markus
>
>