ODBC driver with postgresql
Tim, Coming to this with a bit of a delay, but one of the things that you may wish to try is to set 'rows_at_time = 1' in your calls to sqlQuery(). See the help file for the function. Prior to RODBC version 1.3-0, this defaulted to 1. However, beginning with 1.3-0, the default changed to 100. I had problems with the new default value using RODBC connecting to an Oracle server. These were typically queries returning 0 rows, where I knew that there should be more than that and typically in the hundreds. The problem manifested itself when the view names were > 11 or 12 characters. This had suggested the possibility of a pointer gone awry, stepping on and corrupting the query result. However, I could never confirm that and Prof. Ripley could not replicate the problem on his end. I think that lacking further investigation, it may be representative of a bug in the ODBC driver itself. Setting 'rows_at_time' to 1 has resolved the problem for me and you should see if that resolves anything in your situation. BTW, the error message that you get below after loading RODBC on Linux suggests that you need to re-install it. It appears to have been built using a prior version of R, before dynamically generated help files were introduced in recent versions. HTH, Marc Schwartz
On Dec 15, 2009, at 2:36 PM, Tim Coote wrote:
Well my fix is clearly inadequate as I'm getting malloc errors when I re-read the information :-( On 15 Dec 2009, at 17:49, Tim Coote wrote:
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