Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Enterprise
Expertise: Intermediate
Aug 6, 1997

Correctly Convert SQL Server Floats By Jet

When using the SQL Server ODBC driver and SQL Server, and If ODBC prepared execution is used, certain floating-point values may be incorrectly converted. Microsoft Access and Microsoft Visual Basic commonly use the ODBC prepared execution. For example:
 Dim db As Database
Dim ds As Dynaset

Set ds = db.CreateDynaset("SELECT * FROM test")
ds.AddNew
ds.Fields("col1") = 3.9
ds.Update
A query that checks for equality of the float column to the value inserted does not show the record inserted, whereas a nonqualified query shows the record. For example, the record set for the ds1 dynaset does not show the record inserted, but ds2 dynaset will:
 Set ds1 = db.CreateDynaset("SELECT * FROM test _
	WHERE col1=3.9")
Set ds2 = db.CreateDynaset("SELECT * FROM test")
The difference in behavior is because, in the case of prepared execution, the ODBC driver is doing the conversion to float. In the case of nonprepared execution and DB-Lib client tools, SQL Server is doing the conversion. To work around this problem, do an explicit convert on the SQL Server using a statement similar to this:
 UPDATE test SET foo= (CONVERT(FLOAT, _
	CONVERT(VARCHAR, col1)))
You can do the same thing within a trigger to automatically update the value for all new records inserted. Please note that this problem does not occur using the pass-through mechanism because in that case, the conversion is done by SQL Server.
Douglas Haynes
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date