How to use user defined functions with PROC FEDSQL?

[tomcmacdonald]

I need regular expressions to validate a particular column and can't use PRXMATCH in PROC FEDSQL.  My solution has been to wrap it in a PROC DS2 package like this:

proc ds2 scond=error;
	package regexp / overwrite=yes;
		method match( varchar re, varchar string ) returns integer;
			return prxmatch(re, string) > 0;
		end;
	endpackage;
	run;
quit;

And testing it on a sample dataset:

data foo;
    input id $12.;
    datalines;
20467400081
;
run;

proc fedsql;
    drop table bar force;
    select 
        id,
        regexp.match('/^[1-5](?!00000)\d{5}0(?!0000)\d{4}\w?(?:\s*)$/', id) as is_valid
    into bar
    from foo;
quit;

I get the following note:

NOTE: Argument 1 to function prxmatch is invalid.

Clearly the regular expression is valid.  So just running a test function to see what's going on:

proc ds2 scond=error;
    package testing / overwrite=yes;
        method echo ( varchar string ) returns varchar;
            return string;
        end;
    endpackage;
    run;
quit;

proc fedsql;
    drop table baz force;
    select 
        id,
        testing.echo('/^[1-5](?!00000)\d{5}0(?!0000)\d{4}\w?(?:\s*)$/') as test
    into baz
    from foo;
quit;

It creates this dataset:

20467400081	/^[1-5](

So it is cutting it off at 8 characters. 

How can I tell SAS that the quoted string is a varchar and not a char?  In other words how can I use a string parameter for a function that exceeds 8 characters? 

[s_lassen]

It seems to work (although I do not understand your PRX string, but anyway I did not get an error message) if you change the input parameter definition of your package function, e.g.:

proc ds2 scond=error;
	package regexp / overwrite=yes;
		method match( varchar(50) re, varchar(50) string ) returns integer;
			return prxmatch(re, string) > 0;
		end;
	endpackage;
	run;
quit;

[tomcmacdonald]

This is generating the following error:

ERROR: Access Violation occurred during PREPARE!