Regular Expressions in SQL with Interbase?
Paul Linehan
Hi all,
Is it possible to use Regular Expressions with Interbase SQL?
If no (as I think), I have two questions:
Are there any plans to put them in (I see that Oracle has just
introduced this feature, and PostgreSQL has them for a while).
Has anyone written (or would they like to write 8-) ) a UDF which
accomplishes this?
I would be interested in doing/helping out with such a project. Is it
possible even - I can't see why it would be impossible!
Paul...
--
plinehan__AT__yahoo__DOT__com
C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04 W2K Pro
Please do not top-post.
Bill Todd
On Sat, 11 Oct 2003 12:03:34 +0100, Paul Linehan
<pa...@not.a.chance.ie> wrote:
>Is it possible to use Regular Expressions with Interbase SQL?
Only if you can find a UDF that supports them.
>
>
>If no (as I think), I have two questions:
>
>Are there any plans to put them in (I see that Oracle has just
>introduced this feature, and PostgreSQL has them for a while).
No plans have been announced.
>
--
Bill (TeamB)
(TeamB cannot respond to questions received via email)
Paul Linehan
n...@no.com says...
> >Is it possible to use Regular Expressions with Interbase SQL?
> Only if you can find a UDF that supports them.
I haven't been able to find one.
As a matter of interest though, do you think that it would be a
worthwhile thing to do, or just merely a "buzzfeature"?
Paul...
> Bill (TeamB)
Bill Todd
Certainly there are other functions I would much rather have first
before R&D spends time on regular expressions.
Paul Linehan
> I cannot think of a case where I have needed regular expressions.
OK, that's fine. I have seen a couple of examples on Oracle's site, and
they did seem to be potentially useful.
> Certainly there are other functions I would much rather have first
> before R&D spends time on regular expressions.
I wasn't talking about R&D - I was talking about using UDF's, written by
members of the great unwashed (such as myself). Indeed, I think it's one
of Interbase's great features that one can write one's own functions
into the SQL.
Just as a matter of interest, is there much of a difference between
using UDF's and calculating the value as a function within SQL - e.g.
UDF Select Fahrenheit_to_Celsius(MyVariable) as CelsiusTemp from MyTable
or
Select (5/9)*(MyVariable-32) as CelsiusTemp from MyTable
?
Thanks.
Craig Stuntz [TeamB]
> As a matter of interest though, do you think that it would be a
> worthwhile thing to do, or just merely a "buzzfeature"?
It would be *possible* to implement such a feature with a collation
driver. I wouldn't personally have a use for it, however.
-Craig
--
Craig Stuntz [TeamB] . Vertex Systems Corp. . Columbus, OH
Delphi/InterBase Weblog : http://delphi.weblogs.com
InterBase Performance Monitor -- Analyze and control your IB7
server: http://delphi.weblogs.com/IBPerformanceMonitor
Holger Klemt
http://members.fortunecity.com/polarissoft/Products/rFunc/rfunc.html
CalcExpr(S, S)
For example:
CalcExpr('Sin( Pi*:p11/3)*:P2', 'P11=2;P2=10') f
The unpretentious analyzer of expressions.
Supported functions and operations
+ - * /
Sin, Cos, tg, ctg, Ar?Cos, ArcSin, ArcTg, sh, ch, th,
exp, lh, lg, sqrt, sqr, abs(x1)
sgn(x1) - the sign of number x1
log(x1,x2) - log x1 on the basis x2
power(x1,x2) - exponentation x1 in a degree x2
if(expr, x1, x2) - if expr=0, then x2, else x1
Min(x1, ...) - minimum from numbers
Max(x1, ...) - maximum from numbers
Sum(x1, ...) - toting of arguments
Avg(x1, ...) - average arithmetic
Pi - number Pi.
For usage of variables in expression - before them the colon is put, and
they are described in the second parameter (see example).
--
IBExpert - The most Expert for InterBase and Firebird --- www.ibexpert.com
HK Software - Holger Klemt - Huntestrasse 15 - D-26135 Oldenburg
Telefon Telefax +49 700 IBEXPERT (42397378) www.h-k.de
Schulungen - Projektunterstützung - Delphi - InterBase - AS/400
Bill Todd
<pa...@not.a.chance.ie> wrote:
>Just as a matter of interest, is there much of a difference between
>using UDF's and calculating the value as a function within SQL - e.g.
1) You have to install the UDF on each machine where IB runs.
2) You have to add the UDF functions to every database.
3) You have to hope that whoever wrote the UDF did nat make a mistake
that will bring the server down or cause other errors.
Functions that are build into the database server are always there
and, hopefully, will work correctly out of the box.
Paul Linehan
n...@no.com says...
> I cannot think of a case where I have needed regular expressions.
> Certainly there are other functions I would much rather have first
> before R&D spends time on regular expressions.
Me again Bill,
As a matter of interest, what would those functions be?
Is it true that one cannot use UDF's in SP's?
Craig Stuntz [TeamB]
> As a matter of interest, what would those functions be?
For myself, I'd lean towards things that are part of the SQL standard,
such as CASE and COALESCE.
> Is it true that one cannot use UDF's in SP's?
No.
Steve Weston
makes it a must-have. ;-)
--
Steven S. Weston
Bill Todd
No. That is not true.
Bill Todd
date functions built in. UDF's should be the solution for unusual
requirements, not for the common functions.
On 13 Oct 2003 12:16:53 -0700, "Craig Stuntz [TeamB]"
<cst...@nospam.please [a.k.a. vertexsoftware.com]> wrote:
>Paul Linehan wrote:
>
>> As a matter of interest, what would those functions be?
>
> For myself, I'd lean towards things that are part of the SQL standard,
>such as CASE and COALESCE.
>
>> Is it true that one cannot use UDF's in SP's?
>
> No.
>
> -Craig
--
Paul Linehan
n...@no.com says...
> I agree. I would also like to see a good basic set of string, math and
> date functions built in. UDF's should be the solution for unusual
> requirements, not for the common functions.
I agree to an extent with both yourself and Craig (CASE, COALESCE) and
about the "basic set" of string, maths and date stuff.
Just wondering - is there any (performance or other) penalty to having
the "basic set" of functions built in as UDF's (i.e. you get the UDF's
by default during the install), rather than being included in the core
engine?
The reason I ask this, is that I kinda like the idea of a bare bones
engine (small footprint) to which one can add any or all of the
functionality one wants through UDF's, rather than perhaps what might be
termed the "Oracle approach" (i.e. "Whadda ya mean, the kitchen sink
isn't in there - Get it in now!")
Just a few thoughts.
Thomas Steinmaurer
news:MPG.19f5d5e7c...@newsgroups.borland.com...
>
> n...@no.com says...
>
> > I agree. I would also like to see a good basic set of string, math and
> > date functions built in. UDF's should be the solution for unusual
> > requirements, not for the common functions.
>
>
> I agree to an extent with both yourself and Craig (CASE, COALESCE) and
> about the "basic set" of string, maths and date stuff.
>
> Just wondering - is there any (performance or other) penalty to having
> the "basic set" of functions built in as UDF's (i.e. you get the UDF's
> by default during the install), rather than being included in the core
> engine?
Two come in mind. Stability and portability.
- Stability: A misbehavioued UDF can crash the server
- Portability: The UDF needs to be compiled for every target platform
HTH,
Thomas Steinmaurer
The IB LogManager Product Family
Logging/Auditing Suite for InterBase and Firebird
http://www.iblogmanager.com
Paul Linehan
hkl...@h-k.de says...
> perhaps there is something helpful in this UDF
> http://members.fortunecity.com/polarissoft/Products/rFunc/rfunc.html
Yes, I've seen this and will be studying the source.
It doesn't answer my question though - I know that it is easy to convert
(as an example) from Fahrenheit to a useful temperature scale i.e.
Celsius - I'm asking if there is any performance hit if one does it as
UDF
- Select Fahrenheit_to_Celsius(MyVariable) as CelsiusTemp from MyTable
or SQL
- Select (5/9)*(MyVariable-32) as CelsiusTemp from MyTable
?
Paul...
Paul Linehan
t...@iblogmanager.com says...
> > Just wondering - is there any (performance or other) penalty to having
> > the "basic set" of functions built in as UDF's (i.e. you get the UDF's
> > by default during the install), rather than being included in the core
> > engine?
> Two come in mind. Stability and portability.
> - Stability: A misbehavioued UDF can crash the server
Yes, that is perhaps true for a "roll-your-own" UDF, but I think that
one could assume that the putative "basic set" supplied by Borland was
bug free - no? Although... 8-)
> - Portability: The UDF needs to be compiled for every target platform
But surely modifications to the core engine also need to be compiled for
every target platform, or at the very least, thoroughly tested - just
like a core "basic set" of UDF's?
My question is really about performance - do UDF's (as opposed to
writing the functionality into the core engine) draw
any performance hit?
or
some performance hit - and if some, what is the extent of the hit?
As I said, I (personally) would be happier with a smaller (leaner,
meaner, sexier) core engine with perhaps an install option of <include
basic set UDF's?> with yes as the default, rather than bulking up the
core with non-essential functionality.
I asked this question before
-----------------------------
Just as a matter of interest, is there much of a difference between
using UDF's and calculating the value as a function within SQL - e.g.
UDF Select Fahrenheit_to_Celsius(MyVariable) as CelsiusTemp from MyTable
or
Select (5/9)*(MyVariable-32) as CelsiusTemp from MyTable
?
----------------------------
Any ideas, discussions, rants, URLs, references welcome.
Paul...
> Thomas Steinmaurer
Paul Linehan
n...@no.com says...
> >Just as a matter of interest, is there much of a difference between
> >using UDF's and calculating the value as a function within SQL - e.g.
> 1) You have to install the UDF on each machine where IB runs.
Make installing your "basic set", yes by default. Those who know about
it and don't want it can switch it off, those who don't - it won't make
much difference.
> 2) You have to add the UDF functions to every database.
Have it done by default.
> 3) You have to hope that whoever wrote the UDF did nat make a mistake
> that will bring the server down or cause other errors.
If the "basic set" is written by Borland, one can always hope!
> Functions that are build into the database server are always there
> and, hopefully, will work correctly out of the box.
Just like a "basic set" supplied by Borland would also?
I'm more getting at the performance angle - is there a performance hit
with UDF's and if so, what is it. See my other posts.
Thomas Steinmaurer
>
> > - Stability: A misbehavioued UDF can crash the server
>
>
> Yes, that is perhaps true for a "roll-your-own" UDF, but I think that
> one could assume that the putative "basic set" supplied by Borland was
> bug free - no? Although... 8-)
Yes, usually they are. AFAIR there was (is?) one problem with substr
returning NULL when it should return the original string. Don't know
if that was fixed and in what version.
> > - Portability: The UDF needs to be compiled for every target platform
>
>
> But surely modifications to the core engine also need to be compiled for
> every target platform, or at the very least, thoroughly tested - just
> like a core "basic set" of UDF's?
Sure, but most people forget that, when using third-party UDFs that
are only available e.g. for Windows and they want to move the database
to Linux or any other platform.
> My question is really about performance - do UDF's (as opposed to
> writing the functionality into the core engine) draw
>
> any performance hit?
>
> or
>
> some performance hit - and if some, what is the extent of the hit?
UDFs are running in the same address space of the IB server process, but
to be honested, I've never encountered noticeable performance loss with
UDFs when they are designed to be "non-blocking" that means for tasks
like calculating or something similar. They might be a performance problem
if you are using UDFs e.g. for sending emails or such stuff.
Only the folks of the R&D will have an reliable answer.
Though, I think, more basic functions built-in, wouldn't hurt, especially
when coming from other DBMS like MySQL, ...
--
Best Regards,
Paul Linehan
t...@iblogmanager.com says...
> > But surely modifications to the core engine also need to be compiled for
> > every target platform, or at the very least, thoroughly tested - just
> > like a core "basic set" of UDF's?
> Sure, but most people forget that, when using third-party UDFs that
> are only available e.g. for Windows and they want to move the database
> to Linux or any other platform.
This is an issue that I'm looking at with respect to creating a UDF for
RegEx's. I've downloaded all the UDF's that I could find and am
currently looking at them - many appear to be available only as dll's
without source, which isn't much good (as you point out) to people who
wish to run on Linux (or Solaris or other platforms which support a
different database that shall be nameless 8-) ).
Obviously, if there was a "basic set" supplied by Borland, these should
be supplied in bog standard C or C++ and come with source.
> > any performance hit?
> > or
> > some performance hit - and if some, what is the extent of the hit?
> UDFs are running in the same address space of the IB server process,
ISTR that they are equivalent to code in the engine itself? Not sure of
my ground here - maybe I'll ask on the nameless one's mailing list?
> but
> to be honested, I've never encountered noticeable performance loss with
> UDFs when they are designed to be "non-blocking" that means for tasks
> like calculating or something similar.
Indeed, to quote Greg Deatz
-------------------
Don't get carried away. As powerful as UDFs can be, don't get carried
away. Be very objective when deciding where you should place little
tidbits of functionality: Am I better served by a UDF or a stored
procedure?
-------------------
> They might be a performance problem
> if you are using UDFs e.g. for sending emails or such stuff.
Well, this much is fairly obvious. But, you would also have performance
issues if you did this in your app as well.
> Only the folks of the R&D will have an reliable answer.
Or people who have access to a similar but nameless db?
> Though, I think, more basic functions built-in, wouldn't hurt, especially
> when coming from other DBMS like MySQL, ...
As I said, include a "basic set" by default, but don't bulk up the core
engine, particularly if there is no performance hit in making them UDF's
as opposed to sticking them into the engine.
Steve Tilmann
Paul Linehan wrote:
> Just as a matter of interest, is there much of a difference between
> using UDF's and calculating the value as a function within SQL - e.g.
>
> UDF Select Fahrenheit_to_Celsius(MyVariable) as CelsiusTemp from MyTable
>
> or
>
> Select (5/9)*(MyVariable-32) as CelsiusTemp from MyTable
>
> ?
Personally, I would implement this conversion as a stored procedure. Pass
in the Fahrenheit value and return the Celsius value. Why? First, you
would be able to call the stored proc from where ever you need to do the
conversion. Thus, you would not have the conversion algorithm spread all
over the place. Second, you might find the need to convert the other
direction and a stored proc would make this easy to implement. Third, a
stored procedure (particularly a simple one like this) would probably be
easier to transport across platforms and data base engines (as opposed to a
UDF). Fourth, the build up and breakdown time for a stored procedure is
really quite short and the performance, in my experience, is quite
acceptable.
steve
Paul Linehan
ste...@pontem.com says...
> > UDF Select Fahrenheit_to_Celsius(MyVariable) as CelsiusTemp from MyTable
> Personally, I would implement this conversion as a stored procedure. Pass
> in the Fahrenheit value and return the Celsius value. Why? First, you
> would be able to call the stored proc from where ever you need to do the
> conversion.
Why? What are the restrictions in calling it if it's a UDF?
> Thus, you would not have the conversion algorithm spread all
> over the place.
Again, I don't understand what you mean here.
> Second, you might find the need to convert the other
> direction and a stored proc would make this easy to implement.
Well, if you can write the C/Delphi to do one, surely it's trivial to do
the converse?
> Third, a
> stored procedure (particularly a simple one like this) would probably be
> easier to transport across platforms and data base engines (as opposed to a
> UDF).
This much is true to a point. Although, surely, bog-standard C (gcc?)
could be used and would be compilable by most compilers - esp. given
that the functionality implemented by UDF's should normally be kept
fairly simple?
Plus, in the case of Interbase, there are only three platforms to be
supported - surely Borland could manage to produce a standard UDF set to
cover at least these?
> Fourth, the build up and breakdown time for a stored procedure is
> really quite short and the performance, in my experience, is quite
> acceptable.
Any difference between UDF's and SP's?
Paul...
> steve
Steve Tilmann
Paul Linehan wrote:
> ste...@pontem.com says...
>
> > > UDF Select Fahrenheit_to_Celsius(MyVariable) as CelsiusTemp from MyTable
>
> > Personally, I would implement this conversion as a stored procedure. Pass
> > in the Fahrenheit value and return the Celsius value. Why? First, you
> > would be able to call the stored proc from where ever you need to do the
> > conversion.
>
> Why? What are the restrictions in calling it if it's a UDF?
The comment was directed toward doing the conversion as part of the select
statement. Obviously, this point is the same for UDF's or stored procs.
However, there may be a difference between a UDF and stored procedure from a
multi-threading (thread safe) point of view. I am not qualified to make the
judgement, however.
>
>
> > Thus, you would not have the conversion algorithm spread all
> > over the place.
>
> Again, I don't understand what you mean here.
see above comment
>
>
> > Second, you might find the need to convert the other
> > direction and a stored proc would make this easy to implement.
>
> Well, if you can write the C/Delphi to do one, surely it's trivial to do
> the converse?
>
> > Third, a
> > stored procedure (particularly a simple one like this) would probably be
> > easier to transport across platforms and data base engines (as opposed to a
> > UDF).
>
> This much is true to a point. Although, surely, bog-standard C (gcc?)
> could be used and would be compilable by most compilers - esp. given
> that the functionality implemented by UDF's should normally be kept
> fairly simple?
>
> Plus, in the case of Interbase, there are only three platforms to be
> supported - surely Borland could manage to produce a standard UDF set to
> cover at least these?
I was directing all of my comments toward the issue of converting Fahrenheit to
Celcius. IMHO, the standard UDF set provided by Borland is pitiful and some of
them don't work. The FreeUDFLib fills in the gaps nicely, as far as it goes.
>
>
> > Fourth, the build up and breakdown time for a stored procedure is
> > really quite short and the performance, in my experience, is quite
> > acceptable.
>
> Any difference between UDF's and SP's?
I am not qualified to answer that one. However, it seems to me that the thrust
of the forum is UDFs can crash the server and stored procedures don't. Again,
all of this is directed toward the Farhenheit-to-Celcius issue.
steve
Craig Stuntz [TeamB]
> However, there may be a difference between a UDF and stored
> procedure from a multi-threading (thread safe) point of view. I am
> not qualified to make the judgement, however.
Not really. UDFs must be thread-safe, period, end of sentence. Procs
must be thread safe, but there is no syntax in the proc language to
write non-thread-safe code.
> I am not qualified to answer that one. However, it seems to me that
> the thrust of the forum is UDFs can crash the server and stored
> procedures don't.
I've crashed the server with a proc. :) It's much "easier" to do
with a UDF, however, since UDFs give you more ways to shoot yourself in
the foot. It's a bit like C vs Pascal. The fact that it's easier to
write code vulnerable to a buffer overrun attack in C doesn't mean that
C is necessarily insecure, it just means that you have some more rules
to follow.
-Craig
--
Craig Stuntz [TeamB] . Vertex Systems Corp. . Columbus, OH
Delphi/InterBase Weblog : http://delphi.weblogs.com
IB 6 versions prior to 6.0.1.6 are pre-release and may corrupt
your DBs! Open Editon users, get 6.0.1.6 from http://mers.com
Holger Klemt
> (as an example) from Fahrenheit to a useful temperature scale i.e.
> Celsius - I'm asking if there is any performance hit if one does it as
>
> UDF
>
> - Select Fahrenheit_to_Celsius(MyVariable) as CelsiusTemp from MyTable
>
> or SQL
>
> - Select (5/9)*(MyVariable-32) as CelsiusTemp from MyTable
>
> ?
What do you expect? typically a udf makes a sql command not faster,
but the difference depends on what it does and how. And if performance
is really important, save the value as a calculated field or by using a
trigger.
Martijn Tonies
> I agree. I would also like to see a good basic set of string, math and
> date functions built in. UDF's should be the solution for unusual
> requirements, not for the common functions.
Of course - I'll be crying "Stored Functions - Please" again here...
That _would_ allow you to create these beasts pretty easily.
--
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird & MS SQL Server.
Upscene Productions
http://www.upscene.com
Paul Linehan
hkl...@h-k.de says...
> > - Select Fahrenheit_to_Celsius(MyVariable) as CelsiusTemp from MyTable
> > or SQL
> > - Select (5/9)*(MyVariable-32) as CelsiusTemp from MyTable
> What do you expect? typically a udf makes a sql command not faster,
> but the difference depends on what it does and how.
OK - that's fine - what I am trying to do is to get a handle on the
performance issues (if any), and some idea about what would be
good/bad/indifferent in performance terms. Nobody here seems to be able
to answer that.
> And if performance
> is really important, save the value as a calculated field or by using a
> trigger.
Hmmm.... indeed, one could do that, however, it's a bit difficult for
any db designer to anticipate with calculated fields or triggers all the
possibilities offered by Regular Expressions.
Paul Linehan
> > I agree. I would also like to see a good basic set of string, math and
> > date functions built in. UDF's should be the solution for unusual
> > requirements, not for the common functions.
> Of course - I'll be crying "Stored Functions - Please" again here...
> That _would_ allow you to create these beasts pretty easily.
What are "Stored Functions"?
How do/will/should they differ from UDF's and SP's?
Paul...
Martijn Tonies
> > > I agree. I would also like to see a good basic set of string, math and
> > > date functions built in. UDF's should be the solution for unusual
> > > requirements, not for the common functions.
>
>
> > Of course - I'll be crying "Stored Functions - Please" again here...
> > That _would_ allow you to create these beasts pretty easily.
>
>
> What are "Stored Functions"?
>
> How do/will/should they differ from UDF's and SP's?
Example:
CREATE FUNCTION TimesTwo(VAL_IN INTEGER) RETURNS INTEGER
BEGIN
Result := VAL_IN * 2;
END
They differ from UDFs that they are contained in the database (so don't
suffer from platform specific stuff etc) and they differ from procedures,
that
they can be called/used like this:
select timestwo(mycol), othercol
from mytable
So they can be called like an UDF, but instead of an external function, like
the IB UDFs, it's an internal function for that database only.
I've done a request at QC a long time ago, but people don't seem to like it
:-)