MSStoredProc and "Value of field is out of range" error

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
dshutchings
Posts: 5
Joined: Fri 04 Dec 2009 15:00

MSStoredProc and "Value of field is out of range" error

Post by dshutchings » Fri 04 Dec 2009 15:15

We use the sdac controls in Delphi6, in conjunction with a remote datamodule.

We were previously using the standard tadostoredprocs without any problems.

We have a column type [DecQuantity] in sql server of type Decimal(19,6).

If I create the following
CREATE TABLE [internal_only].[dshdectest](
[id] [int] NOT NULL,
[decfield] [dbo].[DecQuantity] NOT NULL)

and create a stored procedure

create procedure [internal_only].[Dectest2]
as
Select Coalesce(internal_only.dshdectest.decfield,0)
from internal_only.dshdectest

When I wire up on a remote datamodel using an msconnect, msstoredproc and a datasetprovider and on the client a simple clientdataset i get the error above.

We have enabled EnableBCD on the msstoredproc, as existing code has BCD fields, and we don't want to change behaviour in any way as this is a huge peice of financial software.

Please could you help shed some light, to help us.

Dimon
Devart Team
Posts: 2910
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Mon 07 Dec 2009 08:08

We have fixed this problem in the latest SDAC build. Please, upgrade to this build to solve the problem.

dshutchings
Posts: 5
Joined: Fri 04 Dec 2009 15:00

Post by dshutchings » Mon 07 Dec 2009 09:31

fantastic.

(after I have run round the garden cheering) I will download the latest demo and run a few tests and get the an upgrade.

Cheers

dshutchings
Posts: 5
Joined: Fri 04 Dec 2009 15:00

Post by dshutchings » Mon 07 Dec 2009 11:25

Hi,

I have just downloaded the latest sdac trial, and I still get the problem.

I have looked at the msconnection.options.numerictype property, setting this in conjuction with enablebcd (on and off) seems to have no effect.

Using enableBCD = true on the msstoredproc still causes the out of range error on the client.

dshutchings
Posts: 5
Joined: Fri 04 Dec 2009 15:00

Post by dshutchings » Mon 07 Dec 2009 12:54

Hi,

Trying to determine exactly what is going on. I am thinking that maybe this is not an actual bug.

When we used the standard ado components, one of our developers started using decimal(19,6) to hold currency values. These started to come accross to delphi (via a clientdataset) as bcd values.

Looking at the TBCD in delphi help it seems that a bcd holds 20 significant digits with upto 4 decimal places. So I guess the out of range error is kind of correct.

but..

With the SDAC controls, shouldn't it automatically be using the FMTBCD for these columns?

I have found that setting "MSStoredProc.EnableBCD = FALSE" and "MSConnection.Options.NumericType=FMTBCD" I get FMTBCD columns on my client dataset and the UI no longer crashes with the out of range error , as the correct field type is used.

If I enable BCD on the stored procedure components should it be automatically creating BCD and FMTBCD columns based on the precision and size of the column in the data set? It doesn't seem to be.

I am about to run some more tests with my current settings "MSStoredProc.EnableBCD = FALSE" and "MSConnection.Options.NumericType=FMTBCD" and test what floats and date fields come accross as - as this seems to be what I need to do - to get our application built and working correctly.

Could you tell me what I should be doing to handle fields like this - and if what I have done currently with have an adverse effects or cause problems with other field types?

Many thanks

Dave

Dimon
Devart Team
Posts: 2910
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Tue 08 Dec 2009 08:27

I can't understand why you can't use the way with TMSStoredProc.EnableBCD = False and TMSConnection.Options.NumericType = ntFmtBCD.
You can find more detailed information about these properties in the SDAC help.

dshutchings
Posts: 5
Joined: Fri 04 Dec 2009 15:00

Post by dshutchings » Tue 08 Dec 2009 09:21

Sorry, you misunderstand me.

The underlying question was "is that the correct way of proceeding?" I didn't want to make the changes and then find out it was a partial fix and then have to change it again - if there was a bug in the enablebcd and bcd / fmtbcd field handling.

Each time I change something at this level, i trigger a whole load of regression testing and make myself very unpopular.

:)

Dave

Dimon
Devart Team
Posts: 2910
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Tue 08 Dec 2009 14:44

In SDAC help said:
If EnableBCD is True, decimal and numeric fields are mapped to the TBCDField class when field objects are created. If EnableBCD is False, the fields are mapped to the TFloatField class.
Therefore, if you need to use FmtBCD fields, the correct way is to set TMSStoredProc.EnableBCD to False and TMSConnection.Options.NumericType to ntFmtBCD.

Post Reply