To streamline my processes, I’m exploring RStudio’s “SQL Source” script functionality to preview the results of SQL queries. (To be clear, I’m trying to run raw SQL queries in RStudio via a .SQL script, using the DBI package and the previewSql function. More details here.)
When I test run SQL queries in the TOAD IDE, I commonly use bind variables. For example, here is a basic example of a SQL query with bind variables:
SELECT *
FROM DATA
WHERE DATE BETWEEN :START_DATE AND :END_DATE
In TOAD, when SQL queries with bind variables are run, a dialog box automatically opens that allows me to quickly define the values of the bind variables (e.g. START_DATE and END_DATE). This a convenient feature of TOAD that I commonly use, when I’m testing different cases for new SQL queries.
Is there a way to preview raw SQL queries in RStudio, with their bind variables intact?
By the way, I have run SQL queries before with R variables, replacing the SQL bind variables. This alternate method is run in a .R script, using functions from the DBI and ROracle package. For example, I would define the following objects: START_DATE, END_DATE, SQL_QUERY_STRING, and SQL_RESULTS as such:
START_DATE <- "'01-jan-2020'"
END_DATE <- "'31-jan-2020'"
SQL_QUERY_STRING <- paste("SELECT * FROM DATA WHERE DATE BETWEEN", START_DATE, "AND", END_DATE)
SQL_RESULTS <- dbGetQuery(con, SQL_QUERY_STRING)
The method for querying a Oracle database using SQL in the RStudio IDE that I defined above works, but it’s not ideal when I’m at the beginning stages of writing and testing a more complex SQL query. Ultimately, I’d like to know if there’s a more efficient method for running SQL queries in RStudio, while maintaining the convenience of using SQL bind variables.
