query syntax problem

[Former Member]

I got syntax error at end of the query near ; SQLCODE=-131, ODBC 3 State='42000' I believe the error stems from using ...dba.$Regex_Match(...

begin
declare @excludeSubAcctAliasRegexPatternLst long varchar;
declare @acct_id int;
set @excludeSubAcctAliasRegexPatternLst='test';
set @acct_id=1;
select subAcct_ID, acct_type_short_name 
from dba.subAcct where acct_id = @acct_id
            or  not dba.$Regex_Match(acct_type_short_name, @excludeSubAcctAliasRegexPatternLst);

end


where dba.$Regex_Match is defined as 
ALTER FUNCTION "dba"."$Regex_Match"( in @str2Match long varchar,in @regexPattern long varchar ) 
returns bit not deterministic
external name 'Q:\\\\SqlAnywhereExtEnv\\\\SqlAnywhereDotNetDll.dll::SqlAnywhereDotNetDll.Util.regexMatch( string, string) bool' language CLR


and the corresponding clr member is
    public static bool regexMatch(string s, string pattern)
{
    try { return Regex.IsMatch(s, pattern, roIcEc); }
    catch { }
    return false;
}


I have used successfully other members from the dll for clr proc. this is my first time  using clr function.

[VolkerBarth]

Aside: You might also use SQL Anywhere's builtin regular expression support, i.e. SIMILAR TO and/or REGEXP, and note, those can be used as boolean values, i.e.

...or acct_type_short_name NOT SIMILAR TO @excludeSubAcctAliasRegexPatternLst


I guess the .NET RegexOptions can be mapped to according patterns/flags.

[Former Member]

thx

I failed to find flags for clr compatiabiilty of regex in silmilar to and regexp for SA 11... ( found the online syntax:http://dcx.sybase.com/1101/en/dbreference_en11/rf-sqllanguage-s-4915351.html)

if you wonder why clr regex match, the filter is actually a list of ansi like expression reformatted as regex

[VolkerBarth]

Sorry, my last sentence was misleading. SIMILAR TO and REGEXP do not use flags but I guess the .NET RegexOptions flags can be expressed with according patterns and/or the different behaviour of SIMILAR TO vs. REGEXP - e.g. SIMILAR TO uses the database collation (and therefore will use "ignore case" semantics in a case insensitive database) whereas REGEXP will use case sensitive semantics - cf. this overview from the v11.0.1 "What's New" pages...

[VolkerBarth]

I think the error comes from the fact that you treat the bit datatype as a boolean value which is not valid in SQL Anywhere - cf. that sample which also raises -131 (SQLE_SYNTAX_ERROR):

begin
   declare b bit = 0;
   select * from sys.dummy where not b;
end;


In contrast, the following comparison with 0 or 1 will work:
begin
   declare b bit = 0;
   select * from sys.dummy where b = 0;
end;


So I guess you just might have to change the second condition to "...or dba.$Regex_Match(acct_type_short_name, @excludeSubAcctAliasRegexPatternLst) = 0"

FWIW, there's not boolean datatype in SQL Anywhere.