D2006, EDB 1.06

I thought, whilst waiting for someone to put me out of my misery with the dll external functions I'd have a go at the sql ones.

One, or more, of the following is seriously screwed:

1. Me
2. The pdf SQL manual
3. EDBManager

Point 1
DECLARE - it looks as though these have to be at the top of the function - found this out when I added SET RESULT = TRUE; above the DECLAREs

Point 2
I am unable to get setting the result to work - EDBManager moans about ends, elses or ifs - I seem unable to come up with anything to make it happy.

Roy Lambert

BEGIN
DECLARE _FldStr VARCHAR;
DECLARE _CompStr VARCHAR;
DECLARE StrLen INTEGER;
SET RESULT = TRUE;
SET _FldStr = FldStr;
SET _CompStr = CompStr;
SET _FldStr = REPLACE(' ','',_FldStr);
SET _CompStr = REPLACE(' ','',_CompStr);

the problem lines and my variations on a theme
1.
IF _FldStr =_CompStr  THEN SET RESULT = TRUE ELSE SET RESULT = FALSE;

2.;
IF _FldStr =_CompStr
THEN SET RESULT = TRUE
ELSE SET RESULT = FALSE
END IF;

3.
IF _FldStr <> _CompStr  THEN SET RESULT = FALSE;

4.
IF _FldStr <> _CompStr  THEN SET RESULT = FALSE END;

5.
IF _FldStr <> _CompStr  THEN SET RESULT = FALSE END IF;
END

That was me - I finally got it to compile (or whatever it is that it does). But it gets worse.


I decided to display the result using

select nospacecomp(_work,'01943 442604'),  <<<<<<<< sql version
_work,
spacelesscompare(_work,'01943 442604')  <<<<<<<<<<< dll version
from cardex

And at the appropriate line I get True, 01934 44264, True with all other lines displaying False. Great I thought and added

where
nospacecomp(_work,'01943 442604') = TRUE


Whereupon I get no records displayed.

I'm pretty much stuck until I can get this and a load of others working. I chose this cos it was the simple one

Roy Lambert

I've now tried with defaulting the result to X


DECLARE Res VARCHAR DEFAULT 'X';
....
....
RETURN Res;

and that works so its either ignoring a boolean result or my test isn't returning a boolean.

I'm currently trying

RETURN (FldStr = CompStr);

or

RETURN (_FldStr = _CompStr); - this one with the spaces stripped

and since I'm cutting and pasting a field value I'm pretty sure there should be a match

Roy Lambert

As a final test I did something brilliant - nospacecomp(_work,_work) surprisingly enough this works so its the test of the string I'm passing in that isn't working

Roy Lambert

Final test before I go and get drunk - this one is fascinating


select _work, showwork(_Work) as alf, showwork('01933 442604') as bert
from cardex        
where
showwork(_Work) = showwork('01933 442604')

teh function is
BEGIN
RETURN REPLACE(' ','',FldIn);
END

and I'm shown all the records, I can eyeball the fact that the spaces are stripped and all but one of the rows shouldn't be there

beer here I come!!!!!!

Roy Lambert

Roy,

<< DECLARE - it looks as though these have to be at the top of the
function - found this out when I added SET RESULT = TRUE; above the DECLAREs
>>

See the bottom under SQL 2003 standard deviations:

http://www.elevatesoft.com/edb1sql_declare.htm

"Declarations can only be made at the beginning of the outermost BEGIN..END
block in an ElevateDB procedure or function. The standard dictates that
declarations can be made anywhere inside of any BEGIN..END block."

<< I am unable to get setting the result to work - EDBManager moans about
ends, elses or ifs - I seem unable to come up with anything to make it
happy. >>

Use this:

CREATE FUNCTION nospacecomp(IN FldStr VARCHAR, IN CompStr VARCHAR)
RETURNS BOOLEAN
BEGIN
DECLARE _FldStr VARCHAR;
DECLARE _CompStr VARCHAR;
DECLARE Result BOOLEAN DEFAULT TRUE;
SET _FldStr = REPLACE(' ','',FldStr);
SET _CompStr = REPLACE(' ','',CompStr);

IF _FldStr = _CompStr THEN
  SET RESULT = TRUE;
ELSE
  SET RESULT = FALSE;
END IF;

RETURN Result;
END

--
Tim Young
Elevate Software
www.elevatesoft.com

Roy,

<< Whereupon I get no records displayed. >>

See my other reply.  The issue with the WHERE clause is probably this issue:

http://www.elevatesoft.com/scripts/newsgrp.dll?action=openmsg&group=16&msg=3469&page=1#msg3469

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


I searched and searched the newsgroups but obviously had the wrong words - no wonder I can't get it to work

Roy Lambert