Using MySQL in R - getting error trying to return variable as column

Question

I have used the dbConnect and RMySQL packages to successfully connect to my company's database with R. However, I am struggling to run a query in R and am obtaining a frustrating error. The query works in the MySQL Workbench app that I use day to day, hence the frustration that it won't work in R. Here is a snippet of the SQL query:

USE mydb;
SELECT @theDate := '2017-05-03';

SELECT
    @theDate AS today,
    a.user_id AS user_id,
    ... 
    ...

These are the first few lines of the query, and also the part of the query causing the error in R. I receive the following error:

my_query = "    USE mydb;
SELECT @theDate := '2017-05-03';

SELECT
    @theDate AS today,
    a.user_id AS user_id,
    ... 
    ... "

my_db = dbConnect(MySQL(), ...)
requested_query = dbSendQuery(my_db, my_query)

Error in .local(conn, statement, ...) : 
  could not run statement: You have an error in your SQL syntax; check the manual that 
  corresponds to your MySQL server version for the right syntax to use 
  near 'SELECT
           @theDate AS today,
           a.user_id AS user_id,' at line 3

Sorry I cannot provide more reproducible code, but that would involve connecting to the company database and I can't share the database info.

Last comment - I believe the line has to do with the SELECT @theDate: := '2017-05-03'; line. This query was written by my coworker, not myself, and I hadn't before seen 2 select statements used like this before in a query. What's happening in the MySQL Workbench app is that @theDate is essentially a variable set to '2017-05-03'. The first column of the table returned by the query is all '2017-05-03'

Like I said, the query works in MySQL Workbench, but not in R. Quite frustrating. Any suggestions are appreciated!

Thanks!

EDIT - Realizing that this is more a question in trying to understand how setting variables works in SQL. Like I said I hadn't seen this before, but the first SELECT query is setting a variable and then the 2nd SELECT query uses that variable. Is this allowed? I wonder why its allowed in MYSQL Workbench but not R... still frustrating

I can't answer your question but check out the RODBC package [cran.r-project.org/web/packages/RODBC/index.html], maybe this solves your problem. I also had an issue when I tried to receive the result of my queries and I managed to fetch only dataframes. But when the dataframes were a little bit large, then not all the rows were loaded into R. But this packaged solved my problem and until now every query that runs in MySQL Workbench also works in R — sanyi14ka
– sanyi14ka, Commented May 4, 2017 at 6:02

Shadow · Accepted Answer · 2017-05-04 05:57:37Z

1

I believe you cannot execute more than 1 sql query in a single dbSendQuery() call and this causes the error message.

Either execute the 2 queries separately or assign value to the session variable within the 2nd select:

SELECT @theDate := '2017-05-03' AS today, ....

answered May 4, 2017 at 5:57

Shadow

34.5k10 gold badges67 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using MySQL in R - getting error trying to return variable as column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related