Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 2 of 2 total |
How do you test a function using an EDB Manager script? |
Mon, Oct 10 2011 6:29 PM | Permanent Link |
Michael Riley ZilchWorks | 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 |
Mon, Oct 10 2011 8:48 PM | Permanent Link |
Michael Riley ZilchWorks | 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 |
This web page was last updated on Tuesday, April 30, 2024 at 03:55 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |