BorlandTalk.com Borland discussion newsgroups   Archives   FAQ   Search   Memberlist   Usergroups   Register   Profile   Log in to check your private messages   Log in  Round problem with SQL Server          BorlandTalk.com Forum Index -> Delphi Databases (SQL Servers) View previous topic :: View next topic   Author Message Leonardo Hees Drummond Guest Posted: Thu Sep 18, 2003 8:50 pm    Post subject: Round problem with SQL Server I have an app developed in delphi with sql-server 7. When the user put the value 261508,77 in a dbedit and post, the value changes to 261508,76. I was test the same app with oracle 9 and interbase 6 and the problem does not occur. Why ? Back to top John Herbster (TeamB) Guest Posted: Fri Sep 19, 2003 12:26 am    Post subject: Re: Round problem with SQL Server "Leonardo Hees Drummond" <leonardo (AT) tectrilha (DOT) com.br> wrote Quote: ... delphi with sql-server 7. When the user puts the value 261508,77 in a dbedit and post, the value changes to 261508,76. I tested the same app with oracle 9 and Interbase 6 and the problem does not occur. Why? Leonardo, I suggest that drop the following assert statement into your code somewhere before the lines giving the problem Assert( ((Get8087CW and $1F3F) = $1332), Format('Prob with FPUCW($%4x)',[Get8087CW])); Make sure that Asserts are turned *on* in the compiler options. It will give you an exception, if the FPU control word is set wrong. If it does, please report the message back here. Regards, JohnH Back to top Leonardo Hees Drummond Guest Posted: Fri Sep 19, 2003 6:34 pm    Post subject: Re: Round problem with SQL Server The datatype of the field is Numeric(15,2) "Brian Bushay TeamB" <BBushay (AT) Nmpls (DOT) com> wrote Quote: I have an app developed in delphi with sql-server 7. When the user put the value 261508,77 in a dbedit and post, the value changes to 261508,76. I was test the same app with oracle 9 and interbase 6 and the problem does not occur. Why ? What is the datatype of the field? -- Brian Bushay (TeamB) [email]Bbushay (AT) NMPLS (DOT) com[/email] Back to top Leonardo Hees Drummond Guest Posted: Fri Sep 19, 2003 6:36 pm    Post subject: Re: Round problem with SQL Server What is this means ? It may be generate eny problem to the app with oracle or interbase (which runs without problems) ? "John Herbster (TeamB)" <herb-sci1_at_sbcglobal.net> wrote Quote: "Leonardo Hees Drummond" <leonardo (AT) tectrilha (DOT) com.br> wrote ... delphi with sql-server 7. When the user puts the value 261508,77 in a dbedit and post, the value changes to 261508,76. I tested the same app with oracle 9 and Interbase 6 and the problem does not occur. Why? Leonardo, I suggest that drop the following assert statement into your code somewhere before the lines giving the problem Assert( ((Get8087CW and $1F3F) = $1332), Format('Prob with FPUCW($%4x)',[Get8087CW])); Make sure that Asserts are turned *on* in the compiler options. It will give you an exception, if the FPU control word is set wrong. If it does, please report the message back here. Regards, JohnH Back to top John Herbster (TeamB) Guest Posted: Fri Sep 19, 2003 8:13 pm    Post subject: Re: Round problem with SQL Server "Leonardo Hees Drummond" <leonardo (AT) tectrilha (DOT) com.br> wrote Quote: What is this means? Assert( ((Get8087CW and $1F3F) = $1332), Format('Prob with FPUCW($%4x)',[Get8087CW])); Make sure that Asserts are turned *on* in the compiler options. It will give you an exception, if the FPU control word is set wrong. If it does, please report the message back here. Regards, JohnH What it will do is to generate an exception if your application is running with the internal FPU precision reduced to double or single precision. I suspect that this may be the source of your problem with 261508,77 getting changed to 261508,76. If the precision is indeed being changed, then we need to look for source of the change. Regards, JohnH Back to top a Guest Posted: Sun Sep 21, 2003 5:05 pm    Post subject: Re: Round problem with SQL Server Leonardo, Quote: The datatype of the field is Numeric(15,2) We ran into this with MS SQL 7 years ago. Always include a couple of extra decimal points in MS SQL. Numeric(15,2) means the third decimal point didgit could be anything. In MS SQL, anything beyond the precision point isn't considered valid. Sending "5.02" could store "5.02xxxxxxxxxx". When read back, Delphi gets an float and the control rounds it rather than fix'ing it. You can't assume the random numbers at the end are "0000000". In rare cases they come back greater than "5xxxxx" and the rounding takes the number to "5.03". If you make it Numeric(15,4), then "5.02" gets stored as "5.0200xxxxxxx". It then doesn't mater what "xxxxx" is. It gets rounded to "5.02". Otherwise, don't expect the same numbers back on anything where the percision is the same as you are displaying. Thanks, Brett Back to top John Herbster (TeamB) Guest Posted: Sun Sep 21, 2003 5:39 pm    Post subject: Re: Round problem with SQL Server "a" <blwatters (AT) shaw (DOT) ca> wrote Quote: We ran into this with MS SQL 7 years ago. Always include a couple of extra decimal points in MS SQL. Numeric(15,2) means the third decimal point digit could be anything. Brett, I do not think that that is true. What is true, however, is that floating binary point variables like single, double, and extended cannot precisely represent numbers "5.02". Instead, AsFloat (being double ) will actually return +5.01999 99999 99999 57367 43585 43939 88847 73254 39453 125, which if stored as a single will become +5.01999 99809 26513 67187 5. You can use my ExactFloatToStr function from Code Central http://codecentral.borland.com/codecentral/ccweb.exe/listing?id=19421 to see the exact values. If you are concerned about the exact values, you should be using AsCurrency, and/or learning about how the numbers are handled, which is admittedly not a simple subject. Rgds, JohnH Back to top a Guest Posted: Mon Sep 22, 2003 4:33 am    Post subject: Re: Round problem with SQL Server John, Quote: Brett, I do not think that that is true. What is true, however, is that floating binary point variables like single, double, and extended cannot precisely represent numbers "5.02". Instead, AsFloat (being double ) will actually return +5.01999 99999 99999 57367 43585 43939 88847 73254 39453 125, which if stored as a single will become +5.01999 99809 26513 67187 5. When we ran into the problem with MS SQL, we did extensive testing. The number coming back from MS SQL would be 5.02xxxxx. Depending upon the value the xxxx was pretty random. (5.02 was just an example). We solved it using Numeric(15,4) and the numbers came back as 5.0200xxxx. We certainly we're sure why MS SQL did this - whether this was just a side-effect of the way it stored numbers. However, going with the extra digits of precision did solve the problem without having to modify the Delphi components/code in anyway. Thanks, Brett 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.