R devel - Dec 2006 - DBI + ROracle problem with parser ?? (PR#9424)

Full_Name: Christian Hoffmann
Version: 2.4.0
OS: Win 2000
Submission from: (NULL) (193.134.202.252)


Database queries using the combination DBI + ROracle are handicapped by 

quirks in the pipeline between the textual representation of the query 

and the database engine Oracle. Comments and linefeed at start are 
interfering:



dbGetQuery(conn, query):



works:

dbGetQuery(conn, "select * from dual")

dbGetQuery(conn, "select * from dual /* comment */")

dbGetQuery(conn, "select /* comment */ * from dual")



doesn't:

dbGetQuery(conn, "\nselect * from dual")

dbGetQuery(conn, "select\n * from dual")

dbGetQuery(conn, "/* comment */ select * from dual")

christian.hoffmann at wsl.ch wrote:
> doesn't:
> dbGetQuery(conn, "\nselect * from dual")
> dbGetQuery(conn, "select\n * from dual")
> dbGetQuery(conn, "/* comment */ select * from dual")
This sounds like my doing.  What version of Oracle are you using?
Oracle 9i has a bug that interferes with the documented mechanism for
asking Oracle for the type of an SQL statement (i.e. whether it is a
query returning row data, or a statement that modifies rows).  So I
asked David James for a quick fix that consisted of checking the
beginning of the SQL for either "select" or "with".

We could be more sophisticated about parsing things, I guess skipping
over any arbitrary combination of comments and white space.  Or, if
you're using a version of Oracle not affected by the bug, you can edit
src/Makefile and comment out the line:

  WORKAROUND = "-DRS_ORA_SQLGLS_WORKAROUND"

-- Dave