R help - Jan 2009 - shake rattle() and roll

Graham,

nice package this :-)-O.

It was easy to set up MySQL but I struggled immensely to get the stock
ODBC working with PostgreSQL (8.3), which has been discussed on the
mailing list before (Prof Brian):

Warning messages:
1: In odbcDriverConnect(st, ...) :
  [RODBC] ERROR: Could not SQLDriverConnect
2: In odbcDriverConnect(st, ...) : ODBC connection failed

aka (DBD::ODBC in Perl)

Missing server name, port, or database name in call to CC_connect.  
(SQL-IM002)

aka (iodbctest)

SQLDriverConnectW = Unknown connect option (Set) (205) SQLSTATE=S1009

Figured it out, though:

Leopard comes with iODBC installed, and many people install unixODBC  
additionally.
Then then configure /sw/etc/odbc*.ini and unixODBC works, but iODBC  
doesn't.
Even if you symlink the INI files to /Library/ODBC it doesn't work.  
Even if you
remove theunixODBC installation and only use the /Library/ODBC/ 
odbc*.ini files
it doesn't work. Even if you copy libodbcpsql.so and libodbcpsqlS.so  
from
/sw/lib/ to /usr/local/lib/ and then remove unixODBC (through fink) it  
doesn't
work.

However, if you leave the two libs in /usr/local/lib after removel of  
unixODBC
install the libiodbc/libiodbc-shlibs packages from fink, and then  
symlink the
ini files to /sw/etc, it *DOES* work.

Leading me to suspect the version of iODBC that comes with 10.5.5 (and  
earlier)
is broken. Since Google is my friend I know that the CC_connect issue  
is also
prevalent on linux so I reckon it's not Mac specific.

All in all this is extremely cool, because I will have to use a  
firebird database
for my practice management software which doesn't have a direct R  
driver but now
I am confident will be able to use it (with the Read Only option :-)- 
O) to analyse
my ICD and Procedure Codes :-)-O


Three Requests for Features for rattle(): would it not be nice to also  
have direct
PostgreSQL (RdbiPgSQL) and MySQL (RMySQL) support and support for a  
SELECT statement?
In other words, sometimes the database tables directly don't give you  
what you need
and so you have to develop a (convoluted) SQL statement first.

Would be nice if one could ratlle(convolutedSQL) :-)-O

greetings, el
-- 
Dr. Eberhard W. Lisse   \        /          Managing Member, |Ondis (cc)
accounts at ondis.co.NA    / *     |     Telephone: +264 81 124 6733 (cell)
PO Box 98256             \     /Please do NOT send e-mail to el at lisse.NA
Windhoek Namibia         ;____/   if it is in ANY way DNS/|Ondis related




--
Dr. Eberhard W. Lisse  \        / Obstetrician & Gynaecologist (Saar)
el at lisse.NA el108-ARIN /   *   |   Telephone: +264 81 124 6733 (cell)
PO Box 8421             \     / Please send DNS/NA-NiC related e-mail
Bachbrecht, Namibia     ;____/             to dns-admin at na-nic.com.na

Received Fri 09 Jan 2009  5:08pm +1100 from Dr Eberhard W
Lisse:
> Graham,
[...]
> Three Requests for Features for rattle(): would it not be nice to also  
> have direct
> PostgreSQL (RdbiPgSQL) and MySQL (RMySQL) support and support for a  
> SELECT statement?
> In other words, sometimes the database tables directly don't give you  
> what you need
> and so you have to develop a (convoluted) SQL statement first.
>
> Would be nice if one could ratlle(convolutedSQL) :-)-O
Hi Eberhard,

Thanks for the feedback. I'm not a big user of ODBC and so much of
that discussion (particularly re Mac) is beyond me.

I've added the suggestion of directly supporting RdbiPgSQL and RMySQL
to the Rattle todo list. Examples (i.e., R code) of loading data using
these would be very helpful.  So, basically, what is the sequence of R
commands to get the data from either sources into a data frame in R
(yes I could look it up but it would help, time-wise, to be spoon
fed).

The idea of adding the ability to have a SQl SELECT statement written
in the GUI to extract just the data we want is good, and is on my todo
list.

Regards,
Graham