CODEWITHSUNDEEP
phpsqlr
Emily
Emily

Reputation: 21

R script not completing execution when called from PHP - stops when trying to run RODBC's sqlQuery function

SOLUTION: I just wanted to give an update on how this was fixed. Although I could query SQL directly in the R console, PHP did not have a connection to the SQL Server and would stop running my script in R once it hit R's sqlQuery(). I was able to get this working after establishing a connection using sqlsrv_connect() in PHP and by explicitly stating SQL Server Authentication credentials in the connection strings.

"I am trying to execute an R script from PHP that should run a stored procedure that will update a table in a SQL Server 2005 database. I've added in some print() lines to see where the script stops running and it will execute everything up until the sqlQuery function. However, this works fine when I run it in the R Console. My R script is below:

args <- commandArgs(TRUE)
X <- args[1]
sink("test.txt")

library(RODBC)
conn<-odbcDriverConnect('driver={SQL SERVER};server=server;database=database;trusted_connection=true')

print(conn)

print(X)

sqlQuery(conn,paste("EXEC sp.usp_Stored_Procedure @Test='",X,"', @Test2='Y', @Test3='Z'",sep=""))

print(X)

When I pass the argument "bread" through PHP, the R script will run and create the 'test.txt' file with these values:

[1] -1 [1] "bread"

So I at least know the connection has been made and it executes up until sqlQuery(). Again, this runs completely fine directly in the R console, so I'm wondering where it's being lost when I try and execute the R script from PHP. I am running Windows 7 and I connect to SQL Server using Windows authentication.

EDIT: Here's the PHP script: 

echo "<form action='rfile.php' method='get'>";
echo "Enter name: <input type='text' name='X' />";
echo "<input type='submit' />";
echo "</form>";

if(isset($_GET['X']))
{
  $X = $_GET['X'];

  exec("C:\wamp\www\R\R-2.15.2\bin\Rscript em.R $X");

  $indsc = file_get_contents('test.txt');

  echo $indsc;
}
?>

When executed on the front end, $indsc returns the values of the 'test.txt' file fine with the exception of everything following sqlQuery() in the R script."

Upvotes: 2

Views: 1531

Answers (1)

user1578653
user1578653

Reputation: 5028

I believe you are having the same problem as I did when running R scripts from PHP. Firstly, ensure that when calling R from PHP you do it in this way:

exec('"C:\Program Files\R\R-3.0.1\bin\Rscript.exe" "myrscript.r" 2>&1', $output);

Notice the "2>&1" - this allows PHP to collect any errors that have occurred into the $output - otherwise they are lost. You can then print out $ouput using print_r($output) to see any feedback/errors produced by R. This should provide some clues as to what is failing when you run your script.

I think what is happening is that the call to 'library(ODBC)' is failing. I'm not sure why but it always seems to fail when I do this too when I call from PHP. To fix it, in my R script I did this instead:

library(ODBC, lib.loc = "path/to/r/library/root/folder")

The lib.loc tells R exactly where to find the ODBC library. This should fix the problem.

P.S. On my system, the path to the R libary root folder is "C:/users/myusername/Documents/R/win-library/3.0".

Upvotes: 2

Related Questions