Error while loading entity ORM

[NicholasDee]

entityLoad('Account') throws this error,

Error while loading entity

java.sql.SQLException: [Macromedia][Oracle JDBC Driver]Numeric overflow.

However  entityLoad('Account',1) returns the proper record.

I am using oracle 10g as a datebase.

Is this a bug with oracle? entityLoad('Account') should return all my accounts.

[Adam Cameron.]

How many records are we talking, and what's the definition of the entity?

--

Adam

[NicholasDee]

Just over 1000

Now heres a kicker,  entityLoad('Account',{},'account_number asc',{offset=21, maxResults=10}) returns the proper 10 records

but it still does not like it when i do

entityLoad('Account')

[Adam Cameron.]

What if you use the offset & maxrows settings to return the whole lot? Does that work?

If not, it might be bung data: try paging through all the records to see if one page in particular gives you a problem, then you can maybe narrow it down to a specific record.

Can you profile the SQL that Hibernate is passing to the DB? Anything unexpected about it?

--

Adam

[brian428]

I'd also go further and take the SQL being generated and try running it against Oracle directly (in your SQL tool, outside of CF) to see if anything changes.

If you're not sure how to enable logging of the generated SQL, have a look here.

[mark_mandel]

It would probably also help to see the config for Account, so we can have a better idea if you're doing something funky with a formula or something.

[NicholasDee]

Sorry for late reply , been away for a few days,

I just added ormType=interger to my cfproprty that was the primary key and now all is good. I guess I should not assume an example that works fine for a diff database will work exactly the same on my database. I will make sure i define ormtype for every property.

[joseph.yan]

For some reason I am having the same error event though I checked the ormType="integer". My database is Oracle 19c and the primary key data type is "number". Thanks. 

[Dave Watts]

A number data type in Oracle is a float, not an integer. It's not the sort of thing you'd use as a surrogate key, but rather perhaps as a natural key. Anyway, you should be able to choose ormType="float" instead.

Dave Watts, Eidolon LLC

[joseph.yan]

Yeah it's giving me same error. I removed it all only very basic like this:

<cfproperty name="id" column="LICENSE_ID" fieldtype="id" generator="sequence" sqltype="number" params="{sequence='LICENSES_SEQ'}"/>

and it's giving me an error.

The error is happening when I made the entityload() call with/without filter. But when I made a call without filter and added the options such as {maxResults=10} and it's coming back okay.

BTW, this is happening on CF 2021. The CF 2016 has no problem. I haven't tried on CF 2018. Thx.

[joseph.yan]

I found the solution by setting the ormtype as "big_decimal"

<cfproperty name="id" column="LICENSE_ID" fieldtype="id" ormtype="big_decimal" generator="sequence" params="{sequence='LICENSES_SEQ'}"/>

Thanks.

[BKBK]

@joseph.yan , please start a new thread of your own. NicholasDee's original thread is more than 10 years old. That is a long time in software. ColdFusion, Hibernate and Oracle have moved on since then. 

In any case, thanks for sharing your findings.

[joseph.yan]

@BKBK oops sorry I thought it's easier to follow up here. I make it a note next time to start a new thread. Thank you.