Delicious Bookmark this on Delicious Share on Facebook SlashdotSlashdot It! Digg! Digg



PHP : Function Reference : Microsoft SQL Server Functions : mssql_execute

mssql_execute

Executes a stored procedure on a MS SQL server database (PHP 4 >= 4.0.7, PHP 5, PECL odbtp:1.1.1-1.1.4)
mixed mssql_execute ( resource stmt [, bool skip_results] )

Examples ( Source code ) » mssql_execute

<?php
// Create a new statement
$stmt = mssql_init('NewBlogEntry');

// Some data strings
$title = 'Test of blogging system';
$content = 'If you can read this, then the new system is compatible with MSSQL';

// Bind values
mssql_bind($stmt, '@author',    'Felipe Pena',  SQLVARCHAR,     false,  false,     60);
mssql_bind($stmt, '@date',      '08/10/2008',   SQLVARCHAR,     false,  false,     20);
mssql_bind($stmt, '@title',     $title,         SQLVARCHAR,     false,  false,     60);
mssql_bind($stmt, '@content',   $content,       SQLTEXT);

// Execute the statement
mssql_execute($stmt);

// And we can free it like so:
mssql_free_statement($stmt);
?>

Code Examples / Notes » mssql_execute

manda krishna srikanth
While using stored procedures on SQL EXPRESS (and perhaps on SQL Server), you have to specify the column names in SELECT, instead of asterisk (*). Or else you will get some big Unicode error.
That is, instead of "select * from table",
use "select col1, col2 from table".
One more important thing, Before using mssql_execute, you MUST AND SHOULD use mssql_init. mssql_init will generate the MS Sql statement resource, which will be taken as input by mssql_execute. Here is an example,
<?php
if($conn = mssql_connect('localhost\SQLEXPRESS', 'krishna', 'srikanth')) echo 'Connected to SQLEXPRESS';
if(mssql_select_db("Northwind",$conn)) echo 'Selected DB: Northwind
';
$sql_statement =  mssql_init("[Ten Most Expensive Products]", $conn);
$result=mssql_execute($sql_statement);
while ($row = mssql_fetch_assoc($result))
print_r($row);
?>

sql dot user
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.

anton schattenfeld
To get info about table structure you can use such a query:
SELECT
 column_name,
 data_type,
 character_maximum_length,
 numeric_precision,
 column_default,
 is_nullable
FROM
 information_schema.tables t
 INNER JOIN
 information_schema.columns c
 ON
   t.table_catalog = c.table_catalog AND
   t.table_schema = c.table_schema AND
   t.table_name = c.table_name
WHERE    
 (c.table_name = 'TABLE_NAME')

gstratfordatdas.ca
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];

mpoletto
The constant SQLINT4 is not working with datetime. Try using SQLVARCHAR.

brian_caughlin
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.

iqq-pp
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.

stuhood
If you need to get Output params from your stored procedure, make sure to use FreeTDS > 0.6.4... it has a bug that prevents some Output params from being set.

mark dot vanrossum
I was pulling my hair out getting the error:
 "stored procedure execution failed"
when trying to run mssql_execute but you could run:
 $results = mssql_query('sp_test');
fine.
I was connecting using the string:
 $dbserver="xxx.xxx.xxx.xxx:1433";
 $cn = mssql_connect($dbserver, $dbuser, $dbpass);
where xxx is the IP address.
It seems that this doesn't work, you need to do the following:
edit your freetds.conf file and add the connection in here, eg:
[YourServer]
       host =xxx.xxx.xxx.xxx
       port = 1433
       tds version = 8.0
Then try and connect as:
$cn = mssql_connect('YourServer', $dbuser, $dbpass);
And it should work.  No idea why it doesn't work before, took me hours to find this out!

eliseo
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

fjortizatcomunetdotes
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] . "
" );
print ("RETVAL = $val ; intval = $ival ; floatval = $fval ; string = $sval");

mssql_close($conn);
}
else print("ooops!");
?>
Hope it helps. Good luck!

marco dot carvalho
<?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>');
}
?>

Change Language


Follow Navioo On Twitter
mssql_bind
mssql_close
mssql_connect
mssql_data_seek
mssql_execute
mssql_fetch_array
mssql_fetch_assoc
mssql_fetch_batch
mssql_fetch_field
mssql_fetch_object
mssql_fetch_row
mssql_field_length
mssql_field_name
mssql_field_seek
mssql_field_type
mssql_free_result
mssql_free_statement
mssql_get_last_message
mssql_guid_string
mssql_init
mssql_min_error_severity
mssql_min_message_severity
mssql_next_result
mssql_num_fields
mssql_num_rows
mssql_pconnect
mssql_query
mssql_result
mssql_rows_affected
mssql_select_db
Terms of Service Privacy Policy Sitemap

©2024 -EPSILONSYS SOFTWARE inc

Other EPSILONSYS SOFTWARE Web Sites : Programming Resources | News Headline | Free local classifieds

eXTReMe Tracker