Regex expressions didn't work
annaani opened this issue · 9 comments
I've downloaded this powerful feature but was sad when in your exampes all functions are working except of Regex functions. I've downloaded this software only for solving this task. Can you please solve the case?
Msg 6522, Level 16, State 2, Line 16
A .NET Framework error occurred during execution of user-defined routine or aggregate "SQLNET":
Z.Expressions.Compiler.Shared.EvalException: A token is missing ")" at position 21 near "pattern".
Z.Expressions.Compiler.Shared.EvalException:
at Z.Expressions.CodeAnalysis.CSharp.SyntaxParser.(SyntaxKind , Boolean )
at .(SyntaxParser , , SyntaxNode , Boolean )
at .(SyntaxParser , , SyntaxNode , Boolean )
at .(SyntaxParser , , Boolean , SyntaxNode )
at .(SyntaxParser , )
at Z.Expressions.CodeAnalysis.CSharp.SyntaxParser.()
at Z.Expressions.EvalCompiler.CompileSQLNET(EvalContext context, String code, IDictionary`2 parameterTypes, Type resultType)
at Z.Expressions.SqlServer.Eval.SQLNET.InternalEval()
at Z.Expressions.SqlServer.Eval.SQLNET.EvalBit()
.
Hello @annaani ,
The example indeed is missing a closing parenthesis.
DECLARE @valid_email SQLNET = SQLNET::New('Regex.IsMatch(email, pattern')
.ValueString('pattern', @regex).Root()should be
DECLARE @valid_email SQLNET = SQLNET::New('Regex.IsMatch(email, pattern)')
.ValueString('pattern', @regex).Root()(The fix will be deployed next time we release the website)
It looks like now regex throws a memory leak exception in SAFE MODE. We will look at it since this is often a "false positive". For example, adding somewhere in our library simply this line: var hash = new Hashset<int>(); will cause the issue. So we might have added an Hashset or something like this by mistake recently.
Best Regards,
Jon
SQL Example:
use Test;
DECLARE @regex VARCHAR(255) = '^([a-zA-Z0-9_-.]+)@(([[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}.)|(([a-zA-Z0-9-]+.)+))([a-zA-Z]{2,4}|[0-9]{1,3})(]?)$'
DECLARE @customer TABLE ( Email VARCHAR(255) )
INSERT INTO @customer
VALUES ( 'info@zzzprojects.com' ),
( 'invalid.com' ),
( 'sales@zzzprojects.com' )
-- "Regex" is optional, you can directly use IsMatch
DECLARE @valid_email SQLNET = SQLNET::New('Regex.IsMatch(email, pattern)')
.ValueString('pattern', @regex).Root()
-- SELECT 'invalid.com'
SELECT *
FROM @customer
WHERE @valid_email.Val('email', Email).EvalBit() = 0
Still see the error:
Msg 6522, Level 16, State 2, Line 17
A .NET Framework error occurred during execution of user-defined routine or aggregate "SQLNET":
System.Security.HostProtectionException: Attempted to perform an operation that was forbidden by the CLR host.
The protected resources (only available with full trust) were: All
The demanded resources were: MayLeakOnAbort
System.Security.HostProtectionException:
at .(ExpressionScope , Expression , Type , String , List1 , Boolean , List1 , SyntaxNode , Expression& )
at .(ExpressionScope , SyntaxNode , Type , Expression , List1 ) at .(ExpressionScope , SyntaxNode , Expression , Boolean ) at .(ExpressionScope , SyntaxNode , Expression , Boolean ) at .(ExpressionScope , SyntaxNode , Expression , Boolean ) at Z.Expressions.CodeCompiler.CSharp.ExpressionParser.ParseSyntax(ExpressionScope scope, SyntaxNode node, Type resultType) at Z.Expressions.EvalCompiler.CompileSQLNET(EvalContext context, String code, IDictionary2 parameterTypes, Type resultType)
at Z.Expressions.SqlServer.Eval.SQLNET.InternalEval()
at Z.Expressions.SqlServer.Eval.SQLNET.EvalBit()
.
Waiting for solving in new release. Thanks
Hello @annaani ,
The problem has been solved and should be released later today.
It was indeed only an issue with an Hashset.
Best Regards,
Jon
Hello @annaani ,
A new version has been released and the site web fixed.
Make sure that the version you download is not one cached (happened a few times).
Let me know if everything is working as expected.
Best Regards,
Jon
Hi Jon,
Now I see that it works on your examples, but when I use this for my needs (find correct Zip codes in table) like this:
DECLARE @cnt INT = 1
declare @len INT = (select count(1) from [dbOpry].[dbo].[orders])
DECLARE @regex VARCHAR(255) = '(^\d{5}$)|(^\d{9}$)|(^\d{5}-\d{4}$)'
declare @x VARCHAR(255)
DECLARE @sqlnet SQLNET
while @cnt<=@len
begin
SET @x =(select o.[ord_postal_code] from [dbOpry].[dbo].[orders] o where o.ord_id= @cnt)
SET @sqlnet = SQLNET::New('Regex.Matches(input, pattern)').ValueString('input',@x).ValueString('pattern', @regex)
INSERT INTO [dbOpry].[dbo].[Corr_zips]
SELECT CAST(Value_1 AS VARCHAR(250))
FROM dbo.SQLNET_EvalTVF_1(@sqlnet)
SET @cnt = @cnt+1
end;
SELECT * FROM [dbOpry].[dbo].[Corr_zips]It takes alot of time to get result and my SQL don't want to work and resets. I have 912912 rows with zips. SOme of them wrong and I want exclude them (in new table they should be Null).
I've already tried to do view with this table and extra column - SQLNET::New('Regex.Matches(input, pattern)').ValueString('input',o.[ord_postal_code]).ValueString('pattern', @regex) but it didn't work at all.
Thanks fo your attention
I was wrong, It works but too long, 40 minutes, and then resets
Hello @annaani ,
The performance problem is due because you use our library within a loop.
However, you can and should use it within the SQL statement such as:
DECLARE @regex VARCHAR(255) = '(^\d{5}$)|(^\d{9}$)|(^\d{5}-\d{4}$)'
DECLARE @x VARCHAR(255)
DECLARE @sqlnet SQLNET
SET @sqlnet = SQLNET::New('Regex.Match(input, pattern).Success').ValueString('pattern', @regex)
INSERT INTO [dbOpry].[dbo].[Corr_zips]
SELECT o.[ord_postal_code] FROM [dbOpry].[dbo].[orders] o
WHERE @sqlnet.ValueString('input',o.[ord_postal_code]).EvalBit() = 1
SELECT * FROM [Corr_zips]It should take only a few seconds this way
Let me know about your performance.
Best Regards,
Jon
Just used your script:
use dbOpry;
DECLARE @regex VARCHAR(255) = '(^\d{5}$)|(^\d{9}$)|(^\d{5}-\d{4}$)'
DECLARE @x VARCHAR(255)
DECLARE @SQLNET SQLNET
SET @SQLNET = SQLNET::New('Regex.Match(input, pattern).Success').ValueString('pattern', @regex)
INSERT INTO [dbOpry].[dbo].[Corr_zips_x]
SELECT o.ord_id, o.[ord_postal_code] FROM [dbOpry].[dbo].[orders] o
WHERE @sqlnet.ValueString('input',o.[ord_postal_code]).EvalBit() = 1
SELECT * FROM [Corr_zips_x]
and have an error that I had before I've started use loop:
Msg 6522, Level 16, State 2, Line 8
A .NET Framework error occurred during execution of user-defined routine or aggregate "SQLNET":
System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.
System.Data.SqlTypes.SqlNullValueException:
at System.Data.SqlTypes.SqlString.get_Value()
at Z.Expressions.SqlServer.Eval.SQLNET.ValueString(SqlString key, SqlString value)
and I understand why. Because it's works only for one first row in a table, that's why I used Loop above
Ok, really, for my case it works perfect but too long. But if we want optimisation in time or many users parralel work in one server it's not convenient. I'm only one user of my server that's why this case for me is optional. Thank you for your attention