PullBDS.PacFIN() might use fancy quotes that RODBC::sqlQuery() cannot handle

[John-R-Wallace-NOAA]

RODBC::sqlQuery() cannot handle fancy quotes

<‘ ’> 

only standard quotes <' '> inside of double quotes <" "> See the help for sQuote().

So this works:

"SELECT * FROM PACFIN_MARTS.COMPREHENSIVE_BDS_COMM WHERE REGEXP_LIKE (PACFIN_SPECIES_CODE, 'SABL')"

but not:

 "SELECT * FROM PACFIN_MARTS.COMPREHENSIVE_BDS_COMM WHERE REGEXP_LIKE (PACFIN_SPECIES_CODE, ‘SABL’)"

nor:

'SELECT * FROM PACFIN_MARTS.COMPREHENSIVE_BDS_COMM WHERE REGEXP_LIKE (PACFIN_SPECIES_CODE, "SABL")'

sQuote() uses getOption("useFancyQuotes"), the default of which is TRUE in R, but a user may have changed it regardless.

The ways I got PullBDS.PacFIN() to work is to

Change the first line of PacFIN.Utilities:::sql_bds() so that q was forced to be FALSE:

spid <- sQuote(paste(pacfin_species_code, collapse = "|"), q = FALSE)

or

The current options() needs to be saved in PullBDS.PacFIN() and restored on exit:

...
op_old <- options(useFancyQuotes = FALSE)
on.exit(options(op_old)) # Or on.exit(options(op_old), add = TRUE) if not the first on.exit()
...

Note also that the error returned by PacFIN.Utilities:::getDB() was (note the fancy quotes):

[1] "HY000 911 [Oracle][ODBC][Ora]ORA-00911: invalid character\n"                                                                                
[2] "[RODBC] ERROR: Could not SQLExecDirect 'SELECT * FROM PACFIN_MARTS.COMPREHENSIVE_BDS_COMM WHERE REGEXP_LIKE (PACFIN_SPECIES_CODE, ‘CNRY’);'"

This made NROW(data_raw) return a 2 which passed:

stopifnot("No data found" = NROW(data_raw) > 0)

nrow(data_raw) > 0 returns:

logical(0)

which strangly also passes stopifnot()

Something like this would work (a tibble is also a data frame):

stopifnot("No data found" = is.data.frame(data_raw))

Examples:

data_raw <- c("A", "B")
NROW(data_raw)

{ function() { stopifnot("No data found" = NROW(data_raw) > 0); print('hi') } }()
{ function() { stopifnot("No data found" = nrow(data_raw) > 0); print('hi') } }()
{ function() { stopifnot("No data found" = is.data.frame(data_raw)); print('hi') } }() 

data_raw <- tribble(
        ~colA, ~colB,
        "a",   1,
        "b",   2,
        "c",   3
      )

NROW(data_raw)

{ function() { stopifnot("No data found" = NROW(data_raw) > 0); print('hi') } }()
{ function() { stopifnot("No data found" = is.data.frame(data_raw)); print('hi') } }() 

Lastly, I just installed R ver 4.3, and that fresh install revealed that the RODBC also needs to be a required package.

[kellijohnson-NOAA]

Thanks @John-R-Wallace-NOAA for reporting the difficulty you are having with PullCatch.PacFIN().

• [x] I am more inclined to have a check that ensures that users are not using fancy quotes that stops the function rather than allow them. When doing a true sql call you would never use fancy quotes. I am curious if you have a reason why they should be allowed? @John-R-Wallace-NOAA and I talk below about using sQuote(q = FALSE) in the three calls to this function to force quotes to not be fancy.
• [x] Thanks for letting me know about the stopifnot() call not working.
• [x] Not all users of {PacFIN.Utilities} need to pull data nor have access to the data, which is why {RODBC} is a suggested package rather than a required one. Thus, I will leave it as such so not all users have the overhead of needing to install it given they will never use it. I did find advice regarding warnings/messages when a function uses a suggested package rather than an imported package. I can add a require(RODBC) call in an if statement to warn users that they cannot use the function unless they have the necessary packages.

[John-R-Wallace-NOAA]

The fancy quotes are the default in R. I checked many R sessions including Linux and the new Win ver 4.3, since I was curious. Being the default, when I tried the function it didn't work. I'm curious if you were using another package or function that changed the default of fancy quotes.

[iantaylor-NOAA]

@John-R-Wallace-NOAA, my R session doesn't use fancy quotes. It looks like users have control over the default: https://stat.ethz.ch/R-manual/R-devel/library/base/html/options.html.

I have

options()$useFancyQuotes
[1] FALSE

[John-R-Wallace-NOAA]

@iantaylor-NOAA and I meet briefly on Friday and found that it was Radian which has the useFancyQuotes set to FALSE, but that his Rqui console had useFancyQuotes set to TRUE. A search on the Web directed my to the last paragraph of the help Details section for sQuote(), which states:

"To work around this [the correct rendering of fancy quotes], the default for options("useFancyQuotes") is FALSE on Windows except for the Rgui console. There fancy quotes work with the default Courier New font and more elegantly with Lucida Console and default East Asian fonts, but directional double quotes are missing in raster fonts such as Courier and FixedSys."

Hence both R.exe and Radian, neither of which are based on Rgui, have useFancyQuotes set to FALSE as the default.

[kellijohnson-NOAA]

@John-R-Wallace-NOAA I think we can just set sQuote(q = FALSE) in the three instances that it is used within {PacFIN.Utilities} to get around this issue, right? Also, I am reopening this issue because I haven't fixed the stopifnot() call that you kindly reported was not working.

[John-R-Wallace-NOAA]

Setting sQuote(q = FALSE) in PacFIN.Utilities() will work fine.

My bad for not separating out the issues. :-/ The thought did cross my mind. :-)