Search code examples
sqlrserve

Using R variable in SQL Query

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.

Solution

  • 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.