0

I'm using RJDBC in RStudio to pull a set of data from an Oracle database into R.
After loading the RJDBC package I have the following lines:

drv = JDBC("oracle.jdbc.OracleDriver", classPath="C:/R/ojdbc7.jar", identifier.quote = " ")

conn = dbConnect(drv,"jdbc:oracle:thin:@private_server_info", "804301", "password")

rs = dbSendQuery(conn, statement= paste("LONG SQL QUERY TO SELECT REQUIRED DATA INCLUDING REQUEST FOR VARIABLE x"))

masterdata = fetch(rs, n = -1)   # extract all rows

Run through the usual script, they always execute without fail; it can sometimes take a few minutes dependent on variable x, e.g. may result in 100K rows or 1M rows being pulled. masterdata will return everything in a dataframe.

I'm now trying to place all of the above into a function, with one required argument, variable x which is a TEXT argument (a city name); this input however is also part of the LONG SQL QUERY.

The function I wrote called Data_Grab is as follows:

Data_Grab = function(x) {
  drv = JDBC("oracle.jdbc.OracleDriver", classPath="C:/R/ojdbc7.jar", identifier.quote = " ")

  conn = dbConnect(drv,"jdbc:oracle:thin:@private_server_info", "804301", "password")

  rs = dbSendQuery(conn, statement= paste("LONG SQL QUERY TO SELECT REQUIRED DATA, 
       INCLUDING REQUEST FOR VARIABLE x"))

  masterdata = fetch(rs, n = -1)   # extract all rows

  return (masterdata)
}

My function appears to execute in seconds (no error is produced) however I get just the 21 column headings for the dataframe and the line

<0 rows> (or 0-length row.names)

Not sure what is wrong here; obviously expecting function to still take minutes to execute as data being pulled is large, but not being returned any actual data frame.

Help is appreciated!

Qaribbean
  • 178
  • 2
  • 3
  • 17
  • 1
    Just to be clear, it sounds like you intend the function to use it's argument `x` in the sql passed to Oracle in `dbSendQuery`? Is that correct? Because if so, you haven't indicated that you've made any attempt to actually implement that behavior in your code at all. – joran Apr 04 '17 at 15:21
  • @joran Yes that's correct. In the normal script I would change "x" manually for each run, however I intended for that argument in the function to now do this. – Qaribbean Apr 04 '17 at 15:44
  • 1
    Then you'll need some code that inserts the argument into your query; the answer below is one option but you can always just use `paste` or `gsub`. If you poke around, there are some options I believe that will protect you from sql injection issues, if that's a concern. – joran Apr 04 '17 at 15:55

1 Answers1

0

if you want to parameterize your query to a JDBC database, try also using the gsubfn package. code might look like this:

library(gsubfn)
library(RJDBC)
Data_Grab = function(x) {
rd1 = x
df <- fn$dbGetQuery(conn,"SELECT BLAH1, BLAH2
    FROM TABLENAME
    WHERE BLAH1 = '$rd1')
return(df)

basically, you need to put a $ before the variable name that stores the parameter you wish to pass.

simitpatel
  • 641
  • 5
  • 9