[freetds] freetds 0.61 - dbrpcsend looping forever with stored proc with ou tput params

Thomas Melzer (ToMeSoft) tmelzer at tomesoft.de
Tue May 13 12:14:36 EDT 2003


I'm having a problem with freetds 0.61 (and also with several snapshots from
cvs)
using php 4.3.1 mssql extension on freebsd 4.7p10.
Im calling a stored procedure with output parameters and return values from
php.
The php script keeps running forever. 
Tracing down the problem seem to show that the call is running the 
loop withing dbrpcsend forever. 
The same php script and stored procedure accessed from a windows 
machine with ms db-library runs fine.
Has anybody expected a similar behaviour an got a solution ?

Here my environment:
On my webserver:
FreeBSD 4.7P10
Apache 1.3.27
mod_php 4.3.1
FreeTDS 0.61

on my sqlserver:
Windows 2000 SP3
MSSQL 7.0 SP4

Here the Stored procedure:

CREATE PROCEDURE sb_InsertMerchant
	@merchantid INTEGER OUTPUT,
	@serviceid INTEGER,
	@company VARCHAR(100),
	@name VARCHAR(120),
	@owner VARCHAR(120),
	@owner2 VARCHAR(120),
	@street VARCHAR(160),
	@zip VARCHAR(5),
	@city VARCHAR(100),
	@phone VARCHAR(40),
	@fax VARCHAR(40),
	@email VARCHAR(130),
	@contact VARCHAR(200),
	@waranty VARCHAR(200),
	@tt TINYINT,
	@brand_a TINYINT = 0,
	@brand_b TINYINT = 0,
	@brand_c TINYINT = 0
AS
	DECLARE @password VARCHAR(50)
	DECLARE @fetch_stat INTEGER

	BEGIN TRANSACTION
	
	SELECT @merchantid = 0
	
	IF @serviceid = 0 
		BEGIN
			INSERT INTO clients(custno, zip, active, regdate,
custgroup)
			VALUES ('ABCDEFGH', @zip, 0, GETDATE(), NULL)
			
			SELECT @merchantid = @@IDENTITY
			
			SET @password = LEFT(@city, 1) + CAST(@merchantid +
100000 AS VARCHAR(10))

			UPDATE clients
			SET custpassword = @password
			WHERE id = @merchantid
		END
	ELSE
		BEGIN
			DECLARE passwd_cursor CURSOR
				LOCAL
				FAST_FORWARD
				FOR
					SELECT custpassword
					FROM clients
					WHERE id = @serviceid
					
			OPEN passwd_cursor
	
			FETCH NEXT 
			FROM passwd_cursor
			INTO @password
			
			SET @fetch_stat = @@FETCH_STATUS
			
			CLOSE passwd_cursor
			DEALLOCATE passwd_cursor
			
			IF @fetch_stat = 0
				BEGIN
					SELECT @merchantid = @serviceid
				END
			ELSE
				BEGIN
					ROLLBACK
					RETURN(1)
				END
		END
		
	IF @merchantid <> 0
		BEGIN
			INSERT INTO sbmerchant(
				id,company,street,zip,city,phone,
				fax,email,contact,waranty,
				custpassword,tt,name,owner,owner2
			)
			VALUES (
	
@merchantid, at company, at street, at zip, at city, at phone,
				@fax, at email, at contact, at waranty,
				@password, at tt, at name, at owner, at owner2
			)
			
			IF @brand_a <> 0
				INSERT  INTO sbmerchant_brand(merchantid,
brand)
				VALUES (@merchantid, 'A')
				
			IF @brand_b <> 0
				INSERT  INTO sbmerchant_brand(merchantid,
brand)
				VALUES (@merchantid, 'B')

			IF @brand_c <> 0
				INSERT  INTO sbmerchant_brand(merchantid,
brand)
				VALUES (@merchantid, 'C')
								
			COMMIT
			
			RETURN(0)
		END
	ELSE
		BEGIN
			ROLLBACK
			RETURN(2)
		END


here a snippet of my php code

....

			$spcmd = mssql_init("sb_InsertMerchant", $db) or die
( "db stored proc init - " . mssql_get_last_message() );
			
			mssql_bind($spcmd, "RETVAL", $cmdresult, SQLINT4);
			mssql_bind($spcmd, "@merchantid", $merchantid,
SQLINT4, true);
			mssql_bind($spcmd, "@serviceid", $serviceid,
SQLINT4);
			mssql_bind($spcmd, "@company", $company, SQLVARCHAR,
false, false, 100);
			mssql_bind($spcmd, "@name", $name, SQLVARCHAR,
false, false, 120);
			mssql_bind($spcmd, "@owner", $owner, SQLVARCHAR,
false, false, 120);
			mssql_bind($spcmd, "@owner2", $owner2, SQLVARCHAR,
false, false, 120);
			mssql_bind($spcmd, "@street", $street, SQLVARCHAR,
false, false, 160);
			mssql_bind($spcmd, "@zip", $zip, SQLVARCHAR, false,
false, 5);
			mssql_bind($spcmd, "@city", $city, SQLVARCHAR,
false, false, 100);
			mssql_bind($spcmd, "@phone", $phone, SQLVARCHAR,
false, false, 40);
			mssql_bind($spcmd, "@fax", $fax, SQLVARCHAR, false,
false, 40);
			mssql_bind($spcmd, "@email", $email, SQLVARCHAR,
false, false, 40);
			mssql_bind($spcmd, "@contact", $contact, SQLVARCHAR,
false, false, 200);
			mssql_bind($spcmd, "@waranty", $waranty, SQLVARCHAR,
false, false, 200);
			mssql_bind($spcmd, "@tt", $tt, SQLINT1);
			mssql_bind($spcmd, "@brand_a", $brand_a, SQLINT1);
			mssql_bind($spcmd, "@brand_b", $brand_b, SQLINT1);
			
			mssql_execute($spcmd) or die ( "db stored proc
execute - " . mssql_get_last_message() );

...

Thanks in advance 
-- Thomas Melzer




More information about the FreeTDS mailing list