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
