RE: mssql_bind problem....

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Fetching the result as a array or object gives the data (a row returned from
the database), 
but not the Return value of the stored procedure.

The stored procedure is below if it helps....

----- Stored Procedure ------
CREATE PROCEDURE sp_StaffDetail 
	@ShortName	varchar(30)
AS

SET nocount ON

SELECT TblStaff.StaffId, TblStaff.ShortName AS StaffSN, TblStaff.FullName AS
StaffFN, TblStaff.Email , TblStaff.RetroDays
   FROM TblStaff
WHERE TblStaff.ShortName = @ShortName
      AND TblStaff.Online = 'Y'

IF @@Error <> 0 
	Return 0		-- unspecified error
ELSE
	Return 99		-- success

GO


-----Original Message-----
From: colbey@dreamwerx.net [mailto:colbey@dreamwerx.net] 
Sent: 21 July 2003 14:43
To: A.J.Masterton
Cc: 'php-db@lists.php.net'
Subject: RE:  mssql_bind problem....



I found this old piece of code.. not sure if it will still work:

 function calcExc($baseid, $targetid, $amount){
   $sql = "sp_curr_convert $amount, $baseid, $targetid";
   $res = sybase_query($sql);
   $obj = sybase_fetch_object($res);
   return $obj->AMOUNT;
  }

I don't remember what the code was inside the stored procedure.. but I'm
assuming it must have defined AMOUNT .. calced and returned it..

You might also try using mssql_fetch_array .. and see what's returned in 0,1
positions in it..


On Mon, 21 Jul 2003, A.J.Masterton wrote:

> It does work like that, (if you change mssql_execute to mssql_query), 
> but the developer wants the return value from the stored procedure.
>
> >From what I can tell there is no way to get it by executing stored
> procedures
> with mssql_query.
>
>
> -----Original Message-----
> From: colbey@dreamwerx.net [mailto:colbey@dreamwerx.net]
> Sent: 21 July 2003 13:51
> To: A.J.Masterton
> Cc: 'php-db@lists.php.net'
> Subject: Re:  mssql_bind problem....
>
>
>
> I used to use MSSQL..  I've always called the stored procedures like 
> you would call any method/function..  Have you tried just:
>
> > // ****** Query for user details ******* //
> > $query = "sp_StaffDetail $var1, $var2";
> > $result = mssql_execute($query);
>
>
>
>
> On Mon, 21 Jul 2003, A.J.Masterton wrote:
>
> > DB server: MSSQL 2000
> > System: RedHat Linux 8.0
> > Driver FreeTDS: 0.61
> > PHP_rev: 4.3.2 (also tried on 4.3.1 gives same problem) Apache 
> > 2.0.46
> >
> > I'm having a problem with mssql_bind under this configuration it 
> > appears not to be binding variables specified to the stored 
> > procedure and I was wondering If anyone has come across this before.
> >
> > I have noted from a post in the user manual from the 27th April '03
> > (http://uk.php.net/manual/en/function.mssql-execute.php) that there 
> > was a bug in 4.3.2-RC1 for the mssql_bind statement I was wondering 
> > If this could also be causing a problem with the binding to stored 
> > procedures.
> >
> > I've looked on the bug list and I don't see anything referencing 
> > this problem.
> >
> > The code we are using is:
> >
> > $StaffSN = "crl26";
> >
> > // ****** Init return value ******* //
> > $ret = 1;
> >
> > // ****** Set database vars ******* //
> > $myServer = "sqlserver";
> > $myUser = "blah";
> > $myPass = "xxxxxx";
> > $myDB = "InTime";
> > // ****** Link to database ******* //
> > $serverLink = mssql_connect($myServer, $myUser, $myPass) or 
> > die("Could not connect to SQL Server on $myServer");
> >
> > $db = mssql_select_db($myDB, $serverLink) or die("Couldn't open 
> > database $myDB");
> >
> > // ****** Query for user details ******* //
> > $query = mssql_init("sp_StaffDetail", $serverLink); 
> > mssql_bind($query, "@ShortName", $StaffSN, SQLVARCHAR); 
> > mssql_bind($query, "RETVAL", $ret, SQLINT4); $result = 
> > mssql_execute($query);
> >
> > The execute gives the error:
> > Warning: mssql_execute(): message: Procedure 'sp_StaffDetail' 
> > expects parameter '@ShortName', which was not supplied. (severity 
> > 16) in /home/shares/staffweb/index2.php on line 46
> >
> > Warning: mssql_execute(): stored procedure execution failed in 
> > /home/shares/staffweb/index2.php on line 4
> >
> > I have turned on Debug output on FreeTDS and it seems to show the 
> > stored procedure being called without any bound variables (as 
> > indicated by the error from PHP)
> >
> > I'm not quite sure if it is PHP not binding the variables or FreeTDS 
> > ignoring them, but by the post in the PHP manual
> > (http://uk.php.net/manual/en/function.mssql-execute.php) this was 
> > working previously with FreeTDS 0.61 so I can only assume it's 
> > either PHP, a coding error by the developer, or a set-up problem 
> > with FreeTDS.
> >
> > Does anyone have any ideas?
> >
> > Many thanks
> >
> > Andrew Masterton
> >
> > --
> > PHP Database Mailing List (http://www.php.net/)
> > To unsubscribe, visit: http://www.php.net/unsub.php
> >
>

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux