I'm having problems trying to run a stored procedure on MSSQL2000 using PHP PDO. I've tried all the combinations, but cant get any results apart from Invalid cursor state error.
The procedure is inside a database that is used by another application. I'm just reaching inside it to pull information out to display on PHP. So modifying the stored procedure (even to instead that SET NOCOUNT ON) is a no-no.
The stored procedure takes two arguments - a start and end date. There is also a RETURN_VALUE parameter.
I can run the following command in the SQL Query Analyzer and dumps rows of data fine:
EXEC [availability_enquiry] '08-24-2015 0:0:0.000', '08-26-2015 0:0:0.000'
Seems pretty straight forward, but when I try to code it and run it, I get nothin:
$dbConn = null;
$connectionString = sprintf('%s:Driver=%s;Server=%s;Database=%s;TrustedConnection=yes;', 'odbc', '{SQL Server Native Client 10.0}', 'mypc', 'testdb');
$dbConn = new PDO($connectionString, 'root', '123qew');
$dbConn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "EXEC [availability_enquiry] '08-24-2015 0:0:0.000', '08-26-2015 0:0:0.000'";
$stmt = $dbConn->query($sql, PDO::FETCH_ASSOC);
$data = $stmt->fetch();
print_r($data);
I get an PDO Expection: 'Invalid cursor state' on the $stmt->fetch() line.
$stmt = $dbConn->prepare($sql);
$stmt->execute();
$data = $stmt->fetch();
print_r($data);
Still get an PDO Expection: 'Invalid cursor state'. Must be something to do with the fetch. Try something else:
$data = array();
do {
$results[count($results)] = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
while($stmt->nextRowset());
Still nothing!? Any ideas?
UPDATE1:
Tried another method:
$sql = "{CALL availability_enquiry (:startdate, :enddate)}";
$stmt = $dbConn->prepare($sql);
$startdate = "2015-08-24T00:00:00";
$enddate = "2015-08-26T00:00:00";
$stmt->execute(array(
':startdate'=>$startdate,
':enddate'=>$enddate
));
$data = $stmt->fetch();
print_r($data);
Also tried it as:
$startdate = "2015-08-24T00:00:00";
$stmt->bindParam(':startdate', $startdate, PDO::PARAM_STR);
$enddate = "2015-08-26T00:00:00";
$stmt->bindParam(':enddate', $enddate, PDO::PARAM_STR);
$stmt->execute();
But both give me an 'Invalid character value for cast specification' error message. Tried it with my own and the newly suggested date format.
If I use the same format of the date that is inside the table that the stored procedure uses:
$startdate = "2015-08-11 09:42:18.890";
I get an 'Invalid cursor state' error message. I'm hoping this is one step closer?