'Executing stored procedure in Microsoft SQL through PHP
I'm trying to execute a stored procedure in a Microsoft SQL database through PHP 7.4, including the passing of variables. I'm able to successfully execute it through SSMS with the following command.
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[UDX_Page]
@message1 = N'GROUP,user,.Notification Group,Is this thing on? [WebMSG]',
@message2 = NULL
SELECT 'Return Value' = @return_value
GO
It seems that it is expecting message1 as a varchar(200) and message2 as text, though the latter can be NULL. I've tried to call it through a couple different iterations of the following PHP code. I get no errors, but it doesn't seem to execute, so I must be doing something wrong.
<?php
$conn = sqlsrv_connect( $serverName, $connectionInfo); //connection info redacted
echo "<b>DATABASE: </b>";
if( $conn ) {
echo "Connection established. (" . $serverName . ")<br />";
}else{
echo "Connection could not be established.<br />";
die( print_r( sqlsrv_errors(), true));
}
$message1 = 'GROUP,user,.Notification Group,Testing -DW [WebMSG]';
$message2 = NULL;
$sql = "EXEC [dbo].[UDX_Page] @message1 = N'" . $message1 . "', @message2 = NULL";
$stmt = sqlsrv_query($conn, $sql);
if( !$stmt ) {
die( print_r( sqlsrv_errors(), true));
}
?>
And also with the following:
<?php
$conn = sqlsrv_connect( $serverName, $connectionInfo); //connection info redacted
echo "<b>DATABASE: </b>";
if( $conn ) {
echo "Connection established. (" . $serverName . ")<br />";
}else{
echo "Connection could not be established.<br />";
die( print_r( sqlsrv_errors(), true));
}
$message1 = 'GROUP,user,.Notification Group,Testing -DW [WebMSG]';
$message2 = NULL;
$sql = "{CALL [dbo].[UDX_Page] (?,?)}";
$stmt = sqlsrv_query($conn, $sql, array(&$message1, &$message2));
if( !$stmt ) {
die( print_r( sqlsrv_errors(), true));
}
?>
Similarly, when I try it in Python, I'm having similar issues where nothing seems to happen. It recognizes that I need two variables to be passed and complaints if I don't (even if one is None). But it doesn't seem to actually do anything. Any I missing some final execution command?
#/bin/python3
import pyodbc
cursor = cnxn.cursor() #Connection info redacted
SQL_STATEMENT = '{call UDX_Page(?,?)}'
message1 = 'GROUP,user,.Notification Group,Test #7 -DW [WebMSG]'
message2 = None
values = (message1, message2)
cursor.execute(SQL_STATEMENT, (values))
Any ideas?
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
