[racket] Losing decimal in SQL Server query result

From: Ryan Culpepper (ryan at cs.utah.edu)
Date: Fri Nov 2 18:41:42 EDT 2012

Update: I've looked at the ODBC docs again, and apparently I'm there's a 
whole convoluted process for retrieving numeric values that I'm not 
doing. Some of the drivers I tested with don't follow the convoluted 
process either, which is why I missed it. I'll try to commit a fix soon.


On 11/01/2012 09:00 AM, Greg Graham wrote:
> 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

Posted on the users mailing list.