Re: Need advice on unintended and unwanted integer conversion to decimal.

Martin J. Evans wrote:
> Blake Stephens wrote:
>> Greetings all,
>>
>> I'm curious as to what would cause integers to be returned as decimal numbers after switching servers.
>>
>> I just migrated my SQL server from MSSQL 2005 to 2008 and now DBI::ODBC is returning all the INTs as decimals! Even boolean columns that used to return just 1 or 0, now return 0.00. Just the data moved; the server running the scripts hasn't changed since the move, aside from me pointing my DBI connections to the new server, literally only updating the "Server=oldserver;" to "Server=newserver". Both of my servers are Windows Server 64 2008.
>>
>> When I get back a result with an ID (primary key) it will respond with "15.00" instead of "15", for example. This makes using that number in hundreds of already written and previously working queries not work at all.
>>
>> Perl is using: "dbi:ODBC:Driver={SQL Server};" as the connection string to talk to the server and PHP is using "Driver={SQL Server};". PHP seems to be doing it too.
>>
>> Any DBI or ODBC experts out there who could shed light on why this is happening now on the new server and never before on the old? Maybe a tip or configuration change that may fix it?
>>
>> Here's an example &Dumper() dump of a hashref that's populated by int() primary keys as keys and varchar() text as their values.
>>
>> $VAR1 = {
>>           '16.00' => 'Testing manual login',
>>           '11.00' => 'post migration',
>>           '8.00' => 'inserted variable',
>>           '9.00' => 'inserted variable',
>>           '17.00' => 'some text',
>>           '4.00' => 'my text here yay',
>>           '14.00' => 'some text',
>>           '13.00' => 'some text',
>>           '7.00' => 'testing text',
>>           '6.00' => 'test',
>>           '15.00' => 'some text',
>>           '3.00' => 'my text here yay',
>>           '1.00' => 'my test',
>>           '5.00' => 'test',
>>           '10.00' => 'some text',
>>           '2.00' => 'test2',
>>           '18.00' => 'some text',
>>           '12.00' => 'some text'
>>         };
>>
>> And here's the table that it came from:
>>
>> CREATE TABLE [dbo].[Tester](
>> 	[id] [int] IDENTITY(1,1) NOT NULL,
>> 	[clientsemailaddress] [varchar](300) NULL,
>> 	[mytext] [varchar](300) NULL,
>> 	[posteddate] [datetime] NOT NULL,
>> 	CONSTRAINT [PK_Tester] PRIMARY KEY CLUSTERED 
>> 	(
>> 		[id] ASC
>> 	)
>> 	WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
>> ) ON [PRIMARY]
>>
>>
>> Thanks.
>> -Blake
>>
>> -Blake Stephens -
>> Web Applications Developer, Webmaster
>> University of Central Florida
>> Library Systems & Technology
>> http://library.ucf.edu/
>>
> 
> I don't think there is anything in DBD::ODBC which would cause this and
> since you are using the same DBD::ODBC in both cases this would also
> indicate this. It is however possible that the second server is
> returning a different type for the columns. You could test this by
> issuing the query and then examining the TYPE and PRECISION attributes
> for each column (see
> http://search.cpan.org/~timb/DBI-1.613/DBI.pm#Statement_Handle_Attributes).
> 
> Martin

I've had another thought. If you are using a different DSN for each
(which you probably are) there is a rather nasty international/language
setting in the ODBC Driver setup that I think forces all integers to
money types - turn it off, it is horribly flawed and breaks lots of
stuff. I think it is called something like "use regional settings when
outputting...".

Martin
-- 
Martin J. Evans
Easysoft Limited
http://www.easysoft.com