Hi,

In our Oracle database, our "ID" (pk) fields are defined as simply "NUMBER" - we set no precision or scale in Oracle.

When you lead these tables into LLBLGen, it uses the .NET type "System.Decimal" to map to these, which technically is right, but we need it to use System.Int instead!! The reason for this is that we are using the adaptor model to support multiple databases, and we need the entites to exactly match the SQL Server ones (which do map to System.Int).

Is there anyway we can do this. In the designer the .NET type is locked....

Any help greatly appreciated!

You should define precision and scale values in Oracle. According to the Oracle documentation, Oracle internally treats a NUMBER without precision or scale as a full decimal, precision 38. It's IMHO a best practise to define the precision and scale on NUMBER types.

I had a similar problem, sadly "no precision" in Oracle actually translates to a precision of 38 which is why you get a System.Decimal

[edit] oops, frans got their first.

I have to support Oracle and SQL Sever databases too. I had to come up with a chart that helps me to define data types in Oracle and SQL Server that would produce the same .NET data type in a LLBLGen entity. Here is an example:

• Oracle "NUMBER( 8 )" and SQL Server "int" produce .NET "System.Int32"
• Oracle "NUMBER( 4 )" and SQL Server "smallint" produce .NET "System.Int16"
• Oracle "NUMBER( 16,8 )" and SQL Server "decimal" produce .NET "System.Decimal"
• Oracle "NUMBER( 8,4 )" and SQL Server "float" produce .NET "System.Double"
• Oracle "NUMBER( 4,2 )" and SQL Server "real" produce .NET "System.Single"

To make alex' list complete, here's the routine in the Oracle driver to produce .NET types based on the NUMBER setting. You can peek into this source in the beta, which comes with the sourcecode of all drivers (the sourcecode of the drivers will be opened up for customers when the beta is released as final through the SDK)

/// <summary>
/// Converts the specified DbType to its equivalent .NET type. This routine is used for NUMBER types, to convert them
/// to .NET types based on the precision and scale.
/// </summary>
/// <param name="dbType">dbtype to convert</param>
/// <param name="precision">precision of type</param>
/// <param name="scale">scale of type</param>
/// <returns>.NET type which is the equivalent of the passed in dbtype with the passed in precision and scale.</returns>
public Type DBType2NETType(int dbType, int precision, int scale)
{
    if(dbType!=(int)OracleDbTypes.Number)
    {
        // not a number
        throw new ArgumentException("DbType is not of type NUMBER.", "dbType");
    }

    if(scale != 0 && scale != 255 ) 
    {
        // floating point. Filter out singles and doubles
        if(precision > 15) 
        {
            return typeof(System.Decimal);
        }
        if(precision > 7)
        {
            return typeof(System.Double);
        }
        else 
        {
            return typeof(System.Single);
        }
    }
    else 
    {
        // number without a fraction.
        if(precision < 5) 
        {
            return typeof(System.Int16);
        } 
        if(precision < 10)
        {
            return typeof(System.Int32);
        } 
        if(precision < 19) 
        {
            return typeof(System.Int64);
        }
        else
        {
            return typeof(System.Decimal);
        }
    }
}

When the beta is finalized, I'll continue with a sideproject I started some time ago, which converts projects from one DB to another. (so you can feed it with an SqlServer project and it converts it to an Oracle project, that is: what can be converted) and vice versa)

Otis,

Could you post a source code of the method from the SQL Server driver that produces .NET data types?

The drivers work with tables for this. Oracle's Number type is an exception as ODP.NET will return a NUMBER(5,0) with a different type than NUMBER(16,3).

base.DBTypesAsNETType[(int)SqlDbTypes.BigInt] = typeof(System.Int64);
base.DBTypesAsNETType[(int)SqlDbTypes.Binary] = typeof(System.Byte[]);
base.DBTypesAsNETType[(int)SqlDbTypes.Bit] = typeof(System.Boolean);
base.DBTypesAsNETType[(int)SqlDbTypes.Char] = typeof(System.String);
base.DBTypesAsNETType[(int)SqlDbTypes.Datetime] = typeof(System.DateTime);
base.DBTypesAsNETType[(int)SqlDbTypes.Decimal] = typeof(System.Decimal);
base.DBTypesAsNETType[(int)SqlDbTypes.Float] = typeof(System.Double);
base.DBTypesAsNETType[(int)SqlDbTypes.Image] = typeof(System.Byte[]);
base.DBTypesAsNETType[(int)SqlDbTypes.Int] = typeof(System.Int32);
base.DBTypesAsNETType[(int)SqlDbTypes.Money] = typeof(System.Decimal);
base.DBTypesAsNETType[(int)SqlDbTypes.NChar] = typeof(System.String);
base.DBTypesAsNETType[(int)SqlDbTypes.NText] = typeof(System.String);
base.DBTypesAsNETType[(int)SqlDbTypes.Numeric] = typeof(System.Decimal);
base.DBTypesAsNETType[(int)SqlDbTypes.NVarChar] = typeof(System.String);
base.DBTypesAsNETType[(int)SqlDbTypes.Real] = typeof(System.Single);
base.DBTypesAsNETType[(int)SqlDbTypes.SmallDateTime] = typeof(System.DateTime);
base.DBTypesAsNETType[(int)SqlDbTypes.SmallInt] = typeof(System.Int16);
base.DBTypesAsNETType[(int)SqlDbTypes.SmallMoney] = typeof(System.Decimal);
base.DBTypesAsNETType[(int)SqlDbTypes.Sql_Variant] = typeof(System.Object);
base.DBTypesAsNETType[(int)SqlDbTypes.SysName] = typeof(System.String);
base.DBTypesAsNETType[(int)SqlDbTypes.Text] = typeof(System.String);
base.DBTypesAsNETType[(int)SqlDbTypes.TimeStamp] = typeof(System.Byte[]);
base.DBTypesAsNETType[(int)SqlDbTypes.TinyInt] = typeof(System.Byte);
base.DBTypesAsNETType[(int)SqlDbTypes.UniqueIdentifier] = typeof(System.Guid);
base.DBTypesAsNETType[(int)SqlDbTypes.VarBinary] = typeof(System.Byte[]);
base.DBTypesAsNETType[(int)SqlDbTypes.VarChar] = typeof(System.String);

Thanks!

Frans, that project you mention to convert a project from SQL server to Oracle sounds interesting. Would it work in the scenario I have - i.e. I have a "int" in SQL Server and a "NUMBER" in Oracle. Your project would stick with "system.int" for the int column and use this for the oracle generation - so theoretically I could stick with NUMBER for my Oracle database.

Ideally, I would do what you all suggest - I should use a number of scale 8 to make it a "proper" integer and then LLBLGen would also be happy....in fact I tried this, and this discovered.......

Unfourtunately, I can't! The reason for this is that I still need to work with the database in my old ASP application (its a slow transition to .NET). So we have new code in .NET using the database, and old ASP code using it also. "So what" I hear you all saying...well the problem is that due to a Microsoft bug, VBScript cannot correctly convert anything other than NUMBER to a number!! Pass it a NUMBER ( 8 ), and it fails unless you do explcitly CLng or CINt in VB!!! In case you're interested:

http://support.microsoft.com/support/kb/articles/Q195/1/80.ASP&NoWebContent=1

I found this after I created some nice SQL that converts ALL NUMBER's to NUMBER( 8 ) - bwah!!!

So, I need a "workaround". Frans, will I be able to modify the LLBL code so that in my case if I have a NUMBER it maps to an Integer? Or can I do this now by modifying the template?

Any help greatly welcome, I am getting nervous about this problem now...

I think the 'easiest' way is to write a task performer class and a new generator config file.

In your taskperformer, you can reach the complete project and catalog. Load the project, press F7 and select your custom config file. Start the generator and it will start your task performer class. That class then should walk the entities, all fields, if it's type is NUMBER, change the .NET type into System.Int32.

Check the SDK for a full reference manual of the project class, its contents and for example the field properties.

If your project is large, it will be perhaps time consuming. You can also create a console application, reference the application core assembly and load the project like this: (place your console app in the llblgen pro folder)

(just an example, to get the picture)

// load drivers
// Load configuration
ApplicationConfiguration configurationSettings = new ApplicationConfiguration();
DatabaseDriverConfig destinationDriverDefinition = (DatabaseDriverConfig)configurationSettings.DatabaseDriverDefinitions[destinationDriverID];
_destinationDriver = DBDriverHelper.CreateDBDriverInstance(destinationDriverDefinition.FullPathFilename, destinationDriverDefinition.Namespace, destinationDriverDefinition.ClassName);

//... 
Project destinationProject = Project.Load(_sourceProjectFile);
if(destinationProject==null)
{
    return null;
}

destinationProject.ProjectLocationPathFilename = _destinationProjectFile;
destinationProject.DatabaseDriver = _destinationDriver;

'destinationProject' is a full project object. You can modify any object in it. After it, simply call project.Save() , after setting IsChanged to false.

For an example about how to walk the project in a taskperformer, check the 3rd party section for the renamer task performer.

ps: with the gui updates, it is possible to change the driver's sourcecode to meet your needs. The updates will be out of beta next week.

Otis wrote:

I think the 'easiest' way is to write a task performer class and a new generator config file.

OK, I will take a look at the SDk and example as you suggested. Thanks!

Otis wrote:

ps: with the gui updates, it is possible to change the driver's sourcecode to meet your needs. The updates will be out of beta next week.

Is this also something I will need to do via the SDK (i.e. change the LLBLGen code and recompile)?

If only I had 10p for every time Oracle caused me problems....

Hmmmm.

Just looking at creating a "task performer" for this. What I wanted to do was check that:

If DotNetType = system.decimal AND DBType = "NUMBER" and scale = 8, then change DotNetType to system.int.

However, looking at the "entity" object it seems I only have access to the "DotNetType", so I can't check the DBType, scale, or precision I can't just replace all system.decimals, there may be some that I want to remain system.decimals.

Are there any workarounds to this Frans?

Yes you can Check the 'MappedField' property this is the actual table field object. It contains a TypeDefinition property which contains all the info of the actual database field type.

entityField.MappedField.TypeDefinition.<properties>

TypeDefinition is of type IDBTypeDefinition. MappedField is of type IDBTableField, which derives from IDBField.

I never doubted you

Thanks a lot! If you think anyone would be intered in the finished task performer, I'm happy to email it you...

Found another problem! The EntityFieldDefinition object contains the DotNetType, which is what I want to change...but this property is read only

What can I do?

Call entityField.SetNewMappedField(), pass in the already MappedField object and 'false' for the autoGeneratedIdentityFieldValues flag.

I was just about to post that I had found a solution -- although its not the same one as you posted. I ended up doing this:

IDBField fld = entField.MappedField;
IDBTypeDefinition tpe = fld.TypeDefinition;
tpe.DBTypeAsNETType = System.Type.GetType("System.Int32");

Which seems to worked fine Is this acceptable?

No

It does reflect the right value in the gui, but the interpreter uses the entityfield.DotNetType property.

Yep, just discovered this when I generated the code!!

Right, this I think is the job:

    
// Convert this one to a System.Int
IDBTableField tabFld = (IDBTableField) fld;
tpe.DBTypeAsNETType = System.Type.GetType("System.Int32");
entField.SetNewMappedField(tabFld, false);

So I still use DBTYpeAsNETType to change the type for the field, and then call SetNewMappedField to "save" it.

Subject to a small tidyup, does this seem logical?

This is the way to do it. You set the type in the table field type definition and the entity field's type information is derived from that.

Works a treat

Let me know if anyone wants it (although I can't see anyone else wanting to do something so "kludgey")

MattWoberts wrote:

Works a treat

Cool!

Let me know if anyone wants it (although I can't see anyone else wanting to do something so "kludgey")

You never know there are a lot of wicked databases out there, designed by people who should have been denied access to keyboards decades ago

True

I was thinking about your "project convert" project (SQL Server -> Oracle) you mentioned you were working on, and this sounds like something that would really benefit us. Will this be part of the "extras" section to d/l when it is done? Just curious....

Also, while I'm here, do you have a release date in mind for the new LLBL features that are currently being beta'd - it would be really helpful if i had a rough date (thats what our clients say to us all the time too )

Thanks!