Executing stored procedure with IN and OUT parameters in PHP

Silencer001
Silencer001 used Ask the Experts™
on
I want to execute a stored procedure into my PHP code, this stored procedure has an IN and an OUT parameter. The stored procedure is this:
 
USE [phl_pmx]
GO

DECLARE	@return_value int

EXEC	@return_value = [dbo].[PMX_SP_RecreateSynonymsOfSourceDb]
		@sourceDb = phl

SELECT	'Return Value' = @return_value

GO

Open in new window


And I already wrote the following code, but it keeps giving errors that he can't execute it, or he just won't show a thing.

Does anyone know how to correct the code and how I can let PHP show me the output parameter?
 
$link = mssql_connect('server', 'sa', 'pass');

$id = 'phl'
mssql_select_db($id,$link);

$proc = mssql_init("PMX_SP_RecreateSynonymsOfSourceDb");
mssql_bind($proc, "@sourceDb", $id, SQLVARCHAR, FALSE);
mssql_execute($proc);
unset($proc);
mssql_close($link);

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Greg AlexanderLead Developer

Commented:
Looks like you are missing a semi-colon:

<?
$link = mssql_connect('server', 'sa', 'pass');  
  
$id = 'phl';
mssql_select_db($id,$link);  
  
$proc = mssql_init("PMX_SP_RecreateSynonymsOfSourceDb");  
mssql_bind($proc, "@sourceDb", $id, SQLVARCHAR, FALSE);  
mssql_execute($proc);  
unset($proc);  
mssql_close($link);
?>

Open in new window

Author

Commented:
Hmm yeah, thanks, oké now it doesn't give me an error, but how can I show the output parameter? Because I can't see if the stored procedure is executed.

Author

Commented:
This is the code I got now:
 
$link = mssql_connect('server', 'sa', 'password');
$id ="phl";

mssql_select_db($id,$link);

$proc = mssql_init("PMX_SP_RecreateSynonymsOfSourceDb",$link);
mssql_bind($proc, "@sourceDb", $id, SQLVARCHAR, FALSE);
$result = mssql_execute($proc);
unset($proc);
$arr = mssql_fetch_assoc($result);
$mid = $result['return_value'];
echo $mid;

Open in new window


And this is the error I'm getting:
 
Warning: mssql_execute(): stored procedure execution failed in /var/www/mssql/management/StoredProcedure.php on line 14 Warning: mssql_fetch_assoc() expects parameter 1 to be resource, boolean given in /var/www/mssql/management/StoredProcedure.php on line 16

Open in new window

How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Hi here is a class that I use , it returns a array,

you would do something like

$a = new TableOutput();
$a->getTables ("PMX_SP_RecreateSynonymsOfSourceDb","ph1");




<?php


//include_once 'DBC.php';

class TableOutput {

    function getTables($procname, $parameter) {
        //replacement method
        $stmt = null;
        $data = null;
        $vars = null;
        $num = null;
        // $con = DBC::getInstance();
        $num = count($vars);
       
       // $con = DBC::getInstance();
      $con = mssql_connect('server', 'sa', 'password');

        $this->setStmt(mssql_init($procname, $con));
        mssql_bind($this->getStmt(), '@sourceDb', $parameter, SQLVARCHAR, false, false);
   
        if ($rtn != 0) {
            echo ("Errors happened when executing the stored procedure");
        }
        $exec = mssql_execute($this->getStmt());
        $data = array();
        $i = 0;
        while ($row = mssql_fetch_assoc($exec)) {
            $data[++$i] = $row;
        }

        unset($con);
        unset($stmt);
        return $data;
    }

    function setStmt($a_stmt) {
        $this->stmt = $a_stmt;
    }

    function getStmt() {
        return $this->stmt;
    }

}
?>

Open in new window

Author

Commented:
It seemed like it would work, because it loaded for a long time, but finally loaded i got following error:
 
Notice: Undefined variable: rtn in /var/www/mssql/management/DBC.php on line 23 Warning: mssql_execute(): stored procedure execution failed in /var/www/mssql/management/DBC.php on line 26 Warning: mssql_fetch_assoc() expects parameter 1 to be resource, boolean given in /var/www/mssql/management/DBC.php on line 29

Open in new window


the error from rtn is because it's not declared somewhere, but those other errors I really don't know why they keep appering.

It seems good, if I look at the stored procedure I pasted over here, but somewhere it won't work.
Try SQLINT2 instead of SQLVARCHAR

also you need

mssql_select_db("your database name",$con);

 on my line 19 above
class TableOutput {

    function getTables($procname, $parameter) {
 
        $stmt = null;
        $data = null;
        $vars = null;
        $num = null;
         $con = mssql_connect('server', 'sa', 'password');
if
        (!$con) {
            die('Could not connect: ' . mssql_error());
        }

mssql_select_db("your database name",$con);  // this needs changing

        $this->setStmt(mssql_init($procname, $con));
        mssql_bind($this->getStmt(), '@sourceDb', $parameter, SQLINT2, false, false);
   
        if ($rtn != 0) {
            echo ("Errors happened when executing the stored procedure");
        }
        $exec = mssql_execute($this->getStmt());
        $data = array();
        $i = 0;
        while ($row = mssql_fetch_assoc($exec)) {
            $data[++$i] = $row;
        }

        unset($con);
        unset($stmt);
        return $data;
    }

    function setStmt($a_stmt) {
        $this->stmt = $a_stmt;
    }

    function getStmt() {
        return $this->stmt;
    }

}
?>

Open in new window

Author

Commented:
Thanks for the quick respone darren-w. Unfortunatly it still gives the following error.
 
Warning: mssql_execute(): stored procedure execution failed in /var/www/mssql/management/DBC.php on line 24 Warning: mssql_fetch_assoc() expects parameter 1 to be resource, boolean given in /var/www/mssql/management/DBC.php on line 27

Open in new window


I'm going to try to figure it out, why it gives that error when I'm home from work now. I'm already been trying to do this for a whole day. So it's frustrating me, that a single stored procedure can be such a pain in the ass.
mssql_bind($this->getStmt(), '@sourceDb', $parameter, SQLVARCHAR, false, false);

Open in new window


should be

 
mssql_bind($this->getStmt(), "@sourceDb", $parameter, SQLVARCHAR, false, false);

Open in new window


thanks for your help man. Now it works, points are rewarded

Author

Commented:
Works perfect with a great function

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial