Using R variable in SQL Query

Question

I am using RServe and SQL in Java to get data from a database and plot the results in and R plot. This is working fine but now I need to use a variable in the SQL query.

In PHP I know you just need to close the statement, add the variable and the reopen the statement.

However, when I try this in Rserve the page just hangs.

My code is as follows:

String UID = session.getAttribute("sessionUID").toString(); 
c.assign("UID", UID);
c.eval("df <- data.frame(dbGetQuery(con, paste('select UID, BuildingCode, "
+ "DATE_FORMAT(AccessTime, \"%d-%b-%Y\") as Date from test WHERE UID=\"'+UID+'\"')))");

Without the WHERE UID=\"'+UID+'\" the code works perfectly so I know this is the problem, I just don't know the solution?

Note: I have also tried using , instead of + as stated in this answer but that didn't seem to work either.

Dom Abbott · Accepted Answer · 2014-06-24 12:41:12Z

0

Solved it!

The problem was WHERE UID=\"'+UID+'\" so instead of trying to escape the " with \" I just added some quotes when assigning the session to the String.

Instead of this:

String UID = session.getAttribute("sessionUID").toString();

I used this:

String UID = '"'+session.getAttribute("sessionUID").toString()+'"';

And it now works perfectly! Although I am still puzzled why \" was throwing an error.

answered Jun 24, 2014 at 12:41

Dom Abbott

1,2372 gold badges9 silver badges11 bronze badges

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

Collectives™ on Stack Overflow

Using R variable in SQL Query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related