MSSQL Float Precision Problem By Using PHP Driver
Nov 13, 2007
I'm connecting MSSQL Server 2005 Express via MS Driver for PHP (CTP version October 2007) and sometimes I don't retrieve exact float values. For example, in database is 0.7 and I get 0.69999999999999996, but for 1.0 in database I get 1.0. The result is the same, if I use prepared statement (sqlsrv_conn_prepare() and sqlsrv_stmt_execute()) or directly sql_conn_execute().
The table definition is very simple:
CREATE TABLE [dbo].[test](
a float NOT NULL
);
insert into test values(0.7);
insert into test values(1.0);
insert into test values(1.1);
insert into test values(1.2);
insert into test values(1.3);
insert into test values(1.4);
insert into test values(1.5);
insert into test values(1.6);
insert into test values(1.7);
insert into test values(1.8);
insert into test values(1.9);
insert into test values(2.0);
And select command is:
select a from test;
PHP code:
$conn = sqlsrv_connect("localhostsqlexpress", array("UID" => "sa", "PWD" => "password"));
sqlsrv_conn_execute($conn, "USE dbname");
$stmt = sqlsrv_conn_execute($conn, "select a from test");
while($row = sqlsrv_stmt_fetch_array($stmt, SQLSRV_FETCH_TYPE_ARRAY))
echo $row[1]."; ";
Result:
0.69999999999999996; 1.0; 1.1000000000000001; 1.2; 1.3; 1.3999999999999999; 1.5; 1.6000000000000001; 1.7; 1.8; 1.8999999999999999; 2.0
Expected result:
0.7; 1.0; 1.1; 1.2; 1.3; 1.4; 1.5; 1.6; 1.7; 1.8; 1.9; 2.0
Running configuration:
Windows 2003 Server SP2, SQL Server 2005 Express SP2, IIS6, PHP 5.2.5 ISAPI
Thank you very much for help.
Vlasta
View 4 Replies
ADVERTISEMENT
Feb 20, 2000
Hi!
I'm quite new to SQL Server. I need to set a float datatype to display something like 3.55. However, all values that are stored in the float column are truncated to 4 or some other single digit. How can this be prevented?
Regards,
Sam
View 1 Replies
View Related
Sep 5, 2002
Hello everyone,
I am sure this is a newbie question as I am new to Microsoft SQL server but any help is greatly appreciated. I am populating a SQL database from an AS400. The decimal numbers from the AS400 are coming accross with extra decimals. (ie. 63.02 is coming accross as 63.0200001)
Is there a way to limit the number of decimals in a float or real field - or a SQL command I can put in a script to truncate each field to 2 decimal places after they are populated.
Thanks,
Randy
View 1 Replies
View Related
Sep 27, 2007
Hello,
I am connecting to MSSQL through ODBC using regular SQL commands (SQLAllocHandle, SQLConnect, SQLFetch). Sometimes when I retrieve float data from the server it gets corrupted. For instance, instead of 59.457443 I will actually get 59.45744299999998. The value that is stored in the database is 59.457443 and it is stored as a float. The code I use to retrieve the data worked OK with an Access database, the problem only appeared after switching to MSSQL. Does anybody know if it's a configuration issue? Any help would be greatly appreciated.
EoF
View 9 Replies
View Related
Sep 27, 2007
Hi,
I am having problems using MSSQL Express with ODBC. I am trying to retrieve a float field from the MSSQL server, but the value I get is corrupted (or at least approximated wrong). For instance, the value I want to retrieve is 59.457443, but what I actually get is 59.457442999999998. This only happens if I connect through ODBC. I did some tracing with ODBC and I've noticed that it's not a problem in my code, since the wrong value is what is actually extracted from the database. Following is an extract from the log file.
a88-ec EXIT SQLGetData with return code 0 (SQL_SUCCESS) HSTMT 03C62918 UWORD 5 SWORD 1 <SQL_C_CHAR> PTR 0x0415EB30 [ 18] "59.457442999999998" SQLLEN 50 SQLLEN * 0x0415E720 (18)
I am wondering if I overlooked something configuration-wise of if this is a bug in the driver? Any help would be greatly appreciated.
EoF
View 4 Replies
View Related
Jun 16, 2006
Hi,I'm using Sybase driver under Linux and I'm trying to connect to MSSQLserver 2000.But unfortunately connection fails.In Event Log on my Win 2k3 SBS I'm getting the following error message:Error: 17832, Severity: 20, State: 7Connection opened but invalid login packet(s) sent. Connection closed.For more information, see Help and Support Center athttp://go.microsoft.com/fwlink/events.asp.I tried to update SQL server to the lastest version; currently I'm using SP4but it still didn't resolve the problem.Has anyone got an idea what may be wrong?Application is written in perl, using DBD::SybaseThanks,Martin
View 2 Replies
View Related
Apr 11, 2005
Hello,
I normally use Microsoft Access to retrieve data , via ODBC connection, from a table in a SQLserver database. One of the fields in the table has a value 1 but at times when the data is retrieved into MS Access it turns out to be 100 in Access. Moreover, when I try to change it in Access my computer hangs or it takes a long time before the change is effected. It does not matter from which client Pc this action takes place it remains the same. I am using ODBC SQLserver ODBC driver version 2000.81.9042.00.
I would be grateful if you could help me out of this strange situation.
Regards,
Albert
View 4 Replies
View Related
Jul 20, 2005
Hi,I have installed MQ SQL Server 2000 on my laptop and trying to useSPRINTA 2000 JDBC Driver (inetmssql.jar) to connect to the local db. Iwrote a sample Java app to test the connection and I get the followingerror trace,DriverManager.initialize: jdbc.drivers = nullJDBC DriverManager initializedSQLException: SQLState(08S01){sql7=true, port=1433, user=sa, url=jdbc:inetdae7:US0211737-WP01:1433,password=wasfe, host=US0211737-WP01}getConnection failed: com.inet.tds.SQLException: Connection refused:connectcom.inet.tds.SQLException: Connection refused: connectjava.net.ConnectException: Connection refused: connectat java.net.PlainSocketImpl.socketConnect(Native Method)at java.net.PlainSocketImpl.doConnect(Unknown Source)at java.net.PlainSocketImpl.connectToAddress(Unknown Source)at java.net.PlainSocketImpl.connect(Unknown Source)at java.net.Socket.connect(Unknown Source)at java.net.Socket.connect(Unknown Source)at java.net.Socket.<init>(Unknown Source)at java.net.Socket.<init>(Unknown Source)at com.inet.tds.TdsDriver.a(Unknown Source)at com.inet.tds.TdsDriver.run(Unknown Source)at java.lang.Thread.run(Unknown Source)I tried searching the web and product doc. Nothing seems to help me.Has anyone come across similiar problems? Any pointers will beappreciated also.Thanks,Swami.
View 6 Replies
View Related
Apr 9, 2007
I can't take full credit for this. I want to share this with Jeff Moden who did the important research for this calculation here.
All I did was just adapting some old code according to the mantissa finding Jeff made and optimized it a little
Some test codeDECLARE@SomeNumber FLOAT,
@BinFloat BINARY(8)
SELECT@SomeNumber = -185.6125,
@BinFloat = CAST(@SomeNumber AS BINARY(8))
SELECT@SomeNumber AS [Original],
CAST(@SomeNumber AS BINARY(8)) AS [Binary],
dbo.fnBinaryFloat2Float(CAST(@SomeNumber AS BINARY(8))) AS [Converted],
@SomeNumber - dbo.fnBinaryFloat2Float(CAST(@SomeNumber AS BINARY(8))) AS [Error]
And here is the code for the function.CREATE FUNCTION dbo.fnBinaryFloat2Float
(
@BinaryFloat BINARY(8)
)
RETURNS FLOAT
AS
BEGIN
DECLARE@Part TINYINT,
@PartValue TINYINT,
@Mask TINYINT,
@Mantissa FLOAT,
@Exponent SMALLINT,
@Bit TINYINT,
@Ln2 FLOAT,
@BigValue BIGINT
SELECT@Part = 1,
@Mantissa = 1,
@Bit = 1,
@Ln2 = LOG(2),
@BigValue = CAST(@BinaryFloat AS BIGINT),
@Exponent = (@BigValue & 0x7ff0000000000000) / EXP(52 * @Ln2)
WHILE @Part <= 8
BEGIN
SELECT@Part = @Part + 1,
@PartValue = CAST(SUBSTRING(@BinaryFloat, @Part, 1) AS TINYINT),
@Mask =CASE WHEN @Part = 2 THEN 8 ELSE 128 END
WHILE @Mask > 0
BEGIN
IF @PartValue & @Mask > 0
SET @Mantissa = @Mantissa + EXP(-@Bit * @Ln2)
SELECT@Bit = @Bit + 1,
@Mask = @Mask / 2
END
END
RETURNSIGN(@BigValue) * @Mantissa * POWER(CAST(2 AS FLOAT), @Exponent - 1023)
END
Thanks again Jeff!
Peter Larsson
Helsingborg, Sweden
View 3 Replies
View Related
Oct 19, 2006
trying to install sql server2005 on a windows 2003 server box.
getting msg below at the sql server . i looked at other posts on trying to uninstall SQL Native Access Client and norton antivirus. i could never find the snac on the add - remove programs and this server does not have a virus protection program yet.
here's the history of the installs on the server:
wanted to test a 2005 upgrade so:
1) installed sql server 2000 then sp4 then restored some databases to it - all OK
2) tried to upgrade to sql 2005 but ran into problems and left it at that.
had a disk drive crash on the d drive so lost the installs but not the operating system
when the drive was replaced, left alone for a while
then wanted to test a straight 2005 install
1) removed the broken 2005 attempt
2) removed the 2000
3) installed 2005 and got the error on the subject line:
TITLE: Microsoft SQL Server 2005 Setup
------------------------------
SQL Server Setup could not connect to the database service for server configuration. The error was: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified Refer to server error logs and setup logs for more information. For details on how to view setup logs, see "How to View Setup Log Files" in SQL Server Books Online.
i've gone through as many of the forums that i can and have tried several things - like uninstalling 2005 and installing pieces and parts but but nothing seems to work.
Thanks!
Dan <><
View 17 Replies
View Related
Feb 4, 2008
i am attempting to run phpbb using ms sql 2005 on the same box but get the following error during the setup
Could not connect to the database, see error message below.
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
i am not sure yet if this is an issue with sql, php or phpbb
php is installed in iis and has all the modules installed which the php msi installer supported. i can run php code but in this case when i'm running the install.php file for the phphbb setup, i fill in the values for the database and got that error
phpbb detects all the required server settings and sees that i have ms sql installed
any suggestions on what that error means or how to go about configuring odbc driver?
probably a simple issue. but i'm still new with sql stuff
View 1 Replies
View Related
Oct 13, 2004
Hello,
I want to have a comparison about operation of a power builder application with sql server database, using sybase native driver and ole db driver.
View 2 Replies
View Related
Oct 28, 2004
Hi,
Please help share with me if you know the version compatibility matrix of Ms SQL Server, ODBC driver (sqlsrv32.dll), Driver Manager (odbc32.dll) and ODBC API spec. For instance, how can I know Ms SQL Server 2000 can work with which version of sqlsrv32.dll, a particular version of sqlsrv32.dll can work with which version of odbc32.dll and a certain version of sqlsrv32.dll/odbc32.dll conforms to which version of ODBC API spec (e.g. 3.5).
Any help will be appreciated.
Thanks,
vtluu.
View 1 Replies
View Related
Jan 9, 2006
Hi, I have a database field of type "money". But when I rerieve it to text box, it shows 5.0000, but I want only 5.00 to be shown. How do I format that?
Any reply will be much appreciated. :)
View 2 Replies
View Related
Nov 8, 2004
Hello,
I have been trying to develop an automatic way of programmatically accessing datasources and performing some predefined(-supported) processing on them.
The question I would like to ask you people has to do with numeric fields. What exactly is precision? Is it the maximum length in digits of a field, or is there more to it? What about a "field's scale", what is it and how does it affect a field's value handling?
Thank you very much,
Dave
View 1 Replies
View Related
Sep 21, 2006
I have a table with a 'quantity' column (decimal 9:3) and a 'price' column (9:3). I have a third column 'amount' with a formula of (price * quantity). The formula gives the correct answer, but the precision is automatically set to 5. Is there any way to set the precision of the result to 2?
Thanks.
View 4 Replies
View Related
Jan 21, 2008
I am using ASP and SQL 2005 Express.I am inserting a timestamp from an ASP page using <%=now%into asmalldatetime field. All of my timestamps are appearing without anyseconds (e.g., 1/21/2008 4:02:00 PM or 1/18/2008 11:32:00 AM).When I view the source for my page is shows the date/time as 1/21/20084:27:31 PM, but for some reason the seconds will be converted to1/21/2008 4:27:00 PMHow do i get more a more precise timestamp?Please help.
View 1 Replies
View Related
Apr 18, 2007
Hi All
I am trying to pull data from Oracle to SQL Server but if I use Oledb Source than I get this error
Error at Data Flow Task [DTS.Pipeline]: The "output column "CUST_ID" (590)" has a precision that is not valid. The precision must be between 1 and 38.
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC0204018 (Microsoft.SqlServer.DTSPipelineWrap)
The only solution I found is use DataReader Source,
But if I use DataReader Source everything works fine , I mean I am able to see the records and convert it desired data type (using Data Convertion component).
My question is what component should I use as Destination, coz if I use OLEDB Desination I get a red cross on the components although I can map all the columns.....
View 9 Replies
View Related
Nov 3, 2003
Hi I am in the process of creating a new db in sql. In my users table I wish to set the UserIds as Integer datatype. It defualts on precision 4. Does this mean that when the column auto increments as its my primary key with a seed of one, my highest number allowed in the table would be row 9999. ???
Also if you where to store a phone number in your db, what column type would you give it. I have used varChar but its all numbers i want to store. Would this suffice.
Thanks
View 1 Replies
View Related
Sep 24, 2007
We have a field which is decimal (9,2) and another which is decimal(9,3). Is there anyway to subtract the two and get a precision 3value without changing the first field to 9,3?For instance, retail value is 9,2, but our costs are at 9,3 due tobeing averaged. To calculate margin (retail-cost), we want that alsoto be 9,3, but a basic subtraction comes out 9,2. You can see wedon't want to increase retail to be 9,3 (that would look funny), andit seems wasteful to store retail twice (one 9,2 for users and one 9,3for margin calc)...is there any other way?
View 2 Replies
View Related
Dec 7, 2007
Hi , i am receiving data froma flat file it is
These are amount fields
123456.89
i am selecting numeric (8,2) as datatype is this valid please let me know.
View 3 Replies
View Related
Oct 8, 2006
there is a column which type is float in a table, i want to set the precision of its value, for example if its value is 10.333888, i want to get its value as 10.33, how to complete it in a select Sql?
thks
View 3 Replies
View Related
Jan 31, 2008
I receive patient demographic files from hospitals that are in several different formats. I have written translations for each format. I need to upload the files into our accounting software. I have the file layout to upload data and it looks like this.
From To Length
Record Type Code 1 2 2A
Account Number 3 17 15A
Guarantor Name 18 47 30A
Guarantor Zip 125 129 5N
Guarantor Area Code 134 136 3N
In SQL Server I have not found a way to set precision on an int. I have to have the correct length, and data type (A for alfa which is left justified and N for numeric which is right justified) field for a succsessfull upload. Suggestions on what data types to use would be very helpful, and then suggestions on how to output the data in a text file described example above would be a life saver.
View 1 Replies
View Related
Jul 19, 2004
I am using SQL 2000 in a kind of electronic wallet way. Users out money onto an account and spend it on various services on a system. The cost of those services is deducted from the value in their wallet, and everybody's happy. However, some very strange things have been happening to my transactions; seemingly at random.
Some transactions (such as purchasing time on the Internet) are returning values such as 0.10000000000000001 instead of 0.1. This minute difference affects the user's wallet balance because the rouge digit is subtracted from their account. So instead of a balance of, say, 3.4 they have 3.39999999999999999.
"So what?", I hear you say. Well the problem comes when it's time to give them a refund. They walk over to a kiosk and the machine tells them they have 3.40 remaining in their account (it's nicely rounding up the value), but when they click Refund, it tells them they have insufficient funds to complete the refund! (Note: The refund amount is being compared with the wallet balance). If I go into the database via Query Analyzer it tells me their balance is 3.3999999etc, but in Enterprise Manager the value is 3.4. If try to manipulate the data in any way it is treated as 3.4. However, if I add 0.000000000000001 then QA reads the value as 3.4 and the customer can get their refund.
My questions is this. One, how the hell do I stop this from happening? I only need the two decimal places. Taking the value in a query and round it up/chopping off the remaining decimal points hasn't worked. It always picks up the value as 3.4 in a query. Two, why on Earth is this happening??? Has anyone experienced this problem before.
Thanks in advance to anyone that's read this far down.
View 3 Replies
View Related
May 5, 2008
Hi,
I'm importing data from and oracle database to an SQL one through a SSIS package, I'm getting this error:
"The output column "earned_hours" has a precision that is not valid. The precision must be between 1 and 38".
the package runs but returns this column as NULL values
earned_hours is of type "NUMBER" in oracle (some of the values are decimals), I tried making it numeric(x,y),float or decimal(x,y), but I'm still getting the same results.
does anybody know why is this happening or have a solution for this error?
Thanks
View 5 Replies
View Related
Jun 4, 2008
am getting some unexpected behaviour using datetimes (on SP2, though i haven't tried other builds), as below...
<code>
----------------------------------------------------------------------
---if i run...
select dateadd(day,datediff(day,0,getutcdate()),0) as today
,dateadd(ms,-1,dateadd(day,datediff(day,0,getutcdate()),1)) as end_of_today
/*
i'd expect the result to be...
today end_of_today
----------------------- -----------------------
2008-06-04 00:00:00.000 2008-06-04 23:59:59.999
what i actually get is...
today end_of_today
----------------------- -----------------------
2008-06-04 00:00:00.000 2008-06-05 00:00:00.000
*/
------------------------------------------------------------------------
--i run...
select dateadd(day,datediff(day,0,getutcdate()),0) as today
,dateadd(ms,-2,dateadd(day,datediff(day,0,getutcdate()),1)) as end_of_today
/*
i expect...
today end_of_today
----------------------- -----------------------
2008-06-04 00:00:00.000 2008-06-04 23:59:59.998
i get...
today end_of_today
----------------------- -----------------------
2008-06-04 00:00:00.000 2008-06-04 23:59:59.997
*/
------------------------------------------------------------------------
--even as simple as this, the result is the same...
select cast('23:59:59.999' as datetime) as end_of_day
/*
results in....
end_of_day
-----------------------
1900-01-02 00:00:00.000
*/
</code>
can anyone shed any light? and also suggest how to reliably create a datetime like yyyymmdd hh:mi:59.999 ?
Em
View 6 Replies
View Related
Mar 26, 2008
Hi all,
I'm running a transformation script that's taking decimal(18,10) data and trying to shoehorn it into a numeric(9,6). generally this works, as most of the data in the original table is not using anywhere near the precision it's capable of, but once in a while I run into one that does use it.
Is there any way to automagically reduce the precision so that i can cram the data into the destination table?
___________________________
Geek At Large
View 3 Replies
View Related
Dec 8, 2006
This one cost me a solid half hour yesterday. I'm wondering why onearth the default precision for a decimal type is 18,0. Maybe I'mmistaken. A decimal datatype sort of implies that you'd want somethingafter the decimal!Question is, can I set this database-wide? Like all new decimaldatatypes have a precision of 12,6 or something like that? I haven'tseen anything about this in the googling I have done...
View 3 Replies
View Related
Oct 9, 2007
I have a UserDefinedType that is Decimal(21,6)
The fields fltValorPendente e fltTotal are of this type. Field intSinal is an Integer.
I execute the following query:
SELECT
(intSinal * fltValorPendente) / fltTotal as Coef,
cast((intSinal * fltValorPendente) as decimal(21,6)) / fltTotal as CoefCast
into tmp
FROM tbl
Where fltTotal<>0
As a result, table tmp is created with the following structure:
CREATE TABLE [dbo].[tmp](
[Coef] [decimal](38, 6) NULL,
[CoefCast] [decimal](38, 17) NULL
) ON [PRIMARY]
How come both fields don't get the same precision?
View 2 Replies
View Related
Dec 19, 2006
How do I set Precision and Scale in a calulated column?
I'm trying to limit the decimal points returned in a calculated column but can't find where to set the scale. What am I missing please?
Thanks,
Scott
View 6 Replies
View Related
Nov 2, 2007
Hi, first than nothing the query, I'm on sqlserver 2000:
--------------------------------------------------------------------------------------------------------
declare @ordered float
declare @convertion float
set @ordered = 49.0
set @convertion = 24.0
SELECT
CAST((@ordered / @convertion) AS float) as colA,
CAST((@ordered / @convertion) AS INT) as colB,
cast(CAST((@ordered / @convertion) AS float) - CAST((@ordered / @convertion) AS INT) as float) as [colA - colB],
ABS(CAST((@ordered / @convertion) AS float) - CAST((@ordered / @convertion) AS INT)) * @convertion as [The reminder]
--------------------------------------------------------------------------------------------------------
If you run that you'll get this result:
colA colB colA - colB The reminder
2.04166666666667 2 0.0416666666666665 0.999999999999996
You'll notice the workaround to get the decimal part of a number, if you wonder why its because sql2000 in the operand % only supports integers. Anyway, try on your calculator ( 2.04166666666667 - 2 ) it probably answer: 0.04166666666667.
BUT SQL NOT! you see how the result is amazingly 0.0416666666666665 ????
Does anybody knows a solution for this??? What am I doing wrong???
If you try the query but instead of 49.0 use 25.0, it returns the correct value !!.
I really appreciate any comment about this.
View 1 Replies
View Related
Jul 20, 2005
Hi,Could some please tell me whether Microsoft provides Type 2 and Type 4jdbc driver ? For Type 4 MS providescom.microsoft.jdbc.sqlserver.SQLServerDriver driver. What is thecofiguration required for type 2 driver and what driver class filesrequired ?Its very urgent to me please reply.Ajay
View 1 Replies
View Related
Dec 1, 2004
We have a view in a 9205 oracle database. We can query fine
and the decimal precision is there.
When we query this same view from ms sql server we lose the precision
so 115.25 becomes 115.
does anyone know a workaround for this?
View 2 Replies
View Related