What's the status with dotConnect/DevArt Oracle drivers?

IOW can LLBLGenPro work with them? What are the required steps? I don't think they'll work out of the box, will they? A recompile with proper #define?

mihies wrote:

What's the status with dotConnect/DevArt Oracle drivers?

Considering our history with DevArt/CoreLab, the less I have to work with their stuff, the better. For MySQL we made an exception as the native MySQL ado.net provider plain sucks.

IOW can LLBLGenPro work with them? What are the required steps? I don't think they'll work out of the box, will they? A recompile with proper #define?

You should look at the driver code, and create a subclass system similar as is done for ODP.NET and MS Oracle. This way, you can add support for it without a lot of effort. All meta-data is obtained through PL/SQL queries anyway, so this should work with their provider as well. You should check whether they have implemented any type conversion from NUMBER, if not, start with the MS Oracle sub type class and convert that, as you then don't have to change a lot of values in the 3 tables.

For the templates, this is really easy, just 2 tiny templates with a name, nothing fancy.

The DQE, it's 1 code base with #ifdef's, and my guess is you can stick with the MS Oracle code path.

Hi Frans,

Well, those (Oracle devArt) drivers have a big advantage - no need or reference the Oracle client, copy and paste deploy. I can't even explain what an advantage that is Plus I need trivial stuff mostly.

I need those for runtime only, not for design time so I guess I don't need to touch the drivers or templates, just DQE. I took a look at sources and I saw that you have already a #DataDirect directive and a Configuration for it. I guess I could just recompile that configuration and it should theoretically work. I will certainly try it.

Perhaps you might reconsider runtime support.

I slightly modified DQE (MS version) by providing proper DbFactory arguments. It looks fine.

One problem I immediately got is that DevArt converts NUMBER(3,0) -> Int32 while MS converts it to Int16. The result is conversion failure when reading from entity property.

What would be the proper approach to solve it? Probably by modifying the design time driver as you suggested (to map NUMBER(3,0) -> Int32)?

Nevermind, looks like the number mapping can be set through connection instance.

Here is another issue. OracleParameter eats the prefix (':') and thus Oracle fails wherever parameter names are used (i.e. p1 instead of : p1). Is there a centralized way to re-add prefix (by modifying sources probably). I fixed that for select queries but I see that update queries are using something else.

Whew, I solved this one as well. I'll have to blog about. The solution is to provide double colon in OracleSpecificCreator.CreateParameterName, like:

        protected override string CreateParameterName()
        {
            return this.CreateParameterName("::");
        }

Doing it this way it sets the name to

:p1

and it works.

Nice

You can add a conversion method to convert number + precision + scale to a type, see the ODP.NET subclass. MS Oracle doesn't convert number, it's always Decimal.

Frans, where (in sources) do you set .net type that matches Oracle's one (at design time) - i.e. Number(3,0) -> Int16.

I have to provide the same mapping. Plus, is there a way to get the connection instance when it is created from DQE (mappings are applied to connection instance)?

I think you are looking for FillNETToDBTypeConversionsList in OracleDriver.cs

Walaa wrote:

I think you are looking for FillNETToDBTypeConversionsList in OracleDriver.cs

Indeed, thanks.

The other question remains, though - possible to get connection instance from within DQE?

I'm not sure why you need that, but here you go... Derive from the dataAccessAdapter, and override the CreateNewPhysicalConnection.

public partial class MyDataAccessAdapter : DataAccessAdapter
{
     protected override IDbConnection CreateNewPhysicalConnection(string connectionString)
     {
          OracleConnection toReturn = new OracleConnection(connectionString);

          // Do something here...

          return toReturn;
     }
}

Hi Walaa,

I need that to apply type mapping (from Oracle type to .net type). DevArt does it through connection instance.

I know it is possible by overriding DataAccessAdapter, but is it possible from DQE specific assembly. The reason is that I'd like to apply proper mapping from within DQE assembly (all in one place) if possible.

AFAIK there is no direct way to do that. The connection is always created outside by the adapter, and passed to the DQE when it applies (f.i. CreatInsertDQ).

What exactly do you want to do? What mappings code do you want to move to DQE?

daelmo wrote:

AFAIK there is no direct way to do that. The connection is always created outside by the adapter, and passed to the DQE when it applies (f.i. CreatInsertDQ).

What exactly do you want to do? What mappings code do you want to move to DQE?

I intend to provide same type mappings as odp.net does. i.e. Number(3,0) -> Int16. DevArt by default maps to Int32. But they have an option to provide custom mappings which are applied to their connection instance through Mappings property, i.e.

connection.Mappings.Add(new Mapping(OracleType.Integer, 3, typeof(Int16));

Something like that. In order to provide support for devart in one place I'd like to put all required code into DQE assembly, mappings included. Since there is no way to catch connection creation within DQE I guess I could check whether the connection provided has mappings set and if not, set them before execution of anything.

Or, you guys, could add connection creation notification (a virtual method perhaps) to DQE. That way I could simply override that method and set mappings there.

The connection object is inside the query object you're producing, check the query's Connection property . So in the Create query methods you implement, simply do a call to a method you write to set the appropriate mappings and pass in query.Connection. It's DbConnection typed, so you need to cast to the proper type. There's a workaround, see below.

You can also do this in the adapter, not the DQE, as it's IMHO easier. Override in a partial class of DataAccessAdapter the method 'CreateNewPhysicalConnection'. Call the base, then call the method you want.

But why not solve it in the driver? The OracleDBDriver.ConvertDBTypeToNETType method is the one you should alter.

It gives you different .NET types for NUMBER(x, y) than for ODP.NET. This is OK unless you want to swap DQEs at will, then you need the mappings in the DQE of course and not in the Adapter either.

If you don't want to have the hard reference to devart, you can use reflection to set properties and compile a delegate for the property setter. We do that too for ODP.NET specific properties. See OracleSpecificCreator.SetDbProviderFactoryParameterData for how we do this. It should be straight forward, but it might be you need the types anyway so a hard reference might be the only way.