Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > Database drivers> ODP.NET arithmetic overflow
 

Pages: 1
Database drivers
ODP.NET arithmetic overflow
Page:1/1 

  Print all messages in this thread  
Poster Message
Puromtec
User



Location:
MD
Joined on:
22-Jun-2005 16:38:52
Posted:
48 posts
# Posted on: 26-Jun-2006 21:57:36.  
I have consulted all posts on this forum concerning the ODP.net arithmentic overflow problem.

including...this.
http://llblgen.com/TinyForum/Messages.aspx?ThreadID=4543&HighLight=1


I am getting this error and I am using the latest version 1.0.2005.1, and ODP.net for oracle 10g.

I've observed my sql trace and it is indeed specifying a truncation of 28 in the sql call, however I still get the arithmetic overflow error. Shouldn't the trunction take care of the problem?

Also, there is this: http://www.oracle.com/technology/sample_code/tech/windows/odpnet/SafeTypeMapping/readme.html
from oracle. Not too sure what to make of it from the vantage point of using LLBL Gen Pro.

The actual LLBL code is a dynamic list with aggregate field using avg. The call works for Count, but gives up the error on avg.

Thanks
  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
34800 posts
# Posted on: 26-Jun-2006 23:24:42.  
Puromtec wrote:
I have consulted all posts on this forum concerning the ODP.net arithmentic overflow problem.

including...this.
http://llblgen.com/TinyForum/Messages.aspx?ThreadID=4543&HighLight=1


I am getting this error and I am using the latest version 1.0.2005.1, and ODP.net for oracle 10g.

I've observed my sql trace and it is indeed specifying a truncation of 28 in the sql call, however I still get the arithmetic overflow error. Shouldn't the trunction take care of the problem?

Unfortunately, not always. There are situations where this problem still occurs. Oracle refuses to make truncate the value, they say it's better to throw an exception.

Quote:

Also, there is this: http://www.oracle.com/technology/sample_code/tech/windows/odpnet/SafeTypeMapping/readme.html
from oracle. Not too sure what to make of it from the vantage point of using LLBL Gen Pro.

The actual LLBL code is a dynamic list with aggregate field using avg. The call works for Count, but gives up the error on avg.

This is because avg results in a NUMBER which overflows System.Decimal, and this gives the error. This exception occurs inside ODP.NET's datareader code, and this thus means that fetching a datatable with ODP.NET using oracledataadapter (which is used in a dyn. list fetch) gives this exception as well.

It's very unfortunate, and hard to avoid. In v2 I've added code to the generic fetch logic which allows you to override a method when such an exception occurs during entity fetches, however for datatable fetches using dataadapters, it's out of my hands.

What you could try is to implement IExpression to call a truncate function on hte field which you avg. See for an example how to implement IExpression to call a DB function:

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3829

In v2 this is build in so it's simple in v2, but in v1, you've to implement it yourself. You then should get something as
SELECT TRUNCATE(AVG(field),...)


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Puromtec
User



Location:
MD
Joined on:
22-Jun-2005 16:38:52
Posted:
48 posts
# Posted on: 28-Jun-2006 16:29:34.  
Cannot seem to apply my own expression around the field. Database trace still says
"Trunc(Avg(field),28)"
and I still get the arithmetic overflow error.

My goal in this test is to contain the field aggregate/trunc with a Floor function

e.g. "Floor(Trunc(Avg(field),28)"

Below is the calling code that I am using. The IExpression implementation is the same as yours. Any idea why my Floor isn't getting appended to the sql statement?

//create a filter predicate added to f (the predicate expression)

f.Add(PredicateFactory.CompareValue(Hello_C95FieldIndex.Pmp,
ComparisonOperator.GreaterEqual, val));

IPredicateExpressionElement p = (IPredicateExpressionElement)f[0];

FieldCompareValuePredicate fp = (FieldCompareValuePredicate)p.Contents;



IExpression trun = new F18DAL.FunctionExpression(EntityFieldFactory.Create(Hello_C95FieldIndex.Pmp),
"Floor");

fp.FieldCore.ExpressionToApply = trun;

//using the parentheses and the logic operators, organize the p.ex's



ResultsetFields fields = new ResultsetFields(3);
fields.DefineField(Hello_C95FieldIndex.Pmp, 0, "PmpAvg", "Hello_C95", AggregateFunction.Avg);
fields.DefineField(FlightsynFieldIndex.Name, 1, "Name");
fields.DefineField(FlightsynFieldIndex.Segmentstart, 2, "Segmentstart");


IRelationCollection relations = new RelationCollection();
relations.Add(FlightsynEntity.Relations.RecsynEntityUsingFlightid);
relations.Add(RecsynEntity.Relations.Hello_C95EntityUsingRecordid);


IGroupByCollection groupByClause = new GroupByCollection();
groupByClause.Add(fields[1]);
groupByClause.Add(fields[2]);



DataTable dynamicList = new DataTable();

TypedListDAO dao = new TypedListDAO();


dao.GetMultiAsDataTable(fields, dynamicList, 0, null, f, relations, true, groupByClause, null, 0, 0);



---Thanks
  Top
Puromtec
User



Location:
MD
Joined on:
22-Jun-2005 16:38:52
Posted:
48 posts
# Posted on: 28-Jun-2006 17:15:11.  
Ok, i've got the answer.


1. I cannot use the:
fields.DefineField(Hello_C95FieldIndex.Pmp, 0, "PmpAvg", "Hello_C95", AggregateFunction.Avg); to set the Avg. I must define the field like so:
fields.DefineField(Hello_C95FieldIndex.Pmp, 0, "PmpAvg");

2. Alter the IExpression implementation to handle two function expressions. Floor(Avg(x))

3. Add this line of code to set the expression on the field, as opposed to the filter

fields[0].SetExpression(trun) //setting up fields for dynamic list


If i left the aggregate function in the define field statement and set custom expression i will have a problem described below:

SQL side:
TRUNC(AVG(FLOOR("MF"."HELLO_C95"."PMP")) -- which does nothing new



  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
34800 posts
# Posted on: 28-Jun-2006 17:31:25.  
In fact you use 2 expressions:
EXPRESSION_A(EXPRESSION_B)

where EXPRESSION_A is your function emitting expression and expression B is a field with an aggregate function set, AVG.
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.