0

We are executing a stored procedure against an MSSQL database with PHP.

When we hand build the stored procedure and execute it manually with Navicat we get the correct results.

When we build the query and execute it with PHP we get a different result.

Is there any way for us to PREVIEW the stored procedure that php is creating and executing against the server?

This is not the exact code but is is essentially what we are doing.

$stmt = mssql_init("sp_doSomething");
mssql_bind($stmt, "@sid", $sid, SQLINT4, false);
mssql_bind($stmt, "@value", $value, SQLINT4, false);
$result = mssql_execute($stmt);

I would love to be able to preview the actual SQL statement contained in $stmt.

Any help would be appreciated.

Zhorov
  • 28,486
  • 6
  • 27
  • 52
teamcolab
  • 9
  • 2
  • There's no general way to 'preview' a stored procedure because they can do almost anything: how would you preview a procedure that sends an email? SQL Profiler is the correct tool, but you say below that you can't use it. Do you have a test environment where you can use Profiler? And you should also explain what you mean by "a different result" and which version and edition of SQL Server you have. – Pondlife Mar 20 '12 at 13:58

2 Answers2

0

if you windows user you can download 2 file 1.php_pdo_sqlsrv_56_ts.dll 2.php_sqlsrv_56_ts.dll and take php folder after change php.ini file add this line extension=php_pdo_sqlsrv_56_ts.dll and extension=php_pdo_sqlsrv_56_ts.dll and restart apache 

$connection = mssql_connect('hostname', 'username', 'password');
if (!$connection) {
  die('Unable to connect!');
}
if (!mssql_select_db('db-name', $connection)) {
  die('Unable to select database!');
}
$vall='zanala';
$stmt = mssql_init("SYSTEM_USERS_FIND_BY_USERNAME",$connection);
mssql_bind($stmt, "@IN_USERNAME",$vall,  SQLVARCHAR,  false,  false,  -1);
 $result=mssql_execute($stmt);
$row = mssql_fetch_assoc($result);
echo $my_id = $row['IID'];
Habib
  • 34
  • 8
0

SQL Query Profiler is a good tool to hook and watch what a SQL server doing right now in realtime. That would be useful.

Taha Paksu
  • 15,371
  • 2
  • 44
  • 78
  • So to further complicate things, we don't have that level of access to the MSSql server. All we can do is make calls against it. Is there any other way to see the call on our side? – teamcolab Mar 19 '12 at 22:50
  • You can't run a stored procedure in mssql line by line inside php. If you don't have access to MSSQL, how do you know that the sp content is the same with your handwritten query?I would watch the value that the server produces and try to get the same value myself. And try it with different inputs then if my function produces the exact same results, then I would say they are identical. – Taha Paksu Mar 19 '12 at 22:56
  • Trial and error has not worked for us so far. So am I correct in my understanding that there is no way to SEE the call that PHP is creating before I executing it? There is no way to see what mssql_execute() is going to execute? – teamcolab Mar 19 '12 at 23:04
  • And why do you think you don't have right kind of access to do profiling with sql profiler? If you can connect with PHP and send queries with PHP, I think you would be able to profile the server too? If you don't have the access to see what's executing, you have no chance to make PHP do it. – Taha Paksu Mar 19 '12 at 23:09
  • Since I am building and executing the query in PHP it seems like I would be able to see what I am executing. The query does get built on my side and sent to the server. The SQL Profiler is just not an option for me right now and I was hoping that there is another way. – teamcolab Mar 19 '12 at 23:17
  • @tpaksu Using SQL Profiler requires specific permissions: http://msdn.microsoft.com/en-us/library/ms187611(v=sql.105).aspx – Pondlife Mar 20 '12 at 13:26