Error:ORA-01036: illegal variable name/number(dotConnect for Oracle has a bug for EntityFramework.DynamicFilters 1.4.10↑

Error:ORA-01036: illegal variable name/number(dotConnect for Oracle has a bug for EntityFramework.DynamicFilters 1.4.10↑

Postby zipiii » Sun 28 May 2017 14:00

Hi

I am trying to implement db context global filtering using EntityFramework.DynamicFilters (https://www.nuget.org/packages/EntityFramework.DynamicFilters).

I am using EF6, Oracle 11g and dotConnect for Oracle 9.2.187.

Unfortunatelly i got an error in case of using Dynamic filter:ORA-01036: illegal variable name/number.

Code: Select all
SELECT
Var_1.Id,
Var_1.Name,
Var_1.DisplayName,
Var_1.Icon,
Var_1.IsDeleted
FROM MyLanguages Var_1
WHERE (Var_1.IsDeleted = :DynamicFilterParam_1) OR (:DynamicFilterParam_2 IS NOT NULL)

-- DynamicFilterParam_1: 'null' (Type = Boolean, IsNullable = false)
-- DynamicFilterParam_2: 'null' (Type = Boolean, IsNullable = false)


I am attaching simple project where the situation can be replayed.
https://pan.baidu.com/s/1slk8GK9
or
https://github.com/zipiii/EFDynamicFilterDemoOfDevart

To run the project please modify connection string, create oracle db, create schema abp1 and run Update-Database migration command and run it.

When using Oracle Devart connection, EntityFramework DynamicFilters can only be used under the 1.4.9 version, if use 1.4.10 above and got an error:ORA-01036: illegal variable name/number.

Please help me!
My customer username is light.way@gmail.com.

thx!
zipiii
 
Posts: 6
Joined: Sat 20 Feb 2016 16:37

Re: Error:ORA-01036: illegal variable name/number(dotConnect for Oracle has a bug for EntityFramework.DynamicFilters 1.4.10↑

Postby Shalex » Fri 02 Jun 2017 14:54

zipiii wrote:When using Oracle Devart connection, EntityFramework DynamicFilters can only be used under the 1.4.9 version, if use 1.4.10 above and got an error:ORA-01036: illegal variable name/number.
We have reproduced the issue and are investigating it. We will notify you about the result.
Shalex
Devart Team
 
Posts: 7777
Joined: Thu 14 Aug 2008 12:44

Re: Error:ORA-01036: illegal variable name/number(dotConnect for Oracle has a bug for EntityFramework.DynamicFilters 1.4.10↑

Postby zipiii » Sat 10 Jun 2017 14:26

To Shalex:
Have the results?
I nervously await solution to the problem.
Please help me, thank you!
zipiii
 
Posts: 6
Joined: Sat 20 Feb 2016 16:37

Re: Error:ORA-01036: illegal variable name/number(dotConnect for Oracle has a bug for EntityFramework.DynamicFilters 1.4.10↑

Postby Shalex » Tue 13 Jun 2017 11:07

That is a DynamicFilter bug.

Our EF provider generates SQL
Code: Select all
SELECT
Extent1.Id,
Extent1.Name,
Extent1.DisplayName,
Extent1.Icon,
Extent1.IsDeleted
FROM MyLanguages Extent1
WHERE (Extent1.IsDeleted = :DynamicFilterParam_000001) OR (:DynamicFilterParam_000002 IS NOT NULL)

then DynamicFilter edits SQL via command interception and produces
Code: Select all
SELECT
Extent1.Id,
Extent1.Name,
Extent1.DisplayName,
Extent1.Icon,
Extent1.IsDeleted
FROM MyLanguages Extent1
WHERE (Extent1.IsDeleted = :DynamicFilterParam_000001)
but doesn't remove DynamicFilterParam_000002 from parameters collection.

Workaround is usage of a command interception. The main point is to sign after DynamicFilter:

1. Please add a new class, which implements IDbCommandInterceptor, to your project to fix the problem with undeleted parameter, for example:
Code: Select all
  internal class CommandInterceptor: IDbCommandInterceptor {
    public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext) {
    }

    public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext) {
    }

    public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext) {
    }

    public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext) {

      for (int i = command.Parameters.Count - 1; i >= 0; i--) {
        var param = command.Parameters[i];
        if (!command.CommandText.Contains(":" + param.ParameterName))
          command.Parameters.Remove(param);
      }
    }

    public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext) {
    }

    public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext) {
    }
  }

2. Register our command interceptor. For this, turn off filters of DynamicFilter after the first context creation, execute any simple query using context to make DynamicFilter's command interceptor being employed, then add our command interceptor and turn on filters:
Code: Select all
            var context = new ExampleContext();

->
Code: Select all
            var context = new ExampleContext();
            context.DisableAllFilters();
            context.Languages.Any();
            System.Data.Entity.Infrastructure.Interception.DbInterception.Add(new CommandInterceptor());
            context.EnableAllFilters();
Shalex
Devart Team
 
Posts: 7777
Joined: Thu 14 Aug 2008 12:44

Re: Error:ORA-01036: illegal variable name/number(dotConnect for Oracle has a bug for EntityFramework.DynamicFilters 1.4.10↑

Postby zipiii » Wed 14 Jun 2017 04:05

Hi, Shalex, thank you!

as you say: but doesn't remove DynamicFilterParam_000002 from parameters collection.
After I add "CommandInterceptor" calss, The program success.

But, I use "Oracle.ManagedDataAccess"(not use Oracle Devart connection), and not use "CommandInterceptor" class, the program success too.

SqlServer provider generates SQL
Code: Select all
exec sp_executesql N'SELECT
    CASE WHEN ( EXISTS (SELECT
        1 AS [C1]
        FROM [dbo].[MyLanguages] AS [Extent1]
        WHERE ([Extent1].[IsDeleted] = @DynamicFilterParam_000001)
    )) THEN cast(1 as bit) ELSE cast(0 as bit) END AS [C1]
    FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]',N'@DynamicFilterParam_000001 bit,@DynamicFilterParam_000002 bit',@DynamicFilterParam_000001=1,@DynamicFilterParam_000002=NULL


Why? Is it Oracle Devart connection bug?
Last edited by zipiii on Thu 15 Jun 2017 04:42, edited 1 time in total.
zipiii
 
Posts: 6
Joined: Sat 20 Feb 2016 16:37

Re: Error:ORA-01036: illegal variable name/number(dotConnect for Oracle has a bug for EntityFramework.DynamicFilters 1.4.10↑

Postby zipiii » Thu 15 Jun 2017 03:38

Hi, Shalex

I did another test yesterday like this:
Code: Select all
var context = new ExampleContext();
            context.DisableAllFilters();
            context.Languages.Any();
            System.Data.Entity.Infrastructure.Interception.DbInterception.Add(new CommandInterceptor());
            context.EnableAllFilters();
            context.Languages.Any();

it successed,but it's failed after i dont use second and third line. please tell me why,thanks.

Code: Select all
var context = new ExampleContext();
            //context.DisableAllFilters();
            //context.Languages.Any();
            System.Data.Entity.Infrastructure.Interception.DbInterception.Add(new CommandInterceptor());
            context.EnableAllFilters();
           context.Languages.Any();
zipiii
 
Posts: 6
Joined: Sat 20 Feb 2016 16:37

Re: Error:ORA-01036: illegal variable name/number(dotConnect for Oracle has a bug for EntityFramework.DynamicFilters 1.4.10↑

Postby zipiii » Mon 19 Jun 2017 06:14

Hi, Shalex
I post the issue to EntityFramework.DynamicFilters.
https://github.com/jcachat/EntityFramework.DynamicFilters/issues/114

Please you look it, and solve this problem.
thank you !
zipiii
 
Posts: 6
Joined: Sat 20 Feb 2016 16:37

Re: Error:ORA-01036: illegal variable name/number(dotConnect for Oracle has a bug for EntityFramework.DynamicFilters 1.4.10↑

Postby Shalex » Thu 29 Jun 2017 18:54

Hi, zipiii

Taking into account your correspondence at https://github.com/jcachat/EntityFramework.DynamicFilters/issues/114, please confirm that no investigation on our side is required at the moment. Is the issue solved?

Looking forward to your reply.
Shalex
Devart Team
 
Posts: 7777
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle