MapToStoredProcedures Simple Test

MapToStoredProcedures Simple Test

Postby mawilliams » Thu 06 Feb 2014 00:27

I'm attempting to use the MapToStoredProcedures support with EF6 and dotConnect (8.2.90.6) and have been unable to get it to work. I consistently receive "ORA-04043: object XXX does not exist" error where XXX may be the user name or the name of the stored procedure to create.

Here's is the simple code I am using to test with:

Code: Select all
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Data.Entity;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace DevArtEFTest
{
  class Program
  {
    static void Main(string[] args)
    {
      Database.SetInitializer(new DropCreateDatabaseAlways<DevArtContext>());

      var drone = new Drone
      {
        Name = "Test Drone"
      };

      using (var ctx = new DevArtContext())
      {
        ctx.Drones.Add(drone);
        ctx.SaveChanges();
      }

      if (System.Diagnostics.Debugger.IsAttached)
      {
        Console.Write("Any key to terminate... ");
        Console.ReadKey(true);
      }
    }
  }

  public class Drone
  {
    public int DroneId { get; set; }
    public string Name { get; set; }
  }

  public class DevArtContext : DbContext
  {
    public DbSet<Drone> Drones { get; set; }
   
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
      modelBuilder.HasDefaultSchema("DEMO");
     
      modelBuilder.Entity<Drone>().Property(p => p.Name).HasMaxLength(100);

      modelBuilder.Entity<Drone>().MapToStoredProcedures();
    }
  }
}


When I use HasDefaultSchema the error is (the name of the user):

ORA-04043: object DEMO does not exist

If I comment out the HasDefaultSchema setting the error is (the name of the first of the three stored procedures to create):

ORA-04043: object "Drone_Insert" does not exist

Can anyone advise as to what I am doing wrong here?

Would any additional information be helpful?

Regards.
mawilliams
 
Posts: 3
Joined: Thu 06 Feb 2014 00:17

Re: MapToStoredProcedures Simple Test

Postby Shalex » Thu 06 Feb 2014 13:18

Generally, we recommend using the dbMonitor tool for tracing database activity. This helps to identify the reasons of problems.
http://www.devart.com/dotconnect/oracle/docs/?dbmonitor.html
http://www.devart.com/dbmonitor/dbmon3.exe

mawilliams wrote:ORA-01918: user 'DEMO' does not exist

There is a difference in the meaning of schemas between SQL Server and Oracle: http://stackoverflow.com/questions/5365368/what-is-the-difference-between-an-oracle-and-microsoft-schema. Have you created the DEMO user in Oracle database before running your code?

The DEMO user also can be created automatically:

Code: Select all
    var config = Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig.Instance;
    config.DatabaseScript.Schema.DeleteDatabaseBehaviour = Devart.Data.Oracle.Entity.Configuration.DeleteDatabaseBehaviour.Schema;
    modelBuilder.HasDefaultSchema("DEMO");

But this creates the "__MigrationHistory" table in the schema specified in connection string instead of the schema set in .HasDefaultSchema(). We are investigating the issue.

As a workaround, please comment the line of code modelBuilder.HasDefaultSchema("DEMO");. After this, the code will work with schema (user) specified in connection string.

mawilliams wrote:ORA-04043: object "Drone_Insert" does not exist

The problem is caused by the code modelBuilder.Entity<Drone>().MapToStoredProcedures(); (we will fix behaviour to generate NotImplemented exception). If you comment this line, the code will work.

Could you please specify the reason(s) why you are using the .MapToStoredProcedures() functionality in EF Code-First (instead of "common" insert/update/delete SQL statements)? We did not support this feature in our EF-provider because found no reason to generate the stored procedures automatically instead of creating them manually in database.
Shalex
Devart Team
 
Posts: 7780
Joined: Thu 14 Aug 2008 12:44

Re: MapToStoredProcedures Simple Test

Postby mawilliams » Thu 06 Feb 2014 16:36

Hi,

Thanks for the update. Perhaps I should have mentioned in my original post that I am familiar with Oracle. I am, however, not familiar with Entity Framework so I was doing some walkthrough exercises to learn more about Entity Framework. These exercises are for SQL Server but I wanted to compare against Oracle so that is the genesis of the use of MapToStoredProcedures - it came from a SQL Server walkthrough that I wanted to try against Oracle.

This is not my quote:
mawilliams wrote:ORA-01918: user 'DEMO' does not exist


It seems the text has been changed. The error is NOT ORA-01918. The user definitely exists as that is the user I've specified in the connection string in the App.config file.

The text as I originally posted is the text from the exception. The text from the inner exception with the stack trace is:

Code: Select all
ORA-04043: object "DEMO" does not exist
   at Devart.Data.Oracle.a1.c(Int32 A_0)
   at Devart.Data.Oracle.aq.e(Int32 A_0)
   at Devart.Data.Oracle.aq.a(String A_0, Int32 A_1)
   at Devart.Data.Oracle.OracleCommand.DescribeProcedure(String name)
   at Devart.Data.Oracle.OracleCommand.CreateStoredProcSql(String procName)
   at Devart.Common.DbCommandBase.CreateSql()
   at Devart.Common.DbCommandBase.get_Sql()
   at Devart.Data.Oracle.OracleCommand.InternalPrepare(Boolean implicitPrepare, Int32 startRecord, Int32 maxRecords)
   at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
   at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   at Devart.Data.Oracle.Entity.h.a(CommandBehavior A_0)
   at Devart.Data.Oracle.Entity.h.b(CommandBehavior A_0)
   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<>c__DisplayClassb.<Reader>b__8()
   at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TInterceptionContext,TResult](Func`1 operation, TInterceptionContext interceptionContext, Action`1 executing, Action`1 executed)
   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext)
   at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Entity.Core.Mapping.Update.Internal.FunctionUpdateCommand.Execute(Dictionary`2 identifierValues, List`1 generatedValues)
   at System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update()


In any case, you've answered my question in that MapToStoredProcedures is not implemented. I thought I had read that it was, but it seems I have misunderstood that as now I can't find the document where I thought I had read that!

Thanks again for your quick reply!
mawilliams
 
Posts: 3
Joined: Thu 06 Feb 2014 00:17

Re: MapToStoredProcedures Simple Test

Postby Shalex » Fri 07 Feb 2014 09:08

mawilliams wrote:This is not my quote:
mawilliams wrote:ORA-01918: user 'DEMO' does not exist

Sorry for that. It was the error I have encountered with your project.

mawilliams wrote:ORA-04043: object "DEMO" does not exist

We could not reproduce this error. Please enable the dbMonitor tool and specify the exact SQL statement sent to database when the error occurs.
Shalex
Devart Team
 
Posts: 7780
Joined: Thu 14 Aug 2008 12:44

Re: MapToStoredProcedures Simple Test

Postby mawilliams » Fri 07 Feb 2014 14:45

I enabled DbMonitor as well as Oracle trace (10046, level 4) and there is no error in either. Since this appears to occur as a result of "Devart.Data.Oracle.OracleCommand.DescribeProcedure(String name)" call, perhaps the code is sending a "describe packet" (i.e. equivalent of OCIDescribeAny call) and not a traceable SQL statement?

Also, given this only happens with MapToStoredProcedures, and it's already been established that MapToStoredProcedures is not supported, I'd say it's a curiousity, but perhaps not unexpected. If there's additional information you would like, let me know and I can try to provide.
mawilliams
 
Posts: 3
Joined: Thu 06 Feb 2014 00:17

Re: MapToStoredProcedures Simple Test

Postby Shalex » Thu 13 Feb 2014 18:02

mawilliams wrote:But this creates the "__MigrationHistory" table in the schema specified in connection string instead of the schema set in .HasDefaultSchema(). We are investigating the issue.

The following code should be added in the project to change the schema for the __MigrationHistory table:
Code: Select all
  using System.Data.Common;
  using System.Data.Entity;
  using System.Data.Entity.Migrations.History;

  public class MyHistoryContext : HistoryContext {

      public MyHistoryContext(DbConnection existingConnection, string defaultSchema)
        : base(existingConnection, defaultSchema) {
      }
  }

  public class MyConfiguration : DbConfiguration {

    public MyConfiguration() {

      SetHistoryContext("Devart.Data.Oracle", (conn, schema) => new MyHistoryContext(conn, "DEMO"));
    }
  }
Shalex
Devart Team
 
Posts: 7780
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle