Preview SQL Query Results for Parametrized queries

[dfajardo84]

When a SQL script is created which includes placeholders (?s) for parametrization using dbBind, RStudio returns an error when trying to preview the query as the query isn't valid until values are passed to the placeholders.

Ideally, there would be a way to define default values for each of the parameters in the first line along with the connection.

[ronblum]

@dfajardo84 Thanks for reporting this. Can you please provide an example so that we can try to reproduce this? Also, the following information would be useful:

System details

RStudio Edition : <!-- Desktop or Server -->
RStudio Version : 
OS Version      : 
R Version       : 

[dfajardo84]

RStudio Edition : Desktop
RStudio Version : Version 1.2.1335
OS Version : Windows 10 Pro
R Version : 3.5.3 (2019-03-11)

I can't provide a fully working example because I don't have a table to reference, but say I have a connection "con" to a database with a table named "table" with a column with name "col_name".

The following SQL file will return an error - "Failed to query database: Query needs to be bound before fetching"

-- !preview conn=con
SELECT *
FROM table
WHERE col_name = ?

[ronblum]

@dfajardo84 Ah! Thank you. I'll mark this as a (useful) enhancement.

[dfajardo84]

Awesome.

If I were to recommend a way to address this, it would be to past a list of parameters to the magic first line, something along the lines of:

-- !preview conn=con, param=(1, "Los Angeles", 254.99)

[ronblum]

@dfajardo84 I'm reopening the issue—the open state tells us that the issue should be reviewed, both for enhancements and bugs. (Closed tells us the issue is resolved.)

[dfajardo84]

Oh, I must have accidentally hit close and comment instead of just comment. My bad.

[ronblum]

No problem! I've done the same plenty of times. :)

[gcameron89777]

Just adding my voice since it looks like this has been open for a little while. This would be a awesome feature! https://community.rstudio.com/t/passing-variables-between-r-and-sql-scripts-while-still-being-able-to-use-script-preview/52358/4

The ability to frictionlessly shift contexts between r and sql is a really great analytics experience. This works already when using SQL chunks in Rmd docs and I feel more productive with this set up.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs, per https://github.com/rstudio/rstudio/wiki/Issue-Grooming. Thank you for your contributions.

[stale]

This issue has been automatically closed due to inactivity.

[jsowder]

This functionality hasn't been added, but in the meantime I suggest setting a SQL variable using DBI::DbExecute(con, "set var1 = value1") then referencing that SQL variable in your script, eg: "SELECT $var1". I've confirmed this works with SQL Preview in Rstudio. You'll just need to run the DbExecute prior to using the SQL Preview.