Specified cast is not Valid Between SQL and ORACLE

Posts   
 
    
Ganesh
User
Posts: 28
Joined: 18-May-2005
# Posted on: 31-May-2005 15:26:44   

I am switching the DBSpecific project for SQL and ORACLE, Our db structure is both are alike and most of the part it works but I get the following error on some of the attributes in the generated code... What am I doing wrong here?...I would like to have one DBGeneric and switch the DBSpecific depending upon the db choice, so I have generated two separate projects for SQL and ORACLE, but for testing purpose I used SQL DBGeneric and ORACLE DBSpecific to see how it works and I get the following error....any help is appreciated. Thanks.

Exception Details: System.InvalidCastException: Specified cast is not valid.

Source Error:

Line 3040: valueToReturn = TypeDefaultValue.GetDefaultValue(typeof(System.Decimal)); Line 3041: } Line 3042: return (System.Decimal)valueToReturn; Line 3043: } Line 3044: set

Stack Trace:

[InvalidCastException: Specified cast is not valid.] Jenzabar.CRM.EXPlugins.DAL.EntityClasses.AddressMasterEntity.get_Udef112N1() in C:\LLBLGenTemp\EXERP\EXDAL\DatabaseGeneric\EntityClasses\AddressMasterEntity.cs:3042

[TargetInvocationException: Property accessor 'Udef112N1' on object 'Jenzabar.CRM.EXPlugins.DAL.EntityClasses.AddressMasterEntity' threw the following exception:'Specified cast is not valid.'] System.ComponentModel.ReflectPropertyDescriptor.GetValue(Object component) +423 System.Web.UI.WebControls.BoundColumn.OnDataBindColumn(Object sender, EventArgs e) +200 System.Web.UI.Control.OnDataBinding(EventArgs e) +66 System.Web.UI.Control.DataBind() +26 System.Web.UI.Control.DataBind() +86 System.Web.UI.WebControls.DataGrid.CreateItem(Int32 itemIndex, Int32 dataSourceIndex, ListItemType itemType, Boolean dataBind, Object dataItem, DataGridColumn[] columns, TableRowCollection rows, PagedDataSource pagedDataSource) +169 System.Web.UI.WebControls.DataGrid.CreateControlHierarchy(Boolean useDataSource) +1408 System.Web.UI.WebControls.BaseDataList.OnDataBinding(EventArgs e) +49 System.Web.UI.WebControls.BaseDataList.DataBind() +23 EXERP.Default.btnSelect_Click(Object sender, EventArgs e) in c:\llblgentemp\exerp\default.aspx.cs:102 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain() +1292

Posts: 112
Joined: 09-Aug-2004
# Posted on: 31-May-2005 16:35:07   

I am pretty sure that class should be the same between SQL and ORACLE. What is the string value of valueToReturn? I wonder if it is a problem with NULLs or something of the sort between the two DBMS

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 31-May-2005 21:30:20   

It can be that you defined a type in Oracle which didn't result in the same .net type. What's the Oracle type of the 'Udef112N1' field in oracle and in sqlserver?

Frans Bouma | Lead developer LLBLGen Pro
Ganesh
User
Posts: 28
Joined: 18-May-2005
# Posted on: 01-Jun-2005 04:59:02   

'Udef112N1' in Oracle its defined as NUMBER, Width 11, Decimal 2. 'Udef112N1' in SQL its defined as NUMERIC, Width 11, Decimal 2.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 01-Jun-2005 09:55:06   

Ganesh wrote:

'Udef112N1' in Oracle its defined as NUMBER, Width 11, Decimal 2. 'Udef112N1' in SQL its defined as NUMERIC, Width 11, Decimal 2.

NUMBER(11,2) results in ODP.NET in a System.Double. NUMERIC(11,2) results in SqlClient in a System.Decimal.

to get a .NET type of System.Decimal from ODP.NET, either use 'DEC', 'DECIMAL' or 'NUMERIC' on oracle. These are synonym types for NUMBER(x, y) where x > 15.

Frans Bouma | Lead developer LLBLGen Pro
Ganesh
User
Posts: 28
Joined: 18-May-2005
# Posted on: 03-Jun-2005 23:22:57   

NUMBER(11,2) results in ODP.NET in a System.Double. NUMERIC(11,2) results in SqlClient in a System.Decimal.

to get a .NET type of System.Decimal from ODP.NET, either use 'DEC', 'DECIMAL' or 'NUMERIC' on oracle. These are synonym types for NUMBER(x, y) where x > 15.

We tried to create DECIMAL type in ORACLE and it implicitly changes to NUMBER!

Do you have any mapping/header file that we can alter the way LLBLGen creates those?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 03-Jun-2005 23:29:08   

Ganesh wrote:

NUMBER(11,2) results in ODP.NET in a System.Double. NUMERIC(11,2) results in SqlClient in a System.Decimal.

to get a .NET type of System.Decimal from ODP.NET, either use 'DEC', 'DECIMAL' or 'NUMERIC' on oracle. These are synonym types for NUMBER(x, y) where x > 15.

We tried to create DECIMAL type in ORACLE and it implicitly changes to NUMBER!

Do you have any mapping/header file that we can alter the way LLBLGen creates those?

That's not helping much, as ODP.NET will convert the values to the types it thinks will suit the type/value. So if you define NUMBER(11,2), it will end up in a System.Double, nothing can be done about that.

If you define your oracle type in NUMBER(16,2), it will be a decimal, and you will have the same type.

Frans Bouma | Lead developer LLBLGen Pro
Adam
User
Posts: 25
Joined: 03-Jun-2005
# Posted on: 03-Jun-2005 23:36:24   

Like Ganesh, I am having issues with how Llblgen maps Oracle and SQL Server data types to C# data types. When different C# data types are generated for Oracle and SQL Server, it is impossible to use one generic entity class layer. I love the features Llbgen has to offer, but this is a major hurdle.

What method does Llblgen use to determine how database data types are mapped to C# data types? I guess what I would really like to know is if it is possible to expose this "mapping" so that the customer can change it. For example, it would be a great feature in Llblgen to have a window that shows the database type on one side and the corresponding C# data type on the other. Then it would allow the customer to change it if needed. This way I can be certain that my SQL Server and Oracle data types will always match.

Although, this may not be possible if the ODP.Net provider does the mapping and not Llblgen. Anyway, just curious. Thanks!

Adam
User
Posts: 25
Joined: 03-Jun-2005
# Posted on: 04-Jun-2005 00:03:15   

That's not helping much, as ODP.NET will convert the values to the types it thinks will suit the type/value. So if you define NUMBER(11,2), it will end up in a System.Double, nothing can be done about that.

If you define your oracle type in NUMBER(16,2), it will be a decimal, and you will have the same type.

That worked for data types that have precision, but what about data types like numeric(16,0)? I tried this and ODP.Net created a System.Int64 data type. Any ideas on how to convert this to a .Net type of System.Decimal?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 04-Jun-2005 09:31:22   

Adam wrote:

That's not helping much, as ODP.NET will convert the values to the types it thinks will suit the type/value. So if you define NUMBER(11,2), it will end up in a System.Double, nothing can be done about that.

If you define your oracle type in NUMBER(16,2), it will be a decimal, and you will have the same type.

That worked for data types that have precision, but what about data types like numeric(16,0)? I tried this and ODP.Net created a System.Int64 data type. Any ideas on how to convert this to a .Net type of System.Decimal?

This is the routine which shows the .NET types per ODP.NET type. I've found this out through trial/error myself also. I understand your gripes with it, I'm currently addressing it in the current upgrade, so you can provide converter assemblies which will take care of the conversion between data to the type you'd like to use.


public Type DBType2NETType(int dbType, int precision, int scale)
{
    if((dbType!=(int)OracleDbTypes.Number)&&(dbType!=(int)OracleDbTypes.Float))
    {
        // not a number nor float, redirect to overload
        return DBType2NETType(dbType);
    }

    int actualPrecision = precision;
    if(precision==0)
    {
        actualPrecision = 38;
    }

    // Following code is based on ODP.NET's conversion routine, as it is pretty obscure what ODP.NET's doing with values read.
    // the if statements are from ODP.NET. 
    if(scale==0)
    {
        if(actualPrecision < 5)
        {
            return typeof(System.Int16);
        }
        if(actualPrecision < 10)
        {
            return typeof(System.Int32);
        }
        if(actualPrecision < 19)
        {
            return typeof(System.Int64);
        }
    }
    else
    {
        // scale specified.
        if(actualPrecision < 8)
        {
            return typeof(System.Single);
        }
        if(actualPrecision < 16)
        {
            return typeof(System.Double);
        }
    }
    // no match, return decimal
    return typeof(System.Decimal);
}

So if you have NUMBER(16,0), it indeed will become System.Int64. If you create NUMBER(19,0), it will be a decimal.

Adam wrote:

Like Ganesh, I am having issues with how Llblgen maps Oracle and SQL Server data types to C# data types. When different C# data types are generated for Oracle and SQL Server, it is impossible to use one generic entity class layer. I love the features Llbgen has to offer, but this is a major hurdle.

I fully agree. It's possible to get things act the same though, however you have to change Oracle types, like instead of using NUMBER(11,0), you perhaps have to use NUMBER(19,0).

What method does Llblgen use to determine how database data types are mapped to C# data types? I guess what I would really like to know is if it is possible to expose this "mapping" so that the customer can change it. For example, it would be a great feature in Llblgen to have a window that shows the database type on one side and the corresponding C# data type on the other. Then it would allow the customer to change it if needed. This way I can be certain that my SQL Server and Oracle data types will always match.

This is in development at the moment (part of the features to implement in the current upgrade). 'Changing' the .NET type isn't that easy though. The problem is that when OracleDataReader delivers its values, the value in a NUMBER(15,0) column is of type System.Int64. I store that value in the CurrentValue of the field. Though, when you for example selected 'Decimal' as .NET type for that field, the property generated will of course crash, as Int64 doesn't have an implicit converter to Decimal. You can of course imagine the matrix of types to convert from/to.

Although, this may not be possible if the ODP.Net provider does the mapping and not Llblgen. Anyway, just curious. Thanks!

That's indeed the case. Though it is possible, but at a later stage, say, in the property. So instead of returning the CurrentValue of the field, it's first converted using a converter class, which is written by you or supplied with LLBLGen Pro. This also opens the road to nullable fields in .NET 1.x simple_smile

Frans Bouma | Lead developer LLBLGen Pro
Adam
User
Posts: 25
Joined: 03-Jun-2005
# Posted on: 06-Jun-2005 15:48:57   

Thanks so much for the detailed response! Would you be aware of any guide or document that displays how the ODP.Net provider maps to C# data types? Possibly a SQL Server to C# data type mapping as well?

We will most likely go with changing the data types in both our SQL Server and Oracle databases, but it would be great to have a mapping guide so we can make all of our changes at once (rather than making the changes as things break wink ).

Thanks again.

Adam
User
Posts: 25
Joined: 03-Jun-2005
# Posted on: 07-Jun-2005 01:51:37   

I understand your gripes with it, I'm currently addressing it in the current upgrade, so you can provide converter assemblies which will take care of the conversion between data to the type you'd like to use.

When you talk about "addressing it in the current upgrade", can you please provide some more information? I'm curious to know where and how I can "provide converter assemblies which will take care of the conversion between data to the type you'd like to use." We have soooo many tables with all sorts of numeric values, I am told I will not be able to change the Oracle or SQL Server data types just to make it work with llblgen/ ODP.Net.

So basically I'm wondering if I go down the road of using the generic class layer created in by the SQL Server LLBLGen project, are you certain the "converter assembly" method you spoke of will be able to handle the different Oracle types?

My group has to decide in the next few days whether or not to use LLBLGen in our product. So if there is possibly a way to test this functionality (converter assemblies) in some sort of beta capacity, I would appreciate it.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 07-Jun-2005 11:17:00   

Adam wrote:

I understand your gripes with it, I'm currently addressing it in the current upgrade, so you can provide converter assemblies which will take care of the conversion between data to the type you'd like to use.

When you talk about "addressing it in the current upgrade", can you please provide some more information? I'm curious to know where and how I can "provide converter assemblies which will take care of the conversion between data to the type you'd like to use." We have soooo many tables with all sorts of numeric values, I am told I will not be able to change the Oracle or SQL Server data types just to make it work with llblgen/ ODP.Net.

The converter assemblies will be implementing a known interface on a set of classes. The designer reads the assembly and you can then define the .NET type for fields in entities using the converter assembly classes. This means that for example you can define NUMBER(1,0) fields, which are now .NET type int16/short, are then definable as 'bool', using the converter assembly (which I provide, but you can write your own).

The bottom line is that the .NET interface is the same, the data to /from the db is different, through the converter assembly.

So basically I'm wondering if I go down the road of using the generic class layer created in by the SQL Server LLBLGen project, are you certain the "converter assembly" method you spoke of will be able to handle the different Oracle types?

Yes, the update won't be released until this works. It's not that difficult actually, the problems mainly are in the fact that the data is of the wrong .NET type either when it's read from the db, or when it's send to the db. A thin converter layer will solve that. It of course has a price... converting char(1) 'Y' to bool true will take time, not much, but it will. Reading thousands of objects will then be a bit slower through the conversion. I've to decide yet where the conversions will take place, as converting data in the core engine which reads data from the DB is the best place, though also the slowest.

My group has to decide in the next few days whether or not to use LLBLGen in our product. So if there is possibly a way to test this functionality (converter assemblies) in some sort of beta capacity, I would appreciate it.

They're not available in beta code yet, only on the drawing board. Inheritance implementation is first, then converters. Though it's released when it's done. I want this to be a solid set of features and I'm not going to rush it.

Frans Bouma | Lead developer LLBLGen Pro
Adam
User
Posts: 25
Joined: 03-Jun-2005
# Posted on: 07-Jun-2005 16:44:06   

Thanks for your response. Do you anticipate this feature being available in the next month or two? Just seeeing if it will be feasible for my team to use LLBLGEN on this project. Thanks again.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 07-Jun-2005 17:08:07   

Adam wrote:

Thanks for your response. Do you anticipate this feature being available in the next month or two? Just seeeing if it will be feasible for my team to use LLBLGEN on this project. Thanks again.

I hope to have a beta by mid-july. simple_smile Every feature requireing a longer development time will be cut and moved on to v2.0. That is: features besides the main features currently being added: Inheritance, type converters for fields, multiple entities mapped on single target, hide fields in entities, remap entities on tables and some smaller things.

Frans Bouma | Lead developer LLBLGen Pro
Adam
User
Posts: 25
Joined: 03-Jun-2005
# Posted on: 07-Jun-2005 22:14:48   

Frans, you’ve been very helpful! I hope you don’t mind if I ask a few more questions to put my mind at ease. simple_smile

I think we’re going to go ahead and generate the SQL Server DBSpecific and Generic classes. Given this, here are a few questions:

When the type converter enhancement becomes available, can we create the Oracle DBSpecific and Generic classes, throw away the Oracle generic classes and use the generic layer created by the SQL Server project? In other words, can we have a single generic layer work with both the SQL Server AND Oracle DBSpecific classes?

How much re-work (if any) do you think we will need to do to the generic or dbspecific layers? Will it be a matter of regenerating the LLBLGEN classes? Or will it be a matter of adding/creating new classes to support the new converter assembles? Basically I’m hoping to find out what impact there will be on our code when we implement the converter classes in the new release.

Last question, can I get on your list of beta customers as soon as this feature is available? I’m a new customer and I’m not sure how releases are announced, but I would appreciate getting to work on it as soon as possible.

Thanks again! - Adam

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 08-Jun-2005 11:54:15   

Adam wrote:

Frans, you’ve been very helpful! I hope you don’t mind if I ask a few more questions to put my mind at ease. simple_smile

I think we’re going to go ahead and generate the SQL Server DBSpecific and Generic classes. Given this, here are a few questions:

When the type converter enhancement becomes available, can we create the Oracle DBSpecific and Generic classes, throw away the Oracle generic classes and use the generic layer created by the SQL Server project? In other words, can we have a single generic layer work with both the SQL Server AND Oracle DBSpecific classes?

That's the whole point indeed: having a single db generic project which is used with different db specific projects, so the conversion stuff is located in the db specific project.

How much re-work (if any) do you think we will need to do to the generic or dbspecific layers? Will it be a matter of regenerating the LLBLGEN classes? Or will it be a matter of adding/creating new classes to support the new converter assembles? Basically I’m hoping to find out what impact there will be on our code when we implement the converter classes in the new release.

The type conversions will be project wide (llblgen project). This thus means you will define in your oracle project: NUMBER(1, 0) should map on .NET type 'bool' and use converter xyz. Then all NUMBER(1,0) fields, parameters etc will be remapped to bool and their converters will be set to the specified converter. (that's the current designed solution, implementation still has to be done) This then results in entities which use bool types and act as if the field is a boolean, the db specific project has code to pass the bool to the converter to get the NUMBER(1,0) value back. (and vice versa when a row is fetched).

Last question, can I get on your list of beta customers as soon as this feature is available? I’m a new customer and I’m not sure how releases are announced, but I would appreciate getting to work on it as soon as possible.

As a customer, you're already on the list simple_smile . Every beta is open for customers simple_smile

Frans Bouma | Lead developer LLBLGen Pro
Ganesh
User
Posts: 28
Joined: 18-May-2005
# Posted on: 08-Jun-2005 15:18:33   

The type conversions will be project wide (llblgen project). This thus means you will define in your oracle project: NUMBER(1, 0) should map on .NET type 'bool' and use converter xyz. Then all NUMBER(1,0) fields, parameters etc will be remapped to bool and their converters will be set to the specified converter. (that's the current designed solution, implementation still has to be done) This then results in entities which use bool types and act as if the field is a boolean, the db specific project has code to pass the bool to the converter to get the NUMBER(1,0) value back. (and vice versa when a row is fetched).

Suggestion: Can this be done in such a way that it can read the known mapping for ODP.NET to .NET type from a config/header file, rather for each entity we add in the project we need to be changing it. This could save quite a good amount of time. Also in future if we come across any new datatype to be mapped for .NET type then we add that in the list. smile

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 08-Jun-2005 15:56:08   

Ganesh wrote:

The type conversions will be project wide (llblgen project). This thus means you will define in your oracle project: NUMBER(1, 0) should map on .NET type 'bool' and use converter xyz. Then all NUMBER(1,0) fields, parameters etc will be remapped to bool and their converters will be set to the specified converter. (that's the current designed solution, implementation still has to be done) This then results in entities which use bool types and act as if the field is a boolean, the db specific project has code to pass the bool to the converter to get the NUMBER(1,0) value back. (and vice versa when a row is fetched).

Suggestion: Can this be done in such a way that it can read the known mapping for ODP.NET to .NET type from a config/header file, rather for each entity we add in the project we need to be changing it. This could save quite a good amount of time. Also in future if we come across any new datatype to be mapped for .NET type then we add that in the list. smile

It is always a global list, so the list is applied to all entities. By default it is set to the ODP.NET defaults. You then alter it for your project (for example, not always do you need to set these converters), then add entities.

Your suggestion is to store the preferred types somewhere so next time you start a project you've already defined them?

Frans Bouma | Lead developer LLBLGen Pro
Ganesh
User
Posts: 28
Joined: 18-May-2005
# Posted on: 08-Jun-2005 16:52:45   

Perfect Frans. We are eagerly waiting to get this feature soon and hope that will take care of current and future needs of ORACLE. Thanks a lot for taking this issue as a priority and addressing it in a fourth coming release. sunglasses

Adam
User
Posts: 25
Joined: 03-Jun-2005
# Posted on: 08-Aug-2005 19:51:19   

Just curious -- has this feature been released, or is still in the works?

Strike that last question. I see on http://www.llblgen.com/pages/roadmap.aspx that this feature will be available in the August-September upgrade. simple_smile

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 08-Aug-2005 20:22:33   

Adam wrote:

Just curious -- has this feature been released, or is still in the works?

Strike that last question. I see on http://www.llblgen.com/pages/roadmap.aspx that this feature will be available in the August-September upgrade. simple_smile

Yes, it's on the list of that upgrade indeed simple_smile I'm 80% done with inheritance and that feature is next. It's definitely part of the upgrade, I'll cut other features on the (small) list if time is short.

Frans Bouma | Lead developer LLBLGen Pro