This is pretty similar to this question, but I need to capture output. It seems to be basically an issue with getting the correct syntax, but using the syntax suggested in the answer didn't work for me.
I have a php application that calls a stored procedure on a SQL Server database which I developed on my Windows laptop running xampp.
My stored procedure looks like this:
CREATE Procedure [dbo].[check_for_barcode] @Sample_Id nvarchar(20), @Barcode_In_table int OUTPUT
AS
SELECT @Barcode_In_table = count(*) FROM dbo.sample_info
WHERE sample_name = @Sample_Id
RETURN @Barcode_In_table
GO
The php code that works on Windows xampp looks like this:
// use a sample Id known to be present in database
$sample_yes = '11335577';
// use a sample Id known NOT to be in database
$sample_no = '0011223344';
$host = 'hostIP';
$db = 'db';
$user = 'user';
$password = 'pw';
try {
$conn = new PDO("sqlsrv:Server=$host;Database=$db", $user, $password);
$sql = "{CALL dbo.check_for_barcode(@Sample_Id=:barcode, @Barcode_In_table=:isInDatabase)}";
$stmt = $conn->prepare($sql);
$stmt-> bindParam('barcode', $sample_yes, PDO::PARAM_STR);
$stmt->bindParam('isInDatabase', $isInDatabase, PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT, 4);
$stmt->execute();
echo 'for sample id 11335577 $isInDatabase = '.$isInDatabase."\n";
$stmt-> bindParam(':barcode', $sample_no);
$stmt->execute();
echo 'for sample id 0011223344 $isInDatabase = '.$isInDatabase;
$stmt->closeCursor();
}
catch (Exception $e) {
echo $e->getMessage();
}
The output is:
for sample id 11335577 $isInDatabase = 1
for sample id 0011223344 $isInDatabase = 0
Since moving it to the Linux (Ubuntu 4.24) server where it will be deployed, it no longer works.
I installed the dblib drivers as described here.
I adapted the code to as required to use the dblib driver so that it would run without errors. This only involved changing the first two lines inside the try statement.
I have changed the first line to:
...
$conn = new PDO("dblib:host=$host;dbname=$db", $user, $password);
...
I have tried all of the following for the second line:
$sql = "{CALL dbo.check_for_barcode(@Sample_Id=:barcode, @Barcode_In_table=:isInDatabase)}";
$sql = "{CALL dbo.check_for_barcode @Sample_Id=:barcode, @Barcode_In_table=:isInDatabase}";
$sql = "CALL dbo.check_for_barcode(@Sample_Id=:barcode, @Barcode_In_table=:isInDatabase)";
$sql = "CALL dbo.check_for_barcode :barcode, :isInDatabase";
As well as all of the above using EXEC instead of CALL
The output in all cases is:
for sample id 11335577 $isInDatabase =
for sample id 0011223344 $isInDatabase =
If I add ...
while ($result = $stmt->fetch(PDO::FETCH_ASSOC)) {
var_dump($result);
}
... this generates no output.
There are no error message at all, so I can't see where the problem is. I would welcome any suggestions on what to try next.
