I've managed to fix it from erroring by specifying the full mssql_bind command, I was looking at one of the comments under the mssql_bind that says if you Are sending a varchar you have to specify it's size so: mssql_bind($query, "@ShortName", $StaffSN, SQLVARCHAR, FALSE, FALSE, 30); Works but, mssql_bind($query, "@ShortName", $StaffSN, SQLVARCHAR, FALSE); Doesn't work. But the second bind: mssql_bind($query, "RETVAL", $ret, SQLINT4, TRUE, FALSE); Doesn't give the correct return value, $ret is set as 1 earlier on in the code and is still 1 after the mssql_execute, I'm sure I'll work it out Eventually, thanks for the help. Andrew Masterton -----Original Message----- From: A.J.Masterton [mailto:A.J.Masterton@open.ac.uk] Sent: 21 July 2003 14:57 To: colbey; A.J.Masterton Cc: php-db Subject: RE: mssql_bind problem.... 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
