BorlandTalk.com Borland discussion newsgroups   Archives   FAQ   Search   Memberlist   Usergroups   Register   Profile   Log in to check your private messages   Log in  Weird Numeric field problem          BorlandTalk.com Forum Index -> Delphi Databases (SQL Servers) View previous topic :: View next topic   Author Message Walter Prins Guest Posted: Thu Oct 07, 2004 10:06 am    Post subject: Weird Numeric field problem Hi, Using D6 EE (updated), Windows XP, Most recent ADO etc, against an AS/400 via IBM iSeries Access Express (though most of that is likely not that relevant I don't think.) What I'm doing: - Connecting to the AS/400 using the IBM Client Access OLEDB driver with Delphi ADO components (connection object, ADO Dataset) (connection string something like: Provider=IBMDA400.DataSource.1;Persist Security Info=True;Data Source=S44H5004;Protection Level=None;Initial Catalog="";Transport Product=Client Access;SSL=DEFAULT;Force Translate=65535;Default Collection="";Convert Date Time To Char=TRUE;Cursor Sensitivity=3) - Im querying/updating a table/file with a Numeric(16, field (ie fixed point decimal with precision without using persistent field defs on anything like that - This particular field is realised as TBCDField, which you can see if you debug it. What's happening - I try to update this field with a value with 8 significant digits, but the value gets truncated to 4 significant digits. What I've traced it to: - In c:program filesborlanddelphi6sourcevclDB.pas, line 5571, the value I set it to gets passed to the "SetAsFloat" method on the TBCDField class. - Here for some reason, this gets delegated back to "SetAsCurrency(Value)", where SetAsCurrency takes a Currency type parameter. - The Currency type only has 4 significant digits! After the value is passed into this method it appears truncated/rounded! - This seems like a blatant bug - you're losing precision by simply passing the float value into a currency parameter. Even slightly more strangely, I would've expected that to raise some sort of error/exception/problem, but no error occurs. Is this a problem in the VCL or what the flaming hell am I missing? Note: As a test, I've swapped out the ADO circuitry for BDE+ODBC connector+IBM iSeries ODBC driver circuitry... that actually behaves correctly ie it doesnt lose precision (Not checked exactly what it does then - I would have to assume it probably doesn't use TBCDField class at all then.) Obviously I'd really rather not use the BDE etc etc etc for very obvious reasons... Any sanity protecting help/advice would be appreciated Walter Prins Back to top Walter Prins Guest Posted: Thu Oct 07, 2004 10:21 am    Post subject: Re: Weird Numeric field problem Hmm. I see from the help for TBCDField that this behaviour is (more or less) by design, and that I should use TFmtBCDField if I need more precision (which I do.) It also however states in the help that "If the underlying database table contains a value that requires greater precision, TBCDField raises an exception." Now this did not happen with me for some reason (?) Walter Back to top Walter Prins Guest Posted: Thu Oct 07, 2004 10:25 am    Post subject: Re: Weird Numeric field problem Right, So the next question is: How do I get this thing to use TFMTBCDField instead of TBCDField?? I don't use/want to use persistent field defs (this code runs on multiple tables etc.) Walter Back to top Walter Prins Guest Posted: Thu Oct 07, 2004 10:34 am    Post subject: Re: Weird Numeric field problem "Walter Prins" <wprins (AT) no (DOT) sp@m.ananzi.co.za> wrote: Quote: As a test, I've swapped out the ADO circuitry for BDE+ODBC connector+IBM iSeries ODBC driver circuitry... that actually behaves correctly ie it doesnt lose precision (Not checked exactly what it does then - I would have to assume it probably doesn't use TBCDField class at all then.) Obviously I'd really rather not use the BDE etc etc etc for very obvious reasons... I've just checked, this works because the field object instantiated for the field via this method is actually a TFloatField (as opposed to a TBCDField). So then, how to affect this is the question... Any suggestions? Walter Back to top Walter Prins Guest Posted: Thu Oct 07, 2004 11:21 am    Post subject: Solved: Re: Weird Numeric field problem Right, I found what I was missing, basically, you need to set the TADODataSet.EnableBCD to False and the field gets mapped to ftFloat... It is set to True by default. <sheepish grin> Cheers </sheepish grin> Walter Back to top John Herbster Guest Posted: Thu Oct 07, 2004 3:13 pm    Post subject: Re: Solved: Re: Weird Numeric field problem "Walter Prins" <wprins (AT) no (DOT) sp@m.ananzi.co.za> wrote Quote: I found what I was missing, basically, you need to set the TADODataSet.EnableBCD to False and the field gets mapped to ftFloat... It is set to True by default. Walter, Please note that because of the precision of the ftFloat field (which is the same as double), all DECIMAL(16, values will not pass through a type double unchanged. You can prove this yourself with the following code. Regards, JohnH procedure TForm1.Button2Click(Sender: TObject); var Denom, Num0, Num1: int64; Dbl0, Dbl1: double; begin Denom := 100000000; Num0 := 9999999904208159; Num1 := Num0 + 1; Dbl0 := Num0/Denom; Dbl1 := Num1/Denom; If Dbl0 = Dbl1 then raise exception.CreateFmt('error'); end; Back to top John Herbster Guest Posted: Thu Oct 07, 2004 3:18 pm    Post subject: Re: Weird Numeric field problem "Walter Prins" <wprins (AT) no (DOT) sp@m.ananzi.co.za> wrote Quote: So the next question is: How do I get this thing to use TFMTBCDField instead of TBCDField? The FMTBCD code has errors, so be careful about what part of the FMTBCD code your program will use. See QC report #6013. -- Regards, JohnH Quality Central, bug status & reporting http://qc.borland.com/ Back to top Walter Prins Guest Posted: Thu Oct 07, 2004 4:13 pm    Post subject: Re: Solved: Re: Weird Numeric field problem Hi John, Thanks for the heads up. I'm aware that ftFloat is not an always an exact science, and ideally it'd be better to avoid using float if possible. Have you got a suggestion how to get ADO to use TFMTBCDField instead of TBCDField when dynamically instantiating field objects for a query? I've had a brief attempt at trying to see if I can modify the TFieldDefs prior to Fields themselves being created, but it's so far appeared that there's no clean way to do that (I could go hacking the VCL but I'd really rather not go that way...) As for your example, like I say I can believe that you'd possibly have problems with using double/float intermediaries, but I must confess I don't follow your example: decimal(16, has 16 digits of accuracy, 8 of which is after the decimal point. Are you saying that a floating point number will not accurately store Thanks again Walter "John Herbster" <herb-sci1_AT_sbcglobal.net> wrote: Quote: "Walter Prins" <wprins (AT) no (DOT) sp@m.ananzi.co.za> wrote I found what I was missing, basically, you need to set the TADODataSet.EnableBCD to False and the field gets mapped to ftFloat... It is set to True by default. Walter, Please note that because of the precision of the ftFloat field (which is the same as double), all DECIMAL(16, values will not pass through a type double unchanged. You can prove this yourself with the following code. Regards, JohnH procedure TForm1.Button2Click(Sender: TObject); var Denom, Num0, Num1: int64; Dbl0, Dbl1: double; begin Denom := 100000000; Num0 := 9999999904208159; Num1 := Num0 + 1; Dbl0 := Num0/Denom; Dbl1 := Num1/Denom; If Dbl0 = Dbl1 then raise exception.CreateFmt('error'); end; Back to top John Herbster Guest Posted: Thu Oct 07, 2004 4:33 pm    Post subject: Re: Solved: Re: Weird Numeric field problem "Walter Prins" <wprins (AT) no (DOT) sp@m.ananzi.co.za> wrote Quote: Thanks for the heads up. I'm aware that ftFloat is not an always an exact science, and ideally it'd be better to avoid using float if possible. Have you got a suggestion how to get ADO to use TFMTBCDField instead of TBCDField when dynamically instantiating field objects for a query? ... Walter, That is a question for maybe experts like Bill Todd. Quote: ... I must confess I don't follow your example: decimal(16, has 16 digits of accuracy, 8 of which is after the decimal point. Are you saying that a floating point number will not accurately store [it]? Yes; that is what I am saying. From my quick testing, it appears that if you restrict the data range to +/-49999999.99999999, then the type double can handle it. You might call that about 15-1/2 decimal digits or DECIMAL(15.5, <g>. I hope that the example below is easier to follow. It requires a form with Whole_e: TEdit; {Set text to 99999999} Denom_e: TEdit; {Set text to 100000000} Button1: TButton; Memo1: TMemo; Try it with above text first and see failures, then try it with Whole_e.Text = 49999999, and see no errors. Regards, JohnH procedure TForm1.Button1Click(Sender: TObject); var i, denom, ne: integer; whole, numer, numer2: int64; dbl: double; begin Set8087CW($1332); Screen.Cursor := crHourGlass; Try ne := 0; whole := StrToInt(Whole_e.Text); {='99999999'} denom := StrToInt(Denom_e.Text); {='100000000'} Memo1.Lines.Add(Format ('Testing %8.8d.%8.8d to %8.8d.%8.8d', [whole,0,whole,(denom-1)])); For i := 0 to denom - 1 do begin numer:= whole*denom + i; dbl := numer/denom; numer2 := round(dbl*denom); if (numer2 <> numer) then begin inc(ne); Memo1.Lines.Add(Format ('%8.8d.%8.8d',[whole,i])); If ne > 15 then EXIT; end; end; Finally Screen.Cursor := crDefault; End; end; Back to top Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First         BorlandTalk.com Forum Index -> Delphi Databases (SQL Servers) All times are GMT Page 1 of 1   Jump to: Select a forum C++ Builder Newsgroups----------------C++ BuilderC++ Builder (ActiveX)C++ Builder (Announcements)C++ Builder (Command Line Tools)C++ Builder DatabasesC++ Builder Databases (ADO)C++ Builder Databases (Desktop)C++ Builder Databases (InterBase Express)C++ Builder Databases (Multi-Tier)C++ Builder Databases (SQL Servers)C++ Builder (Graphics)C++ Builder (IDE)C++ Builder (Internet)C++ Builder (Internet Socket)C++ Builder (Internet Web)C++ Builder (Jobs)C++ Builder (Language)C++ Builder (Language C++)C++ Builder (Compatibility with MFC)C++ Builder (Native API)C++ Builder (Non-Technical)C++ Builder (Students)C++ Builder (Third Party Tools)C++ Builder (Upgrade)C++ Builder (VCL)C++ Builder (VCL Components Usage)C++ Builder (VCL Components Development)C++ Builder (VCL Usage)C++ Builder (WinAPI)C++ Builder Installation Delphi Newsgroups----------------Delphi (General)Delphi ActiveX Controls UsageDelphi ActiveX Controls DevelopmentDelphi As400Delphi DatabasesDelphi Databases (ADO)Delphi Databases (dbExpress)Delphi Databases (Desktop)Delphi Databases (InterBase Express)Delphi Databases (Multi-Tier)Delphi Databases (SQL Servers)Delphi DeploymentDelphi GraphicsDelphi IDEDelphi InternationalizationDelphi InternetDelphi Internet Isapi-WebBrokerDelphi Internet WebSnapDelphi Internet WinsockDelphi JobsDelphi Language BASMDelphi Language ObjectPascalDelphi Model Driven Architecture (General)Delphi Model Driven Architecture (Third Party)Delphi MultimediaDelphi Native APIDelphi Non-TechnicalDelphi ObjectPascalDelphi OLE AutomationDelphi OO designDelphi OpenToolsAPIDelphi Reporting-ChartingDelphi RTLDelphi StudentsDelphi TeamSourceDelphi Thirdparty ToolsDelphi Thirdparty Tools (General)Delphi Thirdparty Tools (IntraWeb)Delphi Thirdparty Tools (RaveReports)Delphi UpgradeDelphi VCLDelphi VCL ComponentsDelphi VCL Components UsingDelphi VCL Components WritingDelphi WebServices SOAPDelphi WinAPIDelphi XMLDelphi Installationcomp.lang.pascal.delphi.components.misccomp.lang.pascal.delphi.components.usagecomp.lang.pascal.delphi.components.writingcomp.lang.pascal.delphi.databasescomp.lang.pascal.delphi.misccomp.sources.delphi JBuilder Newsgroups----------------JBuilderJBuilder AnnouncementsJBuilder Applet IssuesJBuilder As400JBuilder CompilerJBuilder CORBA/RMIJBuilder DatabasesJBuilder DB SwingJBuilder DebuggerJBuilder DeploymentJBuilder DocumentationJBuilder EnterpriseJBuilder HandheldJBuilder IDEJBuilder JavaJBuilder JavaAPIJBuilder Java LanguageJBuilder Java SwingJBuilder Java BeansJBuilder Java Beans UsageJBuilder Java Beans DevelopmentJBuilder JBCLJBuilder JDataStoreJBuilder JobsJBuilder LinuxJBuilder MacintoshJBuilder & MobileSetJBuilder MultiLingual AppsJBuilder Non-TechnicalJBuilder OpenToolsJBuilder Servlets (JSP)JBuilder StudentsJBuilder Team DevelopmentJBuilder Thirdparty ToolsJBuilder UpgradeJBuilder Installation InterBase Newsgroups----------------InterBaseInterBase Bug DiscussionInterBase GeneralInterBase IBConsoleInterBase OpenSourceInterBase ReplicationInterBase SQLInterBase ThirdpartyInterBase Installation Miscellaneous Borland Newsgroups----------------AppCenterAppServerEnteraVisiBroker  You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum Powered by phpBB © 2001, 2006 phpBB Group SEO toolkit © 2004-2006 webmedic.