search

ssteenkiste / nettiers

Automatically exported from code.google.com/p/nettiers
1 stars 0 forks source link

Oracle NUMBER Type / .NET 4.0 / C# - "Specified cast is not valid." Error #379

Open GoogleCodeExporter opened 9 years ago

GoogleCodeExporter commented 9 years ago 
What steps will reproduce the problem?
1. Create Oracle table with a column of NUMBER type
1.1 Polulate table
2. Build VS 2010 .Nettiers project against the Oracle database
3. Compile and run the VS 2010 project
3.1 When aspx page loads, click the menu choice to display the data

What is the expected output? What do you see instead?
aspx grid shows data from Oracle table.  Instead I get an error: "Specified 
cast is not valid. "

I've traced it to a casting issue between NUMBER and .NET.  

What version of .netTiers and CodeSmith are you using?
.netTiers == 3.2.1
CodeSmith == 5.2.2 Rv 11561

Please provide any additional information below.

This a very urgent issue for us.  In the imediate, I am looking for a 
workaround, in the long-term, I'd like to see a fix.  

Thanks,
Kevin6340

Original issue reported on code.google.com by Kevin6340@gmail.com on 25 Mar 2011 at 1:22

GoogleCodeExporter commented 9 years ago 
Hello,

Could you please use the following work around that is described here 
(http://community.codesmithtools.com/nettiers/f/16/t/11817.aspx). We will get 
this updated in a nightly build of generator / .netTiers.

Thanks
-Blake

Original comment by bniemyjski on 28 Mar 2011 at 3:27

GoogleCodeExporter commented 9 years ago 
Thanks Blake,
I am happy to use the workaround, however, I don't know my way around .netTiers 
or CodeSmith.  I am unable to determine where I to find the correct files and 
edits.  

Can you please provie more detail to implement the workaround?

Thanks,
Kevin6340

Original comment by Kevin6340@gmail.com on 29 Mar 2011 at 5:22

GoogleCodeExporter commented 9 years ago 
[deleted comment]
GoogleCodeExporter commented 9 years ago 
Hello,

You will need to open up the CommonSqlCode.cs file located in the TemplateLib 
folder. Navigate to line 3664 via control + h. Then paste the following code:

            if (field.NativeType.ToLower() == "number")
            {
                return "System.Decimal" + (field.AllowDBNull ? "?" : "");
            }

Thanks
-Blake

Original comment by bniemyjski on 30 Mar 2011 at 8:18

GoogleCodeExporter commented 9 years ago 
Thanks Blake.  This workaround seems to have done the trick.  Any idea when 
this will get worked into the nightly builds?

-Kevin6340

Original comment by Kevin6340@gmail.com on 31 Mar 2011 at 8:41

GoogleCodeExporter commented 9 years ago 
Hello,

I'm going to investigate this more when I get a chance. I might just revert the 
changes in the OracleSchemaProvider. Yes, it is nice to have the smaller Data 
Types for the different sizes of NUMBER. But the Oracle Data Provider seems to 
have a lot of issues with Casting as you have seen.

I know when I commit this, I could be breaking a bunch of people so I'd rather 
have more research to backup this change :-)

Thanks
-Blake

Original comment by bniemyjski on 31 Mar 2011 at 9:06

GoogleCodeExporter commented 9 years ago 
Blanke,
The workaround seems to have worked, however, I am now getting the same kind of 
problem only with a Decimal to a Byte, and also a Decimal to a String.  

I will paste the Stack Trace of each on two seperate posts.  This has become 
very critical and I am now being put on the spot about having chose CodeSmith 
and nettiers.  Any quick assistance provided will be greatly appreciated!

Thanks,
Kevin6340

Original comment by Kevin6340@gmail.com on 13 Apr 2011 at 2:42

GoogleCodeExporter commented 9 years ago 
[OverflowException: Value was either too large or too small for an unsigned 
byte.]
   System.Decimal.ToByte(Decimal value) +9459108
   System.Decimal.System.IConvertible.ToByte(IFormatProvider provider) +66
   System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider) +9532032
   System.Data.OracleClient.OracleParameter.CoerceValue(Object value, MetaType destinationType) +309

[OverflowException: Failed to convert parameter value from a Decimal to a Byte.]
   System.Data.OracleClient.OracleParameter.CoerceValue(Object value, MetaType destinationType) +360
   System.Data.OracleClient.OracleParameter.SetCoercedValueInternal(Object value, MetaType metaType) +23
   System.Data.OracleClient.OracleParameterBinding.PrepareForBind(OracleConnection connection, Int32& offset) +133
   System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals) +726
   System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, ArrayList& resultParameterOrdinals) +25
   System.Data.OracleClient.OracleCommand.ExecuteReader(CommandBehavior behavior) +142
   System.Data.OracleClient.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior) +4
   System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) +10
   Microsoft.Practices.EnterpriseLibrary.Data.Database.DoExecuteReader(DbCommand command, CommandBehavior cmdBehavior) +217
   Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteReader(DbCommand command, DbTransaction transaction) +51
   Microsoft.Practices.EnterpriseLibrary.Data.Oracle.OracleDatabase.ExecuteReader(DbCommand command, DbTransaction transaction) +70
   nu.mdms.tools.Data.Utility.ExecuteReader(TransactionManager transactionManager, DbCommand dbCommand) in C:\Users\Kevin\Desktop\MDMS Admin Tool\nu.mdms.tools.Data\Utility.cs:330
   nu.mdms.tools.Data.OracleClient.OracleGoalsPtLotNoMvProviderBase.GetByPtLotNo(TransactionManager transactionManager, Decimal _ptLotNo, Int32 start, Int32 pageLength, Int32& count) in C:\Users\Kevin\Desktop\MDMS Admin Tool\nu.mdms.tools.Data.OracleClient\OracleGoalsPtLotNoMvProviderBase.generated.cs:592
   nu.mdms.tools.Data.Bases.GoalsPtLotNoMvProviderBaseCore.GetByPtLotNo(TransactionManager transactionManager, Decimal _ptLotNo) in C:\Users\Kevin\Desktop\MDMS Admin Tool\nu.mdms.tools.Data\Bases\GoalsPtLotNoMvProviderBase.generatedCore.cs:311
   nu.mdms.tools.Data.Bases.GoalsMeterTestGasProviderBaseCore.DeepLoad(TransactionManager transactionManager, GoalsMeterTestGas entity, Boolean deep, DeepLoadType deepLoadType, Type[] childTypes, DeepSession innerList) in C:\Users\Kevin\Desktop\MDMS Admin Tool\nu.mdms.tools.Data\Bases\GoalsMeterTestGasProviderBase.generatedCore.cs:644
   nu.mdms.tools.Data.Bases.EntityProviderBaseCore`2.DeepLoad(TransactionManager mgr, Entity entity, Boolean deep, DeepLoadType deepLoadType, Type[] childTypes) in C:\Users\Kevin\Desktop\MDMS Admin Tool\nu.mdms.tools.Data\Bases\EntityProviderBaseCore.generated.cs:836
   nu.mdms.tools.Data.Bases.EntityProviderBaseCore`2.DeepLoad(TransactionManager mgr, TList`1 entities, Boolean deep, DeepLoadType deepLoadType, Type[] childTypes) in C:\Users\Kevin\Desktop\MDMS Admin Tool\nu.mdms.tools.Data\Bases\EntityProviderBaseCore.generated.cs:1003
   nu.mdms.tools.Web.Data.GoalsMeterTestGasDataSourceView.DeepLoad(TList`1 entityList, ProviderDataSourceDeepLoadList properties) in C:\Users\Kevin\Desktop\MDMS Admin Tool\nu.mdms.tools.Web\DataSourceControls\GoalsMeterTestGasDataSource.cs:278
   nu.mdms.tools.Web.Data.ProviderDataSourceView`2.ExecuteDeepLoad(TList`1 entityList) in C:\Users\Kevin\Desktop\MDMS Admin Tool\nu.mdms.tools.Web\Data\ProviderDataSource.cs:298
   nu.mdms.tools.Web.Data.ProviderDataSourceView`2.OnSelected(ObjectDataSourceStatusEventArgs e) in C:\Users\Kevin\Desktop\MDMS Admin Tool\nu.mdms.tools.Web\Data\ProviderDataSource.cs:450
   nu.mdms.tools.Web.Data.BaseDataSourceView`2.ExecuteSelectCore(DataSourceSelectArguments arguments) in C:\Users\Kevin\Desktop\MDMS Admin Tool\nu.mdms.tools.Web\Data\BaseDataSource.cs:1234
   nu.mdms.tools.Web.Data.BaseDataSourceView`2.ExecuteSelect(DataSourceSelectArguments arguments) in C:\Users\Kevin\Desktop\MDMS Admin Tool\nu.mdms.tools.Web\Data\BaseDataSource.cs:1189
   System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +21
   System.Web.UI.WebControls.DataBoundControl.PerformSelect() +143
   System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +74
   System.Web.UI.WebControls.GridView.DataBind() +4
   System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +66
   System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +75
   System.Web.UI.Control.EnsureChildControls() +102
   System.Web.UI.Control.PreRenderRecursiveInternal() +42
   System.Web.UI.Control.PreRenderRecursiveInternal() +175
   System.Web.UI.Control.PreRenderRecursiveInternal() +175
   System.Web.UI.Control.PreRenderRecursiveInternal() +175
   System.Web.UI.Control.PreRenderRecursiveInternal() +175
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2496

Original comment by Kevin6340@gmail.com on 13 Apr 2011 at 2:42

GoogleCodeExporter commented 9 years ago 
[InvalidCastException: Unable to cast object of type 'System.Decimal' to type 
'System.String'.]
   nu.mdms.tools.Data.Bases.SvcOrdTypeExcludesMasterProviderBaseCore.Fill(IDataReader reader, TList`1 rows, Int32 start, Int32 pageLength) in C:\Users\Kevin\Desktop\MDMS Admin Tool\nu.mdms.tools.Data\Bases\SvcOrdTypeExcludesMasterProviderBase.generatedCore.cs:206
   nu.mdms.tools.Data.OracleClient.OracleSvcOrdTypeExcludesMasterProviderBase.GetPaged(TransactionManager transactionManager, String whereClause, String orderBy, Int32 start, Int32 pageLength, Int32& count) in C:\Users\Kevin\Desktop\MDMS Admin Tool\nu.mdms.tools.Data.OracleClient\OracleSvcOrdTypeExcludesMasterProviderBase.generated.cs:468
   nu.mdms.tools.Web.Data.SvcOrdTypeExcludesMasterDataSourceView.GetSelectData(IDictionary values, Int32& count) in C:\Users\Kevin\Desktop\MDMS Admin Tool\nu.mdms.tools.Web\DataSourceControls\SvcOrdTypeExcludesMasterDataSource.cs:181
   nu.mdms.tools.Web.Data.BaseDataSourceView`2.GetCachedData(IDictionary values, Int32& count) in C:\Users\Kevin\Desktop\MDMS Admin Tool\nu.mdms.tools.Web\Data\BaseDataSource.cs:1334
   nu.mdms.tools.Web.Data.BaseDataSourceView`2.GetEntityList(DataSourceSelectArguments arguments, IDictionary values) in C:\Users\Kevin\Desktop\MDMS Admin Tool\nu.mdms.tools.Web\Data\BaseDataSource.cs:1301
   nu.mdms.tools.Web.Data.BaseDataSourceView`2.ExecuteSelectCore(DataSourceSelectArguments arguments) in C:\Users\Kevin\Desktop\MDMS Admin Tool\nu.mdms.tools.Web\Data\BaseDataSource.cs:1238
   nu.mdms.tools.Web.Data.BaseDataSourceView`2.ExecuteSelect(DataSourceSelectArguments arguments) in C:\Users\Kevin\Desktop\MDMS Admin Tool\nu.mdms.tools.Web\Data\BaseDataSource.cs:1189
   System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +21
   System.Web.UI.WebControls.DataBoundControl.PerformSelect() +143
   System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +74
   System.Web.UI.WebControls.GridView.DataBind() +4
   System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +66
   System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +75
   System.Web.UI.Control.EnsureChildControls() +102
   System.Web.UI.Control.PreRenderRecursiveInternal() +42
   System.Web.UI.Control.PreRenderRecursiveInternal() +175
   System.Web.UI.Control.PreRenderRecursiveInternal() +175
   System.Web.UI.Control.PreRenderRecursiveInternal() +175
   System.Web.UI.Control.PreRenderRecursiveInternal() +175
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2496

Original comment by Kevin6340@gmail.com on 13 Apr 2011 at 2:43

GoogleCodeExporter commented 9 years ago 
[deleted comment]
GoogleCodeExporter commented 9 years ago 
Blake,
Can you please move my issue to critical?  I don't see a way to do so myself.

Thanks,
Kevin6340

Original comment by Kevin6340@gmail.com on 13 Apr 2011 at 2:53

GoogleCodeExporter commented 9 years ago 
Hello,

What is the definition of the SvcOrdTypeExcludesMaster table?

Thanks
-Blake

Original comment by bniemyjski on 13 Apr 2011 at 4:17

GoogleCodeExporter commented 9 years ago 
[deleted comment]
GoogleCodeExporter commented 9 years ago 
Blake, do you want my direct email and phone?

-Kevin6340

Original comment by Kevin6340@gmail.com on 13 Apr 2011 at 6:12

GoogleCodeExporter commented 9 years ago 
Hello,

Could you please export this table schema using 
(http://wiki.oracle.com/page/Oracle+export+and+import+). I'm unable to import 
this table and constants without errors. Please feel free to email them to 
support@codesmithtools.com or attach them to this issue.

>[Error] Script lines: 5-12 -------------------------
 ORA-00911: invalid character
 Script line 12, statement line 8, column 5 

 [Executed: 4/13/2011 1:42:01 PM] [Execution: 0ms] 

Thanks
-Blake

Original comment by bniemyjski on 13 Apr 2011 at 6:45

GoogleCodeExporter commented 9 years ago 
[deleted comment]
GoogleCodeExporter commented 9 years ago 
Blake, my manager reminded my of the policy to never distribute data.  He says 
the DDL that I pasted above is the best policy will allow.  

-Kevin

Original comment by Kevin6340@gmail.com on 14 Apr 2011 at 1:02

GoogleCodeExporter commented 9 years ago 
Hello,

That will no longer be needed and I have deleted the above comments with the 
script. I understand policies and would never want you or anyone to get into 
trouble. We only need the bare minimum information to reproduce.

I was having problems running this script even in Aqua Data Studio. So I made 
some modifications and fixed a bug I was having with SQLPlus not allowing me to 
login and was able to run the previous scripts under SQL Plus and they worked 
(Not an Oracle GURU :S). Did you originally export with SQL Plus? I'm currently 
looking into this.

Thanks
-Blake Niemyjski

Original comment by bniemyjski on 14 Apr 2011 at 2:15

GoogleCodeExporter commented 9 years ago 
Hello,

So I tracked this down and I think you are getting this error because you 
didn't execute the type creation.

        create or replace package "REPLACEWITHCURRENTOWNER"."types"
        as 
            type cursorType is ref cursor; 
        end; 

I couldn't get the Update command to work with TimeStamp(6) datatypes. I think 
that this type is not supported by the generated stored procedures and I'm not 
really sure how to work around it as it would required a lot of work. Can you 
changes these to a DateTime type?

I've attached the changes I needed to make to the .netTiers templates in order 
to debug it down to this issue. After applying this 
TemplateLib\CommonSqlCode.cs file, you will need to regenerate to a new folder 
and rerun the generated scripts as the procedure names have changed.

Please let me know if you have any questions.

Thanks
-Blake

Original comment by bniemyjski on 14 Apr 2011 at 8:15

Attachments: