Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

SQL Results preview pane shows only the results of the very first SQL statement #3860

Closed
gregkvas opened this issue Nov 9, 2018 · 7 comments
Assignees
Labels

Comments

@gregkvas
Copy link

@gregkvas gregkvas commented Nov 9, 2018

System details

RStudio Edition : Desktop
RStudio Version : 1.2.1091
OS Version      :  Windows 7 Prof. / Windows 10 Home
R Version       :  3.5.1 Feather Spray

Steps to reproduce the problem

  1. Connecting to SQLServer Express 2014 (or also 2017) via Connection pane in RStudio
  2. Using the SQL button in RStudio to generate a new SQL file with the open connection
  3. Defining two simple SQL SELECT statements within the SQL file and using "Preview" button in RStudio

Describe the problem in detail

When defining multiple SQL statements within a generated SQL file in RStudio only the results of the
very first SQL statement is shown when using the preview button "Preview" (Ctrl+Shift+Enter). As can be seen in the screenshot, only the first selection of the top 5 cases of the specific table is previewed and no results for the second SQL statement, where all cases of the specific table are selected.

rstudio_sql_preview

I have also described this behaviour in RStudio Community: https://community.rstudio.com/t/rstudio-1-2-preview-sql-integration-preview-multiple-sql-statements/17839?u=gregkvas

Describe the behavior you expected

I expected that the results of each SQL statement would be shown in different SQL preview
panes and/or the SQL statements could be invoked to be previewed one by one.

Thank you for any help!
Best, Gregor

@ronblum
Copy link
Contributor

@ronblum ronblum commented Nov 9, 2018

@gregkvas This is the expected behavior. DBI only accepts one statement, and therefore only the first statement in the SQL preview is getting executed. One option would be to make an enhancement request that DBI accept multiple statements at once.

@gregkvas
Copy link
Author

@gregkvas gregkvas commented Nov 10, 2018

@ronblum Thank you Ron for the explanation! So the SQL integration preview in RStudio 1.2 works like a DBI::dbSendQuery() function and therefore does not behave like a SQL batch script as i mistakenly thought of. With an enhancement for DBI to act more like a SQL batch script RStudio would really become an alternative SQL IDE.

@genghiskhanofnz
Copy link

@genghiskhanofnz genghiskhanofnz commented Nov 30, 2018

Hi all, I am using RStudio SQL button to preview my query results before plotting or summarising in RStudio and hoping that evaluating and producing results at least 2 active sql sets would be necessary for me. But, I am not sure what the priorities are for the RStudio dev team.
As a heavy-user of RStudio, this suggestion will serve a 'giant step' for a RStudio-mankind.
Thanks.

@ronblum
Copy link
Contributor

@ronblum ronblum commented Dec 3, 2018

The behavior of the preview button is as expected, since DBI::dbSendQuery() only accepts one statement. To handle multiple statements, I suggest filing a DBI enhancement request at https://github.com/r-dbi/DBI.

Perhaps this could be of use: the following code snippet will execute a single multi-line SQL query from an external file.

sql <- readLines("query.sql")
sql_string <- paste(sql, collapse = '\n')
sql_results <- dbGetQuery(con, sql_string)
sql_results

There is also a discussion about this at https://stackoverflow.com/questions/2003663/import-multiline-sql-query-to-single-string. These will only work for a single statement, though. You might be able to create a workaround and adapt the code examples to split a file with multiple statements—say, separated by semicolons—and looping through them.

@genghiskhanofnz
Copy link

@genghiskhanofnz genghiskhanofnz commented Dec 7, 2018

@ronblum = thanks for your time and effort to comment on this topic.

@TonyFly3000
Copy link

@TonyFly3000 TonyFly3000 commented May 4, 2019

I think its will be great if we can execute the SQL code line by line on Rstudio IDE

@ronblum
Copy link
Contributor

@ronblum ronblum commented May 6, 2019

@TonyFly3000 That sounds like a good suggestion. Can you please file a new enhancement in this repository? This will have the added benefit of other users being able to vote and comment on the request.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
Development

No branches or pull requests

5 participants