I'm creating some UDFs to use in InterBase 6, using Dev-C++ compiler.
I've read some articles and references about it and I'm using the examples
that came with IB.

1) I have a function called FN_LOWER(), which converts the text to
lowercase, whih functions well.
The only problem is that the type of the column I tested is VARCHAR(n) and
with the UDF it acts like CHAR(n). At the function, the return is a pointer
to char, by reference, from a static variable (buffer[256]).
In the declare udf clause, I use CSTRING(n) at the parameter.
Why the data is not trimmed? i.e.: the string "This Is A Test" turns to
"this is a test                   ".

2) How do I make a UDF that receives a DATE or TIMESTAMP parameter and
returns, for example, the number of the month? I tried it including
"i_base.h" datatypes, but was unsuccessful.

Thanks in advance,

Rodrigo Hjort
rodrigo.hj...@actumplus.com.br

        Your function probably didn't terminate the string.  Add a #0 as the
last char.

        First, you'll need two UDFs -- one for DATE and another for TIMESTAMP.
There are examples in Greg Deatz's FreeUDF library, which comes with
source code.

        Secondly, you don't need a UDF for this -- the EXTRACT function (part
of IB6 standard SQL) will do this.

        HTH,

        -Craig

--
 Craig Stuntz (TeamB) Vertex Systems Corp. Columbus, OH
We're hiring: http://www.vertexsoftware.com/careerops.htm#sd
     Delphi/InterBase WebLog: http://delphi.weblogs.com

char.
It's strange that I did so. And then I tested this:
buffer[length] = '\0';
buffer[length+1] = '1';
buffer[length+2] = '2';
buffer[length+3] = '3';
and it terminated, the string "123" did not appear, but the string was still
filled with spaces.

2) I tried including the type ISC_TIMESTAMP as parameter, then used
localtime() function, returning only a specific data, like month, but there
were no results.

Thanks for the help, Craig.

Rodrigo Hjort
http://actumplus.com.br/

I've just read on an article at MERS that VARCHAR datatype includes a 2-byte
binary prefix that contains the effective length of the string.

Was because of this that I had those problems?

Thanks,

Rodrigo Hjort
http://actumplus.com.br/

Hi Rodrigo,
  Are you sure you are not declaring the return parameter as a CHAR?

Regards
  Ian Newby

        No.  VARCHAR and CSTRING are not the same.

        -Craig

--
 Craig Stuntz (TeamB) Vertex Systems Corp. Columbus, OH
We're hiring: http://www.vertexsoftware.com/careerops.htm#sd
     Delphi/InterBase WebLog: http://delphi.weblogs.com

        Hmmm... I'm getting the same results, even with the standard RTRIM UDF
(which has the sole purpose of removing trailing spaces!).

        But I found a workaround:  CAST the result as VARCHAR:

SELECT
  CAST(SUBSTR('Foobar', 1, 3) AS VARCHAR(80))
FROM
  RDB$DATABASE

        This seems to give the desired effect.

        I don't understand your question here.  Can you give more information?
Like your code, for example?

        -Craig

--
 Craig Stuntz (TeamB) Vertex Systems Corp. Columbus, OH
We're hiring: http://www.vertexsoftware.com/careerops.htm#sd
     Delphi/InterBase WebLog: http://delphi.weblogs.com

"Craig Stuntz (TeamB)" <cstuntz@no_spam.vertexsoftware.com> wrote in message
news:3BFBCD4E.C33D510E@no_spam.vertexsoftware.com...

Which is the same [wrong] result you are getting? Are you saying that the
standard RTRIM doesn't return a trimmed string, Craig?

SQL> select rtrim('hello   ')||'x' from rdb$database;
=====================================================
hellox

I don't understand what the problem is. Care to explain?
I only noticed the original post said:

Gosh, is it safe in any way???

C.
--
Claudio Valderrama C. - http://www.cvalde.com - http://www.firebirdSql.org
Independent developer
Owner of the Interbase? WebRing

        That much works fine for the concatenation, but:

select rtrim('hello   ') from rdb$database;

        ...will return "hello" with 75 spaces after it, which you might not be
able to see in ISQL but which is very obvious in IBConsole.  Since the
result type is CSTRING(80) this seems to come out as a CHAR(80), making
the RTRIM pointless.  The CAST to VARCHAR fixes the problem

        -Craig

--
 Craig Stuntz (TeamB) Vertex Systems Corp. Columbus, OH
We're hiring: http://www.vertexsoftware.com/careerops.htm#sd
     Delphi/InterBase WebLog: http://delphi.weblogs.com

"Craig Stuntz (TeamB)" <cstuntz@no_spam.vertexsoftware.com> wrote in message
news:3C02CD31.7FF72F6B@no_spam.vertexsoftware.com...

I didn't understand the point. Now I can tell you the reason: in the DSQL
layer, someone (from Borland, I suppose) wrote an interesting comment saying
that since SQL doesn't deal with CSTRING, UDFs returning CSTRING are set to
return CHAR(n) instead. This is at run-time, when a UDF call is analyzed as
part of an statement. The same person asked himself in the comment "why not
VARCHAR???" and for obvious reasons, it was my question, too. I've been
tempted a couple of times to do the change, but there may be a valid reason
why CHAR is used, so I did nothing. Now, if you could ask someone from
"inside", it would be better. What made me very cautious was that I couldn't
find a problem in the change... and when you can't see the bug that's
{*word*154} a small distance above you head... you know.

C.
--
Claudio Valderrama C. - http://www.cvalde.com - http://www.firebirdSql.org
Independent developer
Owner of the Interbase? WebRing

I made a simple test: instead of declaring the function with
CSTRING(80) return value, I wrote VARCHAR(80). The data
seems like this:

select name, PRETTY1(name), PRETTY2(name) from company;
- "tgv transportes", "Tgv Transportes              ", "v Transportes"
- "auto posto bispo", "Auto Posto Bispo         ", "to Posto Bispo"
- "colgio spei", "Colgio Spei              ", "lgio Spei"

This function puts the first letter upercase and the rest lowercase.
The source-code in C for both is the same, returning a pointer to
a global variable of type char[256].

In PRETTY1 declaration clause, I use CSTRING(80).
In PRETTY2, otherwise, I use VARCHAR(80).

The first way has the problem I already reported: a lot of
blank characters after the #0 terminator.

Altough the second way seems to work, it cuts the first 2 bytes
(maybe reserved to give the used length of the string).
There are also other problems with this, when I ask the table
metadata containing the records.
Then I tried to fill these 2 bytes with something and return it
with the rest of the string, but it still didn't work.

Any clues?

Rodrigo Hjort
rodrigo.hj...@actumplus.com.br
http://hjortsistemas.cjb.net/

        Yes; this is clear from the results.

        Seems to me like either one is valid.  VARCHAR would probably be my
first choice, but there's nothing inherently wrong with CHAR.  Perhaps
there should be a CSTRING VARYING?

        At any rate, the CAST(MY_UDF AS VARCHAR(80)) workaround is completely
effective and only slightly annoying to type.

        I'll ask about this issue when I get the chance.

        -Craig

--
 Craig Stuntz (TeamB) Vertex Systems Corp. Columbus, OH
We're hiring: http://www.vertexsoftware.com/careerops.htm#sd
     Delphi/InterBase WebLog: http://delphi.weblogs.com