[freetds] Output parameters

Gunther Van Butsele gvb at velleman.be
Wed Apr 23 11:02:48 EDT 2008


I thought about that, but the stored procedure doesn't return any data,
and passing the result from mssql_execute to mssql_fetch_array throws an
error (invalid result id, the normal stuff).

This is the stored procedure:

CREATE PROCEDURE sp_web_WebsiteEnabled at WebsiteID INT, @Enabled TINYINT
OUTPUT AS
	SET @Enabled = CASE WHEN EXISTS(SELECT * 
                                  FROM WebsiteDivision wd (NOLOCK) 
                                  WHERE wd.Website_ID = @WebsiteID 
                                    AND wd.SalesordersAllowed = 1)
                 THEN 1
                 ELSE 0
                 END
GO

Note that the same *does* work on another live server, but we haven't
yet figured out what the difference between the two servers is that
might cause output parameters to not get filled up.

Profiler shows a query where the result is returned correctly (SELECT
@P1, where @P1 represents @Enabled passed to the stored procedure).
Somehow the value from SELECT @P1 doesn't make it back to my PHP
variable $canLogin.

Kind regards,
Gunther

 

-----Original Message-----
From: freetds-bounces at lists.ibiblio.org
[mailto:freetds-bounces at lists.ibiblio.org] On Behalf Of ZIGLIO,
Frediano, VF-IT
Sent: woensdag 23 april 2008 16:38
To: FreeTDS Development Group
Subject: Re: [freetds] Output parameters

> 
> Hi,
>  
> I'm having a problem getting my output parameters returned in PHP from

> an sql server stored procedure. This is the code used, it is fairly
> straightforward:
>  
> $canLogin = 666;
> $websiteID = 2;
> 
> $sp = mssql_init('sp_web_WebsiteEnabled');
> mssql_bind($sp, '@WebsiteID', $websiteID, SQLINT4, false); // INT
> mssql_bind($sp, '@Enabled',   $canLogin,  SQLINT1, true);  // TINYINT
> (OUTPUT)
> mssql_execute($sp);
> 
> // $canLogin is still 666
> 
> 
> I'm using freetds 0.64 with PHP 5.2.0 and Microsoft SQL Server 2000. 
> My freetds.conf file has tds version 8.0 (7.0 doesn't work either). 
> The apache server is running on freeBSD.
> 
> Do you need to compile with special options to get output parameters 
> to work for SQL Server 2000? I think RETVAL is also not working but 
> I'd need to test to make sure.
> 
> What could be the problem? The PHP version? FreeTDS setup? SQL Server 
> version?
> 
> Kind regards,
> Gunther

Are you sure store does not return any output?? mssql return output
parameters after all data (rows, errors and so on) are sent. For
instance it can happen that a simple select or an insert return some
token that delays output parameters.

freddy77
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds



More information about the FreeTDS mailing list