[R] ROracle: fetch return zero rows or empty dataset (a workaround!)

[Sunny Ho]

Hi All,

If you had run into the problem with ROracle on Linux: "fetch()
returns zero rows or empty dataset", here is an easy & safe
work-around for you to try out. It works for me, and very likely it
will work for you too. You must have root privilege to do this on your
machine.

Quick Instructions
Part 1 - Check to see if this is the work-around for you
a) log in as "root"
b) cd /usr/include
c) grep sqlerrml sqlca.h
   NOTE: If you see something like below, then bingo! This is the
work-around for you!!
   int              sqlerrml;
Part 2 - Re-install ROracle properly
a) log in as "root"
b) cd /usr/include
c) mv sqlca.h sqlca.h.xxx
   NOTE: This is just to rename the file temporarily
d) Follow all the original steps to re-install your "ROracle" package.
   NOTE: For my system "WhiteBox Linux (Respin)" on x86 hardware with
Oracle 10g and R.2.0, I used:
      R CMD INSTALL --configure-args='--enable-extralibs
--with-oracle=10' ROracle_0.5-5.tar.gz
   NOTE: You should read the ROracle/inst/README* files if you have not.
e) After ROracle is successfully installed, restore the sqlca.h file:
   cd /usr/include
   mv sqlca.h.xxx sqlca.h
f) All done! Drop a note here for the others to share if your ROracle
works now, or if you
   have a better work-around.

---

Technical details for those who are interested...
First, I believe your Linux should also have "PostGreSQL" installed.
Otherwise, you may not run into this problem at all!  I'm also a
victim annoyed by this problem for months.  After searching and
waiting for months, I see no one seems to know what is wrong, so I
decided to trouble-shooting the problem myself.  After a lot of
investigation and debugging, I found that the ROracle Pro*C module
does not get a proper return code in sqlca.sqlerrd[2].  Those who know
embedded SQL should know that this field gives the number of "rows
returned from the database".  In fact, the return code is returned to
a wrong place: sqlca.sqlerrd[1]. This problem is a result of a
non-matching data definition of the data structure "sqlca" in ORACLE &
POSTGRESQL.

In oracle, sqlca is defined in $ORACLE_HOME/precomp/public/sqlca.h, as:
struct   sqlca
{
         /* ub1 */ char    sqlcaid[8];
         /* b4  */ int     sqlabc;
         /* b4  */ int     sqlcode;
         struct
           {
           /* ub2 */ unsigned short sqlerrml;
           /* ub1 */ char           sqlerrmc[70];
           } sqlerrm;
         /* ub1 */ char    sqlerrp[8];
         /* b4  */ int     sqlerrd[6];
         /* ub1 */ char    sqlwarn[8];
         /* ub1 */ char    sqlext[8];
};

In rh-postgresql-devel-7.3.6-1 package, it defines sqlca in
/usr/include/sqlca.h, as:
#define SQLERRMC_LEN    70
struct sqlca
{
        char            sqlcaid[8];
        long            sqlabc;
        long            sqlcode;
        struct
        {
                int             sqlerrml;
                char            sqlerrmc[SQLERRMC_LEN];
        }                       sqlerrm;
        char            sqlerrp[8];
        long            sqlerrd[6];
        char            sqlwarn[8];
        char            sqlext[8];
};

The difference in "sqlerrml" causes the subsequent component "sqlerrd"
shifted by 4 bytes. When the ROracle package is being installed, it
runs Oracle Pro*C to compile the source code. Unfortunately, the Pro*C
include search path causes it to pick up /usr/include/sqlca.h, instead
of the correct one in $ORACLE_HOME/precomp/public/sqlca.h, therefore
the ROracle module uses the incorrect sqlca and always picks up a
"zero" as the number of returned rows. The workaround above forces
ROracle to use the sqlca.h from Oracle, hence it should work well with
Oracle Embedded SQL.

For the code maintainer, I have no idea of whether Oracle and
PostGreSQL should use the same "sqlca", or who's right who's wrong.
But I believe this is a problem of PostGreSQL & Oracle co-exisitence,
but not a R problem.  My work-around just helps me to get my ROracle
working. I hope that those who have good insight into PostGreSQL and
Oracle could provide some ideas of what we should do to get this
PostGreSQL & Oracle problem fixed in Linux.

Regards,
Sunny Ho  (sunny.ho at gmail.com)