BorlandTalk.com Borland discussion newsgroups   Archives   FAQ   Search   Memberlist   Usergroups   Register   Profile   Log in to check your private messages   Log in  Saving TFloatField to numeric(13,0)...          BorlandTalk.com Forum Index -> Delphi Databases (SQL Servers) View previous topic :: View next topic   Author Message Ottar Holstad Guest Posted: Mon Mar 13, 2006 8:03 pm    Post subject: Saving TFloatField to numeric(13,0)... Hi, I have a problem occuring on MSSQL 2000 and 2005, but not on Sybase ASA 6.0.4. The connections to all of the DB-engines are done using BDE driver for ODBC. The database has a huge number of fields, a huge enough number to make it infeasable to modfy them, declared as numeric(13,0). Those fields are accessed using TQuery components, but fields are not made persistent (not taken into the Fields Editor ot the TQuery). If I do persist the fields, just for testing, they are "imported" as TFloatField, and I assume they are also TFloatField at runtime when not persisted. Now, since the fields are TFloatField, it's possible to assign to them decimal-values, and it they are connected to a DB-aware edit then the decimal os duely showed. However, when the TQuery is posted back to the database, a correct rounding takes place on sybase, but not on MSSQL. The value 79999.8 is saved as 80000 on Sybase, wheras on MSSQL the value is just cut hard at the decimal separator, alas the value is saved as 79999 in the database. Making sure all of those TFloatFields are never assigned decimal-values is going to be a though job, so I'm looking for anoter solution... Helsing Ottis Back to top John Herbster Guest Posted: Mon Mar 13, 2006 9:03 pm    Post subject: Re: Saving TFloatField to numeric(13,0)... "Ottar Holstad" <no (AT) no (DOT) no> wrote Quote: Now, since the fields are TFloatField, it's possible to assign to them decimal-values, and it they are connected to a DB-aware edit then the decimal os duely showed. However, when the TQuery is posted back to the database, a correct rounding takes place on sybase, but not on MSSQL. The value 79999.8 is saved as 80000 on Sybase, wheras on MSSQL the value is just cut hard at the decimal separator, alas the value is saved as 79999 in the database. Helsing, Because the fields are not supposed to have any decimal fractions, Why don't you use Int64 variables when doing the assignments? Or is there something that I do not understand? --JohnH Back to top Wayne Niddery [TeamB] Guest Posted: Tue Mar 14, 2006 12:03 am    Post subject: Re: Saving TFloatField to numeric(13,0)... John Herbster wrote: Quote: Because the fields are not supposed to have any decimal fractions, Why don't you use Int64 variables when doing the assignments? Or is there something that I do not understand? He's using data-aware controls tied directly to the fields. -- Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com) RADBooks: http://www.logicfundamentals.com/RADBooks.html "It used to be that other people's achievements were considered an inspiration, not a grievance." Back to top Wayne Niddery [TeamB] Guest Posted: Tue Mar 14, 2006 1:03 am    Post subject: Re: Saving TFloatField to numeric(13,0)... Ottar Holstad wrote: Quote: However, when the TQuery is posted back to the database, a correct rounding takes place on sybase, but not on MSSQL. The value 79999.8 is saved as 80000 on Sybase, wheras on MSSQL the value is just cut hard at the decimal separator, alas the value is saved as 79999 in the database. This is not the Field objects as they know nothing about the underlying database. This behaviour is either at the database itself, or in the drivers for that database (the BDE SQL links DLLs). I would expect the BDE drivers to be consistent but cannot say for sure that is the case. I have confirmed that MS SQL behaves as you expect (rounds) via Enterprise Manager and that, using a TTable/DBGrid, I can reproduce the truncation, so it appears this may be in the BDE driver, although it could also be in the ODBC driver that the BDE talks to (supplied by MS). However, I decided to try the ADO components and, via these, connected using both the ODBC and OLE drivers and I get the exact same behaviour - 79999.8 gets truncated instead of rounded, so it is definitely not a BDE problem and not limited to ODBC either. -- Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com) RADBooks: http://www.logicfundamentals.com/RADBooks.html Working for yourself is great because you get to work half days, and you can choose any twelve hours you want. Back to top John Herbster Guest Posted: Tue Mar 14, 2006 2:03 am    Post subject: Re: Saving TFloatField to numeric(13,0)... "Wayne Niddery [TeamB]" <wniddery (AT) chaffaci (DOT) on.ca> wrote Quote: Because the fields are not supposed to have any decimal fractions, Why don't you use Int64 variables when doing the assignments? Or is there something that I do not understand? He's using data-aware controls tied directly to the fields. In that case, it may be even easier, how about coding a regular OnSetText event for the field objects to round the data to whole numbers. --JohnH Back to top Ottar Holstad Guest Posted: Tue Mar 14, 2006 12:03 pm    Post subject: Re: Saving TFloatField to numeric(13,0)... Quote: I have confirmed that MS SQL behaves as you expect (rounds) via Enterprise Manager and that, using a TTable/DBGrid, I can reproduce the truncation, so it appears this may be in the BDE driver, although it could also be in the ODBC driver that the BDE talks to (supplied by MS). However, I decided to try the ADO components and, via these, connected using both the ODBC and OLE drivers and I get the exact same behaviour - 79999.8 gets truncated instead of rounded, so it is definitely not a BDE problem and not limited to ODBC either. Hi, thanks for your effort! I still haven't found a solution, so I posted the (reformulated) question to a MS newsgroup now. I will post back here if I can find a solution to this. I noticed that the update-statement that TQuery generates (I'm not using TUpdateObject) is different from Sybase - on Sybase the value is allready rounded. I still think MSSQL should round when given this value, but find it a bit strange that the value is rounded before it's sent to the Sybase database: MSSQL: Type = fldFLOAT, Precision = 13, Scale = 0, Data = 79999.800000 SybaseASA: Type = fldFLOAT, Precision = 13, Scale = 0, Data = 80000.000000 Back to top Ottar Holstad Guest Posted: Tue Mar 14, 2006 3:03 pm    Post subject: Re: Saving TFloatField to numeric(13,0)... Quote: I still haven't found a solution, so I posted the (reformulated) question to a MS newsgroup now. I will post back here if I can find a solution to this. Final conclusion: I have given up on finding the actual problem, and will be inserting lots of Round's in my code. Final note: If the value is updated using a regular update statement (update MyTable set MyField = 79999., then rounding occurs as expected. If the update uses parameters (update MyTable set MyField = :MyField), then truncation occurs instead. TQuery makes heavy use of parameters when performing a post, and putting on the Round's is less work than doing somehing about TQuery... Back to top John Herbster Guest Posted: Tue Mar 14, 2006 3:03 pm    Post subject: Re: Saving TFloatField to numeric(13,0)... "Ottar Holstad" wrote Quote: ... I still think MSSQL should round when given this value, Ottar, I disagree. I think that many of the problems that we programmers have with databases are due to the databases performing *lossy* (i.e. non-reversible) transformations on the data. One of Herbster's rules of database manager design is "The database should give out *exactly* what was put in." Regards, JohnH Back to top Wayne Niddery [TeamB] Guest Posted: Tue Mar 14, 2006 6:03 pm    Post subject: Re: Saving TFloatField to numeric(13,0)... Ottar Holstad wrote: Quote: Final note: If the value is updated using a regular update statement (update MyTable set MyField = 79999., then rounding occurs as expected. If the update uses parameters (update MyTable set MyField = :MyField), then truncation occurs instead. TQuery makes heavy use of parameters when performing a post, and putting on the Round's is less work than doing somehing about TQuery... Interesting. This still shouldn't be a TQuery issue (and isn't - as I indicated I also tried the ADO components). Parameters are not a Delphi thing, they are standard on most databases and it is, in this case, MS SQL that accepts the passed parameters and applies them to the prepared SQL statement. So this seems to inidicate the issue is with MS SQL itself - a difference between handling parameters compared to literal values. John H.'s suggestion to use OnSetText might make it easier to deal with this, you can continue using the data-aware controls. -- Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com) RADBooks: http://www.logicfundamentals.com/RADBooks.html "The only reason some people get lost in thought is because it's unfamiliar territory." - Paul Fix Back to top Tomislav Kardaš Guest Posted: Fri Mar 17, 2006 7:03 am    Post subject: Re: Saving TFloatField to numeric(13,0)... Hi Ottar! On Mon, 13 Mar 2006 20:09:26 +0100, "Ottar Holstad" <no (AT) no (DOT) no> wrote: Quote: Now, since the fields are TFloatField, it's possible to assign to them decimal-values, and it they are connected to a DB-aware edit then the decimal os duely showed. However, when the TQuery is posted back to the database, a correct rounding takes place on sybase, but not on MSSQL. The value 79999.8 is saved as 80000 on Sybase, wheras on MSSQL the value is just cut hard at the decimal separator, alas the value is saved as 79999 in the database. If you try in query analyzer: declare @x float set @x = 79999.8 select convert(numeric(13, 0), @x) you're get: --------------- 80000 (1 row(s) affected) 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.