Probleme Executing a stored procedure ....

Question

I'm using Apache 2.2.17 / PHP 5.3.6 / SQL 2.0 PDO Driver

Hello, I'm trying to execute a specific stored procedure but this doesn't work...

This procedure work when running from SQL Manager  ( I take the exact syntax I'm using in PHP and its work)

I used SQL Profiler and saw that the procedure begin and immediatly end without doing anything ....

Any idea of a such strange thing ?????

Answer 1

Hi Bruno

You haven't given us enough information to help so I'm making a guess:

1. Your stored procedure probably contains multiple SQL statements, and you are not executing all of them in PHP...  you need to call PDOStatement::nextRowset() until there are no more results.
2. You are not using the correct ODBC syntax to call the stored procedure.

Here is some example code that demonstrates both points 1 and 2:

$pdo = new PDO('sqlsrv:your connection string');
$stmt = $pdo->query( "{CALL myStoredProc(parameter1, parameter2)}" );
do
{
    // ...
    // process the result...
    // ...
    
} while ( $stmt->nextRowset() ) ;

---

Rob

Answer 2

Hello,

thanks Rob for your answer. I think that the first reason is the good one (1. Your stored procedure probably contains multiple SQL statements ...)

Here my procedure (which is working when running from SQL Management)

CREATE procedure [dbo].[MajDesignationCourteV12] @editeur varchar(5)
as

BEGIN TRANSACTION
 SET QUOTED_IDENTIFIER ON
 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
 SET ARITHABORT OFF
 SET NUMERIC_ROUNDABORT OFF
 SET CONCAT_NULL_YIELDS_NULL ON
 SET ANSI_NULLS ON
 SET ANSI_PADDING ON
 SET ANSI_WARNINGS ON
 COMMIT

BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
COMMIT

declare @fss int
declare @fsr int
declare @id decimal(18,0)
declare @sentence varchar(800)
declare @depart varchar(300)
declare @arrive varchar(300)
declare @refediteur varchar(5)
declare @Obligatoire bit
declare @char_index_debut int
declare @char_index_fin int
declare @lenth int
declare @loop bit
declare @designationConstructeur bit

BEGIN TRANSACTION
update PROD_DES_CORRESPONDANCE
set [Chaine Arrivée] = ''
where [Chaine Arrivée] is null
COMMIT

declare designation cursor for select [ID], DESIGNATION_PRODUIT, REF_EDITEUR, DESIGNATION_COURTE_EDITEUR  from PRODUITS
where ACTUEL = 1
AND DESIGNATION_PRODUIT is not null
and ( REF_EDITEUR = @editeur)
ORDER BY REF_EDITEUR ,REF_PRODUIT

open designation

fetch next from designation into @id, @sentence ,@refediteur,@designationConstructeur

set @fss = @@fetch_status
/* PRINT 'IN0' */

while @fss = 0
BEGIN

 IF @designationConstructeur = 0
 BEGIN

 declare correspondance cursor  for select [Chaine Départ],[Chaine Arrivée],Obligatoire,Editeur from PROD_DES_CORRESPONDANCE
 where ( @sentence like '%' + [Chaine Départ] + '%' or ( @sentence like '%(%' and [Chaine Départ] like '()' ))
 and [Chaine Départ] is not null
 and ( Editeur = @refediteur OR Editeur = 'ALL' )
 ORDER BY Obligatoire DESC,F1
 open correspondance
 /* PRINT 'IN1' */

 fetch next from correspondance into @depart, @arrive , @Obligatoire, @editeur
 set @fsr = @@fetch_status
 while @fsr = 0
 BEGIN
  
  IF (@Obligatoire = 1)
  BEGIN
   --print 'Obligatoire'
   IF(@depart = '()')
   BEGIN
       set @loop = 1

    WHILE @loop = 1
    BEGIN
     declare charcursordebut cursor for select charindex('(' , @sentence)
     open charcursordebut
     fetch next from charcursordebut into @char_index_debut
     close charcursordebut
     deallocate charcursordebut

     if @char_index_debut > 0
     BEGIN
      declare charcursorfin cursor for select charindex(')' , @sentence)
      open charcursorfin
      fetch next from charcursorfin into @char_index_fin
      close charcursorfin
      deallocate charcursorfin
      if @char_index_fin > @char_index_debut
      BEGIN
       set @lenth = len(@sentence) - @char_index_fin
       set @sentence = substring(@sentence,1,@char_index_debut-1) + substring(@sentence,@char_index_fin+1,@lenth)
      END
      else /*@char_index_fin < @char_index_debut*/
      BEGIN
       set @loop = 0
      END
     END
     else /*@char_index_debut = 0*/
     BEGIN
      set @loop = 0
     END
    END /*fin de while loop*/
    print @sentence

   END
   ELSE /* fin de if (@depart != '()') */
   BEGIN
   
    set @sentence = replace ( @sentence , @depart, @arrive )

   END
  END
  ELSE /* Obligatoire = 0 */
  BEGIN
   if len(@sentence) > 69
   BEGIN
    -- PRINT 'LONGUEUR > 69'
    set @sentence = replace ( @sentence , @depart, @arrive )
   END
 END
  --PRINT 'FETCH NEXT'
  fetch next from correspondance into @depart, @arrive , @Obligatoire, @editeur
  set @fsr = @@fetch_status
 END  /*fin du while @fsr = 0*/
 close correspondance
 deallocate correspondance

 set @sentence = replace ( @sentence , '    ', ' ' )
 set @sentence = replace ( @sentence , '   ', ' ' )
 set @sentence = replace ( @sentence , '  ', ' ' )

-- print 'Update ***' +@sentence+'***'
 BEGIN TRANSACTION
 update PRODUITS set DESIGNATION_PRODUIT_COURT = @sentence where ID = @id
 COMMIT
 
 END
 
 fetch next from designation into @id, @sentence ,@refediteur,@designationConstructeur
 set @fss = @@fetch_status

END /*fin du while @fss = 0*/

close designation
deallocate designation

BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
COMMIT

GO

Answer 3

Hi Bruno

Here's a whole PHP file that should work for you, after you change the PDO connection string and the parameter to your stored procedure.

Notice that PDO error mode is not set - otherwise your procedure might stop at the first SQL 'print' statement.

<?php
header('Content-type: text/plain;charset=UTF-8');
$options = array(PDO::SQLSRV_ATTR_ENCODING=>PDO::SQLSRV_ENCODING_UTF8);
$pdo = new PDO('sqlsrv:Server=MY_SERVER;Database=MY_DATABASE', 'MY_USER', 'MY_PASSWORD', $options);
$stmt = $pdo->query( "{CALL MajDesignationCourteV12('ABCDE')}" );
if ( !$stmt )
{
	exit( 'Query error: ' . print_r($pdo->errorInfo(), true));
}
$rowsAffected = 0;
$rowsReturned = 0;
$resultSet = 1;
do
{
	echo 'Result ' . ($resultSet++) . ": \r\n";
	// print warnings...  your SQL 'print' statements will be displayed::
	echo 'Warnings: ' . print_r($stmt->errorInfo(), true);
	// Does this result return rows or change rows?...
	if ( $stmt->columnCount() > 0 )
	{
		// rows were returned
		$rows = $stmt->fetchAll();
		$rowsReturned += count($rows);
		$msg = 'Rows returned: ' . count($rows);
	}
	else
	{
		// rows were changed if rowCount > 0 
		if ( ($rc=$stmt->rowCount()) > 0 )
		{
			$rowsAffected += $rc;
		}
		$msg = 'Rows affected: ' . $rc;
	}
	echo $msg . "\r\n-------------------------------------------------------\r\n\n";
} while ( $stmt->nextRowset() ) ;
echo "Finished - total rows returned:$rowsReturned, total rows affected:$rowsAffected\r\n\n";
if ( $stmt->errorCode )
{
	echo 'Closing Error: ' . print_r($stmt->errorInfo, true) . "\r\n";
}
else
{
	echo "No errors.\r\n";
}
?>

---

Rob