I am trying to create a function that tests whether or not a value is a date. If the input parameter is a valid date, then I'd like to return a 1 otherwise I'd like to return a 0.

I started out by running a script from within EDB Manager. When I ran the script, it stopped execution to tell me that there was a conversion error with the line

SET TestDate = CAST(strDate as Date);

I fully expected this. I then clicked continue and the program stopped at the breakpoint I had set for SET X = 1; When I inspected the local variables, I saw that the script had done exactly what I asked. It set the isDATE value to 0 (meaning not a date). When I toggle between commenting one of the SET strDate = ... lines, the script perfoms correctly.


SCRIPT
BEGIN
----------------------------------------------------------------
-- X is used set a breakpoint within ED Manager
----------------------------------------------------------------
DECLARE X INTEGER;

DECLARE strDate varchar(20);
DECLARE TestDate DATE;
DECLARE isDATE INTEGER;
DECLARE ErrCode Integer;

SET isDATE = 1;
SET strDate = '12345678';
--SET strDate = '1959-03-23';
SET ErrCode = 0;

BEGIN
SET TestDate = CAST(strDate as Date);
EXCEPTION
  SET ErrCode = ERRORCODE();
  IF ERRORCODE()=1011 THEN
    SET isDATE = 0;
  END IF;
END;

SET X = 1;
END

So then I converted my script into a function called isDATE.


CREATE FUNCTION "isDate"  (INOUT "DateStr" VARCHAR(20) COLLATE UNI)
RETURNS INTEGER

BEGIN
DECLARE TestDate DATE;
DECLARE ValidDate INTEGER;

SET DateStr = COALESCE(DateStr,'');
SET ValidDate = 1;

BEGIN
SET TestDate = CAST(DateStr as Date);
EXCEPTION
  IF ERRORCODE()=1011 THEN
    SET ValidDate = 0;
  END IF;
END;

RETURN ValidDate;
END
VERSION 1.00!

I've saved this function inside a database caled ZilchStd. If I right-click and then execute this function is works great. If I populate the parameter with a valid date string it returns the value of 1. If I leave the the parameter as null or populate the parameter with an invalid date string it returns the value of 0. This works perfectly.

However I cannot figure out how to test this function inside an EDB Manager script. Every time I try to execute this function inside an EDB Manager script I get an error. How can I run a test script from within EDB Manager? What is the proper syntax for calling a function from within an EDB Manager script?

Can someone please provide a code snippet that shows how to interact with a function from within an EDB Manager script window.
Michael Riley
GySgt USMC Retired
www.zilchworks.com

I figured it out.

SCRIPT
BEGIN

DECLARE X Integer;
DECLARE Y Integer;

SET Y = isDate('123456');
SET X = 1;
SET Y = isDate('1959-03-23');
SET X = 1;
SET Y = isDate('03-23-1959');
SET X = 1;

END


--
Michael Riley
GySgt USMC Retired
www.zilchworks.com