on 2014 Nov 09 8:54 AM
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
User | Count |
---|---|
71 | |
11 | |
11 | |
10 | |
9 | |
9 | |
7 | |
6 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.