asked on

Executing stored procedure with IN and OUT parameters in PHP

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

Greg Alexander

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

Silencer001

ASKER

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.

Silencer001

ASKER

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

darren-w-

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

Silencer001

ASKER

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.

darren-w-

Try SQLINT2 instead of SQLVARCHAR

also you need

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

on my line 19 above

ASKER CERTIFIED SOLUTION

darren-w-

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Silencer001

ASKER

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.

SOLUTION

Silencer001

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Silencer001

ASKER

Works perfect with a great function