SAP SQL Anywhere

Question

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.

Answer 1

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.

Answer 2

To restate Volker's correct answer: bool maps to BIT in SQL Anywhere, and BIT is an integer data type, NOT a boolean TRUE/FALSE or TRUE/FALSE/UNKKNOWN datatype. There is no boolean data type for variables in SQL Anywhere. Predicates (comparisons) can return boolean values, of course, but you cannot store or pass around those values.

Perhaps you can code it this way...

or not ( dba.$Regex_Match(acct_type_short_name, @excludeSubAcctAliasRegexPatternLst) = 1 )

if 1 is the true bool value that is mapped to integer bit (I don't know, you tell me :)

SAP SQL Anywhere

query syntax problem

Follow this question

Related questions