[R-SIG-Mac] ODBC driver with postgresql

[Tim Coote]

I now have a simplistic fix for this error. I have no idea how to  
create test or submit a patch. Indeed, i've got zero familiarity with  
the testing model.

Here's the skinny. The RODBC.c driver code does not handle SQL_FLOATs  
(ie DATA_TYPE 6 in my original posting) returned by the odbc driver,  
so they fall through to the default encoding mechanism, which is  
character based (I think). As far as I can see, all that's needed is  
to include SQL_FLOAT to all of the SQL_DOUBLE cases in three switch  
statements. (one in cachenbind, two in RODBCFetchRows.)

I'd guess that the reason that I've found this bug on the mac is that  
my linux box (Fedora Core 10) uses an older version of RODBC (rpm: R- 
RODBC-1.2-2.fc10.i386) and the bug's crept in between the two  
versions. However, I've not diff'd the sources. It could just be that  
no one else uses RODBC + PostgreSQL.

What's the process for submitting a suitable patch? or is the above  
description enough to be picked up by the package maintainers?

Tim
On 2 Dec 2009, at 22:49, Tim Coote wrote:

> For some reason I've got a problem with double precision numbers in  
> postgres coming across into R as integers (they're just truncated).
>
> I'm using:
> R 2.10.0 GUI 1.30 Leopard build 32-bit (5511)
> R-RODBC version 1.3
> I've tried both the packaged postgresql odbc driver for the mac and  
> rebuilt from source with the same results (psqlodbc-08.04.0100),  
> against postgres 8.4 on my mac and against 8.3 on Fedora Linux.
>
> the odbc driver works as expected with python + pyodbc, and with R  
> 2.10, RODBC (R-RODBC-1.2-2.fc10.i386, R-2.10.0-2.fc10.i386).
>
> Here's some noddy results (I create a table called x with with one  
> double precision column) - I'm ignoring the warning, which seems to  
> be a distro error for fc10:
>
> From R on Linux:
> > library (RODBC)
> Warning message:
> package 'RODBC' was built under R version 2.8.1 and help may not  
> work correctly
> > c=odbcConnect ("PostgreSQL",  uid="yy", pwd="xxx")
> > sqlQuery (c, "select * from x")
>       x
> 1  1.234
> 2 43.989
> > sqlColumns(c, "x")
>  TABLE_QUALIFIER TABLE_OWNER TABLE_NAME COLUMN_NAME DATA_TYPE  
> TYPE_NAME
> 1           cases      public          x           x         6     
> float8
>  PRECISION LENGTH SCALE RADIX NULLABLE REMARKS COLUMN_DEF  
> SQL_DATA_TYPE
> 1        15      8     0    10        1                
> <NA>             6
>  SQL_DATETIME_SUB CHAR_OCTET_LENGTH ORDINAL_POSITION IS_NULLABLE  
> DISPLAY_SIZE
> 1               NA                NA                1         
> <NA>           22
>  FIELD_TYPE AUTO_INCREMENT PHYSICAL NUMBER TABLE OID
> 1        701              0               1     16517
>
>
> However, on the mac:
> > c=odbcConnect ("Hg", uid="yy", pwd="xxx")
> > sqlQuery(c, "select * from x")
>   x
> 1  1
> 2 43
> > sqlColumns (c, "x")
>  TABLE_QUALIFIER TABLE_OWNER TABLE_NAME COLUMN_NAME DATA_TYPE  
> TYPE_NAME PRECISION LENGTH SCALE RADIX NULLABLE REMARKS
> 1           cases      public          x           x         6     
> float8        15      8     0    10        1
>  COLUMN_DEF SQL_DATA_TYPE SQL_DATETIME_SUB CHAR_OCTET_LENGTH  
> ORDINAL_POSITION IS_NULLABLE DISPLAY_SIZE FIELD_TYPE
> 1       <NA>             6               NA                 
> NA                1        <NA>           22        701
>  AUTO_INCREMENT PHYSICAL NUMBER TABLE OID BASE TYPEID
> 1              0               1     16517           0
>
> Uid and pwd obfuscated. As far as I can see, the driver on the mac  
> is correctly identifying the type of the column 'x' as a float8, but  
> then truncating before the decimal point.  I have seen somewhere  
> that Oracle drivers used to suffer from this issue, when they  
> truncated type 6 variables.
>
> python looks ok on the mac:
> >>> import pyodbc
> >>> c=pyodbc.connect (dsn="Hg")
> >>> cu=c.cursor().execute ("select * from x")
> >>> print cu.fetchall()
> [(1.234, ), (43.988999999999997, )]
>
> I cannot find a pyodbc equivalent to sqlColumns. As far as I can  
> see, R and python on my mac are using a common stack, and R seems to  
> work ok with the fedora odbc drivers. Is it likely that I've still  
> got bugs in my odbc set up or is there an issue with R + ODBC?  I'm  
> not sure where else to look for what's going wrong.
>
> Tim
>
> _______________________________________________
> R-SIG-Mac mailing list
> R-SIG-Mac at stat.math.ethz.ch
> https://stat.ethz.ch/mailman/listinfo/r-sig-mac

Tim Coote
tim at coote.org
+44 (0)7866 479 760