>
> -----Original Message-----
> From: firebird-support@yahoogroups.com

[mailto:firebird-support@yahoogroups.com] On Behalf Of Milan Babuskov

> Sent: Thursday, August 03, 2006 11:26 AM
> To: firebird-support@yahoogroups.com
> Subject: Re: [firebird-support] UDFs and Exceptions
>
>
> firebird@... wrote:
> > It's pretty clear that UDFs can't provoke an SQL error, which is

unfortunate

> > for me, because I have a Regular Expression UDF and it would've been
> > great if I could've returned an error in this way when the pattern had

an

> > error (as opposed to not matching).
>
> You could return an integer:
>
> 0 - matches
> 1 - doesn't match
> 2 - error in pattern
>
> --
> Milan Babuskov
> http://swoes.blogspot.com/
> http://www.flamerobin.org

Well, actually, it does -1 for error, 0 for no match, 1 for match. But the
point
of being able to cause an SQL error to be returned to the client is that I
could
then say what the error was in the returned error structure. Also, the user
would know immediately that an error was occurring. I'm thinking about a
situation where

select bar from foo where REGEXMATCH(bar, <pattern>) = 1

returns an empty set not because because there are no matches, but because
<pattern> is bogus.

I'm also worried about debugging a complex pattern when all you are getting
from the UDF is that it's wrong, with no other information. I'm a fan of
regular expressions
and often find them useful, but I'm very bad at writing them correctly the
first time
if they're anything more than trivial.

I suppose what I'll do is to add another UDF with the same arguments that
returns
a string in which I can describe the error instead of an integer. The
trouble with
that approach is that some errors are transitory (memory allocation failure,
stack
overflow, etc.) and could conceivably not occur on the second call but I
guess
this would be better than nothing.



Michael D. Spence
Mockingbird Data Systems, Inc.


[Non-text portions of this message have been removed]