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

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
zipiii
Posts: 6
Joined: Sat 20 Feb 2016 16:37

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

Post by 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/EntityFr ... micFilters).

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!

Shalex
Site Admin
Posts: 8248
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↑

Post by 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.

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↑

Post by zipiii » Sat 10 Jun 2017 14:26

To Shalex:
Have the results?
I nervously await solution to the problem.
Please help me, thank you!

Shalex
Site Admin
Posts: 8248
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↑

Post by 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();

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↑

Post by 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↑

Post by 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↑

Post by zipiii » Mon 19 Jun 2017 06:14

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

Please you look it, and solve this problem.
thank you !

Shalex
Site Admin
Posts: 8248
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↑

Post by Shalex » Thu 29 Jun 2017 18:54

Hi, zipiii

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

Looking forward to your reply.

Post Reply