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

asked 09 Nov '14, 08:54

gg99's gravatar image

gg99
193202229
accept rate: 0%

edited 09 Nov '14, 09:58

Breck%20Carter's gravatar image

Breck Carter
26.9k438609883

Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×137
×18
×17

question asked: 09 Nov '14, 08:54

question was seen: 403 times

last updated: 09 Nov '14, 09:58