CODEWITHSUNDEEP
phpsql-serverpdoodbc
Benjamin Solum
Benjamin Solum

Reputation: 2321

Calling a Stored Procedure with PHP PDO Error

I'm using PHP PDO with the ODBC driver to connect to an MSSQL database. I have a stored procedure called "uspGetLoginUserInformation". I'm trying to call it like so:

    $username = '[email protected]';
    $password = 'test';
    $stmt = $odbc->prepare("CALL dbo.uspGetLoginUserInformation(:username, :password)");
    $stmt->bindParam(':username', $username);
    $stmt->bindParam(':password', $password);
    $stmt->execute();

I keep getting this error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 102 [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Incorrect syntax near '.'. (SQLExecute[102] at ext\pdo_odbc\odbc_stmt.c:254)' in C:\wamp\www\plugin.php on line 96

Any ideas? I get that it's a syntax error, but even if I remove the "dbo." I still get a syntax error Incorrect syntax near '@P1'.

Thanks!

Upvotes: 1

Views: 5401

Answers (2)

Austin Schmidt
Austin Schmidt

Reputation: 316

I was getting similar errors when trying to use PDO from using sqlsrv before and wanted to document the process to a solution somewhere so that anyone with the same problem can hopefully find this solution.

Converting old sql_srv code to use PDO in PHP. The original code:

$params = array(array($date, SQLSRV_PARAM_IN),array($location, SQLSRV_PARAM_IN),array(3, SQLSRV_PARAM_IN));
$result = sqlsrv_query($sqldb, "{call Some_Stored_Procedure ( @base_date=?,@location=?,@plusdays=? )}", $params);

New code V1:

$q = "{call Some_Stored_Procedure ( @base_date=?,@location=?,@plusdays=? )}";
$params = array(array($date, SQLSRV_PARAM_IN),array($location, SQLSRV_PARAM_IN),array(3, SQLSRV_PARAM_IN));
$stmt = $CONN->prepare($q);
$stmt->execute($params);

Which threw: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Could not find stored procedure 'Some_Stored_Procedure'.'

This error was resolved by specifying the database as in:

$q = "{call Database.dbo.Some_Stored_Procedure ( @base_date=?,@location=?,@plusdays=? )}";
$params = array(array($date, SQLSRV_PARAM_IN),array($location, SQLSRV_PARAM_IN),array(3, SQLSRV_PARAM_IN));
$stmt = $CONN->prepare($q);
$stmt->execute($params);

But this introduced a new error and is the reason this question exists: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Error converting data type nvarchar to datetime.'

Trying to solve this error led to many other errors(none of which point to the actual problem!) including the authors' Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Incorrect syntax near '@P1'.'

The eventual solution(same as authors):

$stmt = $CONN->prepare("Exec Database.dbo.Some_Stored_Procedure  ?, ?, 3");
$stmt->execute(array($date, $location));

The difference(that I feel the author didn't stress) is in the format of calling the stored procedure. Instead of:

CALL Some_Stored_Procedure(Param1, Param2, ...);

It is

EXEC Some_Stored_Procedure Param1, Param2, ...;

Use EXEC instead of CALL(there are some differences) and do not use parenthesizes around your parameters.

Upvotes: 2

Benjamin Solum
Benjamin Solum

Reputation: 2321

Was using the wrong syntax. Prepare line should look something like this:

    $stmt = $odbc->prepare("Exec uspGetLoginUserInformation @Username=:username, @Password=:password");

Upvotes: 1

Related Questions