PHP 手册
后退前进


mssql_execute

(PHP 4 >= 4.1.0, PHP 5)

mssql_execute --  Executes a stored procedure on a MS SQL server database

Description

mixed mssql_execute ( resource stmt [, bool skip_results] )

警告

本函数暂无文档，仅有参数列表。

注: If the stored procedure returns parameters or a return value these will be available after the call to mssql_execute() unless the stored procedure returns more than one result set. In that case use mssql_next_result() to shift through the results. When the last result has been processed the output parameters and return values will be available.

See also mssql_bind(), mssql_free_statement(), and mssql_init().


add a note add a note User Contributed Notes
mpoletto at gmail dot com
22-Feb-2005 03:04
The constant SQLINT4 is not working with datetime. Try using SQLVARCHAR.
SQL dot User at Yandex dot Ru
13-Jul-2004 12:38
To receive output parameter from the procedure which returns one or several recordsets, try this code:

...
mssql_bind($my_procedure, "@OutputParameter", SQLVARCHAR, true);
$result = mssql_execute($my_procedure);
while(mssql_next_recordset($result)) {
## do something
}

after listing last recordset output parameter will be available (strange...).

If you do not need output recordsets, just parameters, try this:

mssql_bind($my_procedure, "@OutputParameter", SQLVARCHAR, true);
$result = mssql_execute($my_procedure, true);

P.S. Tested on PHP 4.3.5.
marco dot carvalho at NOSPAM dot uni-yoga dot org dot br
08-Jun-2004 05:00
<?PHP

/*
<font color="#007f00">
Gets all fields properties from table/stored procedure in <i>systables</i> and stores
in <b>$GLOBALS['MSSQL_PROP_'.$object]</b><br>
Returns: <b>True</b> or <b>false</b>
</font>
*/

function mssql_get_all_fields_properties($object){
   $res=mssql_query("select colorder,name,xtype,length,xprec from syscolumns where id = object_id('$object')");
   if($res){
     while(($tmp = mssql_fetch_assoc($res))){
       $ret[$tmp['colorder']] = $tmp;
     }
     $GLOBALS['MSSQL_PROP_'.$object] = $ret;
     return true;
   }
   else return false;
}

/*
<font color="#007f00">
Gets <b>$field</b> properties from <b>$object</b> table/stored procedure in <i>systables</i> and stores
in <b>$GLOBALS['MSSQL_PROP_'.$object]</b><br>
Returns: <b>Array of properties</b> or <b>false</b> if some error occured.
</font>
*/

function mssql_get_field_properties($object,$field)
{
   if(!isset($GLOBALS['MSSQL_PROP_'.$object]) )
     if(!mssql_get_all_fields_properties($object))
       return false;
  
   foreach($GLOBALS['MSSQL_PROP_'.$object] as $prop){
     if($prop['name'] == $field) return $prop;
   }
}

/*
<font color="#007f00">
Gets <b>$field</b> length from <b>$object</b> table/stored procedure in <i>systables</i> and stores
in <b>$GLOBALS['MSSQL_PROP_'.$object]</b><br>
Returns: <b>length</b> or <b>false</b> if some error occured.
</font>
*/
function mssql_get_field_length($object,$field)
{
  if(!isset($GLOBALS['MSSQL_PROP_'.$object]) )
     if(!mssql_get_all_fields_properties($object))
       return false;

   foreach($GLOBALS['MSSQL_PROP_'.$object] as $prop){
     if($prop['name'] == $field) return $prop['length'];
   }
}

/*
<font color="#007f00">
Gets <b>$field</b> xprec from <b>$object</b> table/stored procedure in <i>systables</i> and stores
in <b>$GLOBALS['MSSQL_PROP_'.$object]</b><br>
Returns: <b>length</b> or <b>false</b> if some error occured.
</font>
*/
function mssql_get_field_length($object,$field)
{
  if(!isset($GLOBALS['MSSQL_PROP_'.$object]) )
     if(!mssql_get_all_fields_properties($object))
       return false;

   foreach($GLOBALS['MSSQL_PROP_'.$object] as $prop){
     if($prop['name'] == $field) return $prop['xprec'];
   }
}

/*
<font color="#007f00">
Gets <b>$field</b> xprec from <b>$object</b> table/stored procedure in <i>systables</i> and stores
in <b>$GLOBALS['MSSQL_PROP_'.$object]</b><br>
Returns: <b>length</b> or <b>false</b> if some error occured.
</font>
*/
function mssql_get_field_colorder($object,$field)
{
  if(!isset($GLOBALS['MSSQL_PROP_'.$object]) )
     if(!mssql_get_all_fields_properties($object))
       return false;

   foreach($GLOBALS['MSSQL_PROP_'.$object] as $prop){
     if($prop['name'] == $field) return $prop['colorder'];
   }
}

?>
marco dot carvalho at NOSPAM dot uni-yoga dot org dot br
08-Jun-2004 04:57
<?PHP

/*This functions will help you to get SQLTYPES direct from systypes and you can associate them with PHPSQLTYPES. */

/* <font color="#007f00">Associete PHP types with systypes..xtypes,
   see <b>mssql_get_types()</b> </font>*/

if(!defined('dbMSSQL_Types'))
{
   define('dbMSSqlTypes',1);
   $MSSQL_types[127] = SQLINT4;    /* bigint*/
   $MSSQL_types[104] = SQLBIT;    /* bit*/
   $MSSQL_types[175] = SQLCHAR;    /* char*/
   $MSSQL_types[56]  = SQLINT2;    /* int*/
   $MSSQL_types[52]  = SQLINT2;    /* smallint*/
   $MSSQL_types[35]  = SQLTEXT;    /* text*/
   $MSSQL_types[48]  = SQLINT1;    /* tinyint*/
   $MSSQL_types[167] = SQLVARCHAR; /* varchar*/
   $MSSQL_types[62]  = SQLFLT8;    /* float*/
   $MSSQL_types[173] = SQLVARCHAR; /* binary*/        // Adaptation
   $MSSQL_types[61]  = SQLINT4;    /* datetime*/      // Adaptation
   $MSSQL_types[106] = SQLFLT8;    /* decimal*/        // Adaptation
   $MSSQL_types[34]  = SQLVARCHAR; /* image*/          // Adaptation
   $MSSQL_types[60]  = SQLFLT8;    /* money*/          // Adaptation
   $MSSQL_types[239] = SQLCHAR;    /* nchar*/          // Adaptation
   $MSSQL_types[99]  = SQLTEXT;    /* ntext*/          // Adaptation
   $MSSQL_types[108] = SQLFLT8;    /* numeric*/        // Adaptation
   $MSSQL_types[231] = SQLVARCHAR; /* nvarchar*/      // Adaptation
   $MSSQL_types[59]  = SQLFLT8;    /* real*/          // Adaptation
   $MSSQL_types[58]  = SQLINT4;    /* smalldatetime*/  // Adaptation
   $MSSQL_types[122] = SQLFLT8;    /* smallmoney*/    // Adaptation
   $MSSQL_types[98]  = SQLVARCHAR; /* sql_variant*/    // Adaptation
   $MSSQL_types[189] = SQLINT4;    /* timestamp*/      // Adaptation
   $MSSQL_types[165] = SQLVARCHAR; /* varbinary*/      // Adaptation
}

/* <font color="#007f00">Gets current connection systypes and shows this:

$MSSQL_types[systypes..xtype] = PutPHPSqlTypeHere  // Name_in_systypes //

</font>*/
function mssql_get_types(){
   $res = mssql_query('select name,xtype from systypes');
   echo('<pre><CODE>');
   while(($val = mssql_fetch_assoc($res))){
     echo('$MSSQL_types['.$val['xtype']."]\t= ;\t/* ".$val['name']." */\n");
   }
   print_r(phpinfo(INFO_VARIABLES));
   echo('</CODE></pre>');
}

?>
iqq-pp at extreme dot ro
30-Oct-2003 07:01
php version 4.3.2

Take care when using stored procedures returning multiple results, seems that if the first result is empty, the pointer will be automatically moved to the next result. As in this example:

CREATE PROCEDURE test
AS
SELECT 0 as zero WHERE 0 = 1
SELECT 1 as one
GO

After executing the stored procedure, mssql_num_rows will report one, ignoring the first result.
eliseo at olografix dot org
13-Oct-2003 01:23
After many attempt I resolved the return output of a store procedure on Win2003 box, MSSQL7 and PHP 4.3.
I have problem to process the result from store procedure strCheckUser, and I must to set a R variable, that must be returned from the last select operation (Select @R as R) see below.

----------------------------
/*
Store procedure to CheckUser Exist
*/
CREATE PROC strCheckUser 
(
@AccountLO varchar(20) ,
@PasswordLO varchar(20)
)
AS
BEGIN
DECLARE @R INT
IF (SELECT count(*) as count FROM tbl_users WHERE AccountLO = @AccountLO and PasswordLO=@PasswordLO
) = 0
SET @R = '0'
END

BEGIN
IF (SELECT count(*) as count FROM tbl_users WHERE AccountLO = @AccountLO  and PasswordLO=@PasswordLO
) = 1
SET @R = '1'

END

Select @R as R

GO
-------------------------

This is the php page
<?

.....
.....
$AccountLO="myuser";
$PassowrdLO="mypass";

$result=mssql_query("strCheckUser ".$AccountLO.", ".$PasswordLO."");
//echo gettype($result);
$arr = mssql_fetch_assoc($result);       
echo $arr["R"];

......
......
?>

No $arr["R"] print 1 if the user exist and 0 if no exist

Thanks to duarte at uma dot pt for the suggestion
Bye eliseo@olografix.org
gstratfordATdas.ca
07-Aug-2003 04:25
The easiest way to use a stored procedure is:

$Result = mssql_query("StoredProcedureName Var1, Var2, Var3...");

$Result is then just like any other result set. You can get the output parameters by:

$arr = mssql_fetch_row($Result);

$OutputParam1 = $arr[0];
$OutputParam2 = $arr[1];
brian_caughlin at hotmail dot com
07-Aug-2003 02:04
Regarding Output Parameters and RETVAL: A change that appears to have begun around 4.3.

According to the documentation and previously posted comments, if a stored procedure returns only one Recordset, you could retrieve the RETVAL and Output Params right away.  THIS IS NO LONGER THE CASE.  Beginning around 4.3, you must always use the mssql_next_result() function if any recordset is returned at all.

If you consider the example posted below by fjortizATcomunetDOTes on 26-Dec-2001...

[...]

// Execute the Stored Proc
$result=mssql_execute($stmt);

// Get the recordset
$arr=mssql_fetch_row($result);
print ("Answer: " . $arr[0] . "
" );

// NEW for 4.3: Switch to the next Recordset
// Since there was only one recordset, it will return false...
mssql_next_result($result);

// And now RETVAL and Output Params are accessible...
print ("RETVAL = $val ; intval = $ival ; floatval = $fval ; string = $sval");

[...]

There is also another way, and that is to use a new optional skip parameter on the execute.

mssql_execute($stmt, true);

This appears to ignore any recordsets, allowing you to get at the retval and output parameters immediately.

For more information, please see Bug #21089.
arnarb at oddi dot is
27-Apr-2003 11:13
The above comment is probably outdated by now. I've been using mssql_init, mssql_bind and mssql_execute with good results on my Linux box, both with MSSQL 7.0 and 2000.

My webserver is running Apache 1.3.24 with PHP 4.3.2-RC1 (this also works in 4.3.1) linked against an out-of-the-box build of FreeTDS 0.61.

However, it should be mentioned here that the latest release (4.3.2-RC1) contains some bugs in the mssql_bind code. For one thing, the size of the buffer is not set correctly so unpatched, php coredumps when binding types of fixed length (SQLINT* and such).
When binding types of variable length, such as strings (SQLVARCHAR and SQLCHAR for example), the _current_ string length is used to choose the buffer size instead of using the optional parameter $maxlen. To make this clearer: if you have a stored procedure that simply sets one of its output parameters to "Fixed string", this happens:

$stringvariable = "";
mssql_bind($statement, "@outputparam", $stringvariable, SQLVARCHAR);
mssql_execute($statement);
// $stringvariable now contains ""

and

$stringvariable = "xxxx";
mssql_bind($statement, "@outputparam", $stringvariable, SQLVARCHAR);
mssql_execute($statement);
// $stringvariable now contains "Fixe"

so the quick ugly hack is to initialize $stringvariable to a string longer than the one you expect to be returned.

The tedious but nicer hack is to find the line
   datalen=Z_STRLEN_PP(var);
in the function PHP_FUNCTION(mssql_bind) in php_mssql.c (around line 1970) and change it to:
   datalen=maxlen;
and recompile :o) (note that this fails though when maxlen isn't specified in the PHP script)
duarte at uma dot pt
12-Apr-2002 04:22
Solution to execute MSSQL Stored Procedures in PHP Linux :

The functions mssql_* in PHP Win are no equal to mssql_* in PHP Linux

In Linux the functions for mssql_*  in PHP are just ALIAS of the sybase functions. You can see that if you consult the PHP Source files or go to PHP CVS.

And because of this, some functions are available in PHP Win an not in PHP Linux. Like mssql_execute(), mssql_init(), ...

The solution to work with MS-SQL stored procedures in PHP Linux Server is to:
1- Execute the stored procedure with: "mssql_query( "EXECUTE stored_procedure_1(@a,@b) " ,$vi_connect )
2- And in the stored procedure you cant get the returned parameters in the normal way, you must do "Select @a as a, @b as b " and the end of the code of your stored procedure.
3- After, you can use mssql_fetch_array to get the result of the stored procedure.

This is working in project with
Database Server:
   Windows 2000 + MS-SQL Server 7.0
Web Server
   SUSe Linux 7.0 + Apache 1.3.19 + PHP 4.1.6 ( with freetds-0.53  )

I hope this helps someone !
fjortizATcomunetDOTes
26-Dec-2001 10:01
After initializing a stored procedure
with mssql_init, and binding all the
parameters (and return value if needed)
with mssql_bind, you can execute the
statement with mssql_execute.

Parameters:
- stmt: statement resource obtained with
mssql_init.

From here, you can use any of the other
mssql_* functions to retrieve the
recordsets as if you had called
mssql_query. Any T-SQL error will also
be reported in the same way. The
variables passed by reference for OUTPUT
and RETVAL parameters will be filled
with the right values.

Now, an example:

if we have this procedure:

CREATE PROCEDURE [procedure]
(
   @sval varchar(50) OUTPUT,
   @intval int OUTPUT,
   @floatval decimal(6,4) OUTPUT
) AS

if @intval is null
   select '@intval is null' as answer
else   
   select '@intval is NOT null' as answer

set @sval='Hello ' + @sval
set @intval=@intval+1
set @floatval=@floatval+1

return 10

We can use this PHP code:

<?php

$conn=mssql_connect("myhost","user","pwd");

if ($conn) {
   mssql_select_db("mydb",$conn);
  
   $stmt=mssql_init("procedure",$conn);
   mssql_bind($stmt,"RETVAL",&$val,SQLINT4);

   $ival=11;
   $fval=2.1416;
   $sval="Frank";
  
   mssql_bind($stmt,"@sval",&$sval,SQLVARCHAR,TRUE);   
   mssql_bind($stmt,"@intval",&$ival,SQLINT4,TRUE);
   mssql_bind($stmt,"@floatval",&$fval,SQLFLT8,TRUE);
  
   $result=mssql_execute($stmt);

   $arr=mssql_fetch_row($result);
   print ("Answer: " . $arr[0] . "<br>" );
   print ("RETVAL = $val ; intval = $ival ; floatval = $fval ; string = $sval");
  
   mssql_close($conn);
}
else print("ooops!");
?>


Hope it helps. Good luck!
add a note add a note


后退起点前进
mssql_data_seek上一级mssql_fetch_array