cancel
Showing results for 
Search instead for 
Did you mean: 

SA 11 clr stored proc trouble with null input

g_g_99
Participant
0 Kudos
1,855

a clr stored proc works fine except even when it's primary input long varchar (string) variable set to empty. but the moment, its set to null, I get

    Could not execute statement.
Procedure '$like_fmt2Regex' terminated with unhandled exception 'Object
reference not set to an instance of an object.'
SQLCODE=-91, ODBC 3 State="HY000"
Line 1, column 1

----------
ALTER PROCEDURE "dba"."$like_fmt2Regex"( in likePattern long varchar,
  out regexPattern long varchar,out flag bit,out Msg long varchar ) 
external name 'C:\\\\SqlAnywhereExtEnv\\\\SqlAnywhereDotNetDll.dll::Util.like_fmt2Regex(string, out string, out bool, out string)' language CLR
----------
begin
        declare @sIn long varchar;
        declare @likeEscpChar varchar;
        declare @fi_symRegex varchar(32000);
        declare @flag bit;
        declare @Msg long varchar;
        set @sIn=null; set @likeEscpChar=null;
--        SELECT "dba"."$formatListNormalized"(' a b, item2 ,   item3 0, item4 ')//" becomes "a b,item2, item3,item4
--        SELECT "dba"."$formatListNormalized"(@sIn)
--        call dba.$LikeLst_format2Regex (@sIn, @likeEscpChar, @fi_symRegex, @flag,        @Msg);
        call dba.$like_fmt2Regex(@sIn, @fi_symRegex, @flag,        @Msg);
        select  @sIn, @likeEscpChar, @fi_symRegex, @flag,        @Msg;
end

On the other hand the corresponding clr member was tested OK directly by a test C# app by calling with both empty and null input string value

For now, to get around the trouble, I recreated the clr based proc with new name %LikeLst_format2Regex and $LikeLst_format2Regex as

    ALTER PROCEDURE "dba"."$like_format2Regex"( in likePattern long varchar, in escChar4LikeFilter long varchar,
  out regexPattern long varchar,out flag bit,out Msg long varchar ) 
begin
    if likePattern is null
    then
        select null,0,'' into regexPattern, flag, Msg;
    else
        call "dba"."%like_format2Regex"(likePattern, escChar4LikeFilter, regexPattern, flag, Msg);
    endif
end

Accepted Solutions (0)

Answers (0)