Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: UDF returning null


This question is not answered. Helpful answers available: 2. Correct answers available: 1.


Permlink Replies: 3 - Last Post: Nov 29, 2016 10:48 AM Last Post By: Jeff Overcash (... Threads: [ Previous | Next ]
Eric ten Westen...

Posts: 78
Registered: 12/8/99
UDF returning null  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 16, 2016 3:00 AM
Hi

I have a GetAge function with 2 dates, birthday and a dat (mostly current_date)

The function returns an integer, the age

However Birthday can be null , so if null i want to return null

How can i return Null if the function result is an integer?

Eric
Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: UDF returning null  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 19, 2016 9:00 PM   in response to: Eric ten Westen... in response to: Eric ten Westen...
Eric ten Westenend wrote:
Hi

I have a GetAge function with 2 dates, birthday and a dat (mostly current_date)

The function returns an integer, the age

However Birthday can be null , so if null i want to return null

How can i return Null if the function result is an integer?

Eric

Actually it can't. Passing NULL to a UDF will cause a transformation to that
type (integers 0, String '' etc. You can test for the default value if it makes
no sense like in this case. I can't remember what a null date converts to but
it is like 11/17/1858 or some date close to that I think.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)
Eric ten Westen...

Posts: 78
Registered: 12/8/99
Re: UDF returning null  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 28, 2016 2:04 AM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...
Hi Jeff

This function is available in FreeAndHocUDF.

What do you think, it returns NULL

Eric

// return null instead of negativ ages
int *ageinyearsnull(ISC_QUAD * date1, ISC_QUAD * date2)
{
    if (!date1 || !date2) {
        return NULL;
    } else {
        int res = intern_ageinyears(date1, date2);
        if (res < 0) {
            return NULL;
        } else {
            int *result = MALLOC(sizeof(int));
            *result = res;
            return result;
        }
    }
}
 


Jeff Overcash (TeamB) wrote:
Eric ten Westenend wrote:
Hi

I have a GetAge function with 2 dates, birthday and a dat (mostly current_date)

The function returns an integer, the age

However Birthday can be null , so if null i want to return null

How can i return Null if the function result is an integer?

Eric

Actually it can't. Passing NULL to a UDF will cause a transformation to that
type (integers 0, String '' etc. You can test for the default value if it makes
no sense like in this case. I can't remember what a null date converts to but
it is like 11/17/1858 or some date close to that I think.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)
Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: UDF returning null  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 29, 2016 10:48 AM   in response to: Eric ten Westen... in response to: Eric ten Westen...
Eric ten Westenend wrote:
Hi Jeff

This function is available in FreeAndHocUDF.

What do you think, it returns NULL

Eric

// return null instead of negativ ages
int *ageinyearsnull(ISC_QUAD * date1, ISC_QUAD * date2)
{
    if (!date1 || !date2) {
        return NULL;
    } else {
        int res = intern_ageinyears(date1, date2);
        if (res < 0) {
            return NULL;
        } else {
            int *result = MALLOC(sizeof(int));
            *result = res;
            return result;
        }
    }
}
 


I don't recommend looking at that UDF code for examples. It introduced bugs
from the original Pascal it was translated from and should be considered
unreliable. I had to get several clients off that UDF library due to it
corrupting databases. C++ or Delphi NULL is not the same things as NULL in SQL.

The only way to get at NULL is to pass your variables by descriptor and check
the dsc_flags first bit to be on or not. This makes things incredibly more
complex. Not all versions have that feature either. It was there for a while,
then pulled in 6.0 I think and now back. Not sure when it came back though.

http://docwiki.embarcadero.com/InterBase/XE7/en/Defining_a_Sample_UDF_with_a_Descriptor_Parameter

Personally I suggest instead of trying to deal with descriptors and NULL values,
just return a value that is inappropriate and treat that as NULL (returning -1
for numbers when only positive number make sense for example). Check for the
NULL conversion to a Data type

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02