The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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
        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;

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 ) 
    if likePattern is null
        select null,0,'' into regexPattern, flag, Msg;
        call "dba"."%like_format2Regex"(likePattern, escChar4LikeFilter, regexPattern, flag, Msg);

asked 09 Nov '14, 08:54

gg99's gravatar image

accept rate: 0%

edited 09 Nov '14, 09:58

Breck%20Carter's gravatar image

Breck Carter

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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 09 Nov '14, 08:54

question was seen: 315 times

last updated: 09 Nov '14, 09:58