OutSystems.RuntimePublic.Db.Command CreateParameter has weird behaviour

OutSystems.RuntimePublic.Db.Command CreateParameter has weird behaviour

  

We're using 9.0.1.75 on our Enterprise cloud - so I'm not sure whether this issue has been fixed in 9.1, but it might be obscure enough not to have been.

When trying to use OutSystems.RuntimePublic.Db.Command for multiple rows, substituting parameters becomes an issue - a really weird issue.

I eventually got suspicious and put the parameter update into a separate method:

    private void UpdateParameter(Command command, string parameterName, DbType dbType, object value)
    {
        command.CreateParameter(parameterName, dbType, value);
        DataParameter parameter = command.GetParameter(parameterName);
        if (parameter.Value != value)
            throw new InvalidCastException(string.Format(
                "Adding parameter did not work {0} should be {1} but is {2} instead.",
                parameterName, value, parameter.Value));
    }

The error actually tripped!

"Adding parameter did not work @TemplateName should be Flushbolt but is Schlage instead."

Making it check and then setting the parameter.Value if it's not equal actually worked:

    private void UpdateParameter(Command command, string parameterName, DbType dbType, object value)
    {
        command.CreateParameter(parameterName, dbType, value);
        DataParameter parameter = command.GetParameter(parameterName);
        if (parameter.Value != value)
            parameter.Value = value;
        parameter = command.GetParameter(parameterName);
        if (parameter.Value != value)
            throw new InvalidCastException(string.Format(
                "Adding parameter did not work {0} should be {1} but is {2} instead.",
                parameterName, value, parameter.Value));
    }

Command does not have a lot of Parameter access available to it. I would use GetParameter, but if the parameter does not exist, it throws an error, so I'm stuck either catching that error or checking if the CreateParameter did not substitute. Either that, or make my own object to remember what parameters I have set on the command so I know whether to call CreateParameter or GetParameter.

This is one of the weirdest errors I've encountered so far; I thought I was going crazy :)

NOTE: You can get a Command from an extension this way:

    using OutSystems.RuntimePublic.Db;

    DatabaseAccess.ForDatabase("dbo").GetRequestTransaction().CreateCommand(sql)

-- Ritchie Annand

That is really very puzzling   . Can you attach a xif that reproduces the thing? 

By your explanation, I don't know what the sql is and maybe that's easier.


Regards,

Sure thing. This extension isn't ready yet (I haven't got the import side fully working), but it's meant to help synchronize data between OutSystems cloud servers.

The SQL in question is fairly standard for SQL Server. It's an INSERT INTO [physical table name for entity] ( [col1], [col2], [col3]) OUTPUT INSERTED.[Id] VALUES (@col1, @col2, @col3)

TabDelimitedEnvironmentImporter.cs contains a method called UpdateParameter. It has the redundant assignment in it in order for it to work. If you leave off the

    if (parameter.Value != value)
       parameter.Value = value;

...then it will semi-randomly fail even on fairly small files.

If you actually want to try out the extension, for the output, use CreateEntityAllFilter, then ExportData (using the Espace name you want to export and the filter object from CreateEntityAllFilter) and then lead that to Download the binary data created in ExportData.

The Import side where the error occurs is a little trickier. There needs to be a key mapping entity in each Espace being imported to. It needs the columns Entity (Text), OriginalID (Integer), MappedID (Integer) and Source (text). You pass in the Espace names to use for the import, the binary from an uploaded export file , the entity name of that key mapping entity and IsWholeEnchilada doesn't matter too much - that tells it whether to delete rows not found in the export.

I think I see what's going on. My pardons for being rude and reaching for ILSpy for this :)

Deep down in CreateParameter, it goes to an OutSystems.HubEdition.Extensibility.Data.ExecutionService.IExecutionService.

There's one implementation of that called BaseExecutionService which looks like it will cause exactly this issue. Decompiled in ILSpy, you get:

public virtual IDbDataParameter CreateParameter(IDbCommand cmd, string name, DbType dbType, object paramValue)
{
    IDbDataParameter dbDataParameter = cmd.CreateParameter();
    dbDataParameter.ParameterName = name;
    this.SetCorrectDBType(dbType, dbDataParameter);
    if (cmd.Parameters.Contains(name))
    {
        return (IDbDataParameter)cmd.Parameters[name];
    }
    this.SetParameterValue(dbDataParameter, dbType, paramValue);
    cmd.Parameters.Add(dbDataParameter);
    return dbDataParameter;
}

Note that if the command parameters already contain a parameter by that name, it just returns that parameter, never having set the value in it.

The middle if block should be something like:

    if (cmd.Parameters.Contains(name))
    {
        cmd.Parameters[name].Value = paramValue;
        return (IDbDataParameter)cmd.Parameters[name];
    }

It seems like a bug to me   

-- Ritchie Annand

Actually, there's no point even creating the new parameter if it already exists. I would rewrite it to:

public virtual IDbDataParameter CreateParameter(IDbCommand cmd, string name, DbType dbType, object paramValue)
{
    bool exists = cmd.Parameters.Contains(name);
    IDbDataParameter dbDataParameter;
    if (exists)
        dbDataParameter = (IDbDataParameter)cmd.Parameters[name];
    else
    {
        dbDataParameter = cmd.CreateParameter();
        dbDataParameter.ParameterName = name;
    }
    // I don't know if we should avoid setting the DBType and/or value if it's already the same
    SetCorrectDBType(dbType, dbDataParameter);
    SetParameterValue(dbDataParameter, dbType, paramValue);
    if (!exists)
        cmd.Parameters.Add(dbDataParameter);
    return dbDataParameter;
}

Solution

I agree with you that the API seems weird, the documentation for the method says:

/// Creates and associates a new parameter to a command.

/// If the command already has a parameter with the same name, that parameter is returned

/// and the command parameters are not changed.

But nothing is said about the value, so I would expect the value to be changed. 

I'll open a bug request. 

In the meantime , I recommend you setting the parameter value "by hand" in the case of it already existing.

Regards,

Solution

That sounds good.

If GetParameter returned null if the parameter didn't exist instead of exploding, I would have used that instead - I really don't like to put in a catch for expected behaviour :)

A GetParameterNames or even a HasParameter(name) would do, but with a weird CreateParameter and an exploding GetParameter, I'll go with weird ;)

Thanks, Luis!

-- Ritchie