devxlogo

Correctly Convert SQL Server Floats By Jet

Correctly Convert SQL Server Floats By Jet

When using the SQL Server ODBC driver and SQL Server, and If ODBC preparedexecution is used, certain floating-point values may be incorrectly converted.Microsoft Access and Microsoft Visual Basic commonly use the ODBC preparedexecution. For example:

 Dim db As DatabaseDim ds As DynasetSet ds = db.CreateDynaset("SELECT * FROM test")ds.AddNewds.Fields("col1") = 3.9ds.Update

A query that checks for equality of the float column to the value inserteddoes not show the record inserted, whereas a nonqualified query shows therecord. For example, the record set for the ds1 dynaset does not show therecord 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 nonpreparedexecution and DB-Lib client tools, SQL Server is doing the conversion.To work around this problem, do an explicit convert on the SQL Serverusing 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 thevalue for all new records inserted. Please note that this problem doesnot occur using the pass-through mechanism because in that case, the conversionis done by SQL Server.

devx-admin

Share the Post: