1

I often run sql queries through R, and I've been working on getting a particular query to work. I provided quite a bit of info about it here in this question.

Long story short, I have a data frame I'd already pulled in, and I'm trying to loop through a sql query for each line of the dataframe I have. I'm running into some errors and it seems like (per the comments) that it may be rjdbc related. So I started a new question to deal specifically with the rjdbc errors. Here's my code:

library(RJDBC)   ## 

#create file path specific to the user
classpath_name="A:/ojdbc8.jar"

# Create connection driver and open connection
jdbcDriver <- JDBC(driverClass="oracle.jdbc.driver.OracleDriver", classPath=classpath_name)  ## jar8 file supplied by company for connecting to the database. (2 other jar file options)
jdbcConnection <- dbConnect(jdbcDriver, "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=lhs-dbprod.forteresearchapps.com)(PORT=2879))(CONNECT_DATA=(SERVICE_NAME=pdblhs)))", "database", "adfhkjsdabfiewubbksdjblablablabla")  ## DB hostname etc


Contacts<- dbGetQuery(jdbcConnection, 
                      "select contact_id, username from lhs_oncore_prod.rv_contact where not username='barcode_user'") 



(qmarks <- paste(rep("?", nrow(Contacts)), collapse = ","))


Access <- dbGetQuery(jdbcConnection,
                 paste("SELECT priv_check.contact_id, priv_check.protocol_id
     FROM LHS_ONCORE_PROD.sv_user_pcl_permission priv_check
     WHERE priv_check.function_name = 'CRPT-Subject Visits'
     AND priv_check.contact_id in (", qmarks, ")"),
                 params = as.list(Contacts$CONTACT_ID) )

And here's the errors I run into:

enter image description here

Based on some comments in the other thread I tried this:

Access <- do.call(dbGetQuery, c(list(jdbcConnection, "SELECT priv_check.contact_id, priv_check.protocol_id FROM LHS_ONCORE_PROD.sv_user_pcl_permission priv_check WHERE priv_check.function_name = 'CRPT-Subject Visits' AND priv_check.contact_id in (", qmarks, ")"), params = as.list(Contacts$CONTACT_ID) ))

but that returned this:

enter image description here

Any ideas?

Joe Crozier
  • 944
  • 8
  • 20
  • 1
    Do I see correctly ... your `Contacts` has 497 rows? Yeah ... you should _really_ be using the other method I suggested in the previous question: upload to a temp table, query on a join. If you have confirmed that you are not permitted even _temporary_ tables, then I suggest you re-attempt this with only 10 or so rows to confirm if the method even works as expected; if it does, then perhaps there is a limit for how many parameters you can bind to the query. – r2evans May 16 '23 at 13:59
  • 1
    Thank you for staying with this. Because of your comment, it got me thinking: I was just about to dive back into whether or not I'm allowed to do a temporary table, but I wonder... the contacts just come from the same database. As you can see, like a line above, I just pull from rv_contact. Given I'm downloading it a second prior, rather than push it back up in, there's gotta be a different way. – Joe Crozier May 16 '23 at 15:54
  • For the record it does seem like I'm not allowed either way. Tried dbwritetable and got the error: insufficient privileges – Joe Crozier May 16 '23 at 15:56
  • 1
    Assuming you tried with `temporary=TRUE`? – r2evans May 16 '23 at 16:02
  • 1
    Looks like you can combine the queries into a single query to pull all data at once. – r2evans May 16 '23 at 16:02
  • Yup! Just did it. Lol now I dont know what to do about this question above. Cant decide whether to delete or leave it – Joe Crozier May 16 '23 at 16:10
  • 1
    since it's so data-specific, I don't know that self-answering it is going to help a lot of other people, but over to you – r2evans May 16 '23 at 16:42

0 Answers0