OrmLite stripping ParamString when using Devart?

ServiceStack.OrmLite
1

I’ve been trying to get OrmLite and Devart to work together. I’ve noticed an issue in that the default OrmLiteDialectProviderBase<OracleOrmLiteDialectProvider>.ParamString is being stripped out of the resulting SQL query.

By default ParamString is “:” but this results in Sql statements containing bind variables that don’t have the “:” prefix. To get the prefix I need to set ParamString to “::”.

E.g.

var ormLiteDialectProvider = new OracleOrmLiteDialectProvider(false, false, "Devart.Data.Oracle");
ormLiteDialectProvider.ParamString = "::";

OrmLiteConfig.DialectProvider = ormLiteDialectProvider;

using (var connection = Devart.Data.Oracle.OracleConnection(connectionString))
{
    var results = connection.Select<MyTable>(w => w.MyField == "FooBar");
}

If I use a CaptureSqlFilter on the above I get the expected SQL

SELECT MyField FROM MyTable WHERE (MyField = :0)

However if I had left ParamString as “:” I would have got

SELECT MyField FROM MyTable WHERE (MyField = 0)

Which results in a ORA-01036: illegal variable name/number error from Oracle.

To try and determine if it’s OrmLite or Devart driver that’s clearing down the “:” I’ve tried executing a query using a param directly using Devarts OracleConnection, OracleCommand, OracleParameter etc and don’t see the “:” prefix being removed.

Can you shed any light on what may be removing this?

2

OrmLite isn’t removing the prefix, it just uses the ParamString, you’ve never needed to double quote it with ::.

You can try turning debug logging on with:

LogManager.LogFactory = new ConsoleLogFactory(debugEnabled:true);

Which should log the generated SQL + Params to the Console.

3

Must be Devart that is causing the issue as something is removing it, will try to further debug in identify what it is.

4

On the off-chance that someone stumbles upon this I’ve tracked the issue down to the Setter for Devart DbParameterBase.ParameterName stripping off the colon.

The solution to this seems to be setting ParamString = “::” as detailed above or setting the ParamNameFilter

OrmLiteConfig.ParamNameFilter = s => string.Concat(":", s);

1 Like