[racket] Losing decimal in SQL Server query result

Thank you, Ryan! The cast as numeric(10,4) did not work, but the cast to varchar worked fine, and that's good enough for me for now. I have no idea why this field is sized so big, but this is not the first puzzling thing I've seen in this database. Let me know if there is anything else I do to help you with your testing.

-Greg

-----Original Message-----
From: Ryan Culpepper [mailto:ryan at cs.utah.edu] 
Sent: Wednesday, October 31, 2012 9:05 PM
To: Greg Graham
Cc: users at racket-lang.org
Subject: Re: [racket] Losing decimal in SQL Server query result

ODBC claims to only support precisions up to 15, so maybe it's overreacting to the unsupported precision by truncating the number to an integer.

Can you try casting the field to a lower-precision numeric and let me know what happens? For example,

   select cast(creditawarded as numeric(10,4)) from ....

Another possible workaround for now would be to cast the field to a varchar and call string->number on the result.

I'll see if I can figure out what's going on, but I don't have a SQL Server test environment handy.

Ryan


On 10/31/2012 05:07 PM, Greg Graham wrote:
> Hello everyone,
>
> I attempting to use the Racket db interface to access a Microsoft SQL 
> Server 2005 database. The field "creditaward" is defined as 
> numeric(19,7), and in the case of the following query, I should get a 
> value of 1.25 rather than 1. Anyone have an idea how I get to the 
> fractional part?
>
> The code:
> #lang racket
> (require db)
> (define dbc (odbc-connect ...))
> (query dbc "select creditawarded from ea7studentgrades where 
> ea7studentcoursesid=58170")
>
> The results:
> (rows-result
>   '(((name . "creditawarded")
>      (typeid . 2)
>      (size . 19)
>      (digits . 7)))
>   '(#(1)))
>
> Here is the result of the same query using SQL Server Management Studio:
>      creditawarded
>      1.2500000
>
> -Greg
>
>
> ____________________
>    Racket Users list:
>    http://lists.racket-lang.org/users
>