Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.

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



Application Security Testing: An Integral Part of DevOps

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.Fields("col1") = 3.9
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:
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.



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