Dear all, I'm afraid I'm still having trouble with RODBC and NULL values on Mac OS X 10.4.8. (I would add that otherwise, RODBC is running perfectly, and is doing an excellent job!). R 2.4.
As before, all my data is stored in Postgresql 8.1.4. I'm using Actual's ODBC drivers (now updated to 2.5). I've removed RODBC and reinstalled, compiling from source rather than using the binary package. Installed using the line (all one line): LIBS='-framework iODBC' PKG_CFLAGS='-I/Library/Frameworks/iODBC.framework/Headers' R CMD INSTALL RODBC_1.1-7.tar.gz NULL values *are* correctly returned to a number of different client applications, including "iodbctest", Filemaker, Excel and R/RdbiPgSQL, but not RODBC. SQL>select calc_survival_unilateral_support as unilateral, has_family_history_ataxia as familial from clinical, patient where clinical.patient_fk=patient_id and excluded=0 and calc_walking_disability_valid=1; unilateral |familial ----------------+---------------- 6 |1 6 |0 4 |0 2 |0 5 |1 ****************|0 ****************|1 8 |0 ****************|0 ****************|0 Running the same query from Excel, Filemaker or RdbiPgSQL correctly imports the data with missing data as NULL or empty. This is not the case using R/RODBC: > unilateral = sqlQuery(channel, "select calc_survival_unilateral_support as unilateral, has_family_history_ataxia as familial from clinical, patient where clinical.patient_fk=patient_id and excluded=0 and calc_walking_disability_valid=1") > unilateral[1:10,] unilateral familial 1 6 1 2 6 0 3 4 0 4 2 0 5 5 1 6 0 0 7 0 1 8 8 0 9 0 0 10 0 0 > These fields are both defined as "numeric". There is no difference if I use 'ALTER TABLE' to change to int4. Using "as.is" makes no difference. I cannot explain why NULL values are not being transferred correctly using RODBC. Are there any other diagnostic strategies I could try? Since I last posted, I have installed RdbiPgSQL, and this seems to work. > conn = dbConnect(PgSQL(), host="localhost", dbname="ataxia") > test.df = dbGetQuery(conn, "select calc_survival_unilateral_support as unilateral, has_family_history_ataxia as familial from clinical, patient where clinical.patient_fk=patient_id and excluded=0 and calc_walking_disability_valid=1") test.df[1:10,] unilateral familial 1 6 1 2 6 0 3 4 0 4 2 0 5 5 1 6 NA 0 7 NA 1 8 8 0 9 NA 0 10 NA 0 In my dataset, there is a considerable interpretative difference between NA and zero. I don't know how to take this further, and perhaps it only applies to my peculiar set-up. If there are any further diagnostic tests that others can suggest, or some debug mode (I can't see this in the source code), then let me know. Obviously, the fact that RdbiPgSQL successfully manages to transfer NULL values limits the problem to either the ODBC driver or RODBC itself. The success with iodbc, Filemaker and Excel as ODBC client presumably suggests this problem is limited to RODBC, and is not an ODBC driver problem? Any ideas? I'll be switching to RdbiPgSQL from now, but I thought it appropriate to flag this up as an unsolved problem. Many thanks, Best wishes, Mark -- Dr. Mark Wardle Clinical research fellow and Specialist Registrar in Neurology, C2-B2 link, Cardiff University, Heath Park, CARDIFF, CF14 4XN. UK ______________________________________________ [email protected] mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
