0

I am connecting to a local SQL Server database and running some queries in loop. The output from each iteration is saved as a RDS datafile locally on the disk.

This works fine when I execute this sequentially in standard for loop as shown below.

for(i in 1: NROW(Employee_Df))
{
     df_results <-  sqlQuery(myconn, paste(" SELECT * FROM Salary_Df 
               WHERE FullName like ",Employee_Df$FullName[i],  "",sep="'"))

        saveRDS(df_results, 
         file=paste0("/Home/Desktop", "Salary",Employee_Df$FullName[i], ".rds"),
        compress = TRUE)
}

When I try to assign the output from the same query using foreach, the code fails. I don't see an error messages but I don't see any valid RDS file saved based on the results from this query.

foreach(i = 1:2, .packages="RODBC")%dopar%{

      df_results <-  sqlQuery(myconn, paste(" SELECT * FROM Salary_Df 
                   WHERE FullName like ",Employee_Df$FullName[i],  "",sep="'"))

            saveRDS(df_results, 
             file=paste0("/Home/Desktop", "Salary",Employee_Df$FullName[i], ".rds"),
            compress = TRUE)
  }

Any suggestions on how to make this work in foreach is much appreciated.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Science11
  • 788
  • 1
  • 8
  • 24
  • What do you want to return with `foreach`? Here, the last thing you're doing is `saveRDS()`, which returns `NULL` I guess. – F. Privé Dec 18 '18 at 06:41
  • @F.Privé, my goal is to save the output from df_results as a RDS file. You are right, it does return a NULL, so how do I force foreach to save the df_results dataframe as a RDS file ? – Science11 Dec 18 '18 at 13:12
  • You are already saving results as RDS files. I don't understand your problem. – F. Privé Dec 18 '18 at 14:11
  • @F.Privé, :) when I save the dataframe -- df_results as RDS the RDS file does not contain the contents of df_results it is null. This is not a problem when I use simple for loop. – Science11 Dec 18 '18 at 14:28
  • Could you provide a small dataset and the full code (e.g. to get `myconn`) so that we can reproduce your issue? – F. Privé Dec 18 '18 at 15:14
  • My guess is that the ODBC connection handle `myconn` only works in the R session where it was created (e.g. when using `%do%`) but fails when running in a parallel R process (e.g. when using `%dopar%` and `doParallel::registerDoParallel(...)`). As @F.Privé says, it's critical that you share _all_ details of what you're doing and what your `sessionInfo()` is. – HenrikB Dec 19 '18 at 01:39

0 Answers0