Sql dialect for Oracle seems data provider specific?

Berend · February 29, 2024, 2:02pm

We are using Oracle as database and Devart dotconnect as data provider:

When using SQL persistence on the endpoint configuration you can specify the database dialect, Oracle in this case, you would expect this dialect to be database specific, but data provider independent.

The Sql dialect for Oracle however looks for a vendor specific property “BindByName” on the DbCommand using reflection, which only seems to exists in the Oracle Data Provider for .NET, see BindByName in the CreateCommand method:

github.com

Particular/NServiceBus.Persistence.Sql/blob/master/src/SqlPersistence/Config/SqlDialect_Oracle.cs

namespace NServiceBus
{
    using System;
    using System.Data;
    using System.Data.Common;
    using System.Reflection;
    using System.Text;

    public abstract partial class SqlDialect
    {
        /// <summary>
        /// Oracle
        /// </summary>
        public partial class Oracle : SqlDialect
        {
            volatile PropertyInfo bindByName;

            internal override void SetJsonParameterValue(DbParameter parameter, object value)
            {
                SetParameterValue(parameter, value);

This file has been truncated. show original

This causes an exception when using the data provider of Devart:
System.Exception: Could not extract field ‘BindByName’ from ‘Devart.Data.Oracle.OracleCommand, Devart.Data.Oracle, Version=10.0.0.0, Culture=neutral, PublicKeyToken=09af7300eec23701’.

We would like to use the outbox feature and are not able to do so, while the documentation does not seem to specify a specific data provider to use.

danielmarbach · February 29, 2024, 3:44pm

Hi @Berend

So far we have only been testing the SQL persistence package against Oracle.ManagedDataAccess.Core client and not against the DevArt provider.

I have done some research and it seems we could potentially make things work by falling back to PassParametersByName instead when BindByName is not available. That would make sure the SQL commands the persistence uses are compatible with DevArt too.

I have tried to setup an acceptance test suit but ran into DevArt always requiring a valid license on .NET. I’m in contact with DevArt to see if they can enable for us a license that we can use for CI/CD purposes without having to get a site license by them.

Meanwhile, I sent in a PR that implements the above fallback behavior. Could you potentially test it out if that small change suits your need until we know more from DevArt?

Regards,
Daniel

danielmarbach · March 1, 2024, 9:19am

@Berend could you please let me know which version of the SQL persistence you are currently using so that I can put a PR with the above changes against that release branch? I can then provide you with a nuget package to download from the build artifacts

Daniel

Berend · March 1, 2024, 9:50am

@danielmarbach,

That sounds good, by the looks of it the OracleCommand indeed has a PassParametersByName. We are currently using NServiceBus.Persistence.Sql 7.0.4.

Thanks!

danielmarbach · March 1, 2024, 11:38am

Hi @berend,

Here is an alpha build against the 7.x release branch

NuGet packages.zip (1.0 MB)

Please let me know if that gets you unblocked, and then we will look at the next steps.

Regards,
Daniel

danielmarbach · March 6, 2024, 3:08pm

Hi @Berend

Did you have a chance to try it out?

Daniel

Berend · March 8, 2024, 6:49am

Hi @danielmarbach,

Finally got around to test it. I see an outbox table and record is inserted. Unfortunately an exception occurs when this record is being read in again related to the DISPATCHED field of the outbox record in OutboxPersister.cs:

System.InvalidCastException: Unable to cast object of type ‘System.Int32’ to type ‘System.Boolean’.
at System.Data.Common.DbDataReader.GetFieldValue[T](Int32 ordinal)
at System.Data.Common.DbDataReader.GetFieldValueAsync[T](Int32 ordinal, CancellationToken cancellationToken)
— End of stack trace from previous location —
at Extensions.GetBoolAsync(DbDataReader reader, Int32 position, CancellationToken cancellationToken) in //src/SqlPersistence/Extensions.cs:line 85
at OutboxPersister.Get(String messageId, ContextBag context, CancellationToken cancellationToken) in //src/SqlPersistence/Outbox/OutboxPersister.cs:line 102
at NServiceBus.TransportReceiveToPhysicalMessageConnector.Invoke(ITransportReceiveContext context, Func2 next) in /_/src/NServiceBus.Core/Pipeline/Incoming/TransportReceiveToPhysicalMessageConnector.cs:line 25 at NServiceBus.TransactionalSession.TransactionalSessionControlMessageExceptionBehavior.Invoke(ITransportReceiveContext context, Func2 next) in //src/NServiceBus.TransactionalSession/TransactionalSessionControlMessageExceptionBehavior.cs:line 14
at NServiceBus.RetryAcknowledgementBehavior.Invoke(ITransportReceiveContext context, Func`2 next) in //src/NServiceBus.Core/ServicePlatform/Retries/RetryAcknowledgementBehavior.cs:line 25
at NServiceBus.MainPipelineExecutor.Invoke(MessageContext messageContext, CancellationToken cancellationToken) in //src/NServiceBus.Core/Pipeline/MainPipelineExecutor.cs:line 49
at NServiceBus.MainPipelineExecutor.Invoke(MessageContext messageContext, CancellationToken cancellationToken) in //src/NServiceBus.Core/Pipeline/MainPipelineExecutor.cs:line 68
at NServiceBus.Transport.SqlServer.ProcessStrategy.TryHandleMessage(Message message, TransportTransaction transportTransaction, ContextBag context, CancellationToken cancellationToken) in //src/NServiceBus.Transport.SqlServer/Receiving/ProcessStrategy.cs:line 49
at NServiceBus.Transport.SqlServer.ProcessWithNativeTransaction.TryProcess(Message message, TransportTransaction transportTransaction, ContextBag context, CancellationToken cancellationToken) in //src/NServiceBus.Transport.SqlServer/Receiving/ProcessWithNativeTransaction.cs:line 112

By the looks of it another case of ‘int’ needs to be added the extension method GetBoolAsync, where a special case for Oracle already exists:

github.com

Particular/NServiceBus.Persistence.Sql/blob/master/src/SqlPersistence/Extensions.cs

using System;
using System.Data;
using System.Data.Common;
using System.Reflection;
using System.Threading;
using System.Threading.Tasks;
using NServiceBus.Extensibility;
using NServiceBus.Settings;
using NServiceBus.Transport;

static class Extensions
{
    public static void AddParameter(this DbCommand command, string name, object value)
    {
        var parameter = command.CreateParameter();
        parameter.ParameterName = name;
        parameter.Value = value;
        command.Parameters.Add(parameter);
    }

This file has been truncated. show original

Thanks!

danielmarbach · March 8, 2024, 5:27pm

I pushed a fix to

will share the artifacts once the build is ready

Daniel

danielmarbach · March 11, 2024, 7:58am

Here we go

NuGet packages(1).zip (1.0 MB)

Berend · March 12, 2024, 2:02pm

Hi @danielmarbach,

Thanks for the fix. The record in the outbox table is updated, DISPATCHED is set to 1, and the JSON string in OPERATIONS is updated to an empty json array. According to the SqlDialect, this looks like the correct behavior as seen in GetOutboxSetAsDispatchedCommand:

github.com

Particular/NServiceBus.Persistence.Sql/blob/master/src/SqlPersistence/Outbox/SqlDialect_Oracle.cs

namespace NServiceBus
{
    public partial class SqlDialect
    {
        public partial class Oracle
        {
            internal override string GetOutboxTableName(string tablePrefix)
            {
                return $"{SchemaPrefix}\"{tablePrefix.ToUpper()}OD\"";
            }

            internal override string GetOutboxSetAsDispatchedCommand(string tableName)
            {
                return $@"
update {tableName}
set
    Dispatched = 1,
    DispatchedAt = :DispatchedAt,
    Operations = '[]'
where MessageId = :MessageId";

This file has been truncated. show original

The last bit I tested is the deletion of records from the outbox by the OutboxCleaner by lowering the deduplication record timespan, this triggered the deletion of records from the Outbox table correctly.

As far as I can see everything seems to be working in regards to Outbox feature. One concern I still have is that there are more features (like sagas for instance) using persistence, so in theory there could still be issues hiding here as well. But I suppose when we would decide using these features in the future and issues occur we could get back online.

Please let me know when this fix is included in your release.

Thanks, Berend

danielmarbach · March 13, 2024, 9:07pm

Hi @Berend

Here we go

Hope that helps
Daniel

Berend · March 14, 2024, 11:15am

Thank you very much!