Running a MS SQL server stored procedure with a mac in php using PDO

Question

I am trying to execute a stored procedure in an instance of ms sql server, using php from a mac.

I have recently bought a mac book pro and seem to be having real problems achieving this.

I now have a connection established, which I know is working as if I change the password I get the message:

"Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[01002] Adaptive Server connection failed (severity 9)' in /Users/davidwhitwell/Sites/test/connect.php on line 6"

Correcting the password does not give me the error.

The script I am trying to run is:

    $sql = $pdo->prepare("CALL [finance].[get_all_company_names]");
    $sql->execute();
    $results = $sql->fetchAll();
    var_dump($results);

The result returns an empty array. I know data exists and that the correct permissions are in place to execute the procedure as I can do this directly with sql server management studio.

Can you run a general query with this connection? I.e. Select * from some table;. Is there any output from errorInfo: php.net/manual/en/pdo.errorinfo.php — Robbert
– Robbert, Commented Dec 17, 2013 at 6:13
no I don't get anything returned even with a general select query — Dave Whitwell
– Dave Whitwell, Commented Dec 17, 2013 at 8:01

Robbert · Accepted Answer · 2013-12-18 04:48:35Z

1

Since you mentioned that you are not able to retrieve any data, even with a simple select query, I suggest adding some error handling to your script.

First, I would add some exception handling to your general pdo object.

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

This will throw an exception when your query fails. Then wrap your execution in a try/catch statement

try {
  $sql = $pdo->prepare("CALL [finance].[get_all_company_names]");
  $sql->execute();
  $results = $sql->fetchAll();
  var_dump($results);
} catch (PDOException $e) {
  echo 'An error occurred: ' . $e->getMessage();
}

The sql and the pdo objects you created also have a method called errorInfo, that will sometimes display an error.

if($sql->execute()) {
  $results = $sql->fetchAll();
}
else {
  $error_info = $dbh->errorInfo();
  print_r($error_info);
}

Some good resources include these page

PDO Error Handling: http://www.php.net/manual/en/pdo.error-handling.php
PDOStatement::errorinfo: http://www.php.net/pdostatement.errorinfo

EDIT

I believe the ultimate is that stored procedures in MS SQL are called using EXEC

  $sql = $pdo->prepare("EXEC [finance].[get_all_company_names]");

edited Dec 18, 2013 at 4:48

answered Dec 17, 2013 at 15:43

Robbert

6,6226 gold badges37 silver badges64 bronze badges

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

4 Comments

Dave Whitwell Over a year ago

Thanks Robert, the first suggestion returns: "An error ocuured: SQLSTATE[HY093]:Invalid parameter number: no parameters were bound" There are no parameters for this stored procedure though

Dave Whitwell Over a year ago

The 2nd suggestion returns a Fatel Error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number: no parameters were bound'

Robbert Over a year ago

Do you get this message when you do a simple select?

Dave Whitwell Over a year ago

Thanks Robbert, I now have it working with the simple select, I believe the problem previously was that I wasn't specifying the scheme name for the table in the select statement, I also now have it working for the stored procedure and you were correct the issue was using call instead of exec. Thanks for your help.

Collectives™ on Stack Overflow

Running a MS SQL server stored procedure with a mac in php using PDO

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related