Custom XPO provider for dotConnect (Oracle)

Custom XPO provider for dotConnect (Oracle)

Postby peter.gejgus@erni.sk » Mon 18 Jul 2011 16:00

Hello,

I try to develop custom XPO provider for Devart dotConnect (Oracle) and use it with DevExpress Reporting. So far my custom provider looks like this:

Code: Select all
using System;
using System.Data;

using DevExpress.Xpo.DB;
using DevExpress.Xpo.DB.Helpers;

namespace Quickstarts.Reporting.Service.Host
{
    class DevartXpoProvider : BaseOracleConnectionProvider
    {

        public DevartXpoProvider(IDbConnection connection, AutoCreateOption autoCreateOption) : base(connection, autoCreateOption)
        {
        }

        public static IDataStore CreateProviderFromString(string connectionString, AutoCreateOption autoCreateOption, out IDisposable[] objectsToDisposeOnDisconnect)
        {
            IDbConnection connection = CreateConnection(connectionString);
            objectsToDisposeOnDisconnect = new IDisposable[1]
            {
                connection
            };
            return CreateProviderFromConnection(connection, autoCreateOption);
        }

        public static IDataStore CreateProviderFromConnection(IDbConnection connection, AutoCreateOption autoCreateOption)
        {
            return (IDataStore)new DevartXpoProvider(connection, autoCreateOption);
        }

        public static IDbConnection CreateConnection(string connectionString)
        {
            IDbConnection dbConnection = Devart.Data.Oracle.OracleProviderFactory.Instance.CreateConnection();
            dbConnection.ConnectionString = connectionString;
            return dbConnection;
        }

        protected override IDbConnection CreateConnection()
        {
            return CreateConnection(this.ConnectionString);
        }

        protected override void CommandBuilderDeriveParameters(IDbCommand command)
        {
            throw new NotImplementedException();
        }

        public static void Register()
        {
            DataStoreBase.RegisterDataStoreProvider(XpoProviderTypeString, new DataStoreCreationFromStringDelegate(CreateProviderFromString));
        }

        public const string XpoProviderTypeString = "DevartXpoProvider";
    }
}


Connection string in app.config looks like this:





When I tried to use this class in my existing project, I got the following DevExpress.Xpo.DB.Exceptions.SqlExecutionErrorException exception in reporting service’s constructor:

Executing Sql 'declare
begin
insert into "XPObjectType"("TypeName","AssemblyName","OID")values(:p1,:p2,"sq_XPObjectType".nextval);select "sq_XPObjectType".CurrVal into p0 from DUAL;
n := 1; end;' with parameters '{Null},{DevExpress.Xpf.Printing.Service.DataContracts.ServiceFault},{DevExpress.Xpf.Printing.v10.2.Service},{Null}' exception 'Devart.Data.Oracle.OracleException (0x80004005): ORA-01036 illegal variable name/number
at Devart.Data.Oracle.u.a(a0[] A_0, Byte[] A_1, Hashtable A_2)
at Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
at Devart.Data.Oracle.OracleCommand.ExecuteNonQuery()
at DevExpress.Xpo.DB.BaseOracleConnectionProvider.Exec(IDbCommand command, Dictionary`2 parameters)'
at DevExpress.Xpo.DB.BaseOracleConnectionProvider.Exec(IDbCommand command, Dictionary`2 parameters)
at DevExpress.Xpo.DB.BaseOracleConnectionProvider.ProcessModifyData(ModificationStatement[] dmlStatements)
at DevExpress.Xpo.DB.DataStoreSerializedBase.ModifyData(ModificationStatement[] dmlStatements)
at DevExpress.Xpo.DB.ConnectionProviderSql.ModifyData(ModificationStatement[] dmlStatements)
at DevExpress.Xpo.SimpleDataLayer.ModifyData(ModificationStatement[] dmlStatements)
at DevExpress.Xpo.SimpleObjectLayer.CommitChanges(Session session, ICollection fullListForDelete, ICollection completeListForSave)
at DevExpress.Xpo.Session.FlushChangesInsideTransaction()
at DevExpress.Xpo.Session.FlushChanges()
at DevExpress.Xpo.Session.ProcessingProcess(ObjectSet markedObjectsHolder, Object theObject)
at DevExpress.Xpo.Session.Save(Object theObject)
at DevExpress.Xpo.SimpleObjectLayer.CreateObjectType(XPObjectType objectType)
at DevExpress.Xpo.Helpers.XPObjectTypesManager.GetObjectType(XPClassInfo objectType)
at DevExpress.Xpo.Session.GetObjectType(XPClassInfo objectType)
at DevExpress.Xpo.Session.CreateObjectTypeRecords(XPClassInfo[] types)
at DevExpress.Xpo.Session.CreateObjectTypeRecords(Assembly[] assemblies)
at DevExpress.Xpf.Printing.Service.Native.Services.SessionUtilityService.SafeInitialize()
at DevExpress.Xpf.Printing.Service.ExportService..ctor()

The Oracle DB was empty, before I ran this program. Do you have any idea what this exception means? Is there any problem with parameters used in command? Something related to "Original_" prefix?

Best Regards,
Peter
peter.gejgus@erni.sk
 
Posts: 3
Joined: Mon 18 Jul 2011 15:50

Postby Shalex » Tue 19 Jul 2011 15:31

Try to debug the query that throws the exception with the dbMonitor tool.
Download link: http://www.devart.com/dbmonitor/dbmon3.exe
Documentation: http://www.devart.com/dotconnect/oracle ... nitor.html
In dbMonitor you will find the SQL statement that fails to execute (like you have mentioned), parameters, and their values. The number of parameters in the OracleCommand.Parameters collection has to coincide with the number of parameters in OracleCommand.CommandText.

If this doesn't help, please send us a small test application to reproduce the issue in our environment.
Shalex
Devart Team
 
Posts: 7839
Joined: Thu 14 Aug 2008 12:44

Postby peter.gejgus@erni.sk » Thu 21 Jul 2011 07:25

The problem is that in the problematic query, parameters p0 and n are not preceded by colon:

insert into "XPObjectType"("TypeName","AssemblyName","OID")values(:p1,:p2,"sq_XPObjectType".nextval);select "sq_XPObjectType".CurrVal into p0 from DUAL;
n := 1; end;'

The reason is that setter from dotConnect's OracleParameter.ParameterName removes colon ":" from parameter's name. DevExpress needs to have parameter names with colon, but dotConnect removes this colons. Even when I set OracleUtils.OracleClientCompatible = true, it was not working.
peter.gejgus@erni.sk
 
Posts: 3
Joined: Mon 18 Jul 2011 15:50

Postby Shalex » Fri 22 Jul 2011 10:47

peter.gejgus@erni.sk wrote:The reason is that setter from dotConnect's OracleParameter.ParameterName removes colon ":" from parameter's name. DevExpress needs to have parameter names with colon, but dotConnect removes this colons. Even when I set OracleUtils.OracleClientCompatible = true, it was not working.

Our code does not remove the colon ":" from parameter's name in the "OracleUtils.OracleClientCompatible = true;" mode. We have reproduced the problem with parameters for this case in the Direct mode. We will investigate the issue and notify you about the results. As a temporary workaround, please use the OCI mode (via Oracle Client).
Shalex
Devart Team
 
Posts: 7839
Joined: Thu 14 Aug 2008 12:44

Postby Shalex » Thu 01 Sep 2011 15:07

The bug with parameter names when OracleUtils.OracleClientCompatible=true in Direct mode is fixed. We will post here when the next build of dotConnect for Oracle is available for download. We plan to release it next week.
Shalex
Devart Team
 
Posts: 7839
Joined: Thu 14 Aug 2008 12:44

Postby Shalex » Wed 07 Sep 2011 12:03

New version of dotConnect for Oracle 6.50 is released!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only): http://secure.devart.com/ .
For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=21942 .
Shalex
Devart Team
 
Posts: 7839
Joined: Thu 14 Aug 2008 12:44

Postby peter.gejgus@erni.sk » Mon 26 Sep 2011 07:51

Bug was not fixed properly. I've tested it on my small app.
peter.gejgus@erni.sk
 
Posts: 3
Joined: Mon 18 Jul 2011 15:50

Postby Shalex » Mon 26 Sep 2011 08:50

Please refer to http://www.devart.com/forums/viewtopic.php?t=22037. We will post here about the results of our investigation.
Shalex
Devart Team
 
Posts: 7839
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle