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
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
